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

Emailing a CLOB Document

The below code snippet is quite useful for emailing a CLOB document, which I have used for emailing a list of lines can't accommodate to a LONG object.
This can as well be used to write the output of a concurrent program to the CLOB and emailing it.

*********************************************************
DECLARE
lc_conn utl_smtp.connection; 
lc_sender             VARCHAR2(200) := 'abc@gmail.com';
lc_output_email   VARCHAR2(200) := 'xyz@gmail.com';
lc_filedata            VARCHAR2(32767);
crlf                        VARCHAR2(2)  := chr(13)||chr(10);
lc_subject             VARCHAR2(2000);
l_boundary           VARCHAR2(50) := '----=*#abc1234321cba#*=';
lc_filedata            VARCHAR2(32767);
lc_data                 CLOB := '';

BEGIN
lc_conn := utl_smtp.Open_Connection('127.0.0.1', 25);
utl_smtp.Helo(lc_conn, '127.0.0.1');
utl_smtp.Mail(lc_conn, lc_sender);
utl_smtp.rcpt(lc_conn, lc_output_email); 
utl_smtp.open_data(lc_conn);
lc_filedata := lc_filedata || 'Date:' || TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') || crlf;
lc_filedata := lc_filedata || 'To: ' || lc_output_email || crlf;
lc_filedata := lc_filedata || 'From: ' || lc_sender || crlf;
lc_filedata := lc_filedata || 'Subject: ' || lc_subject || crlf;
lc_filedata := lc_filedata || 'MIME-Version: 1.0' ||  crlf;  
lc_filedata := lc_filedata || 'Content-Type: multipart/mixed; boundary=' ||chr(34)||l_boundary|| chr(34)||crlf;
lc_filedata := lc_filedata || '--'||l_boundary|| crlf;
lc_filedata := lc_filedata || 'Content-Type: text/plain;'|| crlf ||
                                           'Content-Transfer_Encoding: 7bit'|| crlf ||
                                           crlf || lc_body || crlf || crlf ||
                                           '--'||l_boundary|| crlf ||
                                           'Content-Type: text/plain;'|| crlf ||
                                           ' name="'||lc_filename||'"'|| crlf ||
                                           'Content-Transfer_Encoding: 8bit'|| crlf ||
                                          'Content-Disposition: attachment;'|| crlf ||
                                          ' filename="'||lc_filename||'"'|| crlf || crlf;

  -- Headers from lc_filedata
dbms_lob.createtemporary(lc_data, false, 10);
dbms_lob.write(lc_data, length(lc_filedata), 1, lc_filedata);

ln_temp := dbms_lob.getlength(lc_data) + 1;
 
-- Looping to the Table of lines to be emailed
FOR j in 1..lg_i LOOP
    ln_temp := dbms_lob.getlength(lc_data) + 1;
    IF dbms_lob.getlength(lg_varchar2(j)) > 0 THEN
       dbms_lob.write(lc_data, length(lg_varchar2(j)), ln_temp, lg_varchar2(j));
    END IF;
END LOOP;
 
ln_temp := dbms_lob.getlength(lc_data) + 1;
 
ln_temp := 1;
ln_amount := 1900;
 
WHILE ln_temp < dbms_lob.getlength(lc_data) LOOP
   utl_smtp.write_data(g_Conn, dbms_lob.substr(lc_data, ln_amount, ln_temp));
   ln_temp := ln_temp + ln_amount;
   ln_amount := least(1900, dbms_lob.getlength(lc_data) - ln_amount);
END LOOP;
 
utl_smtp.write_data(lc_Conn, crlf||crlf);
utl_smtp.close_data(lc_conn);
utl_smtp.quit(lc_conn);

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

you can also refer to the link for different ways to email from pl/sql code.

  Print This Post