Infolinks

Showing posts with label EBS. Show all posts
Showing posts with label EBS. Show all posts

Saturday, 21 July 2012

Naming convention of oracle apps DB objects

Naming convention of oracle apps DB objects


Following is the naming convention that oracle apps uses on its Database objects:

Object name ending with
Description of the Object
_B
The Main base tables
_ALL
Contains multi-org data
_V
View created on base table
_TL
Table that supports multi language
_VL
View created on multi language tables
_F
Data tracking tables. Used in HRMS
_S
Sequence related table
_DFV / _KFV
The DFF/KFF table created on the base table

Oracle Function to convert number to words

Oracle Function to convert number to words

Often you get the requirement to convert amount to words. This requirement mainly comes in cheque printing.


Give amount as parameter
Example
SQL> SELECT AMOUNT_TO_WORDS(155221.25) FROM DUAL;

AMOUNT_TO_WORDS(155221.25)
-------------------------------------------------------------------------------
One lakh fifty-five thousand two hundred twenty-one rupees and twenty-five paise only


CREATE OR REPLACE FUNCTION AMOUNT_TO_WORDS (P_AMT  IN NUMBER)                                       
RETURN VARCHAR2 IS
    M_MAIN_AMT_TEXT      VARCHAR2(2000) ;
    M_TOP_AMT_TEXT       VARCHAR2(2000) ;
    M_BOTTOM_AMT_TEXT    VARCHAR2(2000) ;
    M_DECIMAL_TEXT       VARCHAR2(2000) ;
    M_TOP                NUMBER(20,5) ;
    M_MAIN_AMT           NUMBER(20,5) ;
    M_TOP_AMT            NUMBER(20,5) ;
    M_BOTTOM_AMT         NUMBER(20,5) ;
    M_DECIMAL            NUMBER(20,5) ;
    M_AMT                NUMBER(20,5);
    M_TEXT               VARCHAR2(2000) ;
    BEGIN
       M_MAIN_AMT        := NULL ;
       M_TOP_AMT_TEXT    := NULL ;
       M_BOTTOM_AMT_TEXT := NULL ;
       M_DECIMAL_TEXT    := NULL ;
      
       -- To get paise part
       M_DECIMAL    := P_AMT - TRUNC(P_AMT) ;
      
       IF M_DECIMAL >0 THEN
       M_DECIMAL := M_DECIMAL *100;
       END IF;
      
       M_AMT        := TRUNC(P_AMT) ;         
    
    
       M_TOP        := TRUNC(M_AMT / 100000) ;
       M_MAIN_AMT   := TRUNC(M_TOP / 100);
       M_TOP_AMT    := M_TOP - M_MAIN_AMT * 100 ;
       M_BOTTOM_AMT :=  M_AMT - (M_TOP * 100000) ;
    
      IF M_MAIN_AMT > 0 THEN
          M_MAIN_AMT_TEXT := TO_CHAR(TO_DATE(M_MAIN_AMT,'J'),'JSP') ;
          IF M_MAIN_AMT = 1 THEN
            M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORE ' ;
          ELSE
            M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORES ' ;
          END IF ;
       END IF ;
    
       IF M_TOP_AMT > 0 THEN
          M_TOP_AMT_TEXT := TO_CHAR(TO_DATE(M_TOP_AMT,'J'),'JSP') ;
          IF M_TOP_AMT = 1 THEN
            M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKH ' ;
          ELSE
            M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKHS ' ;
          END IF;
       END IF ;
       IF M_BOTTOM_AMT > 0 THEN
          M_BOTTOM_AMT_TEXT := TO_CHAR(TO_DATE(M_BOTTOM_AMT,'J'),'JSP') ;
       END IF ;
       IF M_DECIMAL > 0 THEN
          IF NVL(M_BOTTOM_AMT,0) + NVL(M_TOP_AMT,0) > 0 THEN
             M_DECIMAL_TEXT := ' AND ' || TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') || ' Paise ' ;
          ELSE
             M_DECIMAL_TEXT :=  TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') ||' Paise ';
          END IF ;
            END IF ;
       M_TEXT := LOWER(M_MAIN_AMT_TEXT || M_TOP_AMT_TEXT || M_BOTTOM_AMT_TEXT || ' Rupees' || M_DECIMAL_TEXT || ' ONLY') ;
       M_TEXT := UPPER(SUBSTR(M_TEXT,1,1))|| SUBSTR(M_TEXT,2);
       M_TEXT := ' '|| M_TEXT;
       RETURN(M_TEXT);
    
    END AMOUNT_TO_WORDS;
    /

