get_context(hr_assignment

Oracle HCM Fast Formula

Thursday, april 7, 2016, fast formulas and arrays.

Input Values
Return values.
Type of Value - indicates the value type (Number, Date or Char) stored in the Array.
Type of Index - indicates how the Array is indexed.

/ *************************************************

Name: TCS_PERSON_SELECTION

type: Compensation Person Selection

Date: today

Dev: Tilak

Requirement: Select the Person Whose total salary of all the Job is > 50K as of effective date

*************************************************/

/* Declare DBI */

DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_START_DATE is '1900/01/01' (date)

DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_END_DATE is '1900/01/01' (date)

DEFAULT_DATA_VALUE FOR PER_HIST_ASG_ASSIGNMENT_ID is -1

DEFAULT_DATA_VALUE FOR PER_HIST_ASG_STATUS_TYPE is ' '

DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_LATEST_CHANGE is ' '

DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT is 0

l_temp = ESS_LOG_WRITE('Entering TCS_PERSON_SELECTION ' )

RETAVL = 'Y'

l_person_id = get_context(PERSON_ID, -1 )

l_asg_id = get_context(HR_ASSIGNMENT_ID, -1)

l_date = get_context(EFFECTIVE_DATE, '1900/01/01' (date) )

l_temp = ESS_LOG_WRITE('person id ' + TO_CHAR(l_person_id) )

l_temp = ESS_LOG_WRITE('Asg id ' + TO_CHAR(l_asg_id) )

l_temp = ESS_LOG_WRITE('Eff Date ' + TO_CHAR(l_date) )

/* Loop through the person assignment */

count = PER_HIST_ASG_ASSIGNMENT_ID.count

l_temp = ESS_LOG_WRITE('Count of asg ' + TO_CHAR(count) )

index = PER_HIST_ASG_ASSIGNMENT_ID.FIRST(-1)

While (PER_HIST_ASG_ASSIGNMENT_ID.EXISTS(index))

l_asg = PER_HIST_ASG_ASSIGNMENT_ID[index]

l_s_date = PER_HIST_ASG_EFFECTIVE_START_DATE[index]

l_e_date = PER_HIST_ASG_EFFECTIVE_END_DATE[index]

l_type = PER_HIST_ASG_STATUS_TYPE[index]

l_change = PER_HIST_ASG_EFFECTIVE_LATEST_CHANGE[index]

l_temp = ESS_LOG_WRITE('l_asg ' + TO_CHAR(l_asg) )

l_temp = ESS_LOG_WRITE('l_s_date ' + TO_CHAR(l_s_date) )

l_temp = ESS_LOG_WRITE('l_e_date ' + TO_CHAR(l_e_date) )

l_temp = ESS_LOG_WRITE('l_type ' + l_type )

l_temp = ESS_LOG_WRITE('l_change ' + l_change )

/* Validation */

IF l_date >= l_s_date AND l_date <= l_e_date THEN

(

IF l_type = 'ACTIVE' and l_change = 'Y' then

(

l_temp = ESS_LOG_WRITE('setting context for ' + TO_CHAR(l_asg) )

CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = l_asg)(

SALARY = SALARY + CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT

l_temp = ESS_LOG_WRITE('SALARY ' + TO_CHAR(SALARY) )

)

index = PER_HIST_ASG_ASSIGNMENT_ID.NEXT(index, -1)

IF SALARY > 5000 then

RETVAL = 'Y'

l_temp = ESS_LOG_WRITE('FINAL SALARY ' + TO_CHAR(SALARY) )

l_temp = ESS_LOG_WRITE('Returning TCS_PERSON_SELECTION ' || RETAVL)

Return RETAVL

YoutTube Tutorial on Array Variables and Input Values

/ ******************************

FF : TCS_ARRAY_ITEM_FF

Type : Total Compensation Item

Developer: Tilak

Purpose: Find the salary of each person for every assignment and returns with asg id and start date

********************************/

/* Declare DBI */

DEFAULT FOR CMP_ASSIGNMENT_SALARY_DATE_FROM is '1900/01/01' (date)

/* Default IV */

DEFAULT for CMP_IVR_ASSIGNMENT_ID is EMPTY_NUMBER_NUMBER

/* Default Variables */

DEFAULT FOR COMPENSATION_DATES is EMPTY_TEXT_NUMBER

DEFAULT FOR VALUES is EMPTY_TEXT_NUMBER

DEFAULT FOR ASSIGNMENTS is EMPTY_TEXT_NUMBER

/* Declare IV */

INPUTS ARE CMP_IV_PERIOD_START_DATE(DATE),

CMP_IV_PERIOD_END_DATE (DATE),

CMP_IVR_ASSIGNMENT_ID(NUMBER_NUMBER) ,

CMP_IVR_TERM_ID(NUMBER_NUMBER),

CMP_IVR_PAY_ASSIGNMENT_ID (NUMBER_NUMBER),

CMP_IVR_PAY_TERM_ID(NUMBER_NUMBER),

CMP_IVR_PAY_RELATION_ID(NUMBER_NUMBER)

/* Main */

l_temp = ESS_LOG_WRITE( 'ENTERING TCS_ARRAY_ITEM_FF ' )

l_count = CMP_IVR_ASSIGNMENT_ID.count

l_temp = ESS_LOG_WRITE( 'count asg ' + TO_char(l_count) )

counter = 1

index = CMP_IVR_ASSIGNMENT_ID.FIRST(-1)

WHILE (CMP_IVR_ASSIGNMENT_ID.EXISTS(index) )

l_asg = CMP_IVR_ASSIGNMENT_ID[index]

l_temp = ESS_LOG_WRITE( 'asg ' + TO_char(l_asg) )

CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = l_asg)

(

salary = CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT

fromDate = CMP_ASSIGNMENT_SALARY_DATE_FROM

COMPENSATION_DATES[counter] = to_char(fromDate, 'YYYY/MM/DD')

VALUES[counter] = to_char(salary)

ASSIGNMENTS[counter] = to_char(l_asg)

counter = counter + 1

l_temp = ESS_LOG_WRITE( 'salary ' + TO_char(salary) )

l_temp = ESS_LOG_WRITE( 'fromDate ' + TO_char(fromDate) )

l_temp = ESS_LOG_WRITE( 'l_asg ' + TO_char(l_asg) )

)

index = CMP_IVR_ASSIGNMENT_ID.NEXT(index, -1)

l_temp = ESS_LOG_WRITE( 'Leaving TCS_ARRAY_ITEM_FF ' )

RETURN COMPENSATION_DATES,VALUES,ASSIGNMENTS

32 comments:

This comment has been removed by the author.

Extraordinary. I never know we can use arrays in ff. Great work

Can you write a formula to capture email ID is an array & change each character to its ascii & display it? Thanks, Aloka

you can get emails from PER_PER_EMAIL_MAIL_ADDRESS. I do not understand the second part. Why do you need to convert it to ASCII char ? and what you mean by display it ?

Very useful..Thank you

Unable to get the DFF created in biographical info, the attribute is attribute 1 , can you help with that?

table name ?

you can use this DBI PER_PER_BASIC_ATTRIBUTE_TEXT1

Hi , I am using error while compilation the Compensation Default and override FF code as " compilation failed while creating the formula PL/SQL package FFP300004510533057_01011951." Could you please suggest me where I did the mistake. Code:======== INPUTS ARE CMP_IV_ASSIGNMENT_ID (number), CMP_IV_PLAN_ID (number) /*=========== INPUT VALUES DEFAULTS ENDS======================*/ /*================ FORMULA SECTION BEGIN =======================*/ DEFAULT_DATA_VALUE FOR PER_EXT_CMP_FEED_STATUS_POST_PROMOTION IS 'AA' L_DAT = ESS_LOG_WRITE ('PROCESS HAS STARTED') L_HR_ASSIGNMENT_ID = GET_CONTEXT(HR_ASSIGNMENT_ID,-1) L_PERSON_ID = GET_CONTEXT(PERSON_ID,-1) L_DAT = ESS_LOG_WRITE('ASSIGNMENT_ID IS'|| to_char(L_HR_ASSIGNMENT_ID) ) L_DAT = ESS_LOG_WRITE('PERSON_ID IS'|| to_char(L_PERSON_ID )) L_INDEX = 1 L_SELECT ='N' WHILE PER_EXT_CMP_FEED_STATUS_POST_PROMOTION.EXISTS(L_INDEX) LOOP ( L_DAT = ESS_LOG_WRITE ('INDEX VALUE IS' || TO_CHAR( L_INDEX)) L_VAL = PER_EXT_CMP_FEED_STATUS_POST_PROMOTION[L_INDEX] L_DAT = ESS_LOG_WRITE ( 'VALUE AT POST PROMOTION IS '|| L_VAL ) IF (L_VAL = 'ORA') THEN (L_SELECT ='Y' EXIT ) L_INDEX =L_INDEX + 1 ) RETURN L_SELECT

I got the same error, i think it has something to do with the DBI, the errors raised when you access the DB. You can talk to the oracle support. In my opinion, they do not support your logic 1) You are trying to access the table that is populated by the process, that is not supported. 2) You are using the DBI that does not have any context, you possibly fetching millions of records. even if the ff compiles, you are going to kill the system.

