Query to Get the Employee Salary Increase Summary

  SELECT ppf.employee_number,
         ppf.full_name,
         ppp.proposed_salary_n,
         ppp.change_date
    FROM per_pay_proposals ppp,
         per_all_people_f ppf,
         per_all_assignments_f paaf
   WHERE     ppf.person_id = paaf.person_id
         AND paaf.assignment_id = ppp.assignment_id
         AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
                                 AND ppf.effective_end_date
         AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                 AND paaf.effective_end_date
         AND ppp.approved = 'Y'
         AND change_date =
                (SELECT MIN (change_date)
                   FROM per_pay_proposals
                  WHERE     assignment_id = ppp.assignment_id
                        AND proposed_salary_n = ppp.proposed_salary_n)   
         AND ppf.employee_number = '1234567890'
ORDER BY ppp.change_date DESC

Query to get List of Active Employees along with their Supervisor’s Name and Email Address

SELECT ppf.employee_number,
       ppf.full_name,
       ppf.email_address,
       paaf.supervisor_id,
       sup.full_name,
       sup.email_address
  FROM per_all_people_f ppf
      ,per_all_assignments_f paaf
      ,per_people_x sup
 WHERE     ppf.current_employee_flag = 'Y'
       AND paaf.supervisor_id(+) = sup.person_id
       AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
                               AND ppf.effective_end_date
       AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                               AND paaf.effective_end_date
       AND ppf.person_id = paaf.person_id
       AND ppf.employee_number IS NOT NULL
       AND ppf.person_type_id =
              (SELECT person_type_id
                 FROM per_person_types
                WHERE     user_person_type = 'Employee'
                      AND business_group_id = ppf.business_group_id)
       AND ppf.employee_number = <<XX_EMP_NUM>>   

Query to Get Month Wise Hired Employees Head Count in Core HR

  SELECT TO_CHAR (ORIGINAL_DATE_OF_HIRE, 'MON') MM
       , COUNT (PERSON_ID) TOTAL
    FROM per_people_x
   WHERE TO_CHAR (ORIGINAL_DATE_OF_HIRE, 'RRRR') = '2018'
GROUP BY TO_CHAR (ORIGINAL_DATE_OF_HIRE, 'MON')

Query to get Total Active Employees in the company in Core HR

SELECT ppf.employee_number, ppf.full_name, ppf.email_address
  FROM per_all_people_f ppf
 WHERE     ppf.current_employee_flag = 'Y'
       AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
                               AND ppf.effective_end_date
       AND ppf.employee_number IS NOT NULL
       AND ppf.person_type_id =
              (SELECT person_type_id
                 FROM per_person_types
                WHERE     user_person_type = 'Employee'
                      AND business_group_id = ppf.business_group_id)

Query to show the progress of Long Running queries.

SELECT a.sid,

         a.serial#,

         b.username,

         opname OPERATION,

         target OBJECT,

         TRUNC (elapsed_seconds, 5) "ET (s)",

         TO_CHAR (start_time, 'HH24:MI:SS') start_time,

         ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"

    FROM v$session_longops a, v$session b

   WHERE     a.sid = b.sid

         AND b.username NOT IN ('SYS', 'SYSTEM')

         AND totalwork > 0

ORDER BY elapsed_seconds

;

Query to get Employee GOSI Details from Assignment

SELECT per.employee_number,
       per.full_name,
       ppf.payroll_name,
       hscf.concatenated_segments gosi_concatenated_segments,
       SUBSTR (hscf.concatenated_segments,
               0,
               INSTR (hscf.concatenated_segments, '.') - 1)
          employer
  FROM per_assignments_x paaf,
       hr_soft_coding_keyflex hscf,
       pay_payrolls_f ppf,
       per_people_x per
 WHERE paaf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id
       AND paaf.payroll_id = ppf.payroll_id
       AND per.person_id = paaf.person_id
       AND paaf.assignment_status_type_id = 1
       AND paaf.assignment_type = 'E'
       AND paaf.primary_flag = 'Y'
     ;

Query to get Form and Form Personalization Details in Oracle APPS

Form Details:-

SELECT  DISTINCT application_short_name, forms.form_name,
         formstl.user_form_name,
         func.function_name,
         func.user_function_name,
         fm.menu_name,
         menu.prompt menu_prompt,
         menu.description,
         restl.responsibility_name     
  FROM   fnd_form forms,
         fnd_form_tl formstl,
         fnd_form_functions_vl func,
         fnd_menu_entries_vl menu,
         fnd_menus fm,
         fnd_responsibility res,
         fnd_responsibility_tl restl,
         fnd_application fat
 WHERE     1=1
         AND forms.form_id = formstl.form_id
         AND func.form_id = forms.form_id
         AND menu.function_id = func.function_id
         AND menu.menu_id=fm.menu_id
         AND res.menu_id = menu.menu_id
         AND res.responsibility_id = restl.responsibility_id
         AND fat.application_id=forms.application_id
         AND UPPER (forms.form_name) LIKE 'XX%'
