Oracle Financial Statement Generator Report Basics

Financial Statement Generator (FSG) is a powerful report building tool for Oracle General Ledger.

1. Generate financial reports, such as income statements and balance sheets, based upon data in your general ledger.
2. Define segment value security rules to restrict financial information contained in FSG report output generated by specific users and responsibilities.

FSG Report uses the fundamental row/column concept to build the financial reports,a dn follow the below guidelines while defining a new report:
1. Decide which rows and columns will make up your report.
2. Define the rows and columns, and then tell FSG what attributes those rows and columns have.
3. Build a report using those rows and columns.

More than one row can be grouped to a Rowset and similarly more than once column can be grouped to a Column Set, and can be re-used in another reports.

Row sets and column sets are the two primary building blocks of FSG reports.

Example to create an FSG Report:

Create a Sample Rowset as below,

GL -> Reports -> Define -> Row Set

Click on Define Rows

Click on Account Assignments and define the ccounts for which you want to generate the Row details for,

 

 Activity can contain Net Dr, Cr values

Create a Column set as below,

GL -> Reports -> Define -> Column Set

Click on Define Columns

You can as well define Account Assignments at the Column level as well to control the data and values

Define Column Set with Column Headings as below,

Define a Financial Report as below,

GL -> Reports -> Define -> Report

Run the Report(Run Report from Report Definition screen), and it executes the following Query:

SELECT nvl(bal.PERIOD_TYPE, ''),
    nvl(bal.PERIOD_YEAR, -1),
    bal.PERIOD_NAME,
    nvl(bal.PERIOD_NUM, -1),
    nvl(bal.PERIOD_NUM, -1),
    bal.ACTUAL_FLAG,
    decode(cc.TEMPLATE_ID, NULL, 'N', 'Y'),
    -1,
    nvl(bal.BUDGET_VERSION_ID, -1),
    -1,
    nvl(bal.ENCUMBRANCE_TYPE_ID, -1),
    bal.CURRENCY_CODE,
    bal.SET_OF_BOOKS_ID,
    nvl(bal.TRANSLATED_FLAG, ''),
    nvl(bal.PERIOD_NET_DR, 0) - nvl(bal.PERIOD_NET_CR, 0),
    nvl(bal.PERIOD_NET_DR, 0),
    nvl(bal.PERIOD_NET_CR, 0),
    nvl(bal.QUARTER_TO_DATE_DR,0)-nvl(bal.QUARTER_TO_DATE_CR, 0),
    nvl(bal.QUARTER_TO_DATE_DR, 0),
    nvl(bal.QUARTER_TO_DATE_CR, 0),
    nvl(bal.BEGIN_BALANCE_DR, 0) - nvl(bal.BEGIN_BALANCE_CR, 0),
    nvl(bal.BEGIN_BALANCE_DR, 0),
    nvl(bal.BEGIN_BALANCE_CR, 0),
    nvl(bal.PROJECT_TO_DATE_DR, 0) - nvl(bal.PROJECT_TO_DATE_CR, 0),
    nvl(bal.PROJECT_TO_DATE_DR, 0),
    nvl(bal.PROJECT_TO_DATE_CR, 0),
    nvl(SEGMENT1,''),
    nvl(SEGMENT2,''),
    nvl(SEGMENT3,''),
    nvl(SEGMENT4,''),
    nvl(SEGMENT5,''),
    nvl(SEGMENT6,'')
FROM GL_BALANCES bal,
    GL_CODE_COMBINATIONS cc
WHERE bal.CODE_COMBINATION_ID = cc.CODE_COMBINATION_ID
AND   cc.CHART_OF_ACCOUNTS_ID = 50286
AND   bal.SET_OF_BOOKS_ID  = 1
AND   nvl(bal.TRANSLATED_FLAG, 'x')in ('Y','N','x')
AND   cc.TEMPLATE_ID is NULL
--This is due to the Account assignments defined at row level
AND ( (nvl(SEGMENT2,'') >= '23000' AND  nvl(SEGMENT2,'') <= '23001')
)
--This is due to PTD and YTD Columns defined for the Column Set level
AND    (
(bal.period_name in ('Adj-14','Nov-13')
AND     (    (nvl(bal.period_name,'') = 'Adj-14'
AND    (    (bal.CURRENCY_CODE = 'GBP'
        AND    bal.ACTUAL_FLAG = 'B'
        AND    bal.BUDGET_VERSION_ID = 0
)
 OR
 (    (bal.CURRENCY_CODE = 'GBP'
        AND    bal.ACTUAL_FLAG = 'B'
        AND    bal.BUDGET_VERSION_ID = 0
        AND    ((nvl(bal.PERIOD_NET_DR,0) != 0) or (nvl(bal.PERIOD_NET_CR,0) != 0))) ))) OR        (nvl(bal.period_name,'') = 'Nov-13'
AND    (    (bal.CURRENCY_CODE = 'GBP'
        AND    bal.ACTUAL_FLAG = 'B'
        AND    bal.BUDGET_VERSION_ID = 0
        AND    ((nvl(bal.PERIOD_NET_DR,0) != 0) or (nvl(bal.PERIOD_NET_CR,0) != 0))))))
) )

