How to validate previous record's properties when user tries to enter new record in to the same data block without entering and saving the previous record and it's properties(data for child blocks)

Hi fellas,

This is just for new guys to oracle.
                                           I got an oracle form which has 6 data blocks based on 6 separate tables and form has 3 tabs. From first data block to last block, there is a connection between the particular block and it's previous block. Its like second block has a relation with first block, third block has a relation with second block, fourth block has a relation with third block and like wise. Since all these blocks based on database tables when tab pages changes changes will be inserted into the relevant tables.
                                           
                                           The form i had has data enter process like below.

First block has the master record. This is only one record per one round.
Second block is tabular one and can have multiple records per round. Record can be a item which should have serial numbers or may be a item which shouldn't have serial numbers. Third block based on this concept.
If the second block has serialized item then the third block should have relevant serial numbers accordingly.
Since the data blocks based on tables when tab pages changed relevant input data will get inserted in to tables.
The problem i had was when user create the first record on the second block, user used serialized item and  without entering relevant serial numbers in to third block, he tries to add a second record in to the second block. I have restrict the user in this scenario and navigate him / her to enter serial numbers when tries to enter the  second record in the second block without entering serial numbers for the first record.
                                     all the input data will get inserted in to the tables when tab pages changed but wont get committed until press the save button in the end of the process. Therefore i couldn't check for the serial numbers from the database table. What i did for this situation was i maintain a variable to hold the value of :system.cursor_value. When ever user tries to create a new record in the second block ( :system.cursor_value null because user tries to create a new record. ) i navigate to the previous record, checked whether the previous record is a serialized item and if so i checked the third block's values. If the third block doesn't have any data then i just redirect the user to the third block and forced him / her to enter the serial numbers. If the previous record has the relevant serial numbers in the third block or the previous record is a non serialized item then navigate to the second block and create a new record by using create_record built in.
I just put my code here and you can get the idea by going through that. Leave the additional conditions here  which are unique to my form and it's functionality. Just concentrate on the codings in red color...


DECLARE
    
    ml_new_cursor_rec NUMBER;   
    ml_value VARCHAR2(50);
    
BEGIN
    
    ml_new_cursor_rec := :system.cursor_record;
    ml_value := :system.cursor_value;
    
    IF ml_value IS NULL THEN 
        previous_record;
    
        IF FU_SERIAL_NO_ITEM THEN
        
            IF :stl_adjust_qty != 0 AND :stl_adjust_qty IS NOT NULL AND :SVS_SERIAL_NO IS NULL THEN
                err_msg(3620,2);
            
                set_tab_page_property('ADJUSTED_DETAILS',enabled,property_true);
                set_tab_page_property('APPROVEL_INFORMATION',enabled,property_true);
            
                IF NVL(:ic_stk_take_lines.stl_adjust_qty,0) > 0 THEN 
                    :IC_STK_VARIANCE_LINES.SVL_VARIANCE_TYPE := 'R';
                ELSIF NVL(:ic_stk_take_lines.stl_adjust_qty,0) < 0 THEN
                    :IC_STK_VARIANCE_LINES.SVL_VARIANCE_TYPE := 'I';
                END IF;
            
                :ic_stk_variances.stv_date            := sysdate; 
                :ic_stk_variance_lines.svl_item_code  := :ic_stk_take_lines.stl_item_code;
                :ic_stk_variance_lines.fc_item_desc   := :ic_stk_take_lines.fc_item_desc;
                :ic_stk_variance_lines.svl_adjust_qty := NVL(:ic_stk_take_lines.stl_adjust_qty,0);
                :ic_stk_variance_lines.svl_rack_no    := :ic_stk_take_lines.stl_rack_no;
                :ic_stk_variance_lines.svl_bin_no     := :ic_stk_take_lines.stl_bin_no;
                        
                :FC_SUPPESS_VALIDATE_FLAG := 'Y';
                SELECT SYSDATE INTO :IC_STK_VARIANCES.STV_DATE FROM DUAL; 
                go_item('ic_stk_variance_serials.svs_serial_no');
                first_record;
        
                IF :ic_stk_variance_serials.svs_serial_no IS NULL THEN
     
                    LOOP
        
                        EXIT WHEN :ic_stk_variance_serials.svs_serial_no IS NULL;
            
                        DELETE FROM ic_stk_variance_serials
                        WHERE svs_voucher_no = :ic_stk_variances.stv_voucher_no
                        AND svs_item_code  = :ic_stk_take_lines.stl_item_code
                        AND svs_serial_no  = :ic_stk_variance_serials.svs_serial_no;
                 
                        EXIT WHEN :system.last_record = 'TRUE'; 
                 
                        next_record;
                 
                    END LOOP;
            
                ELSE  
                    go_block('IC_STK_TAKE_LINES');  
                    create_record;  
        
                END IF;  
        
            ELSE
                go_block('IC_STK_TAKE_LINES');  
                create_record;  
        
            END IF;
        
        ELSE
            go_block('IC_STK_TAKE_LINES');  
            create_record;
        
        END IF;
        
    END IF;
    
END;

Happy coding.....:)

