Add popup message while request running

For debugging purposes you can use command SRW.MESSAGE inside pl/sql code when you are creating a report using report builder v.6 application.

i.e: Uses this command in BEFOREREPORT program unit for showing which value has P_PARAMETRO

SRW.MESSAGE(1,'PARAMETRO ' || :P_PARAMETRO);

full code:


function BeforeReport return boolean is
begin
   SRW.MESSAGE(1,'PARAMETRO' || :P_PARAMETRO);

   return (TRUE);

end;

Note: In parallel when form builder is used for creating a form you can add this line for add comment:
 dbms_output.put_line('PARAMETRO' || :P_PARAMETRO);

shortcut to concurrent request.

Execute a request in e-Business Suite is a process which need navigate in many forms. If you need generate reports mamy times you need other. This Blog entry try give a solution creating a shortcut in the menu:

1. Create a new form which call to concurrent manager with a parameters.

(Responsability:Application Developer|Application|Function)

This is the core:

Form:

Run Report

Parameters:

CONCURRENT_PROGRAM_NAME ="XXXX" PROGRAM_APPL_SHORT_NAME="YYYY"

where XXXX= Request Name (Short) and YYYY=Request Applications Program.

Finally, you only have to add IO-INFORME VENTAS... as new form in desired responsability menu.

source: http://oracle-ebs-hispano.blogspot.com.es/search/label/Concurrent%20request

implement IF-THEN-ELSE logic in a SELECT ORACLE statement

Oracle SQL from Oracle 8i support methods of create  IF-THEN-ELSE using CASE SQL .
See example below:

SELECT  CTA.CUSTOMER_TRX_ID ID_FACTURA,CTA.TRX_NUMBER NUMERO_FACTURA,RCTTA.name,

CASE WHEN RCTTA.name='SPA' OR RCTTA.name='RSPA' 

      THEN TO_CHAR(TO_DATE(CTA.TRX_DATE, 'DD/MM/RRRR'), 'DD/MM/RRRR') 

 WHEN RCTTA.name='EXP' OR RCTTA.name='REXP' 

       THEN TO_CHAR(TO_DATE(CTA.TRX_DATE, 'DD/MM/RRRR'), 'MM/DD/RRRR')

ELSE 

        TO_CHAR(TO_DATE(CTA.TRX_DATE, 'DD/MM/RRRR'), 'MM/DD/RRRR')

END AS FECHA_FACTURA

FROM RA_CUSTOMER_TRX_ALL CTA ....

Export to csv doesn't work. APPLSYS.FND_LOBS_CTX

Recently occurred that File|Export option from main menu in oracle apps doesn´t export properly. Problem is due to log file overflow  . For fixing only execute this script:

begin ctx_ddl.sync_index('APPLSYS.FND_LOBS_CTX');

end;

Order-level sales credit total () must equal 100%

This error could appear when you are doing a material return from a sales order.  
Material return workflow need to create a special sales order(return type). Most cases it has been created  as a copy of original and not all fields are copy from source to target. One of these missed fields are Sales Credits , so for fixing this problem you have to inform in target sales order all data regarless Sales Credit.  For do use using  Actions| Sales Credits. In the picture below you can see more details:

Set mandatory fields depending a condition in forms using personalization

Initially you have to know which fields are involved. So one field controls the condition and other field become required depending value of first field.

For this example in Payments (AP responsability)we need Payment Rate(Rate_dsp ) field be required when Account Currency(Currency_code) not equal to EUR.

1. Create condition:
navigate to (Help|Diagnostics|Custom Code|Personalize) and see picture below:



2.make a field be Required if condition rule(above image) is evaluated to true.Navigate to the second Tab called "Actions" and enter a new action as you see in image below:

 

Set mandatoy fields in forms using personalization

From required form, navigate to (Help|Diagnostics|Custom Code|Personalize)

- Navigate to the second Tab called "Actions" and enter a new action :


