Data not visible in _VL AND _TL tables ORACLE APPS

            HOW TO SELECT DATA FROM _VL TABLES


Some times we will not be able to see the data in _vl tables in TOAD or SQL DEVELOPER when others are able to see the same data.

The reason for this is the USERENV('LANG') is set to 'GB'

When you run query select USERENV('LANG') FROM DUAL you get 'GB'

Actually _vl tables gets the data from _tl tables with condition USERENV('LANG')='US'


Solution:

We can change the NLS Lang parameters.

SQL Developer: Go to SQL Developer -> Tools -> Preferences..>Database ->NLS
Set the Language ='AMERICAN', Territory ='AMERICA' , Date Lanugage ='AMERICAN'

Now you can select data from _vl table


TOAD: Go to Toad -> Database ->Administer->NLS Parameters
Set the NLS_LANGUAGE='AMERICAN' ,NLS_DATE_LANGUAGE ='AMERICAN'

Now you can select data from _vl table

credit card tables in oracle apps

AP_CARD_PROGRAMS_ALL :-This is the Parent Table for the Credit Card tables

AP_CARDS_ALL :-This is the  table of the Credit Cards Holder

AP_CREDIT_CARD_TRXNS_ALL :-This table contains all the credit card transactions information's coming from the banks for the Credit cards under the Credit Card programs all.

How to Define Global Table Types in Forms to be accessed across different triggers

If you want to populate the Table Type variables and access the table types in other triggers then you have to define the Global Table Type in Program units Packages of the form.

(PKG)Package Specification definition:

TYPE gtyp_max_grp_data IS TABLE OF NUMBER  INDEX BY VARCHAR2 ( 100 );

(PKG)Package Body Definition:

CURSOR C1 IS select max(emp_id) from EMP where deptno=100;

BEGIN
   FOR REC1 IN C1 LOOP

--REC1 .emp_name||REC1.EMP_ID-- it is the unique index (i) of the array variable like 
    gt_cache_max_grp_data(i)
     gt_cache_max_grp_data( REC1 .emp_name||REC1.EMP_ID) := REC1.emp_id;

END LOOP;

END;

How to access this table type in another trigger?

l_max_emp_id  :=  PKG.gt_cache_max_grp_data ( :blk_emp.emp_name|| PKG.g_cahched_value_delimiter || :blk_emp.emp_id );


Now the variable l_max_emp_id contains the maximum group id that was populated in the program unit package and now that value we are able to access in when-checkbox-changed trigger or any other trigger

The Unix Host Program is ending in ERROR with file permissions issue

Cause of the Problem:

The reason for the Unix Host Program ending in ERROR, even after providing the permissions to the file is because of the control+M characters added to the unix file when you open it in notepad++.

Solution

1.  Navigate to the Edit menu -> EOL Conversion -> Windows.
2. Save the file .
3. Move the file and run the program , the issue will be resolved