Search This Blog

Sunday, June 26, 2016

Validation on HR Special Info (SIT) in SSHR Pages:

Validation on HR Special Info (SIT) in SSHR Pages:

User hooks doesn’t work on SIT SSHR Pages for validations but works once the final approval is done.
This is known bug.

To overcome above issue we will implement logic in FND_FLEX_PLSQL package.

Requirement : To make validation on SIT SSHR page so the user can update once and submit for approval, once the user update the same record twice which is under approval, then the error message should appear.

new update.........

     0. create one new segment in the structure named 'sequence' not mandatory and hidden.
    valuset10 char, default type sql,
SELECT F_FND_FLEX_S.NEXTVAL FROM DUAL WHERE :$FLEX$.FND_DATE4_REQUIRED2 IS NOT NULL

f_fnd_flex_s is the sequence in the database created custom.
FND_DATE4_REQUIRED2 is the valuset for the resumption_date, once this date changed/updated the sequence will update auto.

as the package FND_FLEX_PLSQL mentioned in the step7 will validate/fires only when there is no code combination exists, so we need to have difference code combinations by generating the sequence to validate everytime.

Oracle reference: How to avoid that FND_FLEX_PLSQL.VALIDATE() is not triggered when the same data is re-entered ? (Doc ID 2023950.1)

  1. SIT Open the record
  1. Updating the record of joining Date (or Resumption)
  1. Click Apply,

  1. Click Next, Submit

  1. Confirmed.

  1. Lets update the same transaction , when its under approval.

When clicked ‘Apply’ button the error appears.


  1. Package Body:

CREATE OR REPLACE PACKAGE BODY APPS.FND_FLEX_PLSQL AS
/* $Header: AFFFPLVB.pls 115.0 99/07/16 23:18:46 porting ship $
Modified by Afzal for SIT Validation on 28-06-2016
*/

 FUNCTION validate(application_id         IN   NUMBER,
           id_flex_code           IN   VARCHAR2,
           id_flex_num            IN   NUMBER,
                   vdate                  IN   DATE,
                   segment_delimiter      IN   VARCHAR2,
                   concatenated_segments  IN   VARCHAR2,
                   numsegs                IN   NUMBER,
                   user_segment1          IN   VARCHAR2,
                   user_segment2          IN   VARCHAR2,
                   user_segment3          IN   VARCHAR2,
                   user_segment4          IN   VARCHAR2,
                   user_segment5          IN   VARCHAR2,
                   user_segment6          IN   VARCHAR2,
                   user_segment7          IN   VARCHAR2,
                   user_segment8          IN   VARCHAR2,
                   user_segment9          IN   VARCHAR2,
                   user_segment10         IN   VARCHAR2,
                   user_segment11         IN   VARCHAR2,
                   user_segment12         IN   VARCHAR2,
                   user_segment13         IN   VARCHAR2,
                   user_segment14         IN   VARCHAR2,
                   user_segment15         IN   VARCHAR2,
                   user_segment16         IN   VARCHAR2,
                   user_segment17         IN   VARCHAR2,
                   user_segment18         IN   VARCHAR2,
                   user_segment19         IN   VARCHAR2,
                   user_segment20         IN   VARCHAR2,
                   user_segment21         IN   VARCHAR2,
                   user_segment22         IN   VARCHAR2,
                   user_segment23         IN   VARCHAR2,
                   user_segment24         IN   VARCHAR2,
                   user_segment25         IN   VARCHAR2,
                   user_segment26         IN   VARCHAR2,
                   user_segment27         IN   VARCHAR2,
                   user_segment28         IN   VARCHAR2,
                   user_segment29         IN   VARCHAR2,
                   user_segment30         IN   VARCHAR2,
                   error_message          OUT  VARCHAR2)
          return BOOLEAN IS
          L_COUNT_LEV_SIT NUMBER;
          lb_return       BOOLEAN ;
          l_join_date_exists varchar2(200);
          L_TRANSACTION_VALUE_ID NUMBER;
          L_DATE_START DATE; L_END_DATE DATE; L_JOIN_DATE DATE; L_LEAVE_NAME NUMBER; L_LATE_REASON VARCHAR2(200);
         

BEGIN
   if id_flex_num= 50318 THEN
                  BEGIN
                  DECLARE