ORDER BY 1,2
     


Form Personalization:

SELECT DISTINCT ffv.form_name, fa.application_name,ffv.form_id,  ffv.user_form_name,
                ffv.description form_description, ffcr.sequence,
                ffcr.description personalization_rule_name
           FROM fnd_form_vl ffv, fnd_form_custom_rules ffcr, fnd_application_tl fa
          WHERE ffv.form_name = ffcr.form_name
          AND fa.application_id=ffv.application_id
       ORDER BY 2, 1;

Spacing and Layout CSS Definitions in Oracle OAF

SPACING AND LAYOUT CSS DEFINITIONS
OraIndentHeader	Indents second and later occurrences of subheaders 20 pixels relative to page header
OraSpacingHeader	Spacing for Page Title Header: 7 pixels above and 2 below
OraSpacingHeaderSub	Spacing for first SubHeader on page: 10 pixels above and 2 below
OraSpacingHeaderLarge	Spacing for second and later occurrences of subheaders: 20 pixels above and 2 below

Table CSS Definitions in Oracle OAF

TABLE CSS DEFINITIONS
OraTableTitle	Table Title - used instead of Subheader when other elements separate the subheader from the table AND no control bar is present
OraTable	Background color for table gridlines
OraTableControlBarText	text in table Control Bar (band appearing at top of some tables that contains controls)
OraTableColumnHeader	Table Column Header
OraTableColumnHeaderNumber	same as Table Column Header but with right aligned numeric header
OraTableColumnHeaderIconButton	Table Column Header with centered action button
OraTableSortableColumnHeader	Sortable Table Column Header -- cursor turns to hand over header cell
OraTableSortableColumnHeaderNumber	sortable form of numeric Table Column Header
OraTableSortableColumnHeaderIconButton	sortable form of Table Column Header with centered action button
OraTableRowHeader	Table Row Header
OraTableColumnFooter	for left-aligned text in table column footer, except for totals
OraTableTotal	right-aligned "Total" label that appears in footer cell to left of numeric total
OraTableAddTotal	left-aligned "Total" label that appears in column header cell
OraTableTotalNumber	right-aligned total value that appears in footer cell below the totalled column
OraTableTotalText	left-aligned total value -- useful in updateable tables with more than one totaled column
OraTableCellText	Table Cell Text
OraTableCellTextBand	Table Cell Text in row with band of color
OraTableCellNumber	numeric Table Cell Text - only required if column features calculations
OraTableCellNumberBand	numeric Table Cell Text in row with band of color - only required if column features calculations
OraTableCellIconButton	Table Cell containing an action/navigation button
OraTableCellIconButtonBand	numeric band color table cell containing an action/navigation button
OraTableCellSelect	select column with check box or radio button
OraTableCellSelectBand	select column with check box or radio button in row with band of color
OraTableVerticalGrid	Table Background Vertical Grid Color
OraTableVerticalHeaderGrid	Table Background Vertical Grid Color in Column Header
OraTableHorizontalGrid	Table Background Horizontal Grid Color
OraTableHorizontalHeaderGrid	Table Background Horizontal Grid Color in Row Header
OraTableShadowHeaderGrid	renders shadow for sortable table headers???
OraTableHeaderLink	Used for column headers rendered by SortableHeaderBean for headers which aren't sortable
OraTableSortableHeaderLink	Link in Header of Sortable Column
OraTableDetail	Used for details child in conjunction with Hide/Show in a table row

Header CSS Definition in Oracle OAF

HEADER CSS DEFINITIONS
OraHeader	Standard dark blue header on white background
OraHeaderSub	Standard dark blue subheader on white background
OraHeaderSubSub	Standard dark blue subsubheader on white background
OraDarkHeader	Dark Header on dark beige in Side Navigation of Home page - usually subheader and subsubheader are used instead
OraDarkHeaderSub	Dark Subheader on dark beige in Side Navigation of Home page
OraDarkHeaderSubSub	Dark Subsubheader on dark beige in Side Navigation of Home page
OraLightHeader	Light blue header on light or medium beige background for content containers - usually subheader and subsubheader are used instead
OraLightHeaderSub	Light blue subheader on light or medium beige background - typically in content containers
OraLightHeaderSubSub	Light blue subsubheader on light background - typically in content containers
OraColorHeader	Header on Blue for content containers - usually subheader and subsubheader are used instead
OraColorHeaderSub	Subheader on Blue - typically in content containers
OraColorHeaderSubSub	SubsubHeader on Blue - typically in content containers
OraPortletHeader	Portlet Content Header on White - currently only used for a Portal demo
OraDarkPortletHeader	Portlet Content Header on Beige - currently only used for a Portal demo
OraGlobalPageTitle	Global header when there are no Tab links