Print This Post

 

Journals/Journal Lines created with ‘Journal Import Created’

By Default Oracle creates Journal/Journal Line Reference fields with ‘Journal Import created’ text and Oracle advises to use Reference fields Reference6(for Journal) and Reference10(for Journal Line) in GL_INTERFACE to be populated with the desired details to appear in the Journal/Journal line Reference fields.



Print This Post

 

Email a File from the Oracle Application Server

Below is the most useful code snippet, that can be used to read a file from the application server or any concurrent program output file from the desired location

*********************************************************

declare

l_conn           utl_smtp.connection;
l_file_handle    UTL_FILE.FILE_TYPE;

l_dirpath        VARCHAR2 (150) := '/usr/tmp/';
l_filename       VARCHAR2 (50) :=  'Test.txt';
l_sender         VARCHAR2 (50) := 'abc@gmail.com';
l_recpnt         VARCHAR2 (255):= 'xyz@gmail.com';
l_msg            VARCHAR2 (32767);
l_line           VARCHAR2 (1000);
lc_message       VARCHAR2 (1000);
crlf             VARCHAR2 (2)  := CHR (13) || CHR (10);

l_reply          UTL_SMTP.REPLY;
l_clob           CLOB := '';
l_subject        VARCHAR2(2000) := 'This is a sample test email';
l_body           VARCHAR2(2000) := 'Body email';
l_boundary       VARCHAR2(50) := '----=*#abc1234321cba#*=';
ln_amount        NUMBER := 0;
ln_temp          NUMBER := 0;
l_filedata       VARCHAR(32767);

begin

l_conn := utl_smtp.Open_Connection('127.0.0.1', 25);

utl_smtp.Helo(l_conn, '127.0.0.1');
utl_smtp.Mail(l_conn, l_sender);
utl_smtp.rcpt(l_conn, l_recpnt);
utl_smtp.open_data(l_conn);
l_filedata := l_filedata || 'Date:' || TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') || crlf;
l_filedata := l_filedata || 'To: ' || l_recpnt || crlf;
l_filedata := l_filedata || 'From: ' || l_sender || crlf;
l_filedata := l_filedata || 'Subject: ' || l_subject || crlf;
l_filedata := l_filedata || 'MIME-Version: 1.0' ||  crlf; 
l_filedata := l_filedata || 'Content-Type: multipart/mixed; boundary=' ||chr(34)||l_boundary|| chr(34)||crlf;
l_filedata := l_filedata || '--'||l_boundary|| crlf;
l_filedata := l_filedata || 'Content-Type: text/plain;'|| crlf ||
                                           'Content-Transfer_Encoding: 7bit'|| crlf ||
                                           crlf || l_body || crlf || crlf ||
                                           '--'||l_boundary|| crlf ||
                                           'Content-Type: text/plain;'|| crlf ||
                                           ' name="'||l_filename||'"'|| crlf ||
                                           'Content-Transfer_Encoding: 8bit'|| crlf ||
                                          'Content-Disposition: attachment;'|| crlf ||
                                          ' filename="'||l_filename||'"'|| crlf || crlf;

dbms_lob.createtemporary(l_clob, false, 10);
dbms_lob.write(l_clob, LENGTH(l_filedata), 1, l_filedata);

l_file_handle := UTL_FILE.FOPEN (l_dirpath, l_filename, 'R');

ln_temp := dbms_lob.getlength(l_clob) + 1;

LOOP

  BEGIN
    utl_file.get_line(l_file_handle, l_msg);
    ln_temp := dbms_lob.getlength(l_clob) + 1;
    IF DBMS_LOB.GETLENGTH(l_msg) > 0 THEN
       dbms_lob.write(l_clob, LENGTH(l_msg)+1, ln_temp, l_msg||CHR(10));
    END IF;   
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    EXIT;
  END;

END LOOP;

utl_file.fclose(l_file_handle);

ln_temp := 1;
ln_amount := 1900;

WHILE ln_temp < dbms_lob.getlength(l_clob) LOOP
   utl_smtp.write_data(l_conn, dbms_lob.substr(l_clob, ln_amount, ln_temp));
   ln_temp := ln_temp + ln_amount;
   ln_amount := least(1900, dbms_lob.getlength(l_clob) - ln_amount);
