Query to Get Form Personalization Details ( Oracle Applications ) from Database.

Query to Get Form Personalization Details ( Oracle Applications )  from Database.

FND_FORM_CUSTOM_RULES - The Rules for the form customizations. A rule must have 1 more more FND_FORM_CUSTOM_SCOPES and a rule may have 1 or more FND_FORM_CUSTOM_ACTIONS.

FND_FORM_CUSTOM_ACTIONS - Holds the Actions for a specified Rule

FND_FORM - stores information about your registered application forms. Each row includes names (the actual SQL*Forms form name, and the EasyForm form title) and a description of the form. Each row also includes a flag that indicates whether this form is included in the AuditTrail audit set. You need one row for each form in each application. Oracle Application


Select Distinct
A.Id,
A.Form_Name , A.Enabled, C.User_Form_Name, D.Application_Name ,A.Description,Ca.Action_Type,Ca.Enabled,Ca.Object_Type,
ca.message_type,ca.message_text
from FND_FORM_CUSTOM_RULES a,
     FND_FORM b,
     FND_FORM_TL c,
     Fnd_Application_Tl D,
     Fnd_Form_Custom_Actions ca
where a.form_name = b.form_name
And B.Form_Id = C.Form_Id
And B.Application_Id = D.Application_Id
And D.Application_Id = 660 --For Order Management
And C.User_Form_Name Like 'Sales%'  --All the Forms that Start with Sales
And A.Enabled ='Y'
and a.id = ca.rule_id

User Hook

User Hook

Overview
1.     API User Hooks allow users to extend the business logic of the standard business rules that are executed by APIs. This is done by allowing custom procedures to be called at specific points in the standard APIs. For instance, we are implement User Hooks for extending the validation of data beyond what the standard system has provided.

Steps for Implementing User Hooks


1.     Choose the API you wish to hook some extra logic to.
2.     Write the PL/SQL procedure that you wish to be called by the hook.
3.     Register or associate the procedure you have written with one or more specific user hooks.
4.     Run the pre-processor program which builds the logic to execute your PL/SQL procedure from the hook specified in 3.


Example

1.     If validations need to be done on Person Extra Information when the information is created then we need to check the availability of module_name called as CREATE%PERSON%EXTRA%INFO%
OR we need to guess related API in all_objects
SELECT *
  FROM  hr_api_modules
WHERE  api_module_type = 'BP'
     AND  module_name LIKE 'CREATE%PERSON%EXTRA%INFO%'
OR
select * from all_objects where object_name like '%PERSON%EXTRA%API'


2.     If module exist we need to find out it’s module_id and related hook_ids

SELECT  ahk.api_hook_id,ahm.api_module_id, ahk.hook_package, ahk.hook_procedure,    ahk.api_hook_type, ahm.api_module_type
  FROM  hr_api_hooks ahk, hr_api_modules ahm
 WHERE ahm.module_name = 'CREATE_PERSON_EXTRA_INFO'
     AND  ahm.api_module_type in('BP')
     AND  ahk.api_hook_type in('AP','BP')
     AND  ahk.api_module_id = ahm.api_module_id



Query Result :

API_HOOK_ID	API_MODULE_ID	HOOK_PACKAGE	HOOK_PROCEDURE	API_HOOK_TYPE	API_MODULE_TYPE
2759	1226	HR_PERSON_EXTRA_INFO_BK1	CREATE_PERSON_EXTRA_INFO_A	AP	BP
2758	1226	HR_PERSON_EXTRA_INFO_BK1	CREATE_PERSON_EXTRA_INFO_B	BP	BP

Here BP – Before Process and AP – After Process
The 2 Business Process hook and 3 Row Handler Hooks available:
Before Process – These hooks execute logic before the main API logic. The majority of validation will not have taken place. No database changes will have been made.
After Process  – These hooks will execute after the main API validation has completed and database changes made. If the main validation failed then the user hook will not be called.
The 3 types of Row Handler (RH) hook available are:
§  After Insert
§  After Update
§  After Delete


3.     Usually we use After Process. (say : CREATE_PERSON_EXTRA_INFO_A)

4.     Create Custom Package and procedure code

 CREATE OR REPLACE PACKAGE ak_user_hook_leave_return_pkg