How to get a selected part of a string separate from the back end of the given string in oracle forms

Hi fellas,

Last week i got a task to write a sql statement to select a particular part of a string from given string. The part of the string i need to get separate is in the back end of the original string. Actually given string is path where valuable text document stored in the server. File name is included in this path from the back end. To process the file they need to read the full path from the database and get the file name separated from the original path string.
Actual string is like this,
                       Main_Folder\folder_1\Folder_2\textfile.txt

Every folder is separated by '\' sign and number of folders in string can be different by time to time.Apart from that, the length of the file name can be very. Therefore we cannot predict the length of the original file path and length of the file name as well.My task is to read the original string from back end and cut the string when first '\' sign meets.

To read the string from the back, I had to reverse the given string. We can use REVERSE key word for this. But normally 10g oracle forms doesn't support all the key words in sql. Therefore forms doesn't support for REVERSE as well. Forms doesn't recognize REVERSE as a key word.Therefore we have to use UTL package along with the reverse key word (only for forms).

Lets imagine the original string is like "Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt"
First of all I had to reverse the original string and then i can read it until I can find the first '\' sign. Relevant statement is,
  SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
  ('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL;

Output will be "txt.eliFtxeT\redlof_buS\2_redloF\1_redlof\redloF_niaM"

Then I had to read the string till I can find the position of the first '\' sign. Relevant statement is,
SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
  ('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))),'\')-1 FROM DUAL;

Output will be "12".

Therefore now I know that i have to substring the given string from 12th position to get only the file name separated. The relevant statement is,
SELECT SUBSTR((SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
  ('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL,0,(SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
  ('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) ,'\')-1 FROM DUAL)) FROM DUAL;

Output will be "txt.eliFtxeT".

Now I had to reverse the separated file name to get the original file name. For this process I had to use REVERSE statement again for the above mentioned output.

For the whole process, relevant statement is like below.

SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW((SELECT SUBSTR((SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
  ('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL,0,(SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
  ('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) ,'\')-1 FROM DUAL)) FROM DUAL)))) FROM DUAL;

When we use this in the form, all the SELECT key words and FROM DUAL key words should be left.
Final output of the above mentioned statement will be,
"TextFile.txt".

I used INSTR to calculate the position of the first '\' sing in the reversed string and we must reduce 1 from that position number to get only the file name.

Use SSL in ASP.NET MVC3 Applcation

Hi fellas,

                                 SSL stands for Secure Socket Layer. Normally we use SSL in our web applications. We use it to secure the message transmission on the internet. This will secure the information we transmit via the URL. Different MVC applications use different methods to apply SSL to web application. In this section you will see how to use SSL with your ASP.NET MVC web application.

First of all we have to add related programming codes to the Web.config file of our web application. You can select the form to add this and if you want you can use this for several forms in your application. Bellow is the related XML code you should use in the Web.config file,

<forms loginUrl="~/Account/LogOn" timeout="2880" requireSSL="true"/>

In above XML code requireSSL="true" is the cording part which will take care of SSL. You can add any  related URL to the loginUrl according to your requirement. In above example it's loginUrl="~/Account/LogOn". 

After define Web.config file we have to modify the properties of our application project. to do this right click on the project and select properties.

In project properties select web tab and in that web tab  the Server properties scroll down to the Servers.

In that properties select Use Local IIS Web Server radio button. After that select Use IIS Express checkbox and Overide applicationroot URL checkbox.

Then go the project properties by double clicking on the project and you will get a window like bellow.

In the properties window set SSL Enable as True. After that copy the given URL in SSL URL and paste it on project URl and field under Overide application root URL checkbox like bellow,

Paste the copied URL like bellow,

After modify the web tab in project properties save the changes and the clean the project, Rebuild and run the project. Now your URL will include the https (SSL).

FRM-30457: Warning: Maximum Length ignored for character-datatype subordinate mirror itme

Hi fellas,

Sometimes we used Copy Value From Item property and Synchronize With Item property in oracle forms to connect separate items in same form. 

Just imagine we have two items named ITEM1 and ITEm2 and we need to use number of properties like Data Type, Maximum Length, Required property ,  Highest Allowed Value,..etc... of ITEM1 on ITEM2. In these kind of scenario, we can use Copy Value From Item property and Synchronize With Item property to apply them. 

For apply this you have to keep both items in the same data type. 

FRM-30457: Warning: Maximum Length ignored for character-datatype subordinate mirror item will appear if you assign a value for Maximum Length property in child item (in this example it's ITEM2. ITEM1 is the master item.). 

If we assign a value to Copy Value From Item property to some item, we cant assign a value to the Maximum Length property of that item because it refers the master item and will take those properties from that master item. Therefore we have to keep Maximum Length property as 0 for child item.

FRM-30443: Warning: Highest Value Allowed ignored for subordinate mirror item
and
FRM-30444: Warning: Lowest Value Allowed ignored for subordinate mirror item
will be appear if you assign values for Lowest Allowed Value and Highest Allowed Value properties of the child item. You should keep those properties (Lowest Allowed Value / Highest Allowed Value) empty for child item to avoid those above warnings.