Oracle Apps Financials period closure monitoring scripts

Oracle Apps Financials period closure monitoring scripts


Accounts Payable:
To find whether any orphan entry exist (this prevents period close)
Select *
from apps.ap_ae_lines_all
Where ae_header_id in(
Select ae_header_id

from apps.ap_ae_headers_all
Where org_id in (select organization_id
from apps.hr_operating_units
Where name like 'XYZ%')
and gl_transfer_flag='N'
And GL_TRANSFER_RUN_ID=-1
And set_of_books_id in (select set_of_books_id
from apps.hr_operating_units
Where name like 'XYZ%')
);
  
Select *
from apps.ap_ae_headers_all
Where org_id in (select organization_id
from apps.hr_operating_units
Where name like 'GEMSAS%')
and gl_transfer_flag='N'
And GL_TRANSFER_RUN_ID=-1
And set_of_books_id in (select organization_id
from apps.hr_operating_units
Where name like 'XYZ%');

If the above 2 queries returns row, run the AP transfer to GL. Re-run the query. If still it returns rows, then check whether the accounting_event_id exists in ap_accounting_events_all. If not, then these are orphan entries and needs to be deleted.


To check whether there are any transactions accounted with error or accounted but not getting transferred to GL (this prevents period close)

select 'AP Accounting Errors(This Needs Your Attention)',
         ouname ,
         count (*),
         null,
         ou ,
         aph.ae_header_id
from
       (select hrou.NAME ouname,
                   apl.AE_LINE_ID,
                   aph.ae_header_id,
                   aph.ORG_ID ou,
                   apl.GL_TRANSFER_ERROR_CODE,
                   apl.ACCOUNTING_ERROR_CODE,
                   aph.GL_TRANSFER_RUN_ID 
       from   apps.ap_ae_headers_all aph,
              apps.AP_AE_LINES_ALL apl,
              apps.hr_operating_units hrou,
              gl.gl_sets_of_books sob
       where   sob.SET_OF_BOOKS_ID = aph.SET_OF_BOOKS_ID
               and sob.SET_OF_BOOKS_ID=hrou.SET_OF_BOOKS_ID
               and aph.AE_HEADER_ID = apl.AE_HEADER_ID
               and aph.ORG_ID=hrou.ORGANIZATION_ID
               and aph.PERIOD_NAME =to_char(sysdate,'MM-MON-YY')
   and (apl.GL_TRANSFER_ERROR_CODE is not null or         apl.ACCOUNTING_ERROR_CODE is not null) )
group by ouname,ou, ae_header_id;

To find any payment batch that is either not confirmed or cancelled (this prevents period close)

select a.CHECKRUN_NAME , 
 a.status ,
 a.org_id,b.NAME
from
 apps.ap_inv_selection_criteria_all a,
 apps.hr_all_organization_units b
where  status not in (‘CANCELED’,’CONFIRMED’,’QUICKCHECK’)
 and a.org_id=b.organization_id

To find whether all future dated payment batches are swept to the next period. 
These does not prevent period close but causes no accounting for these checks for Maturity event. Need data fixes in these cases to update the accounting date to current period and then account those.


Select * from apps.ap_payment_history_all
Where org_id in (select organization_id from apps.hr_operating_units
Where name like ‘GEMSAS%’)
And posted_flag = 'N'
And accounting_event_id is null;

Account Receivable:
To find any unposted items. All these prevents period close
select FCR.REQUEST_ID
,substr(resp.RESPONSIBILITY_NAME ,1,50) RESPONSIBILITY_NAME
,TO_CHAR(fcr.REQUESTED_START_DATE, 'DD-MON-YYYY HH24:MI:SS')
,fcr.REQUEST_DATE
, SUBSTR(USERS.DESCRIPTION,1,30) USER_NAME
,SUBSTR(FCP.USER_CONCURRENT_PROGRAM_NAME,1,55) program_name
FROM
APPS.FND_CONCURRENT_REQUESTS fcr,
APPS.FND_CONCURRENT_PROGRAMS_TL fcp,
fnd_user users,
fnd_responsibility_tl resp
where
fcr.PHASE_CODE =  'C' AND --  COMPLETED ;'P' AND --Pending
fcr.STATUS_CODE = 'C'  AND -- NORMAL ;'I' AND --Incomplete
fcr.RESPONSIBILITY_ID = resp.RESPONSIBILITY_ID AND
FCR.REQUESTED_BY = USERs.USER_ID AND
FCP.USER_CONCURRENT_PROGRAM_NAME  = 'Unposted Items Report from GL Transfer Program'  --'General Ledger Transfer Program'
and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID
AND fcr.REQUEST_DATE > (sysdate - 1)
order by fcr.REQUEST_DATE desc ;