IS
 PROCEDURE Air_ticket_request (p_person_id          IN NUMBER,
                                                 p_information_type   IN VARCHAR2,
                                      p_pei_information1   IN VARCHAR2);  
 END ak_user_hook_leave_return_pkg;

Note : While creating Custom procedure pass the parameters which are present in standard procedure
Parameters in procedure Air_ticket_request should match with parameters in CREATE_PERSON_EXTRA_INFO_A.
Due to this standard procedure CREATE_PERSON_EXTRA_INFO_A passes values like p_person_id, p_information_type, p_pei_information1 dynamically to our custom procedure Air_ticket_request.




5.    Registering the User Hook

DECLARE
   l_api_hook_call_id        NUMBER;
   l_object_version_number   NUMBER;
BEGIN
   hr_api_hook_call_api.create_api_hook_call (
      p_validate                => FALSE,
      p_effective_date          => TO_DATE (sysdate),
      p_api_hook_id             => 2759,                                                  ---from point 2
      p_api_hook_call_type      => 'PP',
      p_sequence                => 3000,
      p_enabled_flag            => 'Y',
      p_call_package            => 'XX_USER_HOOK_PKG', ---our custom package
      p_call_procedure          => ‘XXAIR_TICKET_REQ',                       ---our custom procedure
      p_api_hook_call_id        => l_api_hook_call_id,
      p_object_version_number   => l_object_version_number);
END;

Delete User Hook created (Use when required)

DECLARE
   l_api_hook_call_id        NUMBER := 1210;           --pass appropriate  value

   l_object_version_number   NUMBER := 27;          --pass appropriate  value
BEGIN
   hr_api_hook_call_api.delete_api_hook_call (
      p_validate                => FALSE,
      p_api_hook_call_id        => l_api_hook_call_id,
      p_object_version_number   => l_object_version_number);
END;

6.    Check the Table : hr_api_hook_calls
If custom code is properly hooked with standard code then one record will be created

SELECT * FROM hr_api_hook_calls WHERE TRUNC (SYSDATE) = TRUNC (creation_date)


7.    Running the Pre-Processor  (Mostly done by DBA)
      Run following is the command in Putty
(We need to find location of file : hrahkone.sql)
> cd $PER_TOP/admin/sql or > cd $PER_TOP/ patch/115/sql or any other suggested by DBA

(Open sqlplus)
> sqlplus username/password

(Run the file hrahkone.sql)
> @hrahkone.sql

It will ask for api_module_id which we found at point 2
> Enter value for api_module_id: 1226

If all works fine it will show message as:
--------------------------------------------------
PL/SQL procedure successfully completed.
CREATE_PERSON_EXTRA_INFO(Business Process API) successful.


8.    Check the Table again : hr_api_hook_calls
If Pre-Processor is successful STATUS will be ‘V’ else it will be ‘I or N’

Calculating Employee Service years

Calculating Employee Service years

Below script return Number of years, Months and days of an employee

SELECT ppos.person_id,
       TRUNC (
          MONTHS_BETWEEN (
             COALESCE (ppos.projected_termination_date, SYSDATE),
             ppos.date_start)
          / 12)
       || ' Years '
       || TO_CHAR (
             FLOOR (
                MOD (
                   MONTHS_BETWEEN (
                      COALESCE (ppos.projected_termination_date, SYSDATE),
                      ppos.date_start),
                   12)))
       || ' Months '
       || (TO_DATE (COALESCE (ppos.projected_termination_date, SYSDATE))
           - ADD_MONTHS (
                ppos.date_start,
                TRUNC (
                   MONTHS_BETWEEN (
                      COALESCE (ppos.projected_termination_date, SYSDATE),
                      ppos.date_start))))
       || ' Days '
          yearOfService
  FROM per_periods_of_service ppos

HR: Bulk Deletion/ending of element entries

HR: Bulk Deletion/ending of element entries

HR: Bulk Deletion/ending of element entries
Declare
-- select all active opening balances
Cursor op_end_cur Is
Select pee.element_entry_id
From pay_element_types_f petf
    ,pay_element_entries pee
    ,pay_element_links_f pelf
    ,per_all_assignments_f paaf