Used the info above to navigate arrays for a FF that we have. Thanks!

Very apt! Thank you for such clear and informative blog.

Can you please suggest me what material/video's to follow to learn writing FF. I would like to start with the basics

is it not what this blog doing? :). Well I have not seen much doc or video. Well, you can look at oracle documents or white papers.

Facing same issue 'Formula XX_EXT_CONFTE_NEW compilation failed while creating the formula PL/SQL package FFP300000048316049_01011951.'

How can I find a list of arrays that are already defined in Fusion payroll for use in fast formula

please see the documentation.

exiting a while loop, can u please give an example sir.. Navya here.

this is from manual, hope this helps: FOUND = -1 /* -1 is not a valid index for A. */ I = A.FIRST(-1) WHILE (A.EXISTS(I)) LOOP ( /* EXIT-clause for early exit. */ IF A[I] = KEY THEN ( FOUND = I /* Exit the loop. */ EXIT; ) I = A.NEXT(I,-1) )

I am getting error Assign a value to local variable L_LOCAL_ARRAY_OF_VALID_SALARIES_INDEX before it's referenced. on line: IF (L_LOCAL_ARRAY_OF_VALID_SALARIES_INDEX.COUNT> 0) How to check if array has value in it or not? I have written it DEFAULT FOR L_LOCAL_ARRAY_OF_VALID_SALARIES_INDEX IS EMPTY_NUMBER_NUMBER