To find whether any receipts are stuck in the ar_interim table. This prevents period close.         
select * from apps.AR_INTERIM_CASH_RECEIPTS_ALL;
select * from apps.AR_INTERIM_CASH_RCPT_LINES_ALL;

Others:
  1. Check the ar_interface before closing the periods and ensure that you run auto invoice for all the orgs. This can be triggered through an SRS Engine request set.
  2. Before running the postings, ensure that the GL interface errors, if any, are cleared.
  3. Ensure that the Future Payment Maturity Date Report is run before the AP chain runs.
General Ledger
Monitor regularly the gl_interface table for any gl unposted records. If anything is stuck with Error message, follow-up with the users to clear them up.

Selecting multiple values in value set in Custom Form

Selecting multiple values in value set in Custom Form

While developing a custom form there may be need for selecting multiple values in List of Values which is not possible with the standard list of values.
This blog post details the steps involved in selecting multiple values in a value set in custom forms? It is not possible with the standard list of values to select multiple values. You can do this by creating your own LOV in forms.
Steps Involved:
Creation of Block
Create a non database block set the properties as:
Database Data Block -- No
Subclass Information -- Block 
Items in Block
Create two list Items named as list1, list2 set the properties as follows
Two list items list1 and list2
List1 List Style = TList
Visible = Yes
Elements in List => put one item with space
List2 List Style = TList
Visible = YES
Elements in List => put one item with space
2 Buttons ok and cancel
2 parameters for x-Position and y-position Record group
Attaching to canvas
Create a Canvas and attach all the Items defined in the block to canvas.
Select all the items by holding shift button open the property inspector and select the canvas name which you have created so that all items will be attached to canvas.
Attaching Canvas to Window
Create a Window and set the properties as
Subclass Information -- Window
Primary Canvas – Canvas name you have created
Once canvas is attached to window now go to canvas property inspector and check
Whether the same window is attached to canvas else attach your created Window.
Now Open your canvas and Rearrange your items and give proper labels for items.
Triggers Involved
• When new block instance
When list changed
• When-Button-Pressed
• Key-listval
When new block instance:
DECLARE
Error_Flag number;
v_record_group_id RECORDGROUP;
v_query varchar2(5000);
BEGIN
/*Deleting data in list items*/
Clear_List('list1');
Clear_List('list2');
/* Calling the Record Group which we have created in form*/
Error_Flag := POPULATE_GROUP('TPL_LOV_REC_RG');
IF Error_Flag = 0 THEN
v_record_group_id := FIND_GROUP ('TPL_LOV_REC_RG');
/* Query for populating values dynamically*/
v_query:=
’SELECT receipt_num, receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id IN (SELECT shipment_header_id
FROM rcv_shipment_lines
WHERE po_header_id
=:xxtpl_swp_hdr_b.po_header_id)
AND shipment_header_id NOT IN (SELECT shipment_header_id
FROM xxtpl_payinv_b
WHERE main_header_id = :xxtpl_swp_hdr_b.main_header_id
AND shipment_header_id IS NOT NULL)';
Error_Flag := populate_group_with_query(v_record_group_id,v_query);
/*Assigning values dynamically to list1*/
POPULATE_LIST('list1',v_record_group_id);
ELSE
FND_MESSAGE.SET_STRING('Error while populating list group');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
When list changed of List1:
DECLARE
total_list_count NUMBER;
i NUMBER;
double NUMBER;
BEGIN
double := 0;
i := 1;
total_list_count := Get_List_Element_Count('XXTPL_LOV_B.list2') + 1;
WHILE i < total_list_count LOOP
/* Deletion of Element by Multi Selection in selected list*/
IF :list1 = Get_List_Element_value('XXTPL_LOV_B.list2',i) THEN
double := 1;
delete_list_element('XXTPL_LOV_B.list2',i);
END IF;
EXIT when double = 1;
i := i + 1;
END LOOP;
IF double = 0 THEN
total_list_count := Get_List_Element_Count('XXTPL_LOV_B.list2');
total_list_count := total_list_count + 1;
/* adding the selected value to list2,so that list2 contains all selected values*/
ADD_LIST_ELEMENT('XXTPL_LOV_B.list2',total_list_count,:list1,:list1);
END IF;
END;
When Button Pressed: OK Button
DECLARE
total_list_count NUMBER;
i NUMBER;
my_elements VARCHAR2(250);
header_id NUMBER;
ln_hdr VARCHAR2(2000);
receipt_no VARCHAR2(30);
BEGIN
i := 1;
total_list_count := Get_List_Element_Count('XXTPL_LOV_B.list2') + 1;
/* Retrieving all elements that are selected and concatinating to Global variable*/
WHILE i < total_list_count LOOP
my_elements := my_elements ||
','||Get_List_Element_value('XXTPL_LOV_B.list2',i);
i := i + 1;
END LOOP;
/* populating the concatenated string into field where you have to show multiple
values*/
:XXTPL_QUERY.Q_RECEIPT_NUM := substr(my_elements,2);
/*Making the window invisible when selected values gets populated*/
SET_WINDOW_PROPERTY('TPL_LOV',VISIBLE,PROPERTY_FALSE);
/* Navigation to called Window*/
GO_ITEM('XXTPL_QUERY.Q_RECEIPT_NUM');
END; 
When Button Pressed : Cancel Button:
/*Making the window invisible when selected values gets populated*/
SET_WINDOW_PROPERTY('TPL_LOV',VISIBLE,PROPERTY_FALSE);
/* Navigation to called Window*/
GO_ITEM('XXTPL_QUERY.Q_RECEIPT_NUM');
Key-Listval for the Item Where Multi values Have To Be Selected
DECLARE
pl_id PARAMLIST;
pl_name VARCHAR2(10) := 'tempdata';
x_position NUMBER;
y_position NUMBER;
BEGIN
/* Get X,Y Position of item for positioning Window Multiple values selction*/
x_position := GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM,X_POS);
y_position := GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM,Y_POS);
SET_WINDOW_PROPERTY('TPL_LOV',VISIBLE,PROPERTY_TRUE);
SET_WINDOW_PROPERTY('TPL_LOV',X_POS,X_POSITION);
SET_WINDOW_PROPERTY('TPL_LOV',Y_POS,Y_POSITION+0.5);
/* Called Window*/
GO_BLOCK('XXTPL_LOV_B');
END;