Where petf.element_type_id = pee.element_type_id
And pelf.element_type_id = petf.element_type_id
And pelf.element_type_id = pee.element_type_id
And pelf.element_link_id = pee.element_link_id
And petf.element_name = <>
And paaf.assignment_id = pee.assignment_id
And <> between paaf.effective_start_date and paaf.effective_end_date;
l_v number := null;
l_s_date date;
l_e_date date;
l_w boolean;
Begin
pay_db_pay_setup.set_session_date(<>);
-- ending opening balance cursor
For op_end In op_end_cur
Loop
 hr_entry_api.delete_element_entry(p_dt_delete_mode => 'DELETE',
                                   p_session_date => <>,
                                   p_element_entry_id => op_end.element_entry_id);

Single responsibility assign to bulk of users

Single responsibility assign to bulk of users

How to assign a responsiblity to bulk users

Declare
  v_responsibility_id fnd_responsibility.responsibility_id%type;
  v_application_id    fnd_responsibility.application_id%type;
  v_resp_key  fnd_responsibility.responsibility_key%type;
  Cursor c_fnd_Cur Is
         Select a.user_id
         From fnd_user a
             ,per_all_people_f b
             ,per_all_assignments_f c
         Where a.employee_id = b.person_id
         And sysdate between b.effective_start_date and b.effective_end_date
         And sysdate between c.effective_start_date and c.effective_end_date;
  Type v_fnd_rec Is Table Of c_fnd_Cur%Rowtype;
  v_fnd_table v_fnd_rec;
  v_found               boolean := false;
Begin
  Open c_fnd_Cur;
  Fetch c_fnd_Cur Bulk Collect Into v_fnd_table;
  Close c_fnd_Cur;
  For idx In v_fnd_table.first..v_fnd_table.last
  Loop
     -- get responsiblity details
     v_resp_key := 'XXHR_EMPLOYEE_SELF_SERVICE';
     fnd_oid_subscriptions.get_resp_app_id(p_resp_key => v_resp_key
                                         ,x_responsibility_id=> v_responsibility_id
                                         ,x_application_id=> v_application_id      );
     -- verify assignments existing with user or not
     v_found := fnd_user_resp_groups_api.assignment_exists( user_id => v_fnd_table(idx).user_id
                                                          , responsibility_id   => v_responsibility_id
                                                          , responsibility_application_id => v_application_id
                                                          , security_group_id             => null);
     if (not v_found)  then
            fnd_user_resp_groups_api.insert_assignment( user_id  => v_fnd_table(idx).user_id
                                                      , responsibility_id => v_responsibility_id
                                                      , responsibility_application_id => v_application_id
                                                      , security_group_id  => null
                                                      , start_date  => sysdate
                                                      , end_date    => null
                                                      , description   => 'Employee Self Service'    );
     end if;
  End Loop;
  commit;
End;

SQL Query to list Active Responsibilities of a Active User

SQL Query to list Active Responsibilities of a Active User

SELECT fu.user_name,
       frv.responsibility_name,
      frv.responsibility_key,
      furgd.start_date,
       furgd.end_date
FROM fnd_user fu,
  fnd_user_resp_groups_direct furgd,
  fnd_responsibility_vl frv
WHERE fu.user_id                     = furgd.user_id
AND furgd.responsibility_id          = frv.responsibility_id
AND furgd.end_date                  IS NULL
AND furgd.start_date                <= sysdate
AND coalesce(furgd.end_date, sysdate + 1) > sysdate
AND fu.start_date                   <= sysdate
AND coalesce(fu.end_date, sysdate + 1)    > sysdate
AND frv.start_date                  <= sysdate
AND coalesce(frv.end_date, sysdate + 1)   > sysdate;

Oracle SQL Query to list all Form Personalizations

Oracle SQL Query to list all Form Personalizations

SELECT ffv.form_id                 ,
  ffv.form_name                     ,
  ffv.user_form_name                ,
  ffv.description ,
  ffcr.SEQUENCE                     ,
  ffcr.description "Rule Name",
  ffcr.trigger_object,
  ffcr.condition
  From fnd_form_vl ffv,
              fnd_form_custom_rules ffcr
  Where ffv.form_name = ffcr.form_name
  Order By ffv.form_name, ffcr.SEQUENCE;

How to delete a concurrent program in APPS

How to delete a concurrent program in APPS

Oracle application allows creation of concurrent programs but does not allow deletion of the concurrent programs. Through form user can disable a concurrent program.

We can use API for deleting concurrent programs and executables:

Delete the concurrent program. The script is

