Enabling and disabling parameters dynamically. -2

Here is yet another post on same old topic - "Enabling and disabling parameters dynamically"
Well there is a slight change in requirement.
In my first post on this topic, user have a choice of selecting Yes or No, as a first parameter and based on this first parameter selection, we designed second parameter to behave accordingly.

But, what if there is no selection in first parameter.?
Or in other words, value for first parameter is NULL ?

Brief explanation about the requirement.
My concurrent program has got three parameters as below.
1. Receipt Batch Name
2. GL Date From
3. GL Date To.

Initially, when "Receipt Batch Name"  is NULL then GL Date parameters should be enable and ready to take values.

If a value is entered for "Receipt Batch Name", then GL Date parameters should be disabled.

Simple check to find first parameter as NULL or NOT NULL will not work. For example, consider the code below.
decode(:$FLEX$. XXAR_RECEIPT_BATCH_NAME,NULL,'IT IS NULL','IT IS NOT NULL')
-- this command is not enough.
You need to check for NULL values of any value set like below.
decode(nvl(:$FLEX$.XXAR_RECEIPT_BATCH_NAME:NULL,'X'),'X','Y',null) 


For complete solution, consider following screenshots.

Complete SQL Statement entered as default value is - 

select decode(nvl(:$FLEX$.XXAR_RECEIPT_BATCH_NAME:NULL,'X'),'X','Y',null) from dual

Enter following sql statment in place of  Table Name

(SELECT trunc(SYSDATE) - 1000 + LEVEL date_range FROM DUAL CONNECT BY LEVEL <= 1000) temp_tab

Return statement in PL/SQL Procedure.

Question: Can we use RETURN Statement in PL/SQL Procedure.?
Ans: Yes, we can !! ;)

Honestly, as far as I remember, I've never used return statements in my PL/SQL Procedures.
How about you ??

Of coarse, in PL/SQL functions RETURN statement is a mandatory thing. But what about procedures ??

Lets see how and where we can use of return statement in a procedure.
- You have procedure, that contains few lines of code.
- After validating certain condition, you don't need to proceed further. You want control to be returned to calling procedure/main program.

** Couple of approaches here **

One way of doing it is, handling USER defined EXCEPTIONs.
- you can check how to use  USER defined Exceptions. I am not covering it here. ;)
I used this approach many times until now. Big advantage of this way is, you can trap the ERROR code in your calling program.

Another way is having RETURN statement in your sub-routine/child program.
For example:
== sub-routine ==
procedure CHILD_PROC
-- declarations
begin
-- pl/sql statements
if CONDITION = TRUE then
RETURN ;
else
-- do something
end if ;
end CHILD_PROC ;

=== calling program ==
procedure MAIN_PROC
-- declarations
begin
-- pl/sql statements
-- call to child proc
CHILD_PROC();
-- few more statements
end MAIN_PROC ;

Here is some sample code.

-- child procedure.

create or replace procedure child_proc(p_boolean BOOLEAN) as
begin
dbms_output.put_line('in child_proc before return call');
if p_boolean then
return ;
end if ;
dbms_output.put_line('in child_proc after return');
end;
/

-- main procedure

create or replace procedure main_proc(p_boolean boolean) as
begin
dbms_output.put_line('before child_proc call');
child_proc(p_boolean) ;
dbms_output.put_line('after child_proc call');
end;
/

-- execution and  result check --

[1]

begin main_proc(TRUE); end ;

--output --

before child_proc call

in child_proc before return call

after child_proc call

Statement processed.

0.00 seconds

[2]

begin main_proc(FALSE); end ;

--output --

before child_proc call

in child_proc before return call

in child_proc after return

after child_proc call

Statement processed.

0.00 seconds

Enabling/disabling DATE parameters of a concurrent program

Question: How to enable/disable date parameters of a concurrent program  based on certain condition.?
Answer: Handling number and text parameters had been discussed in the following blogs.
http://apps2fusion.com/at/ar/241-enablingdisabling-concurrent-program-parameters
and one more at http://sureshvaishya.blogspot.com/.

My requirement was different. I wanted to enable/disable DATE parameters based on user input.

Refer to the screenshots to understand what the intended functionality is ..

As shown in the screen, If the value for "Include Manual Invoices" parameter is "Yes", then enable and accept values for  parameters "Invoice Txn Date From" and "Invoice Txn Date To".

If the value for "Include Manual Invoices" parameter is "No", then disable parameters "Invoice Txn Date From" and "Invoice Txn Date To".

Let's get to details, how we can implement the functionality discussed above.

I am not going to discuss other parameters, which are not related to Enabling/disabling - functionality.

Note: As you might have noticed, I intentionally removed/masked custom names while taking screenshots.

Ex: Conc. Program Name, Application Name etc.

Steps:

1. Define controlling parameter, which can be based on any "Yes_or_No" valueset.

In my example - I used valueset called "CST_SRS_SYS_YES_NO".

2. Define parameter - P_HIDDEN_PARAM. As it's name suggests, this is a hidden parameter, which will not be visible when we submit the conc. program.

This parameter is directly dependant on the previous parameter - "Include Manual Invoices"