Comparison between XML Publisher and Oracle Reports

Comparison between XML Publisher and Oracle Reports

In Oracle Reports, a single executable RDF file contains the data source, layout, business logic, and the language-specific prompts. However, BI Publisher separates these components into different layers. Following are some of the key limitations of Oracle Reports that are overcome by BI Publisher:

     · Multiple layouts If you wish to publish the data from a report in more than one format—for example, in both an Excel and HTML layout—then multiple RDF files have to be prepared. If any bug fixes are required, fixes will have to be applied to both RDF files.
     · Multiple language Global implementations for a large organization usually require the same report to be developed in multiple languages. In case of Oracle Reports, developers will have to create different copies of the same RDF files and place them in the <application top>/reports/<language> directory. In case of any bug fixes to data or any changes to layout, all the copies of reports have to be amended.
     · Trivial tasks Even trivial tasks like changing the prompt or minor changes to the layout require a developer in Oracle Reports. Using BI Publisher, these tasks can be implemented by trained support staff or business analysts.
     · Distribution The distribution capabilities in the Oracle Reports tool within E-Business Suite are very restrictive. To distribute the output of Oracle Reports, many companies implementing E-Business Suite have to purchase third-party products or even build their own in-house reports distribution framework. With BI Publisher, a mechanism named bursting can automate distribution of the reports using industry standard delivery protocols.
      · Securing the output When distributing the output of Oracle Reports in E-Business Suite, it is not possible to secure the output using a password. BI Publisher facilitates password protection of the documents.

Due to the previously listed limitations, the cost of customization and maintenance is very high for Oracle Reports in Multilanguage implementations. In the case of BI Publisher, the data layer, presentation layer, and the translation layer are separate. Additionally, BI Publisher is based on open standards and thus overcomes many of the limitations of Oracle Reports. In addressing some of the limitations of Oracle Reports and adopting open standards, Oracle has allowed implementations to potentially reduce the overall cost of build, customization, and ongoing maintenance of reports.