BEGIN
   fnd_program.delete_program (program_short_name   =>'XXPMSJB',
                                                 application          =>'XXPMS');
   COMMIT;
END;

The above concurrent program is deleted and it cannot be queried.


The executable exists. It can be deleted also using PL/SQL,


BEGIN
   fnd_program.delete_executable (executable_short_name   =>'XXPMSJB',
                                  application          =>'XXPMS');
   COMMIT;
END;

Calculating Employee Wise Earnings Deductions And Net Pay IN HRMS

Calculating Employee Wise Earnings Deductions And Net Pay IN HRMS

SELECT a.Earnings, b.Deductions, (a.Earnings - b.Deductions) Net_Pay
  FROM (  SELECT SUM (earnings) Earnings, emp_num
            FROM (SELECT papf.employee_number emp_num,
                         papf.full_name,
                         ppa.effective_date,
                         pp.payroll_name,
                         pet.element_name,
                         piv.name input_value,
                         TO_NUMBER (prrv.result_value) earnings
                    FROM apps.pay_payroll_actions ppa,
                         pay_assignment_actions paa,
                         pay_payrolls_f pp,
                         pay_run_results prr,
                         pay_run_result_values prrv,
                         pay_input_values_f piv,
                         pay_element_types_f pet,
                         apps.per_all_assignments_f paaf,
                         apps.per_all_people_f papf,
                         pay_element_classifications_tl pectl,
                         pay_element_classifications pec
                   WHERE --ppa.payroll_action_id = :payroll_action_id -- give your payroll_action_id
                                                                         --and
                         ppa.payroll_action_id = paa.payroll_action_id
                         AND ppa.payroll_id = pp.payroll_id
                         AND paa.assignment_action_id =
                                prr.assignment_action_id
                         AND prr.run_result_id = prrv.run_result_id
                         AND prrv.input_value_id = piv.input_value_id
                         AND piv.element_type_id = pet.element_type_id
                         AND paaf.assignment_id = paa.assignment_id
                         AND paaf.person_id = papf.person_id
                         AND piv.name = 'Pay Value'
                         AND pec.classification_id = pectl.classification_id
                         AND pectl.language = USERENV ('LANG')
                         -- Add All Earnings  CLASSIFICATION_NAMES or ID's
                         AND pec.CLASSIFICATION_NAME IN
                                ('Allowances',
                                 'Fringe Benefits',
                                 'Earnings',
                                 'Advances')
                         AND pet.classification_id = pec.classification_id
                         AND papf.employee_number =
                                NVL (:p_emp_num, papf.employee_number)
                         AND TRUNC (SYSDATE) BETWEEN pp.effective_start_date
                                                 AND pp.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date
                                                 AND pet.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN piv.effective_start_date
                                                 AND piv.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                                 AND paaf.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                                 AND papf.effective_end_date)
        GROUP BY emp_num) a,
       (  SELECT SUM (dectuctions) Deductions, emp_num
            FROM (SELECT papf.employee_number emp_num,
                         papf.full_name,
                         ppa.effective_date,
                         pp.payroll_name,
                         pet.element_name,
                         piv.name input_value,
                         TO_NUMBER (prrv.result_value) dectuctions
                    FROM apps.pay_payroll_actions ppa,
                         pay_assignment_actions paa,
                         pay_payrolls_f pp,
                         pay_run_results prr,
                         pay_run_result_values prrv,
                         pay_input_values_f piv,
                         pay_element_types_f pet,
                         apps.per_all_assignments_f paaf,
                         apps.per_all_people_f papf,
                         pay_element_classifications_tl pectl,
                         pay_element_classifications pec
                   WHERE --ppa.payroll_action_id = :payroll_action_id -- give your payroll_action_id
                                                                         --and
                         ppa.payroll_action_id = paa.payroll_action_id
                         AND ppa.payroll_id = pp.payroll_id
                         AND paa.assignment_action_id =
                                prr.assignment_action_id
                         AND prr.run_result_id = prrv.run_result_id
                         AND prrv.input_value_id = piv.input_value_id
                         AND piv.element_type_id = pet.element_type_id
                         AND paaf.assignment_id = paa.assignment_id
                         AND paaf.person_id = papf.person_id
                         AND piv.name = 'Pay Value'
                         AND pec.classification_id = pectl.classification_id
                         AND pectl.language = USERENV ('LANG')
                         --Add All Deductions CLASSIFICATION_NAMES  or ID's
                         AND pec.CLASSIFICATION_NAME IN
                                ('Involuntary Deductions')
                         AND pet.classification_id = pec.classification_id
                         AND papf.employee_number =
                                NVL (:p_emp_num, papf.employee_number)
                         AND TRUNC (SYSDATE) BETWEEN pp.effective_start_date
                                                 AND pp.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date
                                                 AND pet.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN piv.effective_start_date
                                                 AND piv.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                                 AND paaf.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                                 AND papf.effective_end_date)
        GROUP BY emp_num) b
 WHERE a.emp_num = b.emp_num;

