Oracle Payroll Run Results Discoverer Report

  SELECT    'Period=>'
          || period_name
          || ': Payroll Action ID =>'
          || ppa.payroll_action_id
          || ': Consolidation Set =>'
          || con_set.consolidation_set_name
          || ': Run Date => '
          || ppa.creation_date pay_action,
          pbg.NAME business_group, papf.business_group_id,
          papf.employee_number || ':' || papf.full_name employee,
          papf.employee_number emp_no, papf.full_name emp_name,
          papf.start_date hiredate, hou.NAME org_name, pp.NAME pos_name,
          paaf.assignment_id assignment_id, paa.assignment_action_id,
          ppa.payroll_action_id, ppa.time_period_id, ppa.action_type,
          ppa.effective_date, ppa.payroll_id, ppf.payroll_name,
          ptp.period_name, ptp.end_date, papf.start_date commencement_date,
          petf.element_name, pec.classification_name,
          DECODE (pec.classification_name,
                  'Voluntary Deductions', -TO_NUMBER (prrv.result_value),
                  TO_NUMBER (prrv.result_value)
                 ) result_value,
          ppg.segment1 power_retailer, ppg.segment2 petty_cash_holder,
          ppg.segment3 direct_indirect
     FROM per_all_people_f papf,
          per_all_assignments_f paaf,
          pay_people_groups ppg,
          hr_all_organization_units hou,
          per_positions pp,
          pay_assignment_actions paa,
          pay_payroll_actions ppa,
          per_time_periods ptp,
          pay_run_results prr,
          pay_element_types_f petf,
          pay_input_values_f pivf,
          pay_run_result_values prrv,
          pay_element_classifications pec,
          per_business_groups pbg,
          pay_payrolls_f ppf,
          pay_consolidation_sets con_set
    WHERE ppa.effective_date BETWEEN papf.effective_start_date
                                 AND papf.effective_end_date
      AND papf.person_id = paaf.person_id
      AND paaf.primary_flag = 'Y'
      AND ppa.effective_date BETWEEN paaf.effective_start_date
                                 AND paaf.effective_end_date
      AND paaf.people_group_id = ppg.people_group_id
      AND paaf.organization_id = hou.organization_id
      AND paaf.position_id = pp.position_id(+)
      AND paaf.assignment_id = paa.assignment_id
      AND paa.payroll_action_id = ppa.payroll_action_id
      AND ppa.action_status = 'C'
      AND ppa.payroll_id = paaf.payroll_id
      AND ppa.time_period_id = ptp.time_period_id
      AND ppa.payroll_id = ptp.payroll_id
      AND paa.assignment_action_id = prr.assignment_action_id
      AND prr.element_type_id = petf.element_type_id
      AND petf.element_type_id = pivf.element_type_id
      AND pivf.input_value_id = prrv.input_value_id
      AND prrv.run_result_id = prr.run_result_id
      AND ptp.end_date BETWEEN petf.effective_start_date
                           AND petf.effective_end_date
      AND ptp.end_date BETWEEN pivf.effective_start_date
                           AND pivf.effective_end_date
      AND petf.classification_id = pec.classification_id
      AND pec.classification_name IN
             ('Earnings', 'Voluntary Deductions', 'Social Insurance',
              'Involuntary Deductions', 'Information')
      AND pivf.NAME = 'Pay Value'
      AND prrv.result_value IS NOT NULL
      AND papf.business_group_id = pbg.business_group_id
      AND ppf.payroll_id = ppa.payroll_id
      AND ppa.consolidation_set_id = con_set.consolidation_set_id;

Oracle Organization Hierarchy Query

