QP_CUSTOM--OE_MSG_PUB

Hi All
QP_CUSTOM is a standard package given by oracle to customize the price derivation based on our custom logic.
Recently i customisee this package to get freight charge based on the wegith of the line items.
This was not so tough to achieve but the user want to show some messages when ever any exception occurs....But the qp_custom is supposed to return a number..
This api is called from dynanic formual on some event like saving entering ..
The solution we thought of is form personalization by returning some constant number an throw exception when we encouter that values...But still i didn't work...the possible ways form personalization works are far less than the ways it didnt work...

after sorm R&D on google and metalink i found that there is api oe_msg_pub to put error messages...
But unfortunately i didnt find right code to use it properly after some hit and try
i figured out that
oe_msg_pub.add_text will add a message
oe_msg_pub.add will put the message on the stack..This will display messages in the
message window in order entry screen
i thought it would be better if we display message in a modal window
so i gave a blind try like define a message in application developer message window


fnd_message.set_name ('XXCUST', 'XX_ERROR_MSG');
fnd_message.set_token ('MESSAGE', 'No Shipping Charges defined');
oe_msg_pub.ADD;
"Message is a token in the message"
This really works...every time you change qp_custom package close the order entry form ,reload it and try...

Date parameter in Concurrent programs-FND_DATE

Hi All,
Normally when ever we want to use date parameter for pl/sql stored procedures we ecounter a common error saying that format string doenst match...
then we do a data conversion to_date(p_date,'RRRR/MM/DD HH24:MI:SS') and then use it..

The better way of doing it is define the data parameter as varchar2 and use the function FND_DATE.date_to_canonical(p_date).This will direclty convert the text string comming from the concurrent programs to date value

ln_processing_date := NVL (fnd_date.canonical_to_date (p_processing_date), SYSDATE);

Oracle Apps-Changing LOV Query-Form Pesonalization

How to change a LOV query from Forms Personalization??

This is one of the requrirement that we want to add a additional condition to the LOV query or you want to populate the LOV all together from your SQL.
Its a simple four step process

Step1:
Get the Query that of the LOV of the standard form. You can do it by opening the standard form in the form builder or Get the query by taking the trace of the form and getting the query from the trace..

Step2:
If you want to add only a few more conditions to the query just add the conditions
In other case create a view with column same as in the standard query.

Step3:
In this step on when new form instance we will create the record Group from query

Step4:
Attach the record group to the LOV you want to change…

Step1:Get the query
The standard query is

select event_name, description from fa_maint_schedule_dtl WHERE EVENT_NAME LIKE :1

I want to populate from my own sql so I create a view

create or replace view XX_AMC_EVENT_DETAILS as
select 'SERVICE' EVENT_name ,'ATM SERVICE' DESCRIPTION from dual
union
select 'AMC' ,'AMC CONTRACT' from dual
union
select 'TEST1' ,'TEST2' from dual

Step2:
Plz find teh screen shots attached..

Terminating Concurrent program sessions

Hi ALL,
One way of finding the session id for concurrent is through fnd_cocurrent_request...as discussed in my earlier post
But there is another easy way...


select sid,serial#,MODULE,ACTION,status from V$session

the module will contain the concurrent program short name...If only one instance of the concurrent program is running
we can easily identify the sessions of it..

If we terminate the program and the session not got released..simply find th session

and use the command
alter sytem kill session 'SID,SERIAL#'
Please the values you got from the above query

7 Ways for a Better LOG--My View..Suggestions Welcomed

Hi All,
we all use the log files for debugging issues by writting text into them when ever a exception occurs or to know the process flow of our code...Here are few steps that will make us to have better log and make our life easy during supporting phase..

7 ways for writing a Better Log
1.Always Display the Name of the package/procedure in the LOG File.
The main advantage of doing this is no need query the concurrent program name and executable to find the procedure of
concurrent program

2.It is always better to have a common package having the log function.So that every time we want to write into a log
there is no need to type entire fnd_file.put_line(fnd_file.log,'message'
it can be easily done like
xxab_util_pkg.log('message');
it is better to have out also in the same way...

3.If the code is running very long number of lines define a stage variables and set it at different levels..and the dispaly the stage completed value when ever the exception occurs...This helps to debug the issue faster...

4.Display all the derived values in the log

5.When ever you are displaying statements like no data found for any sqlstatments in the code display all the values passed to the SQL along with nodatafound or too many row exception.This inturn will reduce a lot of time during support...

6.Always make sure you attach the SQLERRM variable with the when other exceptions handled...and diplay the section name which is causing this exception
exception
when others then
fnd_File.put_line('Unhandled exception occured in XX_GL_INTF_PKG.Check_PERIOD:'||SQLERRM);

7.Always End the concurrent program either in ERROR or warning when ever a exception occurs in the programs unless it is a business requirement.

Entering Query Conditions in Standard Forms

Recently i want to query on a stadard form for records having reversal date as null
i remember that i need to give is null but it didn't work...some how i manager to do it from backend..Even i searched developer guide with no use...So how i get this from the form guide and i am publishing for easy reference..these things will be very useful..and makes our life easy..

1. to enter query by example
2. to recall the last query parameters
3. to Execute Query
4. Query : Count Matching Records (after putting in the query parameters)

Query Wild Card Characters
% one or more characters
_ exactly one character

Query Operators
#between and --need to give #
#is null
#is not null
> Greater Than
>= Greater Than or Equal
< Less Than
<= Less Than or Equal
= Equal
!= Not Equal

RETCODE & ERRBUFF

Hi ALL,
As we all know there are two mandatory parameters that need to be pased for all the procedures called
1.ERRBUFF
2.RETCODE..
They are very useful parameters if used properly..like setting the status of the conucrrent program and putting the log messages...
i think it is better we make some rule like we make the program end in Error when ever there is a unidentified exception occurs based on your business rule...

define ERRBUFF as the first parameter and Retcode as the second one...Dont forget to specify the out variable type in it...

CREATE PROCEDURE SAMPLE_PROC (ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_1 varchar2)

The retcode has three values returned by the concurrent manager
0--Success
1--Success & warning
2--Error

we can set our concurrent program to any of the three status by using these parameters...
for example
Begin
.....
Exception
when others then
fnd_File.put_line(fnd_file.log,'Unhandled exception occurred in package.procedure'||SQLERRM);
retcode='2';
end;
Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.....