Query to get list of active employee having Marketing Manager Position

select distinct paa.assignment_number, ppx.first_name, ps.name
from per_all_assignments_f paa,
per_people_x ppx, per_all_positions ps
where paa.position_id = ps.position_id
and ps.name = 'Marketing Manager'               
and paa.business_group_id = 7671 -- use your business group id
and ppx.person_id = paa.person_id
and trunc(sysdate) between trunc(ppx.effective_start_date) and trunc(ppx.effective_end_date)                  
order by paa.assignment_number;

R12 User Guide

R12 User Guide

EOImpl should be declared abstract

In order to avoid the error just Copy past the below lines into your EOImpl

public void setLastUpdateLogin( oracle.jbo.domain.Number n ) {}
public void setLastUpdatedBy( oracle.jbo.domain.Number n ) {}
public void setLastUpdateDate( oracle.jbo.domain.Date n ) {}
public void setCreationDate( oracle.jbo.domain.Date n ) {}
public void setCreatedBy( oracle.jbo.domain.Number n ) {}

Query to get employee leave history Oracle EBS

select distinct
       papf.employee_number "Employee Number",
       papf.full_name "Emp. Name",
       paat.name "Leave Name",     
       paa.date_start "Start Date",
       paa.date_end "End Date"
  from per_all_people_f papf,
       per_all_assignments_f paaf,
       per_absence_attendance_types paat,
       per_absence_attendances paa,
       per_business_groups pbg
 where papf.person_id = paaf.person_id
   and paat.absence_attendance_type_id = paa.absence_attendance_id
   and paa.person_id = papf.person_id
   and pbg.business_group_id = paaf.business_group_id
order by 1 asc, date_start desc;

On-Insert Trigger Raised Unhandled Exception ORA-29273

Login as sysadmin

Navigate: Profileà System.

Type % IRC: Geocode Host% in Profile and Press Find Button

 Clear Site Text Box. 

Save & Close.

Try again to add Location

FRM - 18108: Failed to load the following objects while Opening Forms in Form Builder

SOLUTION for FRM-18108: Failed to load the following objects.
 

Copy the  PLL libraries from resource folder to local PC (e.g. C:\DevSuiteHome\resource).
Copy APPSTAND.fmb from US folder to your local PC (e.g. C:\DevSuiteHome\resource).

Add C:\DevSuiteHome\resource in variable environment.

Note: Create resource folder if it is not existing.

 

How to Delete Grade Rate using API in Oracle HRMS

DECLARE
   CURSOR get_pay_details IS
     
   SELECT * FROM pay_grade_rules_f;
     
   l_effective_start_date         DATE                      := NULL;
   l_effective_end_date          DATE                      := NULL;
   l_grade_rule_id                  NUMBER               := NULL;
   l_object_version_number   NUMBER               := NULL;
   l_err_msg                          VARCHAR2 (500)  := NULL;
   l_rate_id                            NUMBER               := NULL;
  
BEGIN

Before deleting the grade I would recommend you to understanding the p_datetrack_delete_mode Control Parameter.

The p_datetract_update_mode control parameter enables one to define the type of DateTrack deletion     that has to be made. This parameter is mandatory and  must be set to one of the following said values:

     1. ZAP: ZAP mode Completely removes the grade rate from the database
     2. DELETE : DELETE mode Sets end date to effective date
     3. FUTURE_CHANGE : It removes all scheduled changes
     4. DELETE_NEXT_CHANGE: It removes next change


   FOR i IN get_pay_details
   LOOP
      BEGIN
         hr_grade_rate_value_api.delete_grade_rate_value (
            p_validate                => FALSE,
            p_effective_date          => SYSDATE,
            p_datetrack_delete_mode   => 'ZAP',      
            p_grade_rule_id           => i.grade_rule_id,
            p_object_version_number   => i.object_version_number,
            p_effective_start_date    => l_effective_start_date,
            p_effective_end_date      => l_effective_end_date
         );

         COMMIT;
         DBMS_OUTPUT.put_line (
                                   'Grate Rate has been Deleted: ' || i.grade_rule_id
                                 );
      EXCEPTION
         WHEN OTHERS
         THEN
            l_err_msg := SQLERRM;
            DBMS_OUTPUT.put_line ('Inner Exception: ' || l_err_msg);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);
END;

APP-PER-50022: Oracle Human Resources could not retrieve a value for the User Type profile option.

Step 1:

Application developer → Profiles

Set the System administrator access to visible and updateable

 Step 2:

System administrator  → Profiles → System

Search ‘HR:User Type‘ Profile option at Responsibility and site level as per below image.

 Press ‘F11’→ Type→ HR: USER TYPE→ press ‘Ctrl+F11’. 

Save and check your responsibility, error removed.

Checking operating system version: must be 5.0, 5.1, 5.2. Actual 6.1 Failed

Follow the below steps to solve this issue:

1. Copy the installation folder to your local harddesk.
2. Search the folder for oraparam.ini.    
3. Edit the file using notepad.
4. Add 6.1 to the below section.    
5. Save the file and start the installation again.