You declaration and checking looks good to me. how do you assign the values before checking the count ?

Hello, I’m currently attempting to retrieve the most recent effective date using a fast formula. I’ve been trying to use the array.last function, but it doesn’t seem to be working as expected. Instead of returning the last effective date, it’s giving me random dates. Could you provide an example of how to correctly use the array.last function?

The array might not be having a sorted data. I can not provide an example that I do not understand. is it not easy to provide your code and let me have a look?

i = BEN_PIL_LF_EVT_OCRD_DT_TN.LAST(-1) IF (BEN_PIL_PER_IN_LER_STAT_CD_TN[i] = 'PROCD') THEN ( l_eff_dt = BEN_PIL_LF_EVT_OCRD_DT_TN[i] l_evt = BEN_PIL_PER_IN_LER_ID_TN[i] l_evt_name = BEN_PIL_LER_NAME_TN[i] ) How do you sort the dates?

The DBI, BEN_PIL_LF_EVT_OCRD_DT_TN is not coming from sorted SQL therefor you not get the data from last Index. Can you loop through the array and get the greatest date and other informations ?

Would you know how we can sort the data using fast formula? I have tried looping the array and it only gets the earliest date instead of the latest.

you can put ur code in the comment and delete it later.

The looping logic is already in the blog as well in my youtube video. look like you are expecting quick answers. anyway, you can use the following example and please note it is just an example, you need to change it for your requirement a = BEN_PIL_LF_EVT_OCRD_DT_TN.FIRST(-1) WHILE (BEN_PIL_LF_EVT_OCRD_DT_TN.EXISTS(a) ) LOOP ( IF (BEN_PIL_PER_IN_LER_STAT_CD_TN[a] = 'PROCD' AND BEN_PIL_BENEFIT_REL_SYSTEM_CD_TN[a] = 'BEN_OAB') THEN ( if (l_lf_evt_ocrd_dt < BEN_PIL_LF_EVT_OCRD_DT_TN[a] ) THEN ( l_lf_evt_ocrd_dt = BEN_PIL_LF_EVT_OCRD_DT_TN[a] ) ) a = BEN_PIL_LF_EVT_OCRD_DT_TN.NEXT(a, -1) )

how can I fetch the last date of an array dbi

that is depends on the DBI, if the dbi already sorted by date, you can get the last row otherwise, you need to write a logic to get the greatest date of the array

GET_CONTEXT HR_ASSIGNMENT_ID does not get value in Person Changes causes life

I was trying to get the CONTEXT for HR_ASSIGNMENT_ID and Effective Date, I don't get any value in FF

Content (please ensure you mask any confidential information):

I was trying to get the Action Code from the value set for that I need to get the CONTEXT HR_ASSIGNMENT_ID. Current Assignment ID.

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

/**************************************************************************************************** ****************************************************************************************************/

Inputs are BEN_ASG_IN_ASSIGNMENT_NUMBER (text), BEN_ASG_IN_ACTION_CODE(text) ,BEN_ASG_IN_EFFECTIVE_START_DATE(DATE)