Tab/Navigation/Locator CSS Definitions in OAF

TAB/NAVIGATION/LOCATOR CSS DEFINITIONS
OraNav1Selected	Tab [level 1] selected
OraNav1Enabled	Tab [level 1] enabled
OraNav1Disabled	Tab [level 1] disabled
OraNav2Selected	Horizontal Navigation [level 2] selected
OraNav2Enabled	Horizontal Navigation [level 2] enabled
OraNav2Disabled	Horizontal Navigation [level 2] disabled
OraNav3Selected	Side Navigation [level 3] selected
OraNav3Enabled	Side Navigation [level 3] enabled
OraNav3Disabled	Side Navigation [level 3] disabled
OraNavBarActiveLink	used for enabled NavigationBarBean links (a "next" link when there are more items to view.)
OraNavBarInactiveLink	used for NavigationBarBean Next/Previous links when there are no more Next/Previous contents to be viewed
OraNavBarViewOnly	used for the step text (such as "Step 1 of 5") in a single-item NavigationBarBean
OraTrainActive	current page in step-by-step locator a.k.a. "train"
OraTrainVisited	visited page(s) in step by step locator a.k.a. "train"
OraTrainUnvisited	page(s) not yet visited page in step-by-step locator a.k.a. "train"
OraCrumbsSelected	current page (without link) in breadcrumbs
OraCrumbsEnabled	Pages higher in the hierarchy (with links enabled) in breadcrumbs

General Component CSS Definitions in Oracle OAF

GENERAL COMPONENT CSS DEFINITIONS
OraBody	used on the element to define the default bgcolor/font family for the entire page
OraApplicationSwitcherText	label of Application Switcher
OraPromptText	Text Prompts (Labels)
OraDataText	Data text string anywhere in page except in tables - also often used for numeric values which don't need to be right-aligned
OraFieldText	Text and numeric data in standard Web widget (field, poplist)
OraFieldTextDisabled	Renders gray text in disabled form controls such as text fields, radio buttons, check boxes - does not match current BLAF guidelines
OraFieldNumber	Right-aligned numeric Data in Field
OraFieldNumberDisabled	Renders gray text for disabled Numeric Data fields - does not match current BLAF guidelines
OraInstructionText	Instruction Text
OraInstructionTextStrong	Instruction Text with Emphasis
OraPageStampText	Page stamp text
OraPageStampLabel	Page stamp label
OraButtonText	Action/Navigation Button Text
OraButtonTextDisabled	Button Text - disabled
OraLinkText	Link Text (would be underlined with href tag)
OraVLinkText	visited link text (would be underlined with href tag)
OraALinkText	active link text - color change on mouse button down (would be underlined with href tag)
OraGlobalButtonText	Global Button Enabled} (This would be an "a href" link
OraGlobalButtonTextSelected	Global Button Active
OraGlobalButtonTextDisabled	Global Button Disabled
OraTipText	Tip Text for page or section of page
OraTipLabel	Tip label (the word "Tip") in bold
OraInlineErrorText	Inline Error Text embedded in page content (not in message box; not for other message types)
OraInlineInfoText	Inline Info and Hint Text embedded in page content (not in message box; not for other message types)
OraTextInline	Variant of OraInlineInfoText - not specified in current BLAF guidelines
OraErrorHeader	Header for Error Message Only
OraErrorNameText	Error Name Text
OraMessageBoxList	numbered list within message box for list of informational or warning messages (not for error messages)
OraMessageBoxLink	blue links in message boxes - current BLAF guidelines specify brown links
OraMessageBoxParagraph	body text in non-error message boxes
OraMessageBoxErrorList	numbered list in red text within message box for list of error messages
OraMessageBoxErrorLink	blue links in message boxes - current BLAF guidelines specify brown links
OraMessageBoxErrorParagraph	red paragraph text in Error message boxes
OraPrivacy	Link in Footer to Privacy Statement
OraCopyright	Copyright Statement Text in Footer
OraCalendarTitle	Title of Date Picker - usually name of the month
OraCalendarHeader	Date Picker column headers - usually days of the week
OraCalendarEnabled	Days in Date Picker that can be selected
OraCalendarDisabled	Days in Date Picker that are invalid for the current context
OraCalendarSelected	Selected days(s) in the in Date Picker
OraShuttleLinkText	Link text placed below each arrow button in the middle of the shuttle