- Seq to a number at your wish


- Type = 'Property"


- description - add a description here


- language - as needed for customer's business needs


- Object Type - "Item"


- Target Object - "FORM_NAME.field_name_here"

- Property Name = 'Required"


- Value = "TRUE"

see picture below for more datails:

Retrieve user and date information from a register when ORA-01403 error appears.

It's usual retrieve information about what user and which date a resgister has been modified. We can know this information using (Help|Register History)option. But in some forms don't give us this information and instead of this next picture appear:

 For this reason we can use (Help|Diagnostic|Examine...), [but previously we need to know oracle password]. In block Field we choose LAST_UPDATE BY.

User Number id is relationed with a final user and can recover navigating by
Responsability:System Administrator
(Security|User|Define), and
(Help|Diagnostic|Examine...), In block Field we choose USER_ID and look for LAST_UPDATED_BY value.

Add a new report to responsability

Responsability:Application Devevoper.

Function:(Concurrent|Executable).

Fill this fields:

 -Choose Oracle Report in Execution Method. In otherwise you can choose procedure.

 -Executable:Name of report

 -Application is the name of oracle module, i.e, Order Management, Inventory,etc.

 - Execution file name is filled with .rdf file name.

Next step is define output style of report:

Responsability:Application Devevoper.

Function:(Concurrent|Program).

Program: User name.

General Name: the same that executable name(filled before)

Application:

Executable Name:the same that executable name(filled before)

Format: text,pdf

Style:A4,A3.

Now, we are ready for define Parameters pressing Parameter button:

Sql Examples for dates:

- P_ANYO:select to_char(sysdate,'RRRR') FROM DUAL

- P_MES:elect to_char(sysdate,'MON') FROM DUAL

So at this moment new report is accesible. Finally we need assign the report to user or responsability. 

Responsability:System Administrator.

Function:(Security|Responsability|define).

Copy name of Request Group: OM Concurrent Program and go to (Security|Responsability|request) then find with request name copy and add new line below with the program user name.

User cannot shipping

When a registered user cannot shipping is because he hasn't an adecuate permission or ROL.
For add permission  to user , use Commercial responsability,then  go Roles and permissions definitions | Permissions
In Shippping execution permisions form you can add or modify row with  username and appropiates permissions.
See screen below for more details:

User cannot retrieve OPM inventory information

When a registered user cannot view OPM inventoty is because he doesn't OPM organization permission. 
For changing it , use OPM System Administrador responsability and  go Setup System | User Organization.
You have to give organization permission for each user as you see below:

Set mandatory parameters in request

When you submit a request and information retrieved is not you expected it or blank, usually is because you not use success parameter because there are not mandatory.

Retrieve information from a DFF(Descriptive Flexfield)

Many times is neccesary add, modify or delete a DFF, but to find it can be, sometimes, imposible.
For do it, see instructions below:

Scenario: Find a DFF.

1. Go to screen when you need to add a new DFF.
(Help | Register History)

2.Retrieve the table name.

3. Now navigating to Application Developer responsability.(Flexfield | Descriptive | Register)
Query on table name and store Title and Application Content. If the name is ended with _V it means View, so the table could be: OE_ORDER_HEADERS_ALL or OE_ORDER_HEADERS_T. For finding purpose is better use wildcards: OE_ORDER_HEADERS%

4.Navigating (Flexfield | Descriptive | Segment). Quey on  Title and Application Content. Pressing Segment button we will get segment information regardless DFF.

Tip: For modifying you have to unfrezee DFF and freeze DFF when you finish

Kill Session using pl/sql Developer

Many times users process become hung and block other sessions user. For this reason .Using PL/SQL Developer program we can kill this process using Tools->Sessions Monitor as you see below:

In the next window appear all users sessions(including username) and modules. With right click mouse you can choose kill selected session option.

Warning: Kill sessions process have to do it carefully.