CURSOR C1 IS                 
SELECT * --INTO L_TRANSACTION_VALUE_ID, L_DATE_START, L_END_DATE, L_JOIN_DATE, L_LEAVE_NAME, L_LATE_REASON
FROM(
SELECT TRANSACTION_VALUE_ID,fnd_conc_date.string_to_date(PAC.SEGMENT1) DATE_START,fnd_conc_date.string_to_date(PAC.SEGMENT2) END_DATE,
fnd_conc_date.string_to_date(PAC.SEGMENT3) JOIN_DATE,
(SELECT ABSENCE_ATTENDANCE_TYPE_ID FROM PER_ABS_ATTENDANCE_TYPES_TL
WHERE NAME = PAC.SEGMENT8
AND LANGUAGE = 'AR')LEAVE_NAME,
PAC.SEGMENT11 LATE_REASON
from
hr_api_transaction_values hrtv
,per_person_analyses ppa
,per_analysis_criteria pac
,hr_api_transactions hat
,hr_api_transaction_steps hats
where
NAME = 'P_OLD_ANALYSIS_CRITERIA_ID'
AND NUMBER_VALUE = PPA.ANALYSIS_CRITERIA_ID
AND PPA.PERSON_ID =  hat.SELECTED_PERSON_ID--FND_PROFILE.VALUE('PER_PERSON_ID')
AND PPA.ANALYSIS_CRITERIA_ID = PAC.ANALYSIS_CRITERIA_ID
AND hat.transaction_id = hats.transaction_id
and hats.TRANSACTION_STEP_ID = hrtv.TRANSACTION_STEP_ID
AND hat.SELECTED_PERSON_ID = FND_PROFILE.VALUE('PER_PERSON_ID')
AND hat.status IN ('W', 'S', 'Y', 'YS', 'RI', 'RIS','E')
AND fnd_conc_date.string_to_date(PAC.SEGMENT1) = fnd_conc_date.string_to_date(user_segment2)
AND fnd_conc_date.string_to_date(PAC.SEGMENT2) = fnd_conc_date.string_to_date(user_segment3)
UNION
select TO_NUMBER(''),fnd_conc_date.string_to_date(''),fnd_conc_date.string_to_date(''),
fnd_conc_date.string_to_date(PAC.SEGMENT3),
TO_NUMBER(''),''
from
per_person_analyses ppa
,per_analysis_criteria pac
where
 PPA.PERSON_ID in (select  hat.SELECTED_PERSON_ID from hr_api_transactions hat where selected_person_id =  FND_PROFILE.VALUE('PER_PERSON_ID'))--FND_PROFILE.VALUE('PER_PERSON_ID')
AND PPA.ANALYSIS_CRITERIA_ID = PAC.ANALYSIS_CRITERIA_ID
AND fnd_conc_date.string_to_date(PAC.SEGMENT1) = fnd_conc_date.string_to_date(user_segment2)
AND fnd_conc_date.string_to_date(PAC.SEGMENT2) = fnd_conc_date.string_to_date(user_segment3)
AND fnd_conc_date.string_to_date(PAC.SEGMENT3) IS NOT NULL
);

          BEGIN
FOR I  IN C1 LOOP
IF C1%NOTFOUND THEN
        L_JOIN_DATE := NULL;
        L_TRANSACTION_VALUE_ID := 0;
        --below condition to make if any pending transaction for approval pending, no need function , its just for testing
ELSIF F_GET_FNDFLEX_VALID(FND_PROFILE.VALUE('PER_PERSON_ID'), fnd_conc_date.string_to_date(I.DATE_START), fnd_conc_date.string_to_date(I.END_DATE)) = 'Y' OR--TO_DATE('09-Aug-2015'),TO_DATE('23-Aug-2015') 
 I.JOIN_DATE IS NOT NULL OR  I.TRANSACTION_VALUE_ID IS NOT NULL  THEN
   /*lb_return := FALSE;
   error_message  := 'Under IF FUNCTION F_GET_FNDFLEX_VALID--->2-->'   ||fnd_conc_date.string_to_date(user_segment2)||'--3-->'||fnd_conc_date.string_to_date(user_segment3)
   ||'--4-->'||fnd_conc_date.string_to_date(user_segment4);
   */
                           lb_return := FALSE;
                           error_message := 'under approval-';
                     hr_utility.set_message (800,'FUJ_JOINDATE_PEND_VALID');
                     hr_utility.raise_error;
END IF;
               END LOOP;

        EXCEPTION WHEN OTHERS THEN
        L_JOIN_DATE := NULL;
        L_TRANSACTION_VALUE_ID := 0;
           END;
                END;         
  END IF;
--belo condition, if resmption is more than 1 day , need to make the resume_late_type mandatory
IF
    ( fnd_conc_date.string_to_date(user_segment4) - fnd_conc_date.string_to_date(user_segment3) > 1 AND  user_segment10 IS NULL ) THEN
   /*lb_return := FALSE;
   error_message  := 'Under IF FUNCTION F_GET_FNDFLEX_VALID--->2-->'   ||fnd_conc_date.string_to_date(user_segment2)||'--3-->'||fnd_conc_date.string_to_date(user_segment3)
   ||'--4-->'||fnd_conc_date.string_to_date(user_segment4);
   */
                        lb_return := FALSE;
                     hr_utility.set_message (800,'FUJ_LATE_REASON_MAND');
                     hr_utility.raise_error;
   END IF;
   return lb_return;

 END validate;