Employee Bank Account Details In HRMS

SELECT DISTINCT PAPF.PERSON_ID,
                  PAPF.EMPLOYEE_NUMBER,
                  PAAF.ASSIGNMENT_ID,
                  papf.full_name,
                  hl.meaning Bank_name,
                  --pea.segment3,
                  pea.segment1 Acc_no,
                  pea.segment2 Acc_type,
                  pp.BUSINESS_GROUP_ID
    FROM PER_ALL_PEOPLE_F PAPF,
         PER_ALL_ASSIGNMENTS_F PAAF,
         hr_lookups hl,
         pay_external_accounts pea,
         pay_personal_payment_methods_f pp
   WHERE     hl.lookup_code = pea.segment3
         AND pp.external_account_id = pea.external_account_id
         AND PAPF.PERSON_ID = PAAF.PERSON_ID
         AND pp.assignment_id = paaf.assignment_id
         AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_dATE
                                 AND PAPF.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE
                                 AND PAAF.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN pp.effective_start_date
                                 AND pp.effective_end_date
         AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
         AND PAAF.PRIMARY_FLAG = 'Y'
         AND papf.employee_number = NVL (:p_employee_Num, papf.employee_number)
         AND hl.LOOKUP_TYPE = 'MX_BANK'
--AND hl.meaning = NVL (:P_Bank_Name, hl.meaning)
--AND pea.segment1 = NVL (:P_Bank_Accc_Num, pea.segment1)
--and papf.employee_number = 500013
ORDER BY PAPF.EMPLOYEE_NUMBER;

LIST OF KEY FLEXFIELDS IN ORACLE APPS R12

KFF ID	KFF NAME	MODULE
GLLE	GL Ledger Flexfield	General Ledger
GL#	Accounting Flexfield	General Ledger
GLAT	Reporting Attributes:Accountin	General Ledger
CAT#	Category Flexfield	Assets
KEY#	Asset Key Flexfield	Assets
LOC#	Location Flexfield	Assets
CT#	Territory Flexfield	Receivables
RLOC	Sales Tax Location Flexfield	Receivables
FEAC	Activity Flexfield	Enterprise Performance Foundation
FECO	Cost Object Flexfield	Enterprise Performance Foundation
SERV	Service Items	Inventory
MCAT	Item Categories	Inventory
MDSP	Account Aliases	Inventory
MICG	Item Catalogs	Inventory
MKTS	Sales Orders	Inventory
MSTK	System Items	Inventory
MTLL	Stock Locators	Inventory
FII#	Management Flexfield	Financial Intelligence
FWK	FWK Item Flexfield	Common Modules-AK
GRD	Grade Flexfield	Human Resources
JOB	Job Flexfield	Human Resources
PEA	Personal Analysis Flexfield	Human Resources
POS	Position Flexfield	Human Resources
SCL	Soft Coded KeyFlexfield	Human Resources
ICX	Item Contexts Keyflex	Human Resources
CAGR	CAGR Flexfield	Human Resources
CMP	Competence Flexfield	Human Resources
BANK	Bank Details KeyFlexField	Payroll
COST	Cost Allocation Flexfield	Payroll
GRP	People Group Flexfield	Payroll
RES	Training Resources	Learning Management
ACCT	Account Structure	Asia/Pacific Localizations
BPS	PSB Position Flexfield	Public Sector Budgeting
GRP#	Group Asset	Capital Resource Logistics – Assets
SGP#	Super Group	Capital Resource Logistics – Assets
AHLO	AHL Operation	Complex Maintenance Repair and Overhaul
AHLR	AHL Route	Complex Maintenance Repair and Overhaul
ZX#	eBTax Context Flexfield	E-Business Tax