How to use distinct in Value set in Oracle Apps

Value sets in Oracle Apps

Solution 1:

Either you create a View and use it in place of table in value set. You can create your query with distinct values in the view and later use it in value set.

CREATE OR REPLACE VIEW CUST_EXAMPLE

AS

   SELECT DISTINCT EMPLOYEE_NUMBER

              FROM PER_PEOPLE_F PPF

             WHERE SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE

                               AND PPF.EFFECTIVE_END_DATE;

Solution 2:

You can directly use query in place of table name in Value set.

(SELECT DISTINCT EMPLOYEE_NUMBER

              FROM PER_PEOPLE_F PPF

             WHERE SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE

                               AND PPF.EFFECTIVE_END_DATE) ab

Kindly provide your feedback.

Have a nice day :)

How to avoid duplicate record in Oracle Applications Custom forms

It is a very common requirement that end user should not be able to enter same record multiple times.

Solution

Create a function under Program Units in Oracle Forms Builder

FUNCTION DUPLICATE_ENTRY_CHECK (BLK_COL_NAME VARCHAR2)

   RETURN BOOLEAN

IS

   IS_DUPLICATE     BOOLEAN       := FALSE;

   CURRENT_REC_NO   NUMBER (3)    := NULL;

   LAST_REC_NO      NUMBER (3)    := NULL;

   CUR_VAL          VARCHAR2 (20) := NULL;

   FORM_NAME        VARCHAR2 (40) := NULL;

BEGIN

   CURRENT_REC_NO := TO_NUMBER (NAME_IN ('SYSTEM.CURSOR_RECORD'));

   CUR_VAL := NAME_IN (BLK_COL_NAME);

   FORM_NAME := GET_APPLICATION_PROPERTY (CURRENT_FORM_NAME);

   SET_FORM_PROPERTY (FORM_NAME, VALIDATION, PROPERTY_FALSE);

   LAST_RECORD;

   IF NAME_IN ('SYSTEM.CURSOR_RECORD') <> '1'

   THEN

      LAST_REC_NO := TO_NUMBER (NAME_IN ('SYSTEM.CURSOR_RECORD'));

      FIRST_RECORD;

      FOR ROW_COUNT IN 1 .. LAST_REC_NO

      LOOP

         IF :SYSTEM.CURSOR_RECORD <> CURRENT_REC_NO

         THEN

            IF CUR_VAL = NAME_IN (BLK_COL_NAME)

            THEN

               IS_DUPLICATE := TRUE;

               EXIT;

            END IF;

         END IF;

         IF LAST_REC_NO <> ROW_COUNT

         THEN

            NEXT_RECORD;

         END IF;

      END LOOP;

   END IF;

   SET_FORM_PROPERTY (FORM_NAME, VALIDATION, PROPERTY_TRUE);

   IF IS_DUPLICATE = TRUE

   THEN

      GO_RECORD (CURRENT_REC_NO);

      GO_ITEM (BLK_COL_NAME);

      RETURN TRUE;

   ELSE

      GO_RECORD (CURRENT_REC_NO);

      GO_ITEM (BLK_COL_NAME);

      RETURN FALSE;

   END IF;

END;

Go to relevant item and create a trigger "KEY-NEXT-ITEM"

Example

DECLARE

   AL_ID       ALERT;

   AL_BUTTON   NUMBER;

BEGIN

   IF :XX_BB_LINES.SUPPLIER_ID IS NOT NULL

   THEN

      IF NOT DUPLICATE_ENTRY_CHECK ('XX_BB_LINES.SUPPLIER_ID')

      THEN

         --:PO_DETAIL.SALES_TAX:=5;

         NEXT_ITEM;

      ELSE

         -- PRU_MESSAGE(1,'DUPLICATE ENTRY IS NOT ALLOWED.');

         AL_ID := FIND_ALERT ('SUPPLIER');

         AL_BUTTON := SHOW_ALERT (AL_ID);

         RAISE FORM_TRIGGER_FAILURE;

      END IF;

   END IF;

END;

Now go to relevant block and also create a trigger named "KEY-COMMIT"

Example

DECLARE

   AL_ID       ALERT;

   AL_BUTTON   NUMBER;

BEGIN

   IF DUPLICATE_ENTRY_CHECK ('XX_BB_LINES.SUPPLIER_ID')

   THEN

      AL_ID := FIND_ALERT ('SUPPLIER');

      AL_BUTTON := SHOW_ALERT (AL_ID);

      RAISE FORM_TRIGGER_FAILURE;

   ELSE

      COMMIT_FORM;

   END IF;

END;

This activity will not let the user to enter duplicate data even if previous data is not saved.

Kindly provide your feedback.

Retro Pay by Element in Oracle HRMS Payroll

Retro Pay Setup in Oracle Payroll

What is retro pay??

Retro pay is usually run for back dated increments or promotions to be paid in the current period. For instance, in a company, Increment arrears are paid two months after the increment period, so we need to run retro pay to make this payment in current period.

How do we do it??

1) Create an Event Group. See How to create Event Group

2) Attach it to the Element --> Recalculation Tab

3) Make a back dated salary Increment

Consider we are currently running the payroll for period Mar-10 and we made a salary increment in the period Jan-10 and payroll for Jan-10 and Feb-10 is already run.

                       

4) Create assignment set 

Create assignment set to include your desired list of employees for which you want to pay increment.

See How to create Assignment Sets

5) Create Element Set

You may include the elements for which increment are paid. Differential amount (Arrears) will be shown separately for each element, as represented by process name "Retro pay by element"

6) Run Concurrent Program "Retro Pay by Element"

 7) View Element Entries

Global HRMS Manager --> People --> Enter & Maintain --> Assignment --> Entries

As you can see there are 3 entries for Proration Element (Proration formula used) for JAN-10

                           

Proration formula is run for the period JAN-10, See How to use Proration formula

Differentials:

JAN-10      4,193

FEB-10      10,000

MAR-10    10,000

I hope this post is helpful for you.

Kindly provide your feedback.

Leave a comment for any query.