END FND_FLEX_PLSQL;
/



Useful Points:
1. The segments of our Structure will not be same segments as user_segment1 or user_segment2. to overcome just pass the parameters in error_message and get the values, if the segments are ok, then use the parameter segments in your logic. As I used the parameters segments in my logic.
Posted by at

7 comments:

  1. Syed Farhan AshrafJune 26, 2016 at 12:49 AM

    Mashallah,, This is great article indeed.

    ReplyDelete
  2. Amir El-TayebJune 19, 2017 at 10:54 PM

    Really very useful..

    Thanks sooo much.with best regards,

    ReplyDelete
  3. MubeenApril 1, 2018 at 10:22 PM

    where is the code of "F_GET_FNDFLEX_VALID" function as it is calling inside the package body?

    ReplyDelete
    Replies
    1. Oracle Apps KTOctober 27, 2019 at 9:13 PM

      CREATE OR REPLACE FUNCTION APPS.F_GET_FNDFLEX_VALID(P_PERSON_ID NUMBER, P_SEGMENT1 DATE, P_SEGMENT2 DATE)
      RETURN VARCHAR2 AS
      L_TRANSACTION_VALUE_ID NUMBER;
      L_COUNT_LEV_SIT NUMBER;
      L_DATE_START DATE; L_END_DATE DATE; L_JOIN_DATE DATE; L_LEAVE_NAME NUMBER; L_LATE_REASON VARCHAR2(200);
      BEGIN
      --Validating if leave exists under approval/wip/saveforLater
      SELECT COUNT(TRANSACTION_VALUE_ID) INTO L_COUNT_LEV_SIT
      from
      hr_api_transaction_values hrtv
      ,per_person_analyses ppa
      ,per_analysis_criteria pac
      ,hr_api_transactions hat
      ,hr_api_transaction_steps hats
      where
      NAME = 'P_OLD_ANALYSIS_CRITERIA_ID'
      AND NUMBER_VALUE = PPA.ANALYSIS_CRITERIA_ID
      AND PPA.PERSON_ID = P_PERSON_ID--FND_PROFILE.VALUE('PER_PERSON_ID')
      AND PPA.ANALYSIS_CRITERIA_ID = PAC.ANALYSIS_CRITERIA_ID
      AND hat.transaction_id = hats.transaction_id
      and hats.TRANSACTION_STEP_ID = hrtv.TRANSACTION_STEP_ID
      --AND hat.CREATOR_PERSON_ID = FND_PROFILE.VALUE('PER_PERSON_ID')
      AND hat.SELECTED_PERSON_ID = P_PERSON_ID--FND_PROFILE.VALUE('PER_PERSON_ID')
      --and status in 'Y'
      AND hat.status IN ('W', 'S', 'Y', 'YS', 'RI', 'RIS','E')
      AND fnd_conc_date.string_to_date(PAC.SEGMENT1) = P_SEGMENT1
      AND fnd_conc_date.string_to_date(PAC.SEGMENT2) = P_SEGMENT2;
      IF L_COUNT_LEV_SIT >=1 THEN
      RETURN 'Y';
      ELSE
      RETURN 'N';
      END IF;
      EXCEPTION WHEN OTHERS THEN RETURN 'N';
      END;
      /

      Delete
  4. MohammedSeptember 27, 2019 at 5:24 AM

    which profile option for FND_FLEX_PLSQL must be enable . and on which level also what value should be assigned (YES,NO)?

    ReplyDelete
    Replies
    1. Oracle Apps KTOctober 27, 2019 at 9:12 PM

      FND_FLEX_PLSQL is a plsql package. No profile option is needed to enable it. Just test it with above code change as per ur requirement, it will work insha Allah.

      Delete
  5. ShiakDecember 20, 2021 at 2:54 AM

    Hi,

    user_segment29 IN VARCHAR2,
    user_segment30 IN VARCHAR2,
    error_message OUT VARCHAR2
    ) RETURN BOOLEAN IS
    BEGIN

    error_message:='FND QuEST_HR_Letters';
    IF id_flex_num = 50539 THEN --QuEST_HR_Letters
    hr_utility.set_message(800, 'XXQU_HR_LETTERS_ADDR_VAL');
    hr_utility.raise_error;
    END IF;

    RETURN true;
    END validate;

    END fnd_flex_plsql;

    I tried to raise error with the above code, no where its triggering in SIT form nor in self service
    post changes any bounce required, present verison is R12.2

    ReplyDelete

Subscribe to: Post Comments (Atom)