Long time ago I always complained about long time operations without any feedback ( silly me, i didn't know about big companies volume ) and Suddenly a requirement appears. We have a screen that takes a loot of time to make some action and after reviewing we realize that the screen does a loooot of stuff so I can't actually make that screen faster (may be I'm i keep looking) but en the meanwhile. And the worst part the user's are really hyperactive, what a nightmare they found how reduce the time . . . by closing the window and reducing their time by well they didn't but you get it, so how make them realize that the screen is working. Simple
adding a progress bar
so where is the code:
APP_WINDOW.PROGRESS( 0,'First Message..... ');
synchronize
/*Long time operation*/
APP_WINDOW.PROGRESS(.1,'Second Message..... ');
synchronize;/*Long time operation*/
APP_WINDOW.PROGRESS(.3,'Third Message..... ');
synchronize
/*Long time operation*/
APP_WINDOW.PROGRESS(.4,'YOU GET IT .... ');/*Long time operation*/
synchronize;
APP_WINDOW.PROGRESS(100);
Has simple has that well why write this article if this is on the documentation basically because on the documentation talks about closing when the parameter is greater than 99% but they didn't tell you that they move the percentage in a value between 0 and 1 so 50% equal to .5 .
So have fun.
viernes, 16 de octubre de 2015
domingo, 23 de agosto de 2015
Utilizando Variables alenta mi TOAD - Using bind variables in the Toad editor causes delays.
Para todos aquellos que utilizan Toad, tarde o temprano podrían comenzar a tener problemas de performance cada vez que llaman a una variable y esto es gracias a que la herramienta tiene la super habilidad de recordar la configuración y los valores de la variables que has utilizado con anterioridad, y eso a mi en que me afecta se preguntaran. Pues básicamente significa que que Toad Genera un archivo donde guarda toda la configuración de ta manera que toad puede recordar a todas tus variables. Pero todo tiene un precio. y en este caso el precio si ejecutas un script muy grande, con muchas variables ( de esos que te gusta solo darle enter cada que sale una variable ) toad comienza a agregar a ese archivo toda la informacion.
La Solución:
Cierra Toad y deja de usarlo :( . . . Naaaaah solo bromeo.
Ve a tu folder de Usuario:
en mi caso:
C:\Users\Javier\AppData\Roaming\Quest Software\Toad for Oracle\10.0\User Files
Una vez ahí
Renombra(Solo si de verdad pero de verdad necesitas utilizar esas variables despues) / Borra el archivo variables.dat.
Inicia Toad de nuevo.
Espero que te ayude.
For all of those who use toad, sooner or later may start having issues with the performance of the bind variables and it's thanks to the super ability of your tool to remember the settings of your bind variables, and what does it means. Basically toad generates a file where it saves all those setting so it can remember when you call that same variable again and again. but if has a price if you run a large script with many variables it starts appending to that file.
The solution :
Close Toad and stop using it :( . . . just kidding.
go to your user folder:
Mine by example
C:\Users\Javier\AppData\Roaming\Quest Software\Toad for Oracle\10.0\User Files
Once there
rename(If you really but seriously really need those values for later)/ remove the variables.dat file.
Start Toad Again.
Hope it helps.
La Solución:
Cierra Toad y deja de usarlo :( . . . Naaaaah solo bromeo.
Ve a tu folder de Usuario:
en mi caso:
C:\Users\Javier\AppData\Roaming\Quest Software\Toad for Oracle\10.0\User Files
Una vez ahí
Renombra(Solo si de verdad pero de verdad necesitas utilizar esas variables despues) / Borra el archivo variables.dat.
Inicia Toad de nuevo.
Espero que te ayude.
For all of those who use toad, sooner or later may start having issues with the performance of the bind variables and it's thanks to the super ability of your tool to remember the settings of your bind variables, and what does it means. Basically toad generates a file where it saves all those setting so it can remember when you call that same variable again and again. but if has a price if you run a large script with many variables it starts appending to that file.
The solution :
Close Toad and stop using it :( . . . just kidding.
go to your user folder:
Mine by example
C:\Users\Javier\AppData\Roaming\Quest Software\Toad for Oracle\10.0\User Files
Once there
rename(If you really but seriously really need those values for later)/ remove the variables.dat file.
Start Toad Again.
Hope it helps.
jueves, 7 de agosto de 2014
Generating Records from DUAL
How meny times You need to generate a test with a million of rows os some exaxt number of rows,
A trick that a friend told me is,
Just use Dual,
select *
from dual
connect by level <=:p_number_of_rows;
With this you have all the rows that you could need whithout a single insert.
Hope it helps.
A trick that a friend told me is,
Just use Dual,
select *
from dual
connect by level <=:p_number_of_rows;
With this you have all the rows that you could need whithout a single insert.
Hope it helps.
miércoles, 6 de agosto de 2014
Mutli Table Inserts
How many times you have faced the Issue when loading information that your query resolved more than one issue but then you want to insert the results on more than one table,
Normally You will create something like this
INSERT into TABLE A
Values ..
Query
INSERT into TABLEB
Values ..
Query
INSERT into TABLEC
Values ..
Query
For those cases oracle created the Multi Table Insert which loks like this:
INSERT
WHEN ... THEN
INTO ... VALUES ...
WHEN ... THEN
INTO ... VALUES ...
subquery;
Aa simple sample will be if you want to insert the firsr 5 row's on the table A, then on the next 2 records on table b and then everithing else on other table, the sample will look like this:
I created this 3 tables:
create table table_demo_1
(dat_1 varchar2(10),
num_2 number );
create table table_demo_2
(dat_1 varchar2(10),
num_2 number );
create table table_demo_3
(dat_1 varchar2(10),
num_2 number );
And onse done that I execute the sentence has follows
INSERT
WHEN ROW_NUM<=5 THEN
INTO table_demo_1 (dat_1,NUM_2) VALUES ('A',ROW_NUM)
WHEN ROW_NUM BETWEEN 6 AND 7 THEN
INTO table_demo_2 (dat_1,NUM_2) VALUES ('B',ROW_NUM)
ELSE
INTO table_demo_3 (dat_1,NUM_2) VALUES ('C',ROW_NUM)
SELECT DUMMY, ROWNUM ROW_NUM
FROM dual
CONNECT BY LEVEL<=10 ;
Easy has you can see.
Normally You will create something like this
INSERT into TABLE A
Values ..
Query
INSERT into TABLEB
Values ..
Query
INSERT into TABLEC
Values ..
Query
For those cases oracle created the Multi Table Insert which loks like this:
INSERT
WHEN ... THEN
INTO ... VALUES ...
WHEN ... THEN
INTO ... VALUES ...
subquery;
Aa simple sample will be if you want to insert the firsr 5 row's on the table A, then on the next 2 records on table b and then everithing else on other table, the sample will look like this:
I created this 3 tables:
create table table_demo_1
(dat_1 varchar2(10),
num_2 number );
create table table_demo_2
(dat_1 varchar2(10),
num_2 number );
create table table_demo_3
(dat_1 varchar2(10),
num_2 number );
And onse done that I execute the sentence has follows
INSERT
WHEN ROW_NUM<=5 THEN
INTO table_demo_1 (dat_1,NUM_2) VALUES ('A',ROW_NUM)
WHEN ROW_NUM BETWEEN 6 AND 7 THEN
INTO table_demo_2 (dat_1,NUM_2) VALUES ('B',ROW_NUM)
ELSE
INTO table_demo_3 (dat_1,NUM_2) VALUES ('C',ROW_NUM)
SELECT DUMMY, ROWNUM ROW_NUM
FROM dual
CONNECT BY LEVEL<=10 ;
Easy has you can see.
miércoles, 23 de julio de 2014
Date tricks on SQL
Dates are always a confusion when working on most of the languages . . . wait what?
Well the truth is that must of the people never reads a manual because . . . is bigger than the bible.
So here is a simple explanation of the Date Datatype in Oracle Sql. First of all a date data type only stores date + time but only with a precision of seconds if you need to record fractional second then you need to use a different data type.
In almost all the languages the dates can be converted from and to strings directly. well here is not the exception, but you need to be careful because it always depends on the setup of the database.
this 3 parameters:
nls_date_format <---- br="" date="" format="">nls_language <---- br="" database="" language="" of="" the="">nls_territory <--- br="" database="" of="" territory="" the="">You can get the values from them with the following query:
select *
from v$parameter
where name in ('nls_date_format'
,'nls_language'
,'nls_territory');
By default the language and territory are american and america , and the date format is DD-MON-RR, wich means '12-JAN-14' is a valid date that represents 12 January of 2014, that was easy right but . . .
What happens when your source of data has a diferent format, for example
select '07-12-1985' from dual
to make this a date
select to_date('07-12-1985','DD-MM-RRRR') from dua.
If you are bored here begin the tricks
Imagine that somen one ask you to generate a range of dates,
1,2. . . . . 30 jan 2014
how do i this?
Select start_date+level-1 calc_date
from (select to_date('01-01-2014', 'dd-mm-rrrr') start_date,
to_date('31-01-2014', 'dd-mm-rrrr') end_date
from dual) connect by level<=end_date-start_date+1
How to get the first day:
select trunc(to_date('31-01-2014', 'dd-mm-rrrr'),'mm') fisrt_day
from dual;
How to get the last day:
select last_day(to_date('31-01-2014', 'dd-mm-rrrr')) last_day
from dual;
How to add days 12 days:
select to_date('31-01-2014', 'dd-mm-rrrr') + 12 add_days
from dual;
How to add 12 months:
select add_months(to_date('31-01-2014', 'dd-mm-rrrr'),12) add_months
from dual;
How to substract 6 months:
select add_months(to_date('31-01-2014', 'dd-mm-rrrr'),-6 ) add_months
from dual;
**** Yes it's tha same function just using a negative number.
And thats all for now.
Hope it helps some one.
Well the truth is that must of the people never reads a manual because . . . is bigger than the bible.
So here is a simple explanation of the Date Datatype in Oracle Sql. First of all a date data type only stores date + time but only with a precision of seconds if you need to record fractional second then you need to use a different data type.
In almost all the languages the dates can be converted from and to strings directly. well here is not the exception, but you need to be careful because it always depends on the setup of the database.
this 3 parameters:
nls_date_format <---- br="" date="" format="">nls_language <---- br="" database="" language="" of="" the="">nls_territory <--- br="" database="" of="" territory="" the="">You can get the values from them with the following query:
select *
from v$parameter
where name in ('nls_date_format'
,'nls_language'
,'nls_territory');
By default the language and territory are american and america , and the date format is DD-MON-RR, wich means '12-JAN-14' is a valid date that represents 12 January of 2014, that was easy right but . . .
What happens when your source of data has a diferent format, for example
select '07-12-1985' from dual
to make this a date
select to_date('07-12-1985','DD-MM-RRRR') from dua.
If you are bored here begin the tricks
Imagine that somen one ask you to generate a range of dates,
1,2. . . . . 30 jan 2014
how do i this?
Select start_date+level-1 calc_date
from (select to_date('01-01-2014', 'dd-mm-rrrr') start_date,
to_date('31-01-2014', 'dd-mm-rrrr') end_date
from dual) connect by level<=end_date-start_date+1
How to get the first day:
select trunc(to_date('31-01-2014', 'dd-mm-rrrr'),'mm') fisrt_day
from dual;
How to get the last day:
select last_day(to_date('31-01-2014', 'dd-mm-rrrr')) last_day
from dual;
How to add days 12 days:
select to_date('31-01-2014', 'dd-mm-rrrr') + 12 add_days
from dual;
How to add 12 months:
select add_months(to_date('31-01-2014', 'dd-mm-rrrr'),12) add_months
from dual;
How to substract 6 months:
select add_months(to_date('31-01-2014', 'dd-mm-rrrr'),-6 ) add_months
from dual;
**** Yes it's tha same function just using a negative number.
And thats all for now.
Hope it helps some one.
lunes, 12 de mayo de 2014
How know to which responsibilities it's associated a function?
The following query extracts the information from the setup on the E-Business Suite and receives has parameter the USER_FUNCTION_NAME it returns the resposibilities related to that user function name.
/*
get Responsibilities associated to Funtions
Only the funtions not Excluded
*/
WITH DAT
AS (SELECT 'FND_FORM_FUNCTIONS-' || fff.function_id ID,
FUNCTION_NAME,
USER_FUNCTION_NAME,
DESCRIPTION,
'FUNCTION' DATA_TYPE,
APPLICATION_ID,
CAST (NULL AS DATE) START_DATE,
CAST (NULL AS DATE) END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CAST (NULL AS VARCHAR2 (480)) ASOCIATED_function_name,
CAST (NULL AS NUMBER) function_application_id,
'FND_FORM_FUNCTIONS-' || fff.function_id EXC
FROM FND_FORM_FUNCTIONS_VL fff
UNION ALL
SELECT 'FND_MENUS-' || MENU_ID,
MENU_NAME,
USER_MENU_NAME,
DESCRIPTION,
'MENU' DATA_TYPE,
NULL APPLICATION_ID,
CAST (NULL AS DATE) START_DATE,
CAST (NULL AS DATE) END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CAST (NULL AS VARCHAR2 (480)) function_name,
CAST (NULL AS NUMBER) function_application_id,
'FND_MENUS-' || MENU_ID
FROM fnd_menus_VL fm
UNION ALL
SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID,
RESPONSIBILITY_KEY,
RESPONSIBILITY_NAME,
DESCRIPTION,
'RESPONSIBILITY',
APPLICATION_ID,
START_DATE,
END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CAST (NULL AS VARCHAR2 (480)) function_name,
CAST (NULL AS NUMBER) function_application_id,
'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID
FROM fnd_responsibility_vl
UNION ALL
SELECT 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE,
CAST (ENTRY_SEQUENCE AS VARCHAR2 (4000)),
PROMPT,
ent.DESCRIPTION,
'MENU ITEM ' || NVL2 (fun.TYPE, '-' || fun.TYPE, '-SUBMENU'),
NULL APPLICATION_ID,
CAST (NULL AS DATE) START_DATE,
CAST (NULL AS DATE) END_DATE,
ent.CREATION_DATE,
ent.CREATED_BY,
ent.LAST_UPDATED_BY,
ent.LAST_UPDATE_DATE,
FUN.function_name,
FUN.APPLICATION_ID function_application_id,
'FND_FORM_FUNCTIONS-' || fun.function_id EXC
FROM FND_MENU_ENTRIES_VL ent, FND_FORM_FUNCTIONS_VL fun
WHERE 1 = 1
AND PROMPT IS NOT NULL
AND NVL (ent.GRANT_FLAG, 'Y') = 'Y'
AND (ent.function_id IS NOT NULL OR SUB_MENU_ID IS NOT NULL)
AND ent.function_id = fun.function_id(+)
),
REL
AS (SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID PAR, 'FND_MENUS-' || MENU_ID CH FROM FND_RESPONSIBILITY
UNION ALL
SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID PAR, 'FND_REQUEST_GROUPS-' || REQUEST_GROUP_ID CH
FROM FND_RESPONSIBILITY
UNION ALL
SELECT NULL PAR, 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID CH
FROM FND_RESPONSIBILITY
UNION ALL
SELECT 'FND_MENUS-' || MENU_ID PAR, 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE CH FROM FND_MENU_ENTRIES
UNION ALL
SELECT 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE par,
NVL2 (SUB_MENU_ID, 'FND_MENUS-' || SUB_MENU_ID, 'FND_FORM_FUNCTIONS-' || function_id)
FROM FND_MENU_ENTRIES
)
,
FN AS (
SELECT 'FND_RESPONSIBILITY-'||RESPONSIBILITY_ID|| '-' || APPLICATION_ID ID, DECODE (RULE_TYPE, 'F', 'FND_FORM_FUNCTIONS-' || ACTION_ID, 'M', 'FND_MENUS-' || ACTION_ID) RESP_ID
FROM FND_RESP_FUNCTIONS
)
SELECT tree.FUNCTION_NAME RESP_KEY,
tree.USER_FUNCTION_NAME RESP_DESCRIPTION,
tree.RESP_NAME function_name,
tree.RESP_DESCRIPTION USER_FUNCTION_NAME,
tree.NAVIGATION
FROM (SELECT CONNECT_BY_ROOT function_name RESP_NAME,
CONNECT_BY_ROOT USER_FUNCTION_NAME RESP_DESCRIPTION,
CONNECT_BY_ROOT DAT.APPLICATION_ID RESP_APPLICATION_ID,
CONNECT_BY_ROOT DAT.ID RESP_ID,
CONNECT_BY_ROOT dat.DATA_TYPE RESP_DATA_TYPE,
SYS_CONNECT_BY_PATH (function_name || ':' || USER_FUNCTION_NAME, '>') tree,
SYS_CONNECT_BY_PATH (DECODE (data_type,'RESPONSIBILITY',CAST (NULL AS VARCHAR2 (80)),function_name || ':' || USER_FUNCTION_NAME), '>') tree_NO_RESP,
SYS_CONNECT_BY_PATH (LPAD (FUNCTION_NAME, 20, '0'), '>') ORD_FUN,
SYS_CONNECT_BY_PATH (CAST (DECODE (data_type, 'MENU', CAST (NULL AS VARCHAR2 (80)), USER_FUNCTION_NAME || '-') AS VARCHAR2 (80)), '>')
NAVIGATION,
DAT.*,
cr.user_name CREATED_BY_NAME,
upd.user_name LAST_UPDATED_BY_NAME,
REL.par,
rel.ch
FROM DAT,
REL,
fnd_user cr,
fnd_user upd
WHERE dat.ID = rel.ch
AND dat.created_by = cr.user_id(+)
AND dat.last_updated_by = upd.user_id(+)
CONNECT BY NOCYCLE PRIOR par = ch
START WITH (
USER_FUNCTION_NAME = :P_NAME-->>NAME OF THE FUNCTION HERE
)
)
tree ,FN
WHERE DATA_TYPE='RESPONSIBILITY'
AND RESP_DATA_TYPE='FUNCTION'
AND tree.RESP_ID=fn.RESP_ID(+)
AND tree.ID=fn.id(+)
and fn.id is null;
/*
get Responsibilities associated to Funtions
Only the funtions not Excluded
*/
WITH DAT
AS (SELECT 'FND_FORM_FUNCTIONS-' || fff.function_id ID,
FUNCTION_NAME,
USER_FUNCTION_NAME,
DESCRIPTION,
'FUNCTION' DATA_TYPE,
APPLICATION_ID,
CAST (NULL AS DATE) START_DATE,
CAST (NULL AS DATE) END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CAST (NULL AS VARCHAR2 (480)) ASOCIATED_function_name,
CAST (NULL AS NUMBER) function_application_id,
'FND_FORM_FUNCTIONS-' || fff.function_id EXC
FROM FND_FORM_FUNCTIONS_VL fff
UNION ALL
SELECT 'FND_MENUS-' || MENU_ID,
MENU_NAME,
USER_MENU_NAME,
DESCRIPTION,
'MENU' DATA_TYPE,
NULL APPLICATION_ID,
CAST (NULL AS DATE) START_DATE,
CAST (NULL AS DATE) END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CAST (NULL AS VARCHAR2 (480)) function_name,
CAST (NULL AS NUMBER) function_application_id,
'FND_MENUS-' || MENU_ID
FROM fnd_menus_VL fm
UNION ALL
SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID,
RESPONSIBILITY_KEY,
RESPONSIBILITY_NAME,
DESCRIPTION,
'RESPONSIBILITY',
APPLICATION_ID,
START_DATE,
END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CAST (NULL AS VARCHAR2 (480)) function_name,
CAST (NULL AS NUMBER) function_application_id,
'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID
FROM fnd_responsibility_vl
UNION ALL
SELECT 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE,
CAST (ENTRY_SEQUENCE AS VARCHAR2 (4000)),
PROMPT,
ent.DESCRIPTION,
'MENU ITEM ' || NVL2 (fun.TYPE, '-' || fun.TYPE, '-SUBMENU'),
NULL APPLICATION_ID,
CAST (NULL AS DATE) START_DATE,
CAST (NULL AS DATE) END_DATE,
ent.CREATION_DATE,
ent.CREATED_BY,
ent.LAST_UPDATED_BY,
ent.LAST_UPDATE_DATE,
FUN.function_name,
FUN.APPLICATION_ID function_application_id,
'FND_FORM_FUNCTIONS-' || fun.function_id EXC
FROM FND_MENU_ENTRIES_VL ent, FND_FORM_FUNCTIONS_VL fun
WHERE 1 = 1
AND PROMPT IS NOT NULL
AND NVL (ent.GRANT_FLAG, 'Y') = 'Y'
AND (ent.function_id IS NOT NULL OR SUB_MENU_ID IS NOT NULL)
AND ent.function_id = fun.function_id(+)
),
REL
AS (SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID PAR, 'FND_MENUS-' || MENU_ID CH FROM FND_RESPONSIBILITY
UNION ALL
SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID PAR, 'FND_REQUEST_GROUPS-' || REQUEST_GROUP_ID CH
FROM FND_RESPONSIBILITY
UNION ALL
SELECT NULL PAR, 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID CH
FROM FND_RESPONSIBILITY
UNION ALL
SELECT 'FND_MENUS-' || MENU_ID PAR, 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE CH FROM FND_MENU_ENTRIES
UNION ALL
SELECT 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE par,
NVL2 (SUB_MENU_ID, 'FND_MENUS-' || SUB_MENU_ID, 'FND_FORM_FUNCTIONS-' || function_id)
FROM FND_MENU_ENTRIES
)
,
FN AS (
SELECT 'FND_RESPONSIBILITY-'||RESPONSIBILITY_ID|| '-' || APPLICATION_ID ID, DECODE (RULE_TYPE, 'F', 'FND_FORM_FUNCTIONS-' || ACTION_ID, 'M', 'FND_MENUS-' || ACTION_ID) RESP_ID
FROM FND_RESP_FUNCTIONS
)
SELECT tree.FUNCTION_NAME RESP_KEY,
tree.USER_FUNCTION_NAME RESP_DESCRIPTION,
tree.RESP_NAME function_name,
tree.RESP_DESCRIPTION USER_FUNCTION_NAME,
tree.NAVIGATION
FROM (SELECT CONNECT_BY_ROOT function_name RESP_NAME,
CONNECT_BY_ROOT USER_FUNCTION_NAME RESP_DESCRIPTION,
CONNECT_BY_ROOT DAT.APPLICATION_ID RESP_APPLICATION_ID,
CONNECT_BY_ROOT DAT.ID RESP_ID,
CONNECT_BY_ROOT dat.DATA_TYPE RESP_DATA_TYPE,
SYS_CONNECT_BY_PATH (function_name || ':' || USER_FUNCTION_NAME, '>') tree,
SYS_CONNECT_BY_PATH (DECODE (data_type,'RESPONSIBILITY',CAST (NULL AS VARCHAR2 (80)),function_name || ':' || USER_FUNCTION_NAME), '>') tree_NO_RESP,
SYS_CONNECT_BY_PATH (LPAD (FUNCTION_NAME, 20, '0'), '>') ORD_FUN,
SYS_CONNECT_BY_PATH (CAST (DECODE (data_type, 'MENU', CAST (NULL AS VARCHAR2 (80)), USER_FUNCTION_NAME || '-') AS VARCHAR2 (80)), '>')
NAVIGATION,
DAT.*,
cr.user_name CREATED_BY_NAME,
upd.user_name LAST_UPDATED_BY_NAME,
REL.par,
rel.ch
FROM DAT,
REL,
fnd_user cr,
fnd_user upd
WHERE dat.ID = rel.ch
AND dat.created_by = cr.user_id(+)
AND dat.last_updated_by = upd.user_id(+)
CONNECT BY NOCYCLE PRIOR par = ch
START WITH (
USER_FUNCTION_NAME = :P_NAME-->>NAME OF THE FUNCTION HERE
)
)
tree ,FN
WHERE DATA_TYPE='RESPONSIBILITY'
AND RESP_DATA_TYPE='FUNCTION'
AND tree.RESP_ID=fn.RESP_ID(+)
AND tree.ID=fn.id(+)
and fn.id is null;
lunes, 9 de mayo de 2011
Concurrents Hello World !!!!!
Hace algún tiempo cuando comenzaba a desarrollar para la EBS me pidieron crear un concurrente ("Con que?" fue mi pregunta). por lo que hoy decidí hacer un pequeño manual con el "hola mundo" en los concurrentes.
Así que primero un poco de teoría:
Concurrente: Es el medio que tienen los usuarios para ejecutar procesos en el servidor, sin tener acceso al usuario APPS ( Pon ningun motivo un usuario debe tener acceso a esta cuenta, incluso ni siquiera los desarrolladores deberiamos tener acceso a este usuario). La mayoría de las ejecuciones de concurrente se realizan por medio del menu:
ver->solicitudes->ejecutar nueva solicitud->solicitud unica
Si seleccionan en el recuadro amarillo, nos mostrara la lista de programas a los que tenemos acceso.
Ahora que ya se como ejecutarlo como rayos se crean estas cosas.
5 Pasos:
1.- Se genera un ejecutable, esto quiere decir cualquier cosa que se va a procesar en el servidor , la EBS soporta ejecutables pro C, java, perl, pl/sql,reports, etc. (el ejemplo que les daré esta en pl/sql).
ejemplo:
CREATE OR REPLACE PROCEDURE APPS.XXX_TST (errbuf OUT VARCHAR2
/*Mensaje que le regresa el procedimiento al adminstrador de concurrente, este mensaje se muestra en el administrador de concurrente*/ , retcode OUT NUMBER/*Codigo de error que el concurrente regresa al administrador de concurrente sobre el estatus de del concurrente: retcode :=0; El concurrente finaliza normal sin color retcode :=1; El concurrente finaliza en ADVERTENCIA color Amarillo retcode :=2; El concurrente finaliza en ERROR color rojo */,oper1 varchar2 ,oper2 NUMBER )
IS BEGIN
/* apps.fnd_file.output & apps.fnd_file.log constantes que represntan sobre que archivo se realizaran las operaciones del paquete fnd_file. apps.fnd_file.put(which,buff); este comando escribe en el archivo seleccionado los datos que se coloquen en el segundo parametro de entrada. apps.fnd_file.new_line(which,lines);este comando escribe en el archivo seleccionado tantos saltos de linea como se indique en el segundo parametro apps.fnd_file.put_line(which,buff);este comando escribe en el archivo seleccionado los datos que se coloquen en el segundo parametro de entrada concatenados con un salto de linea. */
apps.fnd_file.put(apps.fnd_file.output,'Linea 1 Hola Mundo');
apps.fnd_file.new_line(apps.fnd_file.output, 2);
apps.fnd_file.put(apps.fnd_file.output,'Linea 3 Hola Mundo');
apps.fnd_file.put(apps.fnd_file.output,'-Linea 3 Hola Mundo parte 2');
apps.fnd_file.new_line(apps.fnd_file.output, 1);
apps.fnd_file.put_line(apps.fnd_file.output, 'Linea 4 Hola Mundo');
apps.fnd_file.put(apps.fnd_file.output,'Linea 5 Hola Mundo');
apps.fnd_file.put(apps.fnd_file.log,'Linea LOG 1 Hola Mundo');
apps.fnd_file.new_line(apps.fnd_file.log, 2);
apps.fnd_file.put(apps.fnd_file.log,'Linea LOG 3 Hola Mundo');
apps.fnd_file.put(apps.fnd_file.log,'-Linea LOG 3 Hola Mundo parte 2');
apps.fnd_file.new_line(apps.fnd_file.log, 1);
apps.fnd_file.put_line(apps.fnd_file.log, 'Linea LOG 4 Hola Mundo');
apps.fnd_file.put(apps.fnd_file.log,'Linea LOG 5 Hola Mundo'); errbuf:='HAY ALERTAS';
retcode :=1;
errbuf:='HAY ERRORES';
retcode :=2;
errbuf:='NO HAY ERRORES';
retcode :=0;
END;
2.- Se define el ejecutable, desde la responsabilidad Desarrollador de aplicaciones(Application Developer)/Administrador de Sistema(System Administrator) en el menu:Concurrente->Programa->Ejecutable(Concurrent->Program->Executable) se capturan los datos de tu ejecutable. se podría decir que esta pantalla es solo una definición global del ejecutable.
3.- Se define el programa desde la responsabilidad Desarrollador de aplicaciones(Application Developer)/Administrador de Sistema(System Administrator) en el menu:Concurrente->Programa>Definir->(Concurrent->Program->Define) , aquí se capturan los datos que le servirán al usuario para ejecutar su programa, como son : cual es el ejecutable que utilizara?, con que parámetros?, que parámetro es obligatorio? este programa es incompatible con otro? que tipo de salida da este programa?, etc. aqui les dejo un ejemplo de la definición de programa.
4.- Se define quien va a ejecutar este programa, en la ebs el primer nivel donde se define que puede hacer un usuario es la responsabilidad que este tiene, a esa responsabilidad se le agregan todas las funcionalidades que el usuario debería ser capaz de realizar, ejemplo:
5.- Como se puede observar la responsabilidad tiene asociado un grupo de solicitudes, el grupo de solicitudes es básicamente la lista de programas que el usuario que tiene esa responsabilidad puede ejecutar.
Listo !!!! una vez que ya lograron este paso, ya pueden ejecutar el programa que ustedes realizaron.
Ahora lo ejecutamos y que sucede, dependiendo de la forma en que hayan codificado los códigos de error, el proceso puede finalizar:
a) 2300206 retcode:=0; ejemplo de proceso normal.
b) 2300210 retcode:=1; ejemplo de proceso con advertencias.
c) 2300206 retcode:=2; ejemplo de proceso con error.
Queda del lado del programador el colocar el retcode que le ayude al usuario a identificar mejor la situación en la que se ejecuto el proceso.
Como detalle adicional si como programador no tocas esa variable, el proceso se marca como finalizado
correctamente cuando termina su ejecución sin lanzar excepciones.
Un proceso de pl/sql, hereda los datos de la sesión con la que se encuentra conectado, que quiere decir esto: Que no se requiere ejecutar ni APPS.FND_GLOBAL.APPS_INITIALIZE.
Con esto queda el ejemplo de como realizar la creación y ejecución de un proceso concurrente escrito en PL/SQL.
En el próximo articulo daré un ejemplo en reports.
Así que primero un poco de teoría:
Concurrente: Es el medio que tienen los usuarios para ejecutar procesos en el servidor, sin tener acceso al usuario APPS ( Pon ningun motivo un usuario debe tener acceso a esta cuenta, incluso ni siquiera los desarrolladores deberiamos tener acceso a este usuario). La mayoría de las ejecuciones de concurrente se realizan por medio del menu:
ver->solicitudes->ejecutar nueva solicitud->solicitud unica
Si seleccionan en el recuadro amarillo, nos mostrara la lista de programas a los que tenemos acceso.
Ahora que ya se como ejecutarlo como rayos se crean estas cosas.
5 Pasos:
1.- Se genera un ejecutable, esto quiere decir cualquier cosa que se va a procesar en el servidor , la EBS soporta ejecutables pro C, java, perl, pl/sql,reports, etc. (el ejemplo que les daré esta en pl/sql).
ejemplo:
CREATE OR REPLACE PROCEDURE APPS.XXX_TST (errbuf OUT VARCHAR2
/*Mensaje que le regresa el procedimiento al adminstrador de concurrente, este mensaje se muestra en el administrador de concurrente*/ , retcode OUT NUMBER/*Codigo de error que el concurrente regresa al administrador de concurrente sobre el estatus de del concurrente: retcode :=0; El concurrente finaliza normal sin color retcode :=1; El concurrente finaliza en ADVERTENCIA color Amarillo retcode :=2; El concurrente finaliza en ERROR color rojo */,oper1 varchar2 ,oper2 NUMBER )
IS BEGIN
/* apps.fnd_file.output & apps.fnd_file.log constantes que represntan sobre que archivo se realizaran las operaciones del paquete fnd_file. apps.fnd_file.put(which,buff); este comando escribe en el archivo seleccionado los datos que se coloquen en el segundo parametro de entrada. apps.fnd_file.new_line(which,lines);este comando escribe en el archivo seleccionado tantos saltos de linea como se indique en el segundo parametro apps.fnd_file.put_line(which,buff);este comando escribe en el archivo seleccionado los datos que se coloquen en el segundo parametro de entrada concatenados con un salto de linea. */
apps.fnd_file.put(apps.fnd_file.output,'Linea 1 Hola Mundo');
apps.fnd_file.new_line(apps.fnd_file.output, 2);
apps.fnd_file.put(apps.fnd_file.output,'Linea 3 Hola Mundo');
apps.fnd_file.put(apps.fnd_file.output,'-Linea 3 Hola Mundo parte 2');
apps.fnd_file.new_line(apps.fnd_file.output, 1);
apps.fnd_file.put_line(apps.fnd_file.output, 'Linea 4 Hola Mundo');
apps.fnd_file.put(apps.fnd_file.output,'Linea 5 Hola Mundo');
apps.fnd_file.put(apps.fnd_file.log,'Linea LOG 1 Hola Mundo');
apps.fnd_file.new_line(apps.fnd_file.log, 2);
apps.fnd_file.put(apps.fnd_file.log,'Linea LOG 3 Hola Mundo');
apps.fnd_file.put(apps.fnd_file.log,'-Linea LOG 3 Hola Mundo parte 2');
apps.fnd_file.new_line(apps.fnd_file.log, 1);
apps.fnd_file.put_line(apps.fnd_file.log, 'Linea LOG 4 Hola Mundo');
apps.fnd_file.put(apps.fnd_file.log,'Linea LOG 5 Hola Mundo'); errbuf:='HAY ALERTAS';
retcode :=1;
errbuf:='HAY ERRORES';
retcode :=2;
errbuf:='NO HAY ERRORES';
retcode :=0;
END;
2.- Se define el ejecutable, desde la responsabilidad Desarrollador de aplicaciones(Application Developer)/Administrador de Sistema(System Administrator) en el menu:Concurrente->Programa->Ejecutable(Concurrent->Program->Executable) se capturan los datos de tu ejecutable. se podría decir que esta pantalla es solo una definición global del ejecutable.
3.- Se define el programa desde la responsabilidad Desarrollador de aplicaciones(Application Developer)/Administrador de Sistema(System Administrator) en el menu:Concurrente->Programa>Definir->(Concurrent->Program->Define) , aquí se capturan los datos que le servirán al usuario para ejecutar su programa, como son : cual es el ejecutable que utilizara?, con que parámetros?, que parámetro es obligatorio? este programa es incompatible con otro? que tipo de salida da este programa?, etc. aqui les dejo un ejemplo de la definición de programa.
4.- Se define quien va a ejecutar este programa, en la ebs el primer nivel donde se define que puede hacer un usuario es la responsabilidad que este tiene, a esa responsabilidad se le agregan todas las funcionalidades que el usuario debería ser capaz de realizar, ejemplo:
Listo !!!! una vez que ya lograron este paso, ya pueden ejecutar el programa que ustedes realizaron.
Ahora lo ejecutamos y que sucede, dependiendo de la forma en que hayan codificado los códigos de error, el proceso puede finalizar:
a) 2300206 retcode:=0; ejemplo de proceso normal.
b) 2300210 retcode:=1; ejemplo de proceso con advertencias.
c) 2300206 retcode:=2; ejemplo de proceso con error.
Queda del lado del programador el colocar el retcode que le ayude al usuario a identificar mejor la situación en la que se ejecuto el proceso.
Como detalle adicional si como programador no tocas esa variable, el proceso se marca como finalizado
correctamente cuando termina su ejecución sin lanzar excepciones.
Un proceso de pl/sql, hereda los datos de la sesión con la que se encuentra conectado, que quiere decir esto: Que no se requiere ejecutar ni APPS.FND_GLOBAL.APPS_INITIALIZE.
Con esto queda el ejemplo de como realizar la creación y ejecución de un proceso concurrente escrito en PL/SQL.
En el próximo articulo daré un ejemplo en reports.
Suscribirse a:
Entradas (Atom)