Unable To Install Oracle Workflow Builder On Windows

Oracle Workflow Builder Client has not been Certified yet to run on Windows 64bit.

To configure the compatibility mode for an application, just locate the installation directory.

    1. Navigate to the WF Builder installer (setup.exe) in the file system

  


 2. Right-Click on the “setup.exe” > then go to Properties

  


 3. Check Compatibility Mode and  Select “Windows XP (Service Pack 3)”

    4. Press OK Button
    5. Now restart the Installer for the Workflow Builder.

Query to get User name and related assigned responsibilities

select fu.user_name "User Name",
         frt.responsibility_name  "Responsibility Name"
 from fnd_user fu,
         fnd_user_resp_groups furg,
         fnd_responsibility_tl frt,
         fnd_application_tl fat
where fu.user_id = furg.user_id
   and frt.application_id = fat.application_id
   and furg.responsibility_application_id = frt.application_id
   and furg.responsibility_id = frt.responsibility_id
   and ltrim(rtrim(fu.user_name)) is not null
order by 1 asc nulls last;

EXP-00091: Exporting questionable statistics.

Please use STATISTICS=None while export.
or
set export NLS_LANG=american_america.AR8ISO8859P6

Query to Find All Active Employees and Current Salary

select distinct
       ppf.employee_number "Employee Number",
       ppf.full_name       "Employee Name",
       ppt.user_person_type "Person Type",
       ppp.proposed_salary_n "Basic Salary",
       ppp.change_date       "Change Date",
       ppp.next_sal_review_date "Next Review Date"
from per_people_f ppf,
       per_assignments_f paf,
       per_pay_proposals ppp,
       per_person_types ppt,
       per_person_type_usages_f pptu
 where ppf.person_id     = paf.person_id
   and paf.assignment_id = ppp.assignment_id
   and ppf.employee_number is not null
   and ppp.change_date in (
                            select max(ppp1.change_date)
                              from per_pay_proposals ppp1
                             where ppp1.assignment_id = paf.assignment_id
                               and ppp1.approved = 'Y'
                          )
   and paf.payroll_id = 1131
   and :p_effective_date between ppf.effective_start_date and ppf.effective_end_date
   and :p_effective_date between paf.effective_start_date and paf.effective_end_date
   and ppf.person_id   = pptu.person_id
   and ppf.effective_start_date between pptu.effective_start_date and pptu.effective_end_date
   and pptu.person_type_id = ppt.person_type_id
   and ppt.system_person_type = 'EMP'
order by ppp.change_date desc,
         lpad(ppf.employee_number,10,0),
         ppp.proposed_salary_n,
         ppf.full_name asc; 

Query to Find out all Earning and Deduction Elements and values after running Payroll

select ppx.employee_number "Employee Number",
         ppx.full_name "Employee Name",  
         to_char(ppa.effective_date,'dd/mm/yyyy') "Effective Date",
         ptp.period_name "Period Name",  
         sum(decode(pecv.classification_name,'Earnings', prrv.result_value,0)) Earnings,
         sum(decode(pecv.classification_name,'Voluntary Deductions',
                                         prrv.result_value, 'Involuntary Deductions',
                                         prrv.result_value, 'Employer Charges',
                                         prrv.result_value, 0
                            )
               ) Deductions
  from per_people_x ppx,
          per_assignments_x pax,
          pay_assignment_actions paa,
          pay_payroll_actions ppa,
          pay_run_results prr,
          pay_run_result_values prrv,
          pay_element_types_f petf,
          pay_input_values_F pivf,
          per_time_periods ptp, '
          pay_element_classifications_vl pecv
where ppx.person_id = pax.person_id
    and pax.assignment_id = paa.assignment_id
    and paa.assignment_action_id = prr.assignment_action_id 
    and ppa.payroll_action_id = paa.payroll_action_id
    and prr.element_type_id = petf.element_type_id
    and pivf.element_type_id = petf.element_type_id
    and prrv.run_result_id = prr.run_result_id
   and prrv.input_value_id = pivf.input_value_id
   and ptp.time_period_id = ppa.time_period_id
   and petf.classification_id = pecv.classification_id
   and pivf.name = 'Pay Value'
   and ppx.person_id = 5
group by ppx.employee_number,
          ppx.person_id,
          ppx.full_name,
          ppa.time_period_id,
          ppa.effective_date,
          ptp.period_name,
          pax.organization_id;

Query To get List of Employees which has Supervisors

select papf.full_name "Employee Name",
papf1.full_name "Supervisor Name"
from per_all_people_f papf,
per_all_people_f papf1,
per_all_assignments_f paaf
where papf1.person_id = paaf.supervisor_id
and papf.person_id = paaf.person_id
and papf.employee_number is not null
and paaf.primary_flag = 'Y'
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(nvl(papf.effective_end_date, sysdate))
and trunc(sysdate) between trunc(papf1.effective_start_date) and trunc(nvl(papf1.effective_end_date, sysdate))
group by papf.full_name, papf1.full_name
order by 1 asc

P2P(Procure to Pay) Cycle Complete Query