SELECT
LPAD(' ',10*(LEVEL-1)) || org.name hierarchy,
org.organization_id
FROM
hr_all_organization_units org,
per_org_structure_elements pose
WHERE 1=1
AND porg.organization_id = pose.organization_id_child
AND pose.org_structure_version_id = 61
--and org.name  like '201.Financiale Services'
START WITH
pose.organization_id_parent = 115   -- Orgnization of parent id -- provide the id from which level the downward hierarchy should be displaed
CONNECT BY PRIOR
pose.organization_id_child = pose.organization_id_parent
ORDER SIBLINGS BY
org.location_id,
pose.organization_id_child

How To Default the Allow Account To Be Searched Check Box to be Checked

Goal

How does you check the Allow Account To Be Searched check box by default and furthermore hide the check box so that no one can change the setting?

Solution

Solution

To implement the solution, please execute the following steps:

1. Ensure that you do not have any personalizations attempting to default the 'Initial Value'
for the 'Allow Account to be Searched' check box.
2. Log into System Administrator.
3. Navigate to Profile > System.
4. Select the responsibility that this is not working in.
5. In the profile region, type in IRC: Visible Preference Default.
6. In the responsibility column, set this to 'Yes.'
7. Save and test.

Not all rows have been retrieved.Data may be inaccurate Oracle Discoverer

Scenario: I have 12500 records in my table..when using disco, when am fetching all the records it is always displaying only 10,000 records.. and it displays the a/m subjected error and displays only 10k rows..

Solution:
In Discoverer Desktop, Tools -> Options -> Uncheck Limit Retrived Data to.

Test the worksheet. It should work.

Cheeerssss...

Master Inventory Items with Category and Sub Category Details

CREATE OR REPLACE FORCE VIEW xx_mtl_master_items_v (organization_id,
                                                         inventory_item_id,
                                                         item_code,
                                                         description,
                                                         item_type,
                                                         category_set_id,
                                                         category_id,
                                                         structure_id,
                                                         CATEGORY,
                                                         sub_category
                                                        )
AS
   SELECT DISTINCT msib.organization_id, msib.inventory_item_id,
                   msib.segment1 item_code, msib.description, msib.item_type,
                   mic.category_set_id, mic.category_id, mcs.structure_id,
                   mc.segment1 CATEGORY, mc.segment2 sub_category
              FROM mtl_system_items_b msib,
                   mtl_parameters mp,
                   org_organization_definitions ood,
                   mtl_item_categories mic,
                   mtl_categories mc,
                   mtl_category_sets mcs
             WHERE msib.organization_id = mp.master_organization_id
               AND mp.organization_id = ood.organization_id
               AND mp.master_organization_id = mp.organization_id
               AND mic.category_id = mc.category_id
               AND mp.organization_id = mic.organization_id
               AND msib.inventory_item_id = mic.inventory_item_id
               AND mic.category_set_id = mcs.category_set_id
               AND mcs.structure_id = mc.structure_id;

Recover older Oracle PL/SQL source code from a package body

Q. I had created an Oracle PL/SQL package with a header and a body with lots of code. Later, I ended up accidentally erasing the code from that body after reran the CREATE OR REPLACE PACKAGE BODY... statement with different source code (which actually I intended to save under a different package name). Is there anyway I can recover my older replaced source code from the packege?

A. 1. Login as sysdba on the server ie. \ as sysdba
     2. Spool on
     3. Put the query
         select text
         from all_source
        as of timestamp
         to_timestamp('11-Sep-2011 09:30:00', 'DD-MON-YYYY HH24:MI:SS')
         where name = 'XX_HR_CONVERSIONS' and type = 'PACKAGE BODY'

Cheersss...

Open Leave Requests In - Progress Workflows Oracle SSHR

select papf.employee_number "Staff No.",

       papf.full_name "Staff Name",

       wf.status,

       paat.name "Leave Type",

       hrs.information1 "Start Date",

       hrs.information2 "End Date",

       DECODE(hrs.information5,80,hrs.information7 ||' Hrs',hrs.information8 || ' Days') "Duration",

       wf.begin_date "Applied on",

       wf.due_date "Due Date",

       wf.from_user "Last Approver",

       fnd.user_name,

       wf.to_user "Pending With",

       wf.subject,

       wf.from_role,

       wf.recipient_role,

       wf.original_recipient,

       hra.item_key      

  from wf_notifications             wf,

       hr_api_transactions          hra,

       hr_api_transaction_steps     hrs,

       per_absence_attendance_types paat,

       fnd_user                     fnd,

       per_people_x                 papf