END LOOP;

utl_smtp.write_data(l_conn, crlf||crlf);
utl_smtp.close_data(l_conn);
utl_smtp.quit(l_conn);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);

end;

*********************************************************

Print This Post

 

Useful Scripts

1. Diabling all the users:

DECLARE

CURSOR c_user is
SELECT user_name
  FROM fnd_user
 WHERE TRUNC(NVL(end_date,SYSDATE)) >= TRUNC(sysdate);

type t_user_name is table of fnd_user.user_name%TYPE;
lt_user_name t_user_name;

begin

open c_user;
fetch c_user bulk collect into lt_user_name;
close c_user;

for i in 1..lt_user_name.COUNT LOOP
  fnd_user_pkg.updateuser(x_user_name => lt_user_name(i)
                         ,x_owner     => null
                         ,x_end_date  => TRUNC(sysdate-1) );
                        
  dbms_output.put_line('User Disabled:'||lt_user_name(i));
END LOOP;

COMMIT;

dbms_output.put_line('commit Complete');

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('Exception');

END;
/ 


Print This Post
 

Standing Charges: Setup and Process Overview

Standing Charges:

Standing Charges are used to Enter Standing Charge Agreements which are used as a source to control periodic invoices. The Standing Charges functionality enables the user to charge customers periodically and automatically create invoices without the need to enter duplicate information.

Standing Charges have a start date and items specified, which can be amended and update the price items.

Standing Charges Process:

The following diagram shows the Standing Charges process flow, as described in the accompanying text.

Standing Charges Setup:

Specify the Combined Basis Accounting Standing Charges details in the Extended System Options Window(Standing Charges -> Receivables Setup -> Extended System Options)
Receivables uses the transaction flexfields to uniquely identify each transaction and transaction line created by standing charges. Transactions and transaction lines are imported through AutoInvoice.

Billing and Charging Periods:

Billing periods determine the frequency of standing charge invoice creation. Charge periods determine how often items are charged to the customers.
Set up billing and charging periods before entering standing charge items and standing charge agreements.

There are four types of period component: Day, Week, Month and Year. If using a combination of components for a standing charge, the relationship must be set up in advance.

Enter Charge Items: ( Standing Charges -> Maintain Items)

Charge Items are entered in this form with the Price, Period, Tax Code, Account details and the effective datee, it also has the item price history. Each item should be made active to be included in the Standing Charge.

Enter Standing Charges: (Standing Charges -> Maintain Standing Charges):

When there is an agreement with the customer that a charge is invoiced periodically the details of the agreement can be entered in the system.

Enter Customer Name, Period, Frequency, Next Due Date(This is updated when the Standing Charge programs are run), Next GL Date.

The Synchronize Standing Charges process updates the Next GL Date and the First Date at charge line level if invoicing rules are used. The only difference is that the Next GL Date and the First Date for the accounting rule can be updated and the updated value is used for calculating the GL dates.

and then following Programs are run in an order to generate the Standing Charges periodically,

Standing Charges: Preliminary Invoice Register Report
Standing Charges: Generate Interface Data Program
AutoInvoice
Standing Charges: Synchronize Standing Charges Program



Print This Post

APP-AR-11017: Recreation of Payment schedules failed because the transaction has activity against it

The main reason that the transaction, adjustment, receipt fails validation is due to the difference in the Payment Term(term_id), exchange rate or gl_date between RA_CUSTOMER_TRX_ALL and AR_PAYMENT_SCHEDULES_ALL for a given record.

The below update statement should resolve the issue:

update AR_PAYMENT_SCHEDULES_ALL arps
   set arps.TERM_ID = (select rct.TERM_ID
                         from RA_CUSTOMER_TRX_ALL rct
                        where arps.CUSTOMER_TRX_ID = rct.CUSTOMER_TRX_ID )
where arps.CUSTOMER_TRX_ID = <trans_id> ;

to check if there are other transactions affected you can run the following SQL:

SELECT arps.payment_schedule_id,arps.term_id ps_term_id,rct.term_id ct_term_id
  FROM ra_customer_trx_all rct,
       ar_payment_schedules_all arps
 WHERE rct.customer_trx_id = arps.customer_trx_id
   AND rct.term_id <> nvl(arps.term_id,-1); 



Print This Post

Terminating an HR Employee

I have seen a few organizations sometimes end date the user rather than the employee especially when the organizations tend not to use Employee Payroll, which clearly makes sense. But the Inactive Employees have to be made Ex-Employees at some point of time for the integrity of the system and Reporting purpose.