SELECT PRHA.SEGMENT1 REQNO,
PRHA.TYPE_LOOKUP_CODE REQTYPE,
PHA.SEGMENT1 PONO,
PHA.TYPE_LOOKUP_CODE POTYPE,
APS.VENDOR_NAME SUPPLIERNAME,
ASSA.VENDOR_SITE_CODE SUPPLIERSITE,
RSH.SHIPMENT_NUM RECEIPTNO,
AIA.INVOICE_ID INVID,
AIA.INVOICE_NUM INVOICENO,
AIA.INVOICE_AMOUNT INVAMOUNT,
AIA.INVOICE_TYPE_LOOKUP_CODE INVTYPE,
AIA.INVOICE_CURRENCY_CODE INVCURRENCY,
AIPA.INVOICE_PAYMENT_ID PAYMENTID,
AIPA.AMOUNT PAYMENTAMOUNT,
ACA.CHECK_ID CHECKID,
ACA.BANK_ACCOUNT_NAME BANKNAME,
ACA.BANK_ACCOUNT_NUM BANKNO,
ACA.BANK_ACCOUNT_TYPE BANKTYPE,
GJH.JE_HEADER_ID BATCHHEADERID,
GJH.JE_SOURCE BATCHSOURCE,
GJB.JE_BATCH_ID BATCHID,
GJB.NAME BATCHNAME
FROM PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_DISTRIBUTIONS_ALL PDA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_LINES_ALL PLA,
PO_HEADERS_ALL PHA,
AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL ASSA,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB
WHERE PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND APS.VENDOR_ID = PHA.VENDOR_ID
AND ASSA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
AND APS.VENDOR_ID = ASSA.VENDOR_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSL.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND APS.VENDOR_ID = AIA.VENDOR_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.CHECK_ID = ACA.CHECK_ID
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND TO_CHAR (AIDA.INVOICE_ID) = GJL.REFERENCE_2
AND TO_CHAR (AIDA.DISTRIBUTION_LINE_NUMBER) = GJL.REFERENCE_3
AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
AND PRHA.SEGMENT1 = '100'

Extended Support for Oracle E-Business Suite

Extended Support for Oracle E-Business Suite

For more information please visit

https://blogs.oracle.com/stevenChan/entry/ebs_12_0_extended_ends

Oracle EBS Personalization, Extension, Customization

In Oracle E-Business Suite development terms Personalization, Customizations, Extensions & localization are often used interchangeably. It often creates confusion among developers regarding the meaning of these terms. These terms are critically important terms that developers must understand and use properly. Let’s describe briefly.

What is Personalization?

Personalization/Configuration is the process of making changes to the User Interface (UI) from within an Oracle E-Business Suite Form/Page. It is possible to make personalization to both Form-based and OA Framework based pages.

What is Extension?

Extension is the process of making changes to the programmatic (i.e. PL/SQL or Java) elements of an E-Business Suite form/page, reports etc. It is possible to extend both Forms based and OA Framework-based pages.

What is Customization?

Customization is the process of creating new forms/pages. While Oracle does provide tools to do this (i.e., Oracle Forms and JDeveloper 10g with OA Extension), this is the least supported option.

WHO columns in Oracle EBS

It is best practice if you keep history of record in any application. Oracle implemented the feature of tracking data. The tracking of data stored WHO columns.

Below are the WHO columns exists in almost all tables of Oracle Apps.

• created_by          – Keeps track of user who inserted/created the record.
• creation_date      – Stores the record insertion/creation date.
• last_update_by    – Keeps track of last user who updated the record.
• last_update_date – Stores the record changed/updated date.
• last_update_login – Login Session ID of the user.

Column Name      How data is populated? 
created_by            TO_NUMBER(FND_PROFILE.VALUE(‘USER_ID’))
creation_date         SYSDATE
last_updated_by    TO_NUMBER(FND_PROFILE.VALUE(‘USER_ID’))
last_update_date   SYSDATE
last_update_login  TO_NUMBER(FND_PROFILE.VALUE(‘LOGIN_ID’))

Techno-Functional Consultants Roles & Responsibilities

1.       Requirement gathering, study in details.

2.       Preparation of RD020 document – List of Discovery questions.

3.       Mapping the requirements to Application process / Business Process Mapping.

4.       GAP fit Analysis.

5.      Level-3 Process design (Flow charts).

6.       Application configuration/setup for Oracle Applications (HRMS, Inventory, Purchasing, GL, AP, AR
Modules etc) and custom extensions in the various instances of the release life Cycle.

7.       Setup document management (version Control & Incremental setup) – Business requirement (BR 100) setup for each application.

8.       Functional specification (MD.50) for customizations / Data Flow Diagram.

9.       Development of customization requirements.

10.   Testing of Customizations (Forms, Interfaces and Reports).

11.   Resolution of issues rose during CRP/SIT/UAT sessions by Managers, key users, end users and regression testing team.

12.   Raising Technical Assist Request (TARs) with oracle for different issues and following up till resolution. 

13. Coordination with client managers/Key users and other teams for issue resolutions and getting sign-off for release to be moved to production.