Murthy Ganjams Oracle Apps Technical World
Time 2021-11-14 06:15:48Web Name: Murthy Ganjams Oracle Apps Technical World
WebSite: http://oracleappstechnicalworld.blogspot.com
ID:231734
Keywords:
Oracle,Ganjams,Murthy,World,Technical,Apps,Description:
keywords: description: Murthy Ganjams Oracle Apps Technical WorldThe main idea is to help the technical community and one point store of most of the technical solutions..if you have good content that can be posted..put a mail to murthyganjam@gmail.com
Wednesday, October 3, 2018 Connecting Python to OracleRecently i started learning python and ran it to issue while connecting python to oracle database.
It took me almost 1.5 hours to establish a connection from a python script to the Database I know.
Biggest challenge for the developer is knowing the
First Step:
The below query will give you the host name and Port no(Type=Local Listener)
select * from v$listener_network;
To get the SID user the below query
select instance_name "SID" from v$instance
User pip to install cx_oracle
import cx_Oracle
dsn_tns = cx_Oracle.makedsn('Hostname', 'portno', 'SID')
conn = cx_Oracle.connect(user='APPS', password='PWD', dsn=dsn_tns)
ver = conn.version.split(".")
print(ver)
print(ver[0])
print(ver[-1])
print(ver[1:4])
conn.close()1 comment: Labels:hostname,SID,SQL to get port Monday, June 1, 2015 Oracle Workflow:Providing Approval/Rejection for a notifcation through API'sHi All,
Recently i got a requirement to Overide a work flow Approval process programmatically
Scenario:Let assume the workflow is pending with supervisor and he is not available to provide approval/rejection a oracle notification.
Possible reasons:
1.users didnt set up vaccation rules
2.Notification is timedout and pending with supervisor manager
One way to handle these scenarios is througth work flow administartor which requires a production incident and it own SLA to resolve.
There are oracle wokflow notification API' s which allows to set the result of the notification through APIS
--Set all the notification attributes result/any comments attributes on the notification etc
wf_notification.setattrtext (nid = l_notification_id,
aname = l_aname,
avalue = l_avalue);
--API to complete the worklfow node and move it
wf_notification.respond (l_notification_id,
This solution is usesful when the oracle approval mechanism need to be handled from third party applications24 comments: Sunday, January 6, 2013 Oracle DBMS_UTILITY error handlingToday we observed on weird issue when using one of the standard API which used work prior started giving error
ORA-06508: PL/SQL: could not find program unit being called
The easiest way to resolve this issue is by adding DBMS_UTILITY format_error_stack and format_call_stack to the when others block
This gave us the exact error of the internal package which was invalid
Begin
Null;
--keep your code
EXCEPTION
when others then
dbms_output.put_line(l_progress
'-when other exception'
DBMS_UTILITY.FORMAT_ERROR_STACK
'@'
DBMS_UTILITY.FORMAT_CALL_STACK);
END;9 comments: Sunday, November 11, 2012 Oracle SQL:Converting Row to ColumnsRows to columns..This is second time i came across this requirement of converting rows as columns.
I remember thisquestion in one of the exercises in oracle OCP certification program.The solution was to use Decode and max functionality in combination
Posting it here for easy reference and for people new to oracle.
In the below query our requirement was to get the current quarter and next quarter from a custom calendar in one row..use a similar approach whenever u have similar requirement
SELECT MAX (DECODE (ROWNUM, 1, display_forecast, NULL))
Period1_status_frcst,
MAX (DECODE (ROWNUM, 2, display_forecast, NULL))
Period2_status_frcst,
MAX (DECODE (ROWNUM, 3, display_forecast, NULL))
Period3_status_frcst,
MAX (DECODE (ROWNUM, 1, period_name, NULL)) Period1_disp,
MAX (DECODE (ROWNUM, 1, start_date, NULL)) Period1_start_dt,
MAX (DECODE (ROWNUM, 1, quarter_num, NULL)) Current_quarter,
MAX (DECODE (ROWNUM, 1, end_date, NULL)) Period1_end_dt,
MAX (DECODE (ROWNUM, 2, period_name, NULL)) Period2_disp,
MAX (DECODE (ROWNUM, 2, start_date, NULL)) Period2_start_dt,
MAX (DECODE (ROWNUM, 2, end_date, NULL)) Period2_end_dt,
MAX (DECODE (ROWNUM, 3, period_name, NULL)) Period3_disp,
MAX (DECODE (ROWNUM, 3, start_date, NULL)) Period3_start_dt,
MAX (DECODE (ROWNUM, 3, end_date, NULL)) Period3_end_dt,
MAX (DECODE (ROWNUM, 1, period_name, NULL)) Period1_disp_actual,
MAX (DECODE (ROWNUM, 2, period_name, NULL)) Period2_disp_actual,
MAX (DECODE (ROWNUM, 3, period_name, NULL)) Period3_disp_actual,
MAX (DECODE (ROWNUM, 4, period_name, NULL)) Period4_disp,
MAX (DECODE (ROWNUM, 4, start_date, NULL)) Period4_start_dt,
MAX (DECODE (ROWNUM, 4, end_date, NULL)) Period4_end_dt,
MAX (DECODE (ROWNUM, 4, quarter_num, NULL)) next_quarter,
MAX (DECODE (ROWNUM, 5, period_name, NULL)) Period5_disp,
MAX (DECODE (ROWNUM, 5, start_date, NULL)) Period5_start_dt,
MAX (DECODE (ROWNUM, 5, end_date, NULL)) Period5_end_dt,
MAX (DECODE (ROWNUM, 6, period_name, NULL)) Period6_disp,
MAX (DECODE (ROWNUM, 6, start_date, NULL)) Period6_start_dt,
MAX (DECODE (ROWNUM, 6, end_date, NULL)) Period6_end_dt
FROM ( SELECT CASE
WHEN sysdate end_date
THEN
'P'
WHEN sysdate BETWEEN start_Date
AND end_date
THEN
'C'
ELSE
'F'
END
display_forecast,
period_name,
start_date,
end_date,
quarter_num
FROM gl_periods
WHERE period_set_name = 'Fiscal Year'
AND ADJUSTMENT_PERIOD_FLAG = 'N'
AND (quarter_num, period_year) IN
(SELECT quarter_num, period_year
FROM gl_periods
WHERE sysdate BETWEEN start_date
AND end_Date
AND period_set_name = 'Fiscal Year'
AND ADJUSTMENT_PERIOD_FLAG = 'N')
OR (quarter_num, period_year) IN
(SELECT DECODE (quarter_num, 4, 1, quarter_num + 1),
DECODE (quarter_num,
4, period_year + 1,
period_year)
FROM gl_periods
WHERE sysdate BETWEEN start_date
AND end_Date
AND period_set_name = 'Fiscal Year'
AND ADJUSTMENT_PERIOD_FLAG = 'N')
)
ORDER BY start_date ASC
7 comments: Saturday, November 10, 2012 OAF:Set Text Color/Highlight recordLets discuss on some of the common requirements in custom OAF pages
When ever there is a business validation failure display the content(text) in red color/other or mark the text.
This can be achieved by defining a custom css style.
CSSStyle cellBGColor = new CSSStyle();
cellBGColor.setProperty("color","#FF0000");
#FF0000--red color
OAMessageStyledTextBean field12=(OAMessageStyledTextBean)webBean.findChildRecursive("field12");
if(field12 != null )
field12.setInlineStyle(cellBGColor);
-----------------------------------------------------------------
One more requirement was to set color for the records dynamically.
Once user select and submit a record in a advanced table
the region is refreshed and the selected row should be unchecked and record should be highlighted.
That mean when the page refreshes only certain record should be highlighted.
TO achieve this we create a transient attribute in the VO
based on our condition we set the value for it.
--Code in AM
--rowlineVo --row handler
--we try to use existing styles instead of defining new one
rowlineVo.setAttribute("color","OraBGGrayMedium");
--Process request CO
OAMessageStyledTextBean field1 = (OAMessageStyledTextBean)webBean.findChildRecursive("Field1");
OADataBoundValueViewObject css2 = new OADataBoundValueViewObject(field1, "color");
field1.setAttributeValue(UIConstants.STYLE_CLASS_ATTR,css2);
This worked with out any issues..
If you want different colors based on the data we need to use OADataBoundValueViewObject.
OADataBoundValueViewObject css2 = new OADataBoundValueViewObject(field1, "color");
The first parameter the field which we want to apply the color and the second parameter is the vo attribute which need to be used.
6 comments: Oracle OAF:Date Field -set min and max dateHi All,
Its long time i have written anything interesting..too much occupied in job and not able find time to write anything...
Now a days i am working some interesting projects.. projects just to enhance user experience in using oracle projects.
Instead of multiple clicks in OOB we are developing a single screen to perform the same transaction...
and one screen to view all the data he need.
we are working developing Custom OAF page and integrating with Oracle OOB.
OOB-- Out of box(oracle standard pages)
One of the requirements was to control the calendar in the date field..our requirement was to stop user from selecting any date in the past.
There are multiple approaches for doing thiseither in complete java or JAVA and PLSQl
here i am presenting one of the approaches of getting date from DB
--Get page connection
Connection conn = pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();
--sql query
String selectSQL = "select NVL(TO_DATE (fnd_profile.VALUE ('XX_GLOBAL_DATE'),'DD-MON-RRRR'),SYSDATE) date1 FROM DUAL";
System.out.println("SQL: "+selectSQL);
--parse the sql
PreparedStatement stmt = conn.prepareStatement(selectSQL);
--execute query
ResultSet rs = stmt.executeQuery(selectSQL );
System.out.println("RS: "+rs);
while (rs.next())
{
java.sql.Date date = rs.getDate("date1");
--get the date field handler
OAMessageDateFieldBean dateField = (OAMessageDateFieldBean)webBean.findIndexedChildRecursive(stat_date);
--set the minimun value
dateField.setMinValue(date);
}
similary we can set the max date also ...But make sure the date is in sql date type.
But one catch is if user dont use the calendar and type date in the field the system will allow a past date.To over come this issue
you need to have a validation logic defined either in the set attribute of the VO or a error raised from DB if you are not comfortable in java :)9 comments: Tuesday, November 15, 2011 Migration Business events and subscriptionsMigrating business events and subscriptions
How do migrate business events and subscription..one of them is using java command wfx load or using packages
Business Events
$AFJVAPRG oracle.apps.fnd.wf.WFXLoad -d apps apps_pwd machine_name:port_no:SID thin US Custom_File_Name.wfx EVENTS Business_Event_Name
Subscriptions:
$AFJVAPRG oracle.apps.fnd.wf.WFXLoad -d apps apps_pwd machine_name:port_no:SID thin US Custom_File_Name.wfx SUBSCRIPTIONS Business_Event_Name
$AFJVAPRG oracle.apps.fnd.wf.WFXLoad -u apps apps machine_name:port_no:SID thin US Custom_File_Name.wfx
TO force upload a Subscription:
$AFJVAPRG oracle.apps.fnd.wf.WFXLoad -uf apps apps_pwd machine_name:port_no:SID thin US Custom_File_Name.wfx
use the below package to load business event and subscription information using script
wf_event_pkg
wf_event_subscriptions_pkg
This looks easy way for me..
use wf_event_pkg.generate to generate the xml
use that xml as input data for wf_event_pkg.recieve to create a script and migrate to other instances..
l_GUID is the event unique identifier which is available in wf_events table.
declare
l_GUID raw(32000):='AAD7E8E4319215BFE04025ADD478036C';
l_xml_data varchar2(32000);
begin
l_xml_data:=wf_Events_pkg.generate(l_GUID );
dbms_output.put_line ('xml data'||l_xml_data);
end;
Use the output we got above as the input for wf_even_pkg.receive.By using the below script we can migrate the event data to other environments
The xml generated above will contain xml tags like wf_event,guid,name etc..But the some how while posting the tags are removed..
begin
wf_Events_pkg.receive(l_xml_data );
--dbms_output.put_line ('xml data'||l_xml_data);
end;
select * from wf_events where name like 'xxpor%'
Similarly use the wf_event_subscriptions_pkg.generate to generate susbscription xml and use the same XML as inpu to the recieve function
Always make sure you load the event before subscription.
One other way directly use the package insert_row procedures to load data.That requires data that needs to be derived89 comments: Older PostsHomeSubscribe to:Posts (Atom)Search within the blogLabelsAOL(9)FORMSCUSTOMIZATION(4)PERFORMANCE TUNNING(4)FORMS(3)IMPLEMENTATION(3)USEFUL SQLQUERIES(3)USEFULSCRIPTS(3)HRMS(2)MIGRATION(2)MISCELLEANOUS(2)UNIX(2)XML REPORTS(2)oracle workflow(2)AIM Documents(1)Business Events(1)DATA LOADERS(1)INTERFACES(1)OA Page personalizations(1)Oracle(1)R12(1)SID(1)SQL to get port(1)STANDARD CUSTOMISATIONS(1)Useful Application Queries(1)XML Publisher(1)\(1)hostname(1)order management(1)Blog Archive 2018(1) October(1)Connecting Python to Oracle 2015(1) June(1) 2013(1) January(1) 2012(3) November(3) 2011(5) November(2) July(2) March(1) 2010(7) December(3) October(1) September(2) May(1) 2009(21) September(3) August(1) July(1) June(1) May(3) April(4) March(1) February(1) January(6) 2008(34) November(1) September(1) August(6) July(2) June(2) April(11) March(4) February(3) January(4) 2007(4) December(1) November(3)
About MeMurthy GanjamI am oracle apps techno Functional consultant with expertise on designing custom solutions and work arounds.I am involved in project with 50-60% customisations for good or bad :)View my complete profileTotal Visits
TAGS:Oracle Ganjams Murthy World Technical Apps
<<< Thank you for your visit >>>
Websites to related : Home - X Car
keywords: sites,
description:Está é a home do nosso site, X Car. Confira nossos carros em destaque, os serviços que oferecemos e leia
keywords:
description:
ip-address.comHomeMy IPSpeedtestSitemapProxy CheckerProxy ListVerify Email AddressTrace Email AddressIP to Zip CodeIP Address D
keywords:
description:ℹ️ Tlwen - Show detailed analytics and statistics about the domain including traffic rank, visitor statistics, website informati
keywords:
description:
Skip to content Bert Jewelry
keywords:
description:Millennium Trailers is a Nationwide Retailer of Enclosed Trailers. We offer trailers for cargo as well as cars. Custom trailers
keywords:Artists, Interior design, home decorations, oil painting on canvas, oil on canvas, art reproduction, art gallery, collectables, decor, vietna
Klaxcar France - pièces détach keywords:
description:
À propos de nousProduitsKit LEDFreinage et frictionVisionSuspension et amortisseursRefroidissementCapteurs et vannesFiltra
keywords:
description:
ip-address.comHomeMy IPSpeedtestSitemapProxy CheckerProxy ListVerify Email AddressTrace Email AddressIP to Zip CodeIP Address D
keywords:trail,gator,spare,parts,part,uk,europe
description:Bicycle Lights 247 is an Online cycle lighing store with a large range of bike lights from
keywords:
description:Discover the most desired Hotels & Resorts in Cyprus, designed for couples in love and families seeking a truly happy break.
COV
Hot Websites