How to upload flex values in value set in Oracle Applications

CREATE OR REPLACE PROCEDURE APPS.CUST_LOAD_FLEX_VALUES

IS

   CURSOR C_COMP

   IS

    SELECT FLEX_VALUE_SET_NAME, FLEX_VALUE, FLEX_VALUE_MEANING, DESCRIPTION,

           ENABLED_FLAG, SUMMARY_FLAG, QUALIFIER1, QUALIFIER2, QUALIFIER3,

           HIERARCHY_LEVEL

      FROM DEV_SNGPL_ENTITY;

     

  /*

      CREATE TABLE DEV_SNGPL_ENTITY

(

  FLEX_VALUE_SET_NAME  VARCHAR2(60 BYTE),

  FLEX_VALUE           VARCHAR2(150 BYTE),

  FLEX_VALUE_MEANING   VARCHAR2(150 BYTE),

  DESCRIPTION          VARCHAR2(240 BYTE),

  ENABLED_FLAG         VARCHAR2(1 BYTE),

  SUMMARY_FLAG         VARCHAR2(1 BYTE),

  QUALIFIER1           VARCHAR2(1 BYTE),

  QUALIFIER2           VARCHAR2(1 BYTE),

  QUALIFIER3           VARCHAR2(1 BYTE),

  FINAL_QUALIFIER      VARCHAR2(2000 BYTE),

  FLEX_VALUE_SET_ID    NUMBER(10),

  FLEX_VALUE_ID        NUMBER(15),

  HIERARCHY_LEVEL      VARCHAR2(30 BYTE)

)*/

   --V_ENABLED_FLAG           VARCHAR2 (2)              := 'Y';

   --V_SUMMARY_FLAG          VARCHAR2 (2)              := 'Y';

   V_START_DATE_ACTIVE       DATE                              := SYSDATE;

   V_ERROR_MSG               VARCHAR2 (1000)       := NULL;

   V_WHO_TYPE                FND_FLEX_LOADER_APIS.WHO_TYPE;

   V_REQUEST_ID              NUMBER;

   V_REC_SUCCESS             NUMBER;

   V_REC_ERROR               NUMBER;

   V_REC_CNT                 NUMBER                        := 0;

   V_USER_ID                 NUMBER                        := FND_GLOBAL.USER_ID;

   V_LOGIN_ID                NUMBER                        := FND_GLOBAL.LOGIN_ID;

   V_REQ_ID                  NUMBER                        := FND_GLOBAL.CONC_REQUEST_ID;

  

   lc_c_comp                c_comp%ROWTYPE;

 

          

   BEGIN

       OPEN C_COMP;

       LOOP

          FETCH C_COMP

           INTO LC_C_COMP;

          EXIT WHEN C_COMP%NOTFOUND;

            DBMS_OUTPUT.PUT_LINE('Before Begin');  

  

               BEGIN

               

                  

                   V_WHO_TYPE.CREATED_BY := V_USER_ID;

                   V_WHO_TYPE.CREATION_DATE := SYSDATE;

                   V_WHO_TYPE.LAST_UPDATED_BY := V_USER_ID;

                   V_WHO_TYPE.LAST_UPDATE_DATE := SYSDATE;

                   V_WHO_TYPE.LAST_UPDATE_LOGIN := V_LOGIN_ID;

                   

                  DBMS_OUTPUT.PUT_LINE('After Begin');

                                       

                  FND_FLEX_VALUES_PKG.LOAD_ROW

                           (X_FLEX_VALUE_SET_NAME             => LC_C_COMP.FLEX_VALUE_SET_NAME,

                            X_PARENT_FLEX_VALUE_LOW           => NULL,

                            X_FLEX_VALUE                      => LC_C_COMP.FLEX_VALUE,

                            X_WHO                             => V_WHO_TYPE,

                            X_ENABLED_FLAG                    => LC_C_COMP.ENABLED_FLAG ,

                            X_SUMMARY_FLAG                    => LC_C_COMP.SUMMARY_FLAG,

                            X_START_DATE_ACTIVE               => V_START_DATE_ACTIVE,

                            X_END_DATE_ACTIVE                 => NULL,

                            X_PARENT_FLEX_VALUE_HIGH          => NULL,

                            X_STRUCTURED_HIERARCHY_LEVEL      => NULL,

                            X_HIERARCHY_LEVEL                 => LC_C_COMP.HIERARCHY_LEVEL,

                            X_COMPILED_VALUE_ATTRIBUTES       => NULL,

                            X_VALUE_CATEGORY                  => NULL,

                            X_ATTRIBUTE1                      => NULL,

                            X_ATTRIBUTE2                      => NULL,

                            X_ATTRIBUTE3                      => NULL,

                            X_ATTRIBUTE4                      => NULL,

                            X_ATTRIBUTE5                      => NULL,

                            X_ATTRIBUTE6                      => NULL,

                            X_ATTRIBUTE7                      => NULL,

                            X_ATTRIBUTE8                      => NULL,

                            X_ATTRIBUTE9                      => NULL,

                            X_ATTRIBUTE10                     => NULL,

                            X_ATTRIBUTE11                     => NULL,

                            X_ATTRIBUTE12                     => NULL,

                            X_ATTRIBUTE13                     => NULL,

                            X_ATTRIBUTE14                     => NULL,

                            X_ATTRIBUTE15                     => NULL,

                            X_ATTRIBUTE16                     => NULL,

                            X_ATTRIBUTE17                     => NULL,

                            X_ATTRIBUTE18                     => NULL,

                            X_ATTRIBUTE19                     => NULL,

                            X_ATTRIBUTE20                     => NULL,

                            X_ATTRIBUTE21                     => NULL,

                            X_ATTRIBUTE22                     => NULL,

                            X_ATTRIBUTE23                     => NULL,

                            X_ATTRIBUTE24                     => NULL,

                            X_ATTRIBUTE25                     => NULL,

                            X_ATTRIBUTE26                     => NULL,

                            X_ATTRIBUTE27                     => NULL,

                            X_ATTRIBUTE28                     => NULL,

                            X_ATTRIBUTE29                     => NULL,

                            X_ATTRIBUTE30                     => NULL,

                            X_ATTRIBUTE31                     => NULL,

                            X_ATTRIBUTE32                     => NULL,

                            X_ATTRIBUTE33                     => NULL,

                            X_ATTRIBUTE34                     => NULL,

                            X_ATTRIBUTE35                     => NULL,

                            X_ATTRIBUTE36                     => NULL,

                            X_ATTRIBUTE37                     => NULL,

                            X_ATTRIBUTE38                     => NULL,

                            X_ATTRIBUTE39                     => NULL,

                            X_ATTRIBUTE40                     => NULL,

                            X_ATTRIBUTE41                     => NULL,

                            X_ATTRIBUTE42                     => NULL,

                            X_ATTRIBUTE43                     => NULL,

                            X_ATTRIBUTE44                     => NULL,

                            X_ATTRIBUTE45                     => NULL,

                            X_ATTRIBUTE46                     => NULL,

                            X_ATTRIBUTE47                     => NULL,

                            X_ATTRIBUTE48                     => NULL,

                            X_ATTRIBUTE49                     => NULL,

                            X_ATTRIBUTE50                     => NULL,

                            X_ATTRIBUTE_SORT_ORDER            => NULL,

                            X_FLEX_VALUE_MEANING              => LC_C_COMP.FLEX_VALUE_MEANING,

                            X_DESCRIPTION                     => LC_C_COMP.DESCRIPTION

                           );

                         

               END;

                DELETE FROM DEV_SNGPL_ENTITY

                WHERE FLEX_VALUE = LC_C_COMP.FLEX_VALUE;

               

                COMMIT;              

           END LOOP;

          

EXCEPTION

 WHEN OTHERS

    THEN

      DBMS_OUTPUT.PUT_LINE ('ERROR IS ' || SUBSTR (SQLERRM, 1, 1000));

    

END;

/

Read more

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 :)

Read more

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.

Read more

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.

Read more