Restricting Absence Type LOV in Oracle Self Service

Oracle has given easy way to handle absence type LOV in SSHR.

Using Seeded package “HR_ABSENCE_RESTRICTED” we can achieve this by returning absence attendance type Id.

Below is sample code, where based on Responsibility how we can restrict absence types.

CREATE OR REPLACE PACKAGE BODY APPS.hr_absence_restricted

AS

   /* $Header: peabrest.pkb 120.0.12010000.4 2010/03/18 10:25:49 ghshanka noship $ */

   -- ----------------------------------------------------------------------------

   -- |----------------------< absences_restricted >--------------------------|

   -- Added by Sameer Patil

   -- ----------------------------------------------------------------------------

   --

   FUNCTION absences_restricted (selected_person_id   IN VARCHAR2,

                                 login_person_id      IN VARCHAR2)

      RETURN VARCHAR2

   IS

      l_count                 NUMBER;

      l_responsibility_name   VARCHAR2 (2000);

   BEGIN

      l_responsibility_name := fnd_profile.VALUE ('RESP_NAME'); -- added by Sameer Patil

      IF l_responsibility_name LIKE '%Employee%Self%'

      THEN

         --D.M ('After IF ');

         RETURN '2116’; -- Say '2116’ is Unpaid Leave

      ELSIF l_responsibility_name LIKE '%Manager%Self%'

      THEN

       RETURN ‘1019’; -- Say ‘1019’ is Unpaid Leave

      ELSE

         RETURN '-1';                                  -- do not return null..

      END IF;

   END absences_restricted;

--

END hr_absence_restricted;

/

"Team Work" More We Less Me.

Query to get Vacancy Details in Oracle Apps - iRec

Query to get Vacancy Details in Oracle iRec

SELECT name vacancy_name,
       attribute1 requestition_number,
       hr_general.decode_organization (BUSINESS_GROUP_ID) business_group,
       (SELECT name
          FROM per_all_positions
         WHERE position_id = paa.position_id AND ROWNUM = 1),
       hr_general.DECODE_JOB (job_id),
       hr_general.DECODE_GRADE (grade_id) grade,
       hr_general.decode_organization (ORGANIZATION_ID),
       hr_general.decode_location (LOCATION_ID),
       date_from,
       date_to,
       Status,
       (SELECT ppf.full_name || '(' || ppf.employee_number || ')'
          FROM per_all_people_f ppf, fnd_user fu
         WHERE     TRUNC (SYSDATE) BETWEEN effective_start_date
                                       AND effective_end_date
               AND ppf.person_id = fu.employee_id
               AND fu.user_id = paa.CREATED_BY)
          created_by,
       (SELECT ppf.full_name || '(' || ppf.employee_number || ')'
          FROM per_all_people_f ppf
         WHERE     TRUNC (SYSDATE) BETWEEN effective_start_date
                                       AND effective_end_date
               AND ppf.person_id = MANAGER_ID)
          manager,
       position_id
  FROM PER_ALL_VACANCIES paa

Stay Tuned for more updates.


"Team Work" More We Less Me.

FNDLOAD in Oracle ERP

To FNDLOAD Request groups

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REP_GROUP.ldt REQUEST_GROUP REQUEST_GROUP_NAME="GROUP_NAME" APPLICATION_SHORT_NAME="PER"

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REP_GROUP.ldt


------------------------------------------------------------------------------------------


To FNDLOAD Concurrent Programs

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CUSTOM_ORACLE_INTERFACE_PROG"

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt


------------------------------------------------------------------------------------------


To FNDLOAD Oracle Descriptive Flexfields

$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_REQ_HEADERS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PER DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_HEADERS'

$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_REQ_HEADERS_DFF.ldt

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FND_COMMON_LOOKUPS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=FND DESCRIPTIVE_FLEXFIELD_NAME='FND_COMMON_LOOKUPS'

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PA_EXPENDITURE_TYPES_DESC_FLEX_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PA DESCRIPTIVE_FLEXFIELD_NAME='PA_EXPENDITURE_TYPES_DESC_FLEX'

------------------------------------------------------------------------------------------


To FNDLOAD Oracle Menus

$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct CUS_PER_MENU.ldt MENU MENU_NAME="CUS_PER_MENU"

$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct CUS_PER_MENU.ldt


----------------------------------------------------------------------------------------------------------------------------

FND Messages to download a single message

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUS_MESSAGE.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='PER' MESSAGE_NAME=XX_ICX_POR_LIFECYCLE_PAY_TIP

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUS_MESSAGE_OO.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='PER'


FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUS_MESSAGE_OO.ldt


----------------------------------------------------------------------------------------------------------------------------

Lookup values

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD aflvmlu.lct XX_LOOKUP_LDT.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='PER' LOOKUP_TYPE="XX_LOOKUP_LDT"

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD aflvmlu.lct XX_LOOKUP_LDT.ldt

----------------------------------------------------------------------------------------------------------------------------

Responsibility

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_RESP_NAME.ldt FND_RESPONSIBILITY RESP_KEY="XX_RESP_NAME"


FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_RESP_NAME.ldt


----------------------------------------------------------------------------------------------------------------------------
Forms personalizations

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt FND_FORM_CUSTOM_RULES function_name="PERWSHRG-404"

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt FND_FORM_CUSTOM_RULES function_name="HZ_ARXCUDCI_STD"

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt FND_FORM_CUSTOM_RULES function_name="AP_APXVDMVD"


FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt

Write me for any more clarification.

Stay tuned !!!!!

"Team Work" More We Less Me.

Welcome to Oracle Self Learning!

Oracle Payroll - GL  Interface query.

SELECT gl.*
  FROM pay_payroll_actions ppa,
       pay_assignment_actions paa,
       pay_costs pcv,
       gl_interface gl
 WHERE     ppa.payroll_action_id = 201496               -- for costing process
       AND ppa.action_type = 'C'
       AND pcv.transfer_to_gl_flag = 'Y'
       AND ppa.payroll_action_id = paa.payroll_action_id
       AND paa.assignment_action_id = pcv.assignment_action_id
       AND gl.user_je_source_name = 'Payroll'
       AND pcv.ASSIGNMENT_ACTION_ID = 1275993 -- for costing process
       AND gl.reference22 = TO_CHAR (pcv.cost_allocation_keyflex_id)

Team Work More "We" Less "Me"