Hereby is the script to modify Inactive Employees(personalized to the Employees without a user-login, but can also be personalized to your need) to Ex-Employees:


DECLARE

ld_last_std_process_date_out date := TRUNC(TO_DATE('23-JUL-2002', 'DD-MON-RRRR'));
l_period_of_service_id  PER_PERIODS_OF_SERVICE.period_of_service_id%TYPE;

lb_supervisor_warning               boolean;
lb_event_warning                    boolean;
lb_interview_warning                boolean;
lb_review_warning                   boolean;
lb_recruiter_warning                boolean;
lb_pay_proposal_warning             boolean;
lb_dod_warning                      boolean;

--lc_object_version_number number(15) := 1;

ld_final_process_date    date := SYSDATE-14;
lb_org_now_no_manager_warning boolean;
lb_asg_future_changes_warning boolean;
lb_entries_changed_warning    varchar2(240);
ln_error number := 0;

TYPE t_person_id IS TABLE OF PER_ALL_PEOPLE_F.person_id%TYPE;
TYPE t_full_name IS TABLE OF PER_ALL_PEOPLE_F.full_name%TYPE;
TYPE t_eff_start_date IS TABLE OF PER_ALL_PEOPLE_F.effective_start_date%TYPE;
TYPE t_eff_end_date IS TABLE OF PER_ALL_PEOPLE_F.effective_end_date%TYPE;
TYPE t_ass_eff_start_date IS TABLE OF PER_ALL_PEOPLE_F.effective_start_date%TYPE;
TYPE t_ass_eff_end_date IS TABLE OF PER_ALL_PEOPLE_F.effective_end_date%TYPE;
TYPE t_last_update_date IS TABLE OF PER_ALL_PEOPLE_F.last_update_date%TYPE;
TYPE t_ass_last_update_date IS TABLE OF PER_ALL_PEOPLE_F.last_update_date%TYPE;
TYPE t_object_version_number IS TABLE OF PER_PERIODS_OF_SERVICE.object_version_number%TYPE;
TYPE t_period_of_service_id IS TABLE OF PER_PERIODS_OF_SERVICE.period_of_service_id%TYPE;
TYPE t_end_date IS TABLE OF fnd_user.end_date%TYPE;

lt_person_id             t_person_id;
lt_full_name             t_full_name;
lt_eff_start_date        t_eff_start_date;
lt_eff_end_date          t_eff_end_date;
lt_ass_eff_start_date    t_ass_eff_start_date;
lt_ass_eff_end_date      t_ass_eff_end_date;
lt_last_update_date      t_last_update_date;
lt_ass_last_update_date  t_ass_last_update_date;
lt_object_version_number t_object_version_number;
lt_period_of_service_id  t_period_of_service_id;
lt_end_date              t_end_date;

CURSOR c_inactive_emp
IS
select distinct papf.person_id, papf.full_name, papf.effective_start_date, papf.effective_end_date,
       pasf.effective_start_date, pasf.effective_end_date, papf.last_update_date, pasf.last_update_date, pps.object_version_number
      ,pps.period_of_service_id, fu.end_date
  from per_all_people_f papf
      ,per_all_assignments_f pasf
      ,per_periods_of_service pps
      ,fnd_user fu
 where trunc(sysdate) between trunc(papf.effective_start_date) and trunc(NVL(papf.effective_end_date, sysdate))
   and trunc(sysdate) between trunc(pasf.effective_start_date) and trunc(NVL(pasf.effective_end_date, sysdate))
   and papf.person_id = pasf.person_id
   and papf.person_id = pps.person_id
   and papf.person_id = fu.employee_id
   and EXISTS (select 1
                from fnd_user fu
               where fu.employee_id = papf.person_id
                 and trunc(sysdate) not between trunc(fu.start_date) and trunc(fu.end_date)
              )
   and NVL(current_employee_flag, 'N') = 'Y'
 order by papf.person_id;
   
/*
CURSOR c_inactive_emp
IS
SELECT DISTINCT papf.person_id, papf.full_name, papf.effective_start_date, papf.effective_end_date,
       pasf.effective_start_date, pasf.effective_end_date, papf.last_update_date, pasf.last_update_date, pps.object_version_number
      ,pps.period_of_service_id
  FROM per_all_people_f papf
      ,per_all_assignments_f pasf
      ,per_periods_of_service pps
 WHERE TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date) and trunc(NVL(papf.effective_end_date, sysdate))
   and trunc(sysdate) between trunc(pasf.effective_start_date) and trunc(NVL(pasf.effective_end_date, sysdate))
   and papf.person_id = pasf.person_id
   and papf.person_id = pps.person_id
   and EXISTS (select 1
                from fnd_user fu
               where fu.employee_id = papf.person_id
                 and trunc(sysdate) not between trunc(fu.start_date) and trunc(fu.end_date)
              )
   and NVL(current_employee_flag, 'N') = 'Y'

 ORDER BY papf.person_id;
*/

