Query to get the front end URL from back-end
SELECT home_url FROM icx_parameters; http://hostname.domainname.com:8000/OA_HTML/AppsLogin
Posts Tagged ‘Oracle Application’
Query To Get The Front End URL From Backend
February 6th, 2022, posted in Oracle EBS Application, Oracle QueriesWorkflow Notification Mailer Setup in Oracle Apps R12/12i
May 16th, 2021, posted in Oracle EBS ApplicationOn popular demand from readers, I am going to cover Workflow Notification Mailer in Oracle Applications R12/12i.
Workflow notification mailer setup in R12 is similar to 11i ( In both release 11i (OWF.H and higher in 11i) & R12 are Java Mailer)
* Previous version of Notification Mailer in 11i was based on C also called as C Mailer
Things To Note :
1. You use Oracle Application Manager (OAM) to configure Workflow Notification Mailer.
2. There are two kind of Notification (Outbound & Inbound) in Workflow Mailer
3. For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.
4. For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.
5. Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/FNDC*.txt
6. Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)
7. If you don’t wish to send mail notification to end user (from Dev/Test instance) then configure Test Address in configuration screen.
Step to configure Workflow Notification Mailer :
1. Login to Apps R12 with System Administrator Reponsibility
2. under Workflow : Oracle Applications Manager click on Workflow Manager
If this is first time you are configuring Workflow Notification Mailer in Oracle Apps R12/12i you will see Notification Mailers as unavailable as shown in screenshot
Click on Notification Mailers
In next screen (as shown below, click on Edit Button)
Here you have option to select Inbound notification setup or Just outgoing Notification Setup.
Provide SMTP Server Name (ensure that CM node should be able to connect to SMTP Server or SMTP Relay)
Uncheck Inbound Processing (from above screen), if you don’t wish to configure Inbound Notification Mailer.
If you wish to configure Inbound Notification as well then ensure IMAP Server should be configured with a valid user (create Inbox, Processed & Discard folder for this User)
Click on Apply button to finish configuration, at this stage Notification Mailer will test SMTP Server & IMAP Server connectivity.
For Advanced setup, click on Advanced at top right of configuration screen.
Metalink Notes for Notification Mailer
1. 453137.1 Oracle Workflow Best Practices Release 12 and Release 11i
2. 274764.1 Oracle Workflow Cartridge Workflow Java Mailer Setup Test
3. 433359.1 Tracking Workflow Notification Event Messages
4. 456921.1 Queries Related to Alert and Mailer Integration Post RUP4
5. 454706.1 How to Stop mails from Workflow Notification Mailer
Change Allowed In Items Primary UOM in Oracle EBS R12.2.9 And Higher Versions
December 1st, 2020, posted in Oracle EBS ApplicationAllowed to change Item’s Primary UOM in Oracle EBS R12.2.9 and higher versions
Prior to Oracle EBS R12.2.9, Item’s Primary Unit Of Measure(UOM) can be selected only at the time of item creation and cannot be updated once the item is saved.
There is a new feature added in Oracle EBS 12.2.9 latest version, where it is allowed change item’s primary UOM after the item is created When no transactions exist like material transactions, Sales Orders, Purchase Orders, Work Orders, Onhand etc.,
Item’s UOM won’t be updated if there are transactions or related records exists. This is to Maintain data Integrity and avoid corruption.
- Creating “VM TEST” master item with primary uom as Each.
2. Extending “VM TEST” item to M1 inventory org.
3. Query “VM TEST” item in M1 inventory org.
Initially the item is created with primary UOM as “Each”. This item is newly created and there are no transactions exist.
4. Change primary uom from EACH to EA in the master item form ( primary UOM is master controlled item attribute) -> A concurrent request will be submitted when UOM is changed and saved.
5. It runs “EGO Spreadsheet Java Concurrent Program” process and completes normal when the UOM is changed.
Note: The concurrent program will have error like below if there are any transactions exists.
MESSAGE NAME: INV_UOM_CANNOT_UPDATE
ERROR MESSAGE : You cannot update UOM field when the inventory transactions exist in current or one of child orgs.
6. Re-Query “VM TEST” in the organization items form for M1 inventory org. You can see that primary UOM is changed to EA
Check Patches Applied in Oracle Database
October 12th, 2020, posted in Oracle Queries
How to Check Latest Patch Applied To The Database
1. Listing Applied Patches
To print a summary of installed patches, you can can use opatch lspatches command.
[oracle@den03adm03 admin]$ $ORACLE_HOME/OPatch/opatch lspatches
30503372;OJVM PATCH SET UPDATE 11.2.0.4.200114
29938455;OCW Patch Set Update : 11.2.0.4.191015 (29938455)
30310975;DATABASE PATCH FOR EXADATA (Jan 2020 - 11.2.0.4.200114) : (30310975)
2. Using opatch lsinventory
[oracle@orahowdb ~]$ $ORACLE_HOME/OPatch/opatch lsinventory|grep "Patch description"
Patch description: "Database PSU 12.1.0.2.190716, Oracle JavaVM Component (JUL2019)"
Patch description: "Database Patch Set Update : 12.1.0.2.190716 (29494060)"
3. Get detailed info Using opatch lsinventory
[oracle@orahowdb ~]$ $ORACLE_HOME/OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.17 Copyright (c) 2019, Oracle Corporation. All rights reserved. Oracle Home : /scratch/u01/app/oracle/product/12.1.0/dbhome_1 Central Inventory : /scratch/u01/app/oraInventory from : /scratch/u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc OPatch version : 12.2.0.1.17 OUI version : 12.1.0.2.0 Log file location : /scratch/u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2019-10-12_11-28-58AM_1.log Lsinventory Output file location : /scratch/u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2019-10-12_11-28-58AM.txt Local Machine Information:: Hostname: orahowdb ARU platform description:: Linux x86-64 Installed Top-level Products (1): Oracle Database 12c 12.1.0.2.0 There are 1 products installed in this Oracle Home. Interim patches (2) : Patch 29774383 : applied on Wed Sep 04 03:44:12 PDT 2019 Unique Patch ID: 22961858 Patch description: "Database PSU 12.1.0.2.190716, Oracle JavaVM Component (JUL2019)" Created on 7 Jun 2019, 10:02:47 hrs PST8PDT Bugs fixed: 29774383, 29774367, 29251241, 29254615, 19165673, 28790654, 28915933 28440711, 28502128, 28502113, 27923320, 27952584, 27952577, 27642235 27475603, 27461789, 27461842, 25649873, 27001733, 27000663, 27000690 26635845, 26637592, 26570171, 26027162, 26023002, 26023025, 25437695 25494413, 25494379, 24917972, 25067795, 24534298, 25076732, 25076756 24315824, 21659726, 24448240, 24448282, 23177536, 22675136, 23265914 23265965, 23727148, 22674709, 22670413, 22670385, 21188537, 22139226 22118835, 22118851, 21555660, 21811517, 19623450, 21566993, 21566944 19176885, 21068507, 21047803, 21047766, 20415564, 20408829, 20408866 19877336, 19855285, 19909862, 19895362, 19895326, 19153980, 19231857 19223010, 19245191, 19699946 Patch 29494060 : applied on Wed Sep 04 02:28:08 PDT 2019 Unique Patch ID: 22993235 Patch description: "Database Patch Set Update : 12.1.0.2.190716 (29494060)" Created on 28 Jun 2019, 07:47:40 hrs PST8PDT Bugs fixed: 19309466, 19902195, 18250893, 25437699, 19383839, 19781326, 16756406 18456643, 26546664, 22364044, 18845653, 19915271, 20172151, 18417036 19516448, 23713236, 24907917, 24796092, 23140259, 19243521, 19658708 18272672, 21153266, 19174430, 22243719, 19548064, 26556014, 20493163 20688221, 21387964, 13542050, 22250006, 22734547, 22243983, 21623164 19012119, 19932634, 19869255, 22232606, 18681056, 23324000, 25427662 22068305, 24589081, 19439759, 19303936, 22916353, 24835538, 22353346 19790243, 21106027, 26444887, 23088803, 22529728, 26256131, 19134173 24303148, 20447445, 21101873, 21188584, 19390567, 26513709, 25780343 19769480, 21097043, 21225209, 26245237, 20677396, 19284031, 19450314 19016730, 22517782, 20919320, 22205263, 22075064, 22551446, 22721409 18440095, 22496904, 16439813, 18354830, 20596234, 22022760, 20936905 23197103, 22515353, 21514877, 19809171, 21186167, 26111842, 18990023 25654936, 21492036, 18705806, 28420042, 19578247, 22024071, 22238921 22809871, 21184223, 19995869, 23089357, 19404068, 18921743, 19065677 19018447, 19018206, 18308268, 19777862, 29027694, 22223463, 19304354 22519146, 19445860, 26654363, 27199245, 22977256, 20890311, 27445727 21142837, 20869721, 24555417, 22179537, 21756699, 20217801, 18819908 22760595, 25483815, 23628685, 23007241, 19593445, 21080143, 27351628 20582405, 24966594, 20031873, 25489367, 18618122, 24737581, 21698350 26784509, 24739928, 18966843, 19077215, 20704450, 19068970, 20543011 19023822, 24713381, 20432873, 21756677, 22836801, 20328248, 18674047 18849537, 20087383, 25459958, 20315311, 22897344, 27534509, 25178179 19308965, 18948177, 19468991, 20868862, 21780146, 23315153, 20466628 21756661, 20397490, 19706965, 20302006, 24831514, 23240358, 22178855 19032777, 20862087, 19329654, 18974476, 20603378, 20859910, 19307662 26203182, 21847223, 20281121, 22568797, 19075256, 19076343, 18866977 28026866, 29511611, 22808310, 25635149, 20844426, 20904530, 20441797 21442094, 25079710, 24674955, 18840932, 18740837, 20294666, 25602488 21517440, 22062517, 19180394, 27337759, 19174942, 20671094, 21889720 19450116, 18411216, 20117253, 24386767, 20641666, 19931367, 25264559 19930276, 22092979, 25616268, 21625179, 20879709, 23003979, 20165574 28578164, 19272708, 19547370, 22624709, 23084507, 23184263, 20228093 21281532, 25093872, 19805359, 26324206, 19461270, 19434529, 18799063 20354900, 20378086, 29388020, 17008068, 21246723, 20831538, 20424899 24929210, 24560906, 20144308, 21620471, 19670108, 19068610, 20267166 25123585, 20476175, 18549238, 19297917, 22950945, 19385656, 23528412 19684504, 19330795, 21174504, 28357401, 20899461, 20557786, 21911701 19143550, 20118035, 19024808, 25760195, 20009833, 19604659, 16359751 26039623, 22820579, 19928926, 23314180, 20212067, 24737403, 20480209 18904062, 29030780, 26430737, 20856766, 17258582, 27169796, 21668627 20877664, 23272045, 19487147, 23149541, 24577566, 19430401, 19676905 20925795, 26482376, 21296029, 21629064, 23229229, 22865673, 20708701 25353983, 19280225, 21315084, 20613079, 19375649, 19213447, 19989009 18191823, 27314390, 26336977, 25775213, 24393981, 25639019, 17319928 14705949, 19703301, 28390273, 21626377, 20122715, 6418158, 23105538 26198926, 19258504, 21188532, 23151677, 24792678, 17890099, 21649497 26446098, 16887946, 26024732, 18791688, 19721304, 27012701, 19490948 19619732, 21164318, 23148260, 18090142, 21641760, 19818513, 23002524 20139391, 24693382, 19978542, 23543183, 22165897, 19373893, 22359063 19409212, 18373438, 23035249, 20677974, 18990693, 20470877, 21422580 21632821, 22351572, 20235511, 23220453, 18742258, 18604493, 23008056 22901797, 18610915, 20978259, 20832516, 24801152, 26089440, 20907061 19523462, 20505778, 19183343, 21787056, 21273804, 22782647, 25093739 17835294, 28708023, 24413809, 27846298, 18371441, 26714910, 24385983 20413820, 24421668, 28986231, 25897615, 25643931, 23195445, 21281607 20513399, 20558005, 20093776, 18909599, 20618595, 23572982, 19211433 20331945, 19512341, 22256431, 19637186, 19022470, 18607546, 26875822 24573817, 23115139, 19649152, 19201867, 21294938, 20898997, 18510194 21842740, 22454326, 24683149, 19534363, 25489607, 23061702 OPatch succeeded.
3.1: Detailed information on the applied patch.
[oracle@orahowdb ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -details
4: As a sysdba you can also execute below command from the sql prompt to get the patch detail.
SQL> set serverout on;
SQL> exec dbms_qopatch.get_sqlpatch_status;
Patch Id : 27338041
Action : APPLY
Action Time : 18-APR-2018 15:48:43
Description : DATABASE PATCH SET UPDATE 12.1.0.2.180417
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/27338041/22036385/27338041_apply_D0
2NAQCD_CDBROOT_2018Apr18_15_46_27.log
Status : SUCCESS
Patch Id : 28259833
Action : APPLY
Action Time : 24-OCT-2018 03:29:23
Description : DATABASE PATCH SET UPDATE 12.1.0.2.181016
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28259833/22488632/28259833_apply_D0
2NAQCD_CDBROOT_2018Oct24_03_28_14.log
Status : SUCCESS
Patch Id : 28440711
Action : APPLY
Action Time : 24-OCT-2018 03:38:12
Description : Database PSU 12.1.0.2.181016, Oracle JavaVM Component (OCT2018)
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28440711/22440644/28440711_apply_D0
2NAQCD_CDBROOT_2018Oct24_03_34_30.log
Status : SUCCESS
Patch Id : 28440711
Action : ROLLBACK
Action Time : 05-MAR-2019 03:12:23
Description : Database PSU 12.1.0.2.181016, Oracle JavaVM Component (OCT2018)
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28440711/22440644/28440711_rollback
_D02NAQCD_CDBROOT_2019Mar05_03_09_19.log
Status : SUCCESS
Patch Id : 28790654
Action : APPLY
Action Time : 05-MAR-2019 03:12:23
Description : Database PSU 12.1.0.2.190115, Oracle JavaVM Component (JAN2019)
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28790654/22620251/28790654_apply_D0
2NAQCD_CDBROOT_2019Mar05_03_10_18.log
Status : SUCCESS
Patch Id : 28729169
Action : APPLY
Action Time : 05-MAR-2019 03:12:23
Description : DATABASE PATCH SET UPDATE 12.1.0.2.190115
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28729169/22626239/28729169_apply_D0
2NAQCD_CDBROOT_2019Mar05_03_10_18.log
Status : SUCCESS
Patch Id : 28790654
Action : ROLLBACK
Action Time : 30-APR-2019 04:50:11
Description : Database PSU 12.1.0.2.190115, Oracle JavaVM Component (JAN2019)
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/28790654/22620251/28790654_rollback
_D02NAQCD_CDBROOT_2019Apr30_04_44_47.log
Status : SUCCESS
Patch Id : 29251241
Action : APPLY
Action Time : 30-APR-2019 04:50:11
Description : Database PSU 12.1.0.2.190416, Oracle JavaVM Component (APR2019)
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/29251241/22839506/29251241_apply_D0
2NAQCD_CDBROOT_2019Apr30_04_46_08.log
Status : SUCCESS
Patch Id : 29141015
Action : APPLY
Action Time : 30-APR-2019 04:50:11
Description : DATABASE PATCH SET UPDATE 12.1.0.2.190416
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/29141015/22785785/29141015_apply_D0
2NAQCD_CDBROOT_2019Apr30_04_46_08.log
Status : SUCCESS
Patch Id : 29251241
Action : ROLLBACK
Action Time : 04-SEP-2019 04:09:43
Description : Database PSU 12.1.0.2.190416, Oracle JavaVM Component (APR2019)
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/29251241/22839506/29251241_rollback
_D02NAQCD_CDBROOT_2019Sep04_04_06_10.log
Status : SUCCESS
Patch Id : 29774383
Action : APPLY
Action Time : 04-SEP-2019 04:09:43
Description : Database PSU 12.1.0.2.190716, Oracle JavaVM Component (JUL2019)
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/29774383/22961858/29774383_apply_D0
2NAQCD_CDBROOT_2019Sep04_04_07_20.log
Status : SUCCESS
Patch Id : 29494060
Action : APPLY
Action Time : 04-SEP-2019 04:09:43
Description : DATABASE PATCH SET UPDATE 12.1.0.2.190716
Logfile :
/scratch/u01/app/oracle/cfgtoollogs/sqlpatch/29494060/22993235/29494060_apply_D0
2NAQCD_CDBROOT_2019Sep04_04_07_20.log
Status : SUCCESS
PL/SQL procedure successfully completed.
5: To get information on particular patch ID.
SQL> select xmltransform(dbms_qopatch.is_patch_installed('29494060'),dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual; Patch installed? Patch Information: 29494060: applied on 2019-09-04T02:28:08-07:00
6: Patch detail from registry
SQL> select * from sys.registry$history; SQL> select * from sys.dba_registry_sqlpatch;
Get Current Patch Set Level of Oracle Applications Products For R12
October 11th, 2020, posted in Oracle EBS Application, Oracle Queries
1) Login to OAM
(Responsibility “Oracle Applications Manager“, menu “OAM Support Cart“)
and Navigate: Support Cart -> Applications Signature -> Collect -> Check “Product Information” box -> Click on “View” (eyeglasses)
In R12
(Responsibility “System Administrator“, Menu “Oracle Application Manager“)
and Navigate : Applications Usage >> Products Installed
This will display the following patchset information :
Application Name
Current Patch Level
Product Version
Status (Installed, Shared Product, Inactive)
2) The patchset level information can be retrieved directly via sqlplus with the following diagnostic script :
$AD_TOP/sql/adutconf.sql
3) Use the below query to pull out the details
SQL>
SELECT fav.application_id, fav.application_short_name, fav.application_name, fav.basepath, fav.creation_date, fpi.status, fpi.patch_level, fpi.product_version, fpi.TABLESPACE, fpi.index_tablespace, fpi.temporary_tablespace FROM fnd_application_vl fav, fnd_product_installations fpi WHERE fav.application_id = fpi.application_id(+) ORDER BY creation_date DESC
Syed Ali Immam Rizvi
@aliimmam- RT @BusinessInsider: Buzzy startups failed to fix mental-health care. Now their mistakes are fueling the space's next generation. https://t…
about 3 hours ago - RT @MehrTarar: India is doing it the right way
about 3 hours ago - RT @sabizak: My question is: who is Sumayya?
about 4 hours ago - RT @EmadZafar: Wait, WHAT? #PAKvENG
about 4 hours ago - RT @verysmolsnek: will i keep guessing when salary has been credited or will standard chartered actually send me a notification? stay tuned…
about 4 hours ago
- RT @BusinessInsider: Buzzy startups failed to fix mental-health care. Now their mistakes are fueling the space's next generation. https://t…
Share It With Others :