where wf.item_key = hra.item_key

   and hrs.transaction_id = hra.transaction_id

   and paat.absence_attendance_type_id = hrs.information5

   AND fnd.employee_id = hra.creator_person_id

   AND papf.PERSON_ID = fnd.employee_id

   AND WF.MESSAGE_TYPE = 'HRSSA'

   AND WF.MESSAGE_TYPE = 'HRSSA'

   AND WF.STATUS IN ('OPEN', 'CANCELLED')

   AND Wf.subject like '%Leave%'

   AND wf.subject not like 'Application%Error%'

   AND wf.from_user not like 'SYSADMIN'

order by wf.begin_date

Bouncing Apache for OAF deployment in R12

To clear HTML cache and bounce Apache at R12 level :

1.
cd $INST_TOP/admin/scripts
dierctory is :
/global/oracle/your_sid/inst/apps/sid_server/admin/scripts

here are the scripts :
adalnctl.sh adexecsql.pl adoafmctl.sh adstrtal.sh jtffmctl.sh
adapcctl.sh adformsctl.sh adopmnctl.sh gsmstart.sh mwactl.sh
adautocfg.sh adformsrvctl.sh adpreclone.pl ieo mwactlwrpr.sh
adcmctl.sh adoacorectl.sh adstpall.sh java.sh

2.

adapcctl.sh stop

3.

To clear HTML cache :

cd $COMMON_TOP/_pages

Direcetory is :

/global/oracle/your_sid/apps/apps_st/comn/_pages

Here are the java classes to clear.

Do rm * in _pages directory after having checked there are only compiled classes in this directory

4.

cd $INST_TOP/admin/scripts
dierctory is :
/global/oracle/your_sid/inst/apps/sid_server/admin/scripts

adapcctl.sh start

Registering Discoverer Workbook in Oracle Applications R12

1) Create the workbook

2) Open the workbook in the Discoverer Desktop or Plus edition and go to
'File->Manage Workbooks->Properties' look for the value for 'Identifier'. Save this value.

3) Create a form function. The form function definition includes the properties listed in these tabs:

3.1 Description tab:
3.1.1 Function Name: XX_[FUNCTION_NAME] (it is accepted practice to identify customizations with an XX prefix)
3.1.2 User Function Name: This is the name that will show in the menu
3.1.3 Description: Add a description of the function if you want.

3.2 Properties tab:
3.2.1 Type : SSWA jsp function
3.2.2 Maintenance Mode Support: Leave as "None"
3.2.3 Context Dependence: Leave as "Responsibility"

3.3 Form tab:
3.3.1 Form: Leave the field blank.
3.3.2 Application: Leave the field blank.
3.3.3 Parameters: mode=DISCO&workbook=(workbook identifier from step2)&parameters=(Disco parameters name/values)

3.4 Web HTML tab:
3.4.1 HTML call : OracleOasis.jsp

3.5 Web Host tab:
3.5.1 Leave all fields blank.

3.6 Region tab:
3.6.1 Leave all fields blank.

3.7 Save the form.

4) Open the menu form as sysadmin.

4.1 Search for the main menu under which you want the link to appear.
4.2 Add the information you need such as prompt, submenu, description etc.
4.3 Enter into the Function field the name of the function you created in step 3.
4.4 Save the menu form.

A message will appear saying that a concurrent program will run to regenerate the menus.

5) Set below Profile options, if your End User Layer Name = EUL_US
ICX: Discoverer Default End User Layer Schema Prefix = EUL
ICX: Discoverer EDW End User Layer Schema Prefix = US

6) Bounce Apache and Forms.