Set it's Default Type as  "SQL Statement " and

Default value as - "select decode(:$FLEX$.CST_SRS_SYS_YES_NO,'Y','Y',null) from dual".

3. Define custom valueset, which will used for the date parameters - "Invoice Txn Date From" and "Invoice Txn Date To".

- Enter valueset name , Select Format Type as Standard Date. Refer to the screenshot below for other details.

a) Click on Edit Information and enter forllowing query in Table Name field.

(SELECT (TO_DATE (SYSDATE - 200 + LEVEL, 'DD-MON-RRRR')) date_range FROM DUAL CONNECT BY LEVEL <= 1000)temp_tab

I am using "CONNECT BY LEVEL" concept to produce dates with the range of 1000 days.
You can modify 1000 to any other figure as per your requirement.
b) Set Value Name as date_range, which is nothing but the alias name in the query above.
c) Eter following WHERE clause in Where/Order By field.

 WHERE :$FLEX$.AMS_SRS_CHAR1='Y'

4. Attach newly created Value Set for the date parameters - "Invoice Txn Date From" and Invoice "Txn Date To" as shown below.

Save your changes and test it. If you have done all the setups correctly, you will see "Enabling/disabling" functionality something like this..

Write to me if you have any questions.

Distinct your data in PL/SQL tables

Question: How do you eliminate duplicate values from a PL/SQL table.

Answer: It is easy to select unique values from database table, with the help of SELECT DISTINCT query.
But if you have some data in PL/SQL table, then it is very difficult to eliminate duplicate records.

But Oracle 10g made it easy to filter out duplicate data from PL/SQL table or Nested table as well.
This is possible in 10g with a concept called - MULTISET 

See following example..
-------
declare
type nested_typ is table of varchar2(200);
v_test_type nested_typ := nested_typ();
v_temp nested_typ ;
begin
v_test_type.extend(8);
-- read values into array.
v_test_type(1) := 'NEW YORK';
v_test_type(2) := 'SYDNEY';
v_test_type(3) := 'SINGAPORE';
v_test_type(4) := 'PERTH';
v_test_type(5) := 'NEW YORK';
v_test_type(6) := 'NEW YORK';
v_test_type(7) := 'DELHI';
v_test_type(8) := 'PERTH';

dbms_output.put_line('Cities before distinct..'||chr(10)||'------------------------');
-- display values before distinct..
for i in v_test_type.first..v_test_type.last
loop
dbms_output.put_line(v_test_type(i));
end loop ;
-- assign all values to v_temp, which are in v_test_type
v_temp := v_test_type ;
-- take ditinct values out of v_temp, v_test_type into v_test_type
v_test_type := v_test_type MULTISET UNION DISTINCT v_temp ;
dbms_output.put_line(chr(10)||'...'||chr(10)||'Cities after distinct..'||char(10)||'-----------------------');
-- display values after distinct..
for i in v_test_type.first..v_test_type.last
loop
dbms_output.put_line(v_test_type(i));
end loop ;
end ;
/
out put
========
Cities before distinct..


------------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
NEW YORK
NEW YORK
DELHI
PERTH
...
Cities after distinct..
-----------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
DELHI

PL/SQL procedure successfully completed.
===
In the same way we can use other SET operations like UNION, UNION ALL, MINUS, INTERSECT on pl/sql tables.

Here is another way of doing it..


DECLARE
            presidents_t person_names_t ;
            presidents_t1 person_names_t ;
            cursor cur_user is select user_name from fnd_user where rownum < 5;
            cursor cur_user1 is select user_name from fnd_user where rownum < 10;

BEGIN
    open cur_user ;
    fetch cur_user bulk collect into presidents_t ;
    close cur_user ;
  
     open cur_user1 ;
    fetch cur_user1 bulk collect into presidents_t1 ;
    close cur_user1 ;

          
    FOR rec IN (SELECT column_value
                  FROM TABLE (CAST (presidents_t AS person_names_t))
                          union
                        SELECT column_value
                  FROM TABLE (CAST (presidents_t1 AS person_names_t))
                 )
        LOOP
            DBMS_OUTPUT.PUT_LINE(rec.column_value);
        END LOOP;
END;
/

Write to ".out" file from shell script.

Question: How to write messages to FND OUT file.?
Ans:
Here are the steps, to write something into fnd out file.
In shell script what ever we echo something that will be written in log file. This is expected behaviour.!
To write some content into ".out" file, one need to do it explicitly.
Here are the steps.
1. findout out file name. which is --> o.out
2. findout out directory name. Mostly $APPLCSF/out or something like this.
3. write the intended content/text to ".out" file.
4. if you are able to write text in .out file correctly, then same can be seen from "View Output" button as soon as concurrent program is completed.

Sample code:
# do sed on f2 and add 'o' and '.out' as suffix and prefix.
OUTFILE_NAME=o`echo $1 cut -d" " -f2 sed 's/FCP_REQID=//g' sed 's/"//g' `.outecho $OUTFILE_NAME
# any other code here ..
# finally write text in to outfile. In my case, I am writing contents of temp.txt to out file.
cat temp.txt >> $APPLCSF/out/$OUTFILE_NAME

..