Working with FNDLOAD in Oracle Apps Introduction to FNDLOAD FNDLOAD is an oracle utility used to download AOL components data from an application entity into an editable text file, which can be uploaded to another database or the same database. This utility is extensively used to download any AOL components like the Concurrent Programs, Value Sets, DFF, Responsibilities, Request groups etc. The downloaded components can then be easily uploaded into any other database or the same database as per the requirement. This is particularly useful during the Instance refresh or patching or during installations. The major components of the FNDLOAD are: Configuration File (.lct file): The configuration file is an Oracle seeded file which is provided by the oracle. This file is used to upload or download the AOL components into the database. The Conversion between database format and text file format is specified by a configuration file. The .lct file or the configuration file is specific to each type of AOL component that is under question. Each type of AOL component has a different configuration file. These are found in the location ‘$FND_TOP/patch/115/import’. The exhaustive list of .lct files available in oracle for different AOL entities is: Oracle Applications Datafile or .ldt file: The .ldt file is the data file in oracle applications which contains the downloaded data from the fndload. When we download an AOL component from the database, we have to give the .ldt file. This will hold the downloaded entity. Process: The Generic Loader downloads data from a database according to a configuration (.lct) file, and converts the data into a data file i.e. (.ldt file). The Generic Loader can then upload this data to another database using a configuration file. The loader operates in one of two modes: Download or Upload. In the download mode, the AOL component like the Concurrent Program is downloaded from the database in the form of an .ldt file. In the upload mode, AOL component is uploaded from a .ldt file to the database. FNDLOAD is designated to transfer Oracle Applications "setup" entities (concurrents, flexfields, menues etc.) FNDLOAD can't be used to transfer forms, reports etc. FNDLOAD Options: • Application level OR single entity level download This downloads all the objects at the application level. (Example) Download all the profile options of Inventory or just the INV: Default Primary UOM • Entire OR Partial upload of a data file This is used to upload just an individual entity or all entities in the .ldt file (Example) Upload the entire myfile.ldt or just a single enity indicated by - and mode UPLOAD or UPLOAD_PARTIAL Entity name required for UPLOAD_PARTIAL mode • Custom mode force update To override Oracle's upload algorithm and update the custom AOL data regardless, use CUSTOM_MODE= FORCE UPLOAD_MODE= REPLACE (only for menus) • Support for NLS uploads NLS data uploads for translated columns are supported, use UPLOAD_MODE= NLS You can give the FNDLOAD commands by logging in to the server. We have to use only the ‘APPS’ user for FNDLOAD.

Working with FNDLOAD in Oracle Apps

Introduction to FNDLOAD
FNDLOAD is an oracle utility used to download AOL components data from an application entity into an editable text file, which can be uploaded to another database or the same database.
This utility is extensively used to download any AOL components like the Concurrent Programs, Value Sets, DFF, Responsibilities, Request groups etc. The downloaded components can then be easily uploaded into any other database or the same database as per the requirement.
This is particularly useful during the Instance refresh or patching or during installations.

The major components of the FNDLOAD are:
Configuration File (.lct file):
The configuration file is an Oracle seeded file which is provided by the oracle.
This file is used to upload or download the AOL components into the database.
The Conversion between database format and text file format is specified by a configuration file.
The .lct file or the configuration file is specific to each type of AOL component that is under question.
Each type of AOL component has a different configuration file.
These are found in the location ‘$FND_TOP/patch/115/import’.

The exhaustive list of .lct files available in oracle for different AOL entities is:


Oracle Applications Datafile or .ldt file:
The .ldt file is the data file in oracle applications which contains the downloaded data from the fndload.
When we download an AOL component from the database, we have to give the .ldt file.
This will hold the downloaded entity.

Process:
The Generic Loader downloads data from a database according to a configuration (.lct) file, and converts the data into a data file i.e. (.ldt file). The Generic Loader can then upload this data to another database using a configuration file.
The loader operates in one of two modes: Download or Upload.
In the download mode, the AOL component like the Concurrent Program is downloaded from the database in the form of an .ldt file.
In the upload mode, AOL component is uploaded from a .ldt file to the database.
FNDLOAD is designated to transfer Oracle Applications "setup" entities (concurrents, flexfields, menues etc.)
FNDLOAD can't be used to transfer forms, reports etc.

FNDLOAD Options:
• Application level OR single entity level download
  • This downloads all the objects at the application level.
  • (Example) Download all the profile options of Inventory or just the INV: Default Primary UOM
• Entire OR Partial upload of a data file
  • This is used to upload just an individual entity or all entities in the .ldt file
  • (Example) Upload the entire myfile.ldt or just a single enity indicated by - and mode UPLOAD or UPLOAD_PARTIAL
  • Entity name required for UPLOAD_PARTIAL mode
• Custom mode force update
  • To override Oracle's upload algorithm and update the custom AOL data regardless, use CUSTOM_MODE= FORCE
  • UPLOAD_MODE= REPLACE (only for menus)
• Support for NLS uploads
  • NLS data uploads for translated columns are supported, use UPLOAD_MODE= NLS
You can give the FNDLOAD commands by logging in to the server.
We have to use only the ‘APPS’ user for FNDLOAD.