begin

OPEN c_inactive_emp;
FETCH c_inactive_emp BULK COLLECT INTO lt_person_id, lt_full_name, lt_eff_start_date, lt_eff_end_date, lt_ass_eff_start_date
                                      ,lt_ass_eff_end_date, lt_last_update_date, lt_ass_last_update_date, lt_object_version_number
                                      ,lt_period_of_service_id, lt_end_date;
CLOSE c_inactive_emp;


FOR i in 1..lt_person_id.COUNT LOOP

ln_error := 0;

dbms_output.put_line('Full Name:'||lt_full_name(i));
dbms_output.put_line('Effective Start Date:'||lt_eff_start_date(i));
dbms_output.put_line('Effective End Date:'||lt_eff_end_date(i));
dbms_output.put_line('Assignment Effective Start Date:'||lt_ass_eff_start_date(i));
dbms_output.put_line('Assignment Effective End Date:'||lt_ass_eff_end_date(i));
dbms_output.put_line('Period of Service:'||lt_period_of_service_id(i));
dbms_output.put_line('Object Version:'||lt_object_version_number(i));
dbms_output.put_line('End Date:'||lt_end_date(i));

BEGIN

hr_ex_employee_api.actual_termination_emp(
 p_effective_date => lt_end_date(i)
,p_period_of_service_id => lt_period_of_service_id(i)
,p_object_version_number => lt_object_version_number(i)
,p_actual_termination_date => lt_end_date(i)
,p_person_type_id => 69 -- Person Type Id is EX_EMP

,p_last_std_process_date_out => ld_last_std_process_date_out
,p_supervisor_warning        => lb_supervisor_warning
,p_event_warning             => lb_event_warning
,p_interview_warning         => lb_interview_warning
,p_review_warning              => lb_review_warning
,p_recruiter_warning           => lb_recruiter_warning
,p_asg_future_changes_warning   => lb_asg_future_changes_warning
,p_entries_changed_warning      => lb_entries_changed_warning
,p_pay_proposal_warning         => lb_pay_proposal_warning
,p_dod_warning                  => lb_dod_warning
);
dbms_output.put_line('actual_termination_emp completed successfully for Employee:'||lt_full_name(i));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception for Employee:'||SQLCODE||SQLERRM);
ln_error := 1;
END;

IF (ln_error = 0) THEN

 BEGIN

 hr_ex_employee_api.update_term_details_emp
   (p_effective_date                => lt_end_date(i)
   ,p_period_of_service_id          => lt_period_of_service_id(i)
   ,p_object_version_number         => lt_object_version_number(i)
   ,p_accepted_termination_date     => lt_end_date(i)
   );
 dbms_output.put_line('update_term_details_emp completed successfully for Employee:'||lt_full_name(i));
 
 EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('Exception for Employee:'||SQLCODE||SQLERRM);
 ln_error := 1;
 END;

 END IF;

dbms_output.put_line('Employee: '||lt_full_name(i)||' update completed');
 
  /* Not Required if Payroll is not used
  hr_ex_employee_api.final_process_emp
    (p_period_of_service_id         => l_period_of_service_id
    ,p_object_version_number        => lc_object_version_number
    ,p_final_process_date           => ld_final_process_date
    ,p_org_now_no_manager_warning   => lb_org_now_no_manager_warning
    ,p_asg_future_changes_warning   => lb_asg_future_changes_warning
    ,p_entries_changed_warning      => lb_entries_changed_warning
    );
  */

END LOOP;

exception
when others then
dbms_output.put_line('Exception'||SQLERRM||SQLCODE);

end;


hr_ex_employee_api1.final_process_emp is not required to be called following the other 2 API's if Oracle Payroll is not implemented.


Print This Post

FRM-92095: Oracle JInitiator version too low. Please install version 1.1.8.2 or higher

Reason: The forms version of Oracle EBS formsweb.cfg is modified to use JRE7 when JRE7 (JRE1.7) is installed on the client. 

The Forms version older than 11.1.1.6 or Forms 11.1.2 and older are not compatible with JRE7, and you have to uninstall JRE 1.7 and Reinstall the latest JRE 1.6 release

Java installation is straight forward and Java uninstall installations instructions can be found here

P.S: Updates for Java 6 are no longer available to the public. Oracle offers updates to Java 6 only for customers who have purchased Java support or have Oracle products that require Java 6.

Print This Post

Deleting an Expense Report

I have come across this case where user has wrongly entered some expense details for approval and was accidentally approved, and a correct expense report was later created. Now she wants this old approved and Ready for Payment expense report to be deleted; as this cannot be done from the application I have to use the below API to withdraw the Expense Report so that all the Payables actions are disabled and withdrawn from any further payment.

ap_web_oa_mainflow_pkg.withdrawexpensereport(p_report_header_id=>ln_report_hdr_id);

ln_report_hdr_id := report_header_id from AP_EXPENSE_REPORT_HEADERS_ALL.

please APPS initialize before calling this API

Print This Post

FRM-40654: Record has been updated. Requery block to see change

This error usually comes up when the base form modifies and saves it to the database without cosing the form through personalization. If the this change is not due to personalization then mostly it could be due to trailing spaces in the underlying table values, and Oracle has identified this issue advising the users to run the Applications DBA Diagnostics script under ‘Trailing and Leading Spaces’ to identify the trailing spaces, which are not visible from the application screen.

Navigation to run the script from Oracle Applications:
Oracle Diagnostics Tool(Responsibility) - > Applications DBA (Application) -> Trailing and Leading Spaces

Also you can update the Comments, Description fields of the underlying table with the trim of the text.


Print This Post

APP-11492: A MORE RECENT CREDIT MEMO HAS BEEN APPLIED TO THIS TRANSACTION

This error message appears whenever the user tries to complete a Credit Memo which has been applied to an Invoice and another more recent credit memo is applied to the same invoice.

Right now oracle are still working on this issue, and the only possibility to overcome is to completely remove the associated Invoice from the Initial Credit memo at both the header and line level using the below sql statements,

UPDATE ra_customer_trx_all
   SET previous_customer_trx_id = NULL
 WHERE customer_trx_id = XXXXXX;

UPDATE ra_customer_trx_lines_all
   SET previous_customer_trx_id = NULL,
       previous_customer_trx_line_id = NULL
 WHERE customer_trx_id = XXXXXX;

Make sure you create a Table/line level backup before doing this.

Print This Post

Table space Space availability

Below is the query to identify the available table space's space in MB, if in case you tend to use the Tablespace in creating database objects.

SELECT b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb, c.totalusedspace UsedMB
  FROM (SELECT tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, sum(bytes)/1024/1024 as tbs_size
          FROM dba_data_files
         GROUP BY tablespace_name
         UNION
        SELECT tablespace_name, sum(bytes)/1024/1024 tbs_size
          FROM dba_temp_files
         GROUP BY tablespace_name) b,
        (SELECT tablespace_name, round(sum(bytes)/1024/1024, 2) as totalusedspace
          FROM dba_segments
         GROUP BY tablespace_name) c
 WHERE a.tablespace_name(+) = b.tablespace_name
   AND a.tablespace_name    = c.tablespace_name;

Print This Post

Monthly Payroll Elements and associated Pay Details

Just want to share the query I have worked on to extract all the Employee Payroll Elements and Pay Elements generated dynamically for an Employee on a single Pay Period June'2012:

*******************************************************************************************************

SELECT person_id, employee_number, assignment_id, full_name,   position_number, position_name, sex, ethnicity, years, employment_category
 --   ,actual_fte
      ,fte
      ,actual_hours, reporting_name, team, service, MAX(NVL(BASIC_PAY  , 0)) "BASIC_PAY",SUM(NVL(ADDITIONAL_PAY, 0)) "ADDITIONAL_PAY"
      ,MAX(NVL(BASIC_PAY  , 0)) + SUM(NVL(ADDITIONAL_PAY, 0)) "GROSS_PAY"
      ,MAX(GROSS_PAY) "NET_PAY"
 FROM (
SELECT papf.person_id, papf.employee_number, paaf.assignment_id, papf.full_name, SUBSTR(pap.name, 1, INSTR(pap.name, '.')-1) "POSITION_NUMBER"
      ,SUBSTR(pap.name, INSTR(pap.name, '.') + 1) "POSITION_NAME", papf.sex, flv.meaning "ETHNICITY"
      ,trunc((to_date('01-JUN-2012', 'DD-MON-RRRR') - papf.date_of_birth)/365) "YEARS"
      ,flv1.meaning "EMPLOYMENT_CATEGORY"
--      ,wcev.fte "ACTUAL_FTE"
      ,pabf.value "FTE"   
      ,DECODE(NVL(paaf.normal_hours, 0) , 0 , NVL(otlh.total_hours, 0) * 4.34, paaf.normal_hours) "ACTUAL_HOURS"
      ,NVL(wpbc.basic_salary, 0) "BASIC_PAY"
      ,NVL(wpbc1.basic_salary, 0) "ADDITIONAL_PAY"
      ,wpbc1.reporting_name       "REPORTING_NAME"
      ,NVL(bp1.gross_pay, 0) "GROSS_PAY"
      ,DECODE(pap.position_id, 10297, 'CE Office', whg.name) "TEAM"
      ,DECODE(pap.position_id, 10297, WBC_SERVICE_UNIT_NAME(83), whg.name) "SERVICE"
  FROM per_all_people_f               papf
      ,per_all_assignments_f          paaf
      ,per_all_positions              pap
      ,fnd_lookup_types               flt
      ,fnd_lookup_values              flv
      ,fnd_lookup_types               flt1
      ,fnd_lookup_values              flv1
      ,wbc_otl_hours_v                otlh
      ,per_assignment_budget_values_f pabf
--    ,wdc_current_establishment_v wcev
      ,wbc_hr_org_information_mv   whg
      ,(SELECT prb1.assignment_id, MAX( NVL( DECODE(defined_balance_id, 163, NVL(balance_value, 0), 0), 0) ) "GROSS_PAY"
          FROM pay_run_balances   prb1
              ,gl_period_statuses gps1
         WHERE /*prb1.defined_balance_id in (163, 166, 238)
           AND */gps1.application_id = 101
           AND prb1.effective_date = gps1.end_date
           AND gps1.period_name = 'Jun-12'
         GROUP BY prb1.assignment_id) bp1
      ,(SELECT pasa.assignment_id  "ASSIGNMENT_ID"
             , petf.element_name   "ELEMENT_NAME"
             , petf.reporting_name "REPORTING_NAME"
             , petf.description    "DESCRIPTION"
             , pivf.name           "NAME"
             , SUM(prrv.result_value)   "BASIC_SALARY"
          FROM pay_run_types_f        prt
              ,pay_run_results        prrt
              ,pay_run_result_values  prrv
              ,pay_input_values_f     pivf
              ,pay_payroll_actions    papa
              ,pay_assignment_actions pasa
              ,pay_element_types_f    petf
         WHERE prrt.assignment_action_id = pasa.assignment_action_id
           and prrt.element_type_id = petf.element_type_id
           and pasa.run_type_id = prt.run_type_id
           and prrv.run_result_id = prrt.run_result_id  
           and prrv.input_value_id = pivf.input_value_id
           and trunc(papa.effective_date) between trunc(pivf.effective_start_date) and trunc(pivf.effective_end_date)
           and trunc(papa.effective_date) between trunc(petf.effective_start_date) and trunc(petf.effective_end_date)
           and papa.business_group_id = petf.business_group_id
           and papa.business_group_id = pivf.business_group_id
           and to_char(papa.effective_date, 'DD-MON-RRRR') = '30-JUN-2012'
           and papa.business_group_id = &business_group_id
           and papa.payroll_id = &Payroll_id
           and papa.action_status = 'C'
           and papa.action_type = 'R'  
           and pasa.payroll_action_id = papa.payroll_action_id
           and pivf.name = 'Pay Value'
           and petf.reporting_name = 'Basic Salary'
         GROUP BY assignment_id
             , petf.element_name
             , petf.reporting_name
             , petf.description
             , pivf.name ) wpbc
      ,(SELECT pasa.assignment_id  "ASSIGNMENT_ID"
             , petf.element_name   "ELEMENT_NAME"
             , petf.reporting_name "REPORTING_NAME"
             , petf.description    "DESCRIPTION"
             , pivf.name           "NAME"
             , prrv.result_value   "BASIC_SALARY"
          FROM pay_run_types_f        prt
              ,pay_run_results        prrt
              ,pay_run_result_values  prrv
              ,pay_input_values_f     pivf
              ,pay_payroll_actions    papa
              ,pay_assignment_actions pasa
              ,pay_element_types_f    petf
              ,pay_element_classifications pecl
         WHERE prrt.assignment_action_id = pasa.assignment_action_id
           and prrt.element_type_id = petf.element_type_id
           and pasa.run_type_id = prt.run_type_id
           and prrv.run_result_id = prrt.run_result_id  
           and prrv.input_value_id = pivf.input_value_id
           and trunc(papa.effective_date) between trunc(pivf.effective_start_date) and trunc(pivf.effective_end_date)
           and trunc(papa.effective_date) between trunc(petf.effective_start_date) and trunc(petf.effective_end_date)
           and papa.business_group_id = petf.business_group_id
           and papa.business_group_id = pivf.business_group_id
           and to_char(papa.effective_date, 'DD-MON-RRRR') = '30-JUN-2012'
           and papa.business_group_id = &business_group_id
           and papa.payroll_id = &Payroll_id
           and papa.action_status = 'C'
           and papa.action_type = 'R'  
           and pasa.payroll_action_id = papa.payroll_action_id
           and pivf.name = 'Pay Value'
           and petf.classification_id = pecl.classification_id
           and pecl.classification_name = 'Earnings'
           and pecl.legislation_code = 'GB'
           and petf.reporting_name <> 'Basic Salary') wpbc1
       --,wbc_user_assignments wua          
  WHERE /*papf.person_id = wua.person_id
    AND wua.assignment_id = wpbc.assignment_id(+)
    and */paaf.assignment_id = wpbc.assignment_id(+)
    AND paaf.assignment_id = wpbc1.assignment_id(+)
    AND papf.person_id = otlh.resource_id(+)
    AND trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(papf.effective_start_date) AND trunc(NVL(papf.effective_end_date, sysdate))
    AND trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(paaf.effective_start_date) AND trunc(NVL(paaf.effective_end_date, sysdate))
    AND papf.person_id = paaf.person_id   
    AND paaf.position_id = pap.position_id
--  and wcev.person_id = papf.person_id
--  and wcev.assignment_id = paaf.assignment_id
    and paaf.organization_id = pap.organization_id   
    and trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(pap.date_effective) and trunc(NVL(pap.date_end, sysdate))
    and paaf.job_id = pap.job_id
    and paaf.business_group_id = pap.business_group_id
    and flv.lookup_code = papf.per_information1
    and flt.lookup_type = 'ETH_TYPE'
    and flt.lookup_type = flv.lookup_type
    and flv1.lookup_code = paaf.employment_category
    and flt1.lookup_type = 'EMP_CAT'
    and flt1.lookup_type = flv1.lookup_type
    and paaf.assignment_id = bp1.assignment_id(+)
    and paaf.organization_id = whg.org_id
    and paaf.assignment_id = pabf.assignment_id
    and pabf.unit = 'FTE'
    and trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(pabf.effective_start_date) and trunc(pabf.effective_end_date)
    )
GROUP BY person_id, employee_number, assignment_id, full_name,   position_number, position_name, sex, ethnicity, years, employment_category
--      ,actual_fte
        ,fte
        ,actual_hours ,reporting_name , team, service)

*******************************************************************************************************

Print This Post

Oracle Apps User Password

Being an Oracle consultant, Sometimes you might need the Oracle Applications passwords having provided only the database access and you want to login through the common user credentials to check a few screens in a test instance.

Below is the simple way of executing a decrypt java function.

-- Package Spec

CREATE OR REPLACE PACKAGE get_pwd
AS
FUNCTION decrypt (
KEY IN VARCHAR2
,VALUE IN VARCHAR2
)
RETURN VARCHAR2;
END get_pwd;
/

-- Package Body

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
FUNCTION decrypt (
KEY IN VARCHAR2
,VALUE IN VARCHAR2
)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/

/** Run Login Apps User on Toad/Oracle-- Will get apps password */
SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL));

--Run this on toad will get all the usernames and passwords
SELECT usertable.user_name
, (SELECT get_pwd.decrypt (UPPER ((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE
UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL))))
,usertable.encrypted_user_password)
FROM DUAL) AS encrypted_user_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ('&username');

Creating a read only Responsibility

Here I am going to show you 2 ways to create a read only resposibility:

1. Form Function Method
2. custom.pll

Form Function Method:
1. Identify the menu attached to the responsibility that you wish to make it a Read only Responsibility
2. Identify the form functions that are attached to this menu.
3. Define a new form function exactly similar to the existing one but with the option QUERY_ONLY="YES" at the location "Application"-->"function"-->"Form"-->"Parameter".
4. The Parameter sets the function in Query Only mode.
5. Final step is to create a custom menu with the new read only functions created and attach the menu to the  new responsibility.

CUSTOM.pll (Reference metalink note: 363298.1)
1.Open the CUSTOM.pll in the form builder
2.Add the following sample code in the procedure event for the event ‘WHEN-NEW-FORM-INSTANCE’

BEGIN

IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
   IF FND_PROFILE.VALUE('USER_NAME')='<USER_NAME>' THEN

      BEGIN -- Begin Query Only Mode

      COPY('Entering app_form.query_only_mode.','global.frd_debug');
      COPY('YES', 'PARAMETER.QUERY_ONLY');
      APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
      APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
      formname := NAME_IN('system.current_form');
      blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);

      WHILE (blockname is not null) LOOP

        IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN
           SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
           SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
           SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);
        END IF;

        blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);
      END LOOP;

    END; -- End Query Only Mode

   END IF;
END IF;

END;

3. Compile the Custom.pll and place it under the directory $AU_TOP/resource



Print This Post