1 2 Previous Next 16 Replies Latest reply: Apr 24, 2013 8:48 AM by Jani Rautiainen-Oracle RSS

    context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF

    994670
      Hi,

      There is a requirement to to attain eligibility based on job function. Job Function is attribute8 in Job DFF. The formula should search the employee’s assignment and compare the job function against a user defined table. In UDT, columns are nothing but Pay components and Rows are Job functions ( attribute8 in JOB DFF).

      Is there a way to input Component ID ( for pay components to match Columns in UDT ) ?

      I have not seen anywhere where it has Component ID as context or as Input values under "Participation and Rate Eligibility" Formula Type in Fusion.

      Can anyone throw me some light on how to find the context or the inputvalue to match the component ID using this type of Formula?

      I have seen "Workforce Compensation FastFormula Reference Guide", but unfortunately it doesnt have any details for "Paticipation and Rate Eligibility" Formula type, where as it has context and inputvalue for other forumula types.

      I have also googled and looked into Oracle Metalink, I did not find the context or input value for Component ID under this Formula Type.

      Thanks and regards,
      Murali
        • 1. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
          Jani Rautiainen-Oracle
          Can you clarify the use case ? If I understood correctly you are attempting to use [url http://docs.oracle.com/cd/E28271_01/fusionapps.1111/e16691/ext_ff.htm]Flexfield for Custom Attribute by adding a attribute with [url http://docs.oracle.com/cd/E28271_01/fusionapps.1111/e16691/ext_ff.htm#BABIHACG]Custom Value Sets ? Is the issue that you are not able to use the [url http://docs.oracle.com/cd/E28271_01/fusionapps.1111/e16691/ext_ff.htm#BABIAEJC]bind variables available? If so can you give an example of the FF structure similar to the "Task: Plan the Descriptive Flexfield Structure" in the [url docs.oracle.com/cd/E28271_01/fusionapps.1111/e16691/ext_ff.htm]Using Flexfields for Custom Attributes guide ?


          --
          Jani Rautiainen
          Fusion Applications Developer Relations
          https://blogs.oracle.com/fadevrel/
          • 2. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
            994670
            Hi Jani,

            Thanks for your intrest on this thread :)

            For your questions, I'm not trying configure or doing any set up here using DFF.
            Set has already done in Fusion Application. I was trying to write a Fast Formula of type "Participation and Rate Eligibility".

            In Fusion Application, JOB has been already defined and there is a DFF filed called JOB Function under Job description which is actually attribute8 from per_jobs_f in Fusion schema.

            A UDT in Fusion has already been defined which has Pay components like Merit, promotions etc.. under columns and Job Function ( attribute8 values from per_jobs_f) under rows.

            I need to write a FF of type "Participation and rate Eligibility" to attain eligibility based on job function. Job function will be stored within job descriptive flexfield (attribute8). The formula should search the employee’s assignment and compare the job function against a user defined table. Columns will be created per pay component and rows as active job functions in EBS. If the table value returns a Y, the employee is eligible. If the table value returns a N, the employee is ineligible

            The challenge what Im facing is, under Pariticipation and Rate Eligibilty Forumula Type, How Can I map/link pay components ( UDT Columsn) for an assignment or job?
            Do we have any Database Item or context or Input Values for COmponent ID or Job ID under this type of Formula?

            Appreciate your inputs on this.

            Thanks and regards,
            Murali
            • 3. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
              Jani Rautiainen-Oracle
              I am not familiar with Fast Formulas, so reached out to a colleague. I will update this thread once I get response.

              --
              Jani Rautiainen
              Fusion Applications Developer Relations
              https://blogs.oracle.com/fadevrel/
              • 4. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
                Jani Rautiainen-Oracle
                Response from colleague:
                JOB_ID context is available for the formula type and customer can use it.
                I'll try to find out if this is included in any documentation and if not have it updated..
                --
                Jani Rautiainen
                Fusion Applications Developer Relations
                https://blogs.oracle.com/fadevrel/
                • 5. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
                  994670
                  Hi Thanks for your Reply.. I have been looking in to various FF Guides for Fusion, But unfortunately I did not get anywehere required information like what are all Contexts, DB items, Input Values can be used for this type "Participation and Rate Eligibility" FF in Fusion under COMPENSATION MODULE.. But in one of the FF guides, I can see that there are few contexts avaiable as below...
                  But again these are for BENEFITS Module and not for COMPENSATION Module.

                  BUSINESS_GROUP_ID ( ENTERPRISE_ID)

                  EFFECTIVE_DATE

                  HR_ASSIGNMENT_ID

                  LER_ID

                  OPT_ID

                  ORGANIZATION_ID

                  PGM_ID

                  PL_ID

                  PL_TYP_ID

                  PERSON_ID ( available Post RUP3 only)


                  Can you pleaee list out what all are the INput values, DB Items or COntext available under Participation and Rate Eligiblity FF Type under COMPENSATIONN FUSION MODULE?
                  Any document whihc provides these details would also help a lot :)

                  Thanks and regards,
                  Murali
                  • 6. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
                    994670
                    Hi jani,

                    Any update on this please? Appreciate any inputs for the same.

                    Thanks and regards,
                    Murali
                    • 7. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
                      Jani Rautiainen-Oracle
                      Unfortunately no, I will send a reminder to the colleague to try to expedite this ..
                      --
                      Jani Rautiainen
                      Fusion Applications Developer Relations
                      https://blogs.oracle.com/fadevrel/
                      • 8. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
                        994670
                        Hi Jani/Team,

                        Appreciate any help here ASAP.. ...

                        Our need is to identify a way to fetch Component ID for a Plan, none of the oracle docs gives this info, whether it has Input Variables/context/Database Item for "Participation and Rate Eligibiility" formula type in COMPENSATION FUSION MODULE. (Docs says only we can use in Benefits Module). Not sure if we can use this type of FF in Compensation Module or not, If we can use, how can we get Component ID using Input Variables or Context or Database Item?

                        Note : Also tried to fetch the value of CMP_IV_COMPONENT_ID as Input Variables using "Compensation Default and Override" Formula Type, but it is not fetching the component iD, it just returning Default value.
                        DEFAULT FOR CMP_IV_PLAN_ID is 25
                        DEFAULT FOR CMP_IV_PERIOD_ID is 0
                        DEFAULT FOR CMP_IV_COMPONENT_ID is 22
                        INPUTS ARE CMP_IV_PLAN_ID, CMP_IV_PERIOD_ID,CMP_IV_COMPONENT_ID
                        l_output = GET_COMP_ID(CMP_IV_PLAN_ID, CMP_IV_PERIOD_ID,CMP_IV_COMPONENT_ID)
                        l_return = TO_CHAR(CMP_IV_COMPONENT_ID)
                        RETURN l_return

                        it is returning only 22 which is default value.

                        Even if we get comp id here in "Compensation Default and override", we need the way to to find the component ID using "Participation and Rate Eligibility" Formula type in COMPENSATION MODULE.

                        Thanks and regards,
                        Murali
                        • 9. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
                          Jani Rautiainen-Oracle
                          Unfortunately I still haven't received a response. I reached out to number of colleagues HCM / Compensation will update the thread immediately when get response.
                          --
                          Jani Rautiainen
                          Fusion Applications Developer Relations
                          https://blogs.oracle.com/fadevrel/
                          • 10. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
                            user793175-Oracle
                            If the formula type supports PERSON_ID and EFFECTIVE_DATE contexts then the formula has access to quite a number of JOB-related database items - see the list below.

                            What do you mean by Component ID ?

                            List of database Items

                            PER_ASG_CONTRACT_JOB_TITLE
                            PER_ASG_JOB_ACTIVE_STATUS
                            PER_ASG_JOB_APPROVAL_AUTHORITY
                            PER_ASG_JOB_ATTRIBUTE1
                            PER_ASG_JOB_ATTRIBUTE10
                            PER_ASG_JOB_ATTRIBUTE11
                            PER_ASG_JOB_ATTRIBUTE12
                            PER_ASG_JOB_ATTRIBUTE13
                            PER_ASG_JOB_ATTRIBUTE14
                            PER_ASG_JOB_ATTRIBUTE15
                            PER_ASG_JOB_ATTRIBUTE16
                            PER_ASG_JOB_ATTRIBUTE17
                            PER_ASG_JOB_ATTRIBUTE18
                            PER_ASG_JOB_ATTRIBUTE19
                            PER_ASG_JOB_ATTRIBUTE2
                            PER_ASG_JOB_ATTRIBUTE20
                            PER_ASG_JOB_ATTRIBUTE21
                            PER_ASG_JOB_ATTRIBUTE22
                            PER_ASG_JOB_ATTRIBUTE23
                            PER_ASG_JOB_ATTRIBUTE24
                            PER_ASG_JOB_ATTRIBUTE25
                            PER_ASG_JOB_ATTRIBUTE26
                            PER_ASG_JOB_ATTRIBUTE27
                            PER_ASG_JOB_ATTRIBUTE28
                            PER_ASG_JOB_ATTRIBUTE29
                            PER_ASG_JOB_ATTRIBUTE3
                            PER_ASG_JOB_ATTRIBUTE30
                            PER_ASG_JOB_ATTRIBUTE4
                            PER_ASG_JOB_ATTRIBUTE5
                            PER_ASG_JOB_ATTRIBUTE6
                            PER_ASG_JOB_ATTRIBUTE7
                            PER_ASG_JOB_ATTRIBUTE8
                            PER_ASG_JOB_ATTRIBUTE9
                            PER_ASG_JOB_ATTRIBUTE_CATEGORY
                            PER_ASG_JOB_ATTRIBUTE_DATE1
                            PER_ASG_JOB_ATTRIBUTE_DATE10
                            PER_ASG_JOB_ATTRIBUTE_DATE11
                            PER_ASG_JOB_ATTRIBUTE_DATE12
                            PER_ASG_JOB_ATTRIBUTE_DATE13
                            PER_ASG_JOB_ATTRIBUTE_DATE14
                            PER_ASG_JOB_ATTRIBUTE_DATE15
                            PER_ASG_JOB_ATTRIBUTE_DATE2
                            PER_ASG_JOB_ATTRIBUTE_DATE3
                            PER_ASG_JOB_ATTRIBUTE_DATE4
                            PER_ASG_JOB_ATTRIBUTE_DATE5
                            PER_ASG_JOB_ATTRIBUTE_DATE6
                            PER_ASG_JOB_ATTRIBUTE_DATE7
                            PER_ASG_JOB_ATTRIBUTE_DATE8
                            PER_ASG_JOB_ATTRIBUTE_DATE9
                            PER_ASG_JOB_ATTRIBUTE_NUMBER1
                            PER_ASG_JOB_ATTRIBUTE_NUMBER10
                            PER_ASG_JOB_ATTRIBUTE_NUMBER11
                            PER_ASG_JOB_ATTRIBUTE_NUMBER12
                            PER_ASG_JOB_ATTRIBUTE_NUMBER13
                            PER_ASG_JOB_ATTRIBUTE_NUMBER14
                            PER_ASG_JOB_ATTRIBUTE_NUMBER15
                            PER_ASG_JOB_ATTRIBUTE_NUMBER16
                            PER_ASG_JOB_ATTRIBUTE_NUMBER17
                            PER_ASG_JOB_ATTRIBUTE_NUMBER18
                            PER_ASG_JOB_ATTRIBUTE_NUMBER19
                            PER_ASG_JOB_ATTRIBUTE_NUMBER2
                            PER_ASG_JOB_ATTRIBUTE_NUMBER20
                            PER_ASG_JOB_ATTRIBUTE_NUMBER3
                            PER_ASG_JOB_ATTRIBUTE_NUMBER4
                            PER_ASG_JOB_ATTRIBUTE_NUMBER5
                            PER_ASG_JOB_ATTRIBUTE_NUMBER6
                            PER_ASG_JOB_ATTRIBUTE_NUMBER7
                            PER_ASG_JOB_ATTRIBUTE_NUMBER8
                            PER_ASG_JOB_ATTRIBUTE_NUMBER9
                            PER_ASG_JOB_BENCHMARK_JOB_FLAG
                            PER_ASG_JOB_CODE
                            PER_ASG_JOB_EFFECTIVE_END_DATE
                            PER_ASG_JOB_EFFECTIVE_START_DATE
                            PER_ASG_JOB_FULL_PART_TIME
                            PER_ASG_JOB_FUNCTION_CODE
                            PER_ASG_JOB_ID
                            PER_ASG_JOB_MANAGER_LEVEL
                            PER_ASG_JOB_MANAGER_LEVEL_NAME
                            PER_ASG_JOB_MED_CHECKUP_REQ
                            PER_ASG_JOB_NAME
                            PER_ASG_JOB_POST_SOURCE_NAME
                            PER_ASG_JOB_REGULAR_TEMPORARY
                            • 11. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
                              994670
                              Hi,

                              Thanks for your intrest in your reply :)
                              Component is attached to the Compensation Plan and minium 1 component should be there for each compensation Plan in Fusion Compensation Module.
                              Unless we know the way to fetch Compoenent ID in FF under "Participation & Rate Eligibility" Formula type in Fusion Compensation Module, how can we link it to assignment/person?

                              Thanks and regards,
                              Murali
                              • 12. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
                                994670
                                Hi jani,

                                I have been provdided with the Patch 16527985 for the same issue on SR 3-6888350721.

                                Thanks for your support and closing this thread :)

                                But before closing this thread looking forward an solution on same issue not with component id on input value but issue is something else but on the same note..


                                We are using Fast Formula of type "Participation and Rate Eligibility" and recently we raised SR(3-6888350721) with S1 and we got the patch "16527985 " for getting component ID using the Input value CMP_IV_COMPONENT_ID . After Applying patch we could see the Component ID is fetching properly, but when now further on testing this FF, having issue that when we run the Plan for any component wiht Eligibility profile, it always gives the error as follows...
                                ---------------------------------------------------------------
                                FROM COMP 2 ELIG: Errored For Assignment_ID:100000002574125, with error message as :User-Defined Exception
                                Check_eligibility errored for Assignment_ID:100000002574125, with error message as :User-Defined Exception
                                Process_component errored for Person_Id: 100000000291263Assignment_Id: 100000002574125Component_Id: 300000000756886Error Message:ORA-06503: PL/SQL: Function returned without value
                                Process_employee errored with error message as :ORA-06503: PL/SQL: Function returned without value
                                Process_rows person errored with Assignment_Id: 100000002574125, PersonId: 100000000291263 Error Message :ORA-06503: PL/SQL: Function returned without value
                                --------------------------------------------------------------------

                                Here is the Formula code for your reference..

                                DEFAULT FOR CMP_IV_COMPONENT_ID is 0
                                INPUTS ARE CMP_IV_COMPONENT_ID
                                eligible='N'
                                l_output = GET_CAREER_BAND_NEW(CMP_IV_COMPONENT_ID)
                                IF TO_CHAR( l_output) = 'Y'
                                then
                                (
                                eligible = 'Y'
                                )
                                else
                                eligible = 'N'
                                return eligible


                                Whenever we call the formula function and retun any value directly from the formula funciton to Formula without even wriiting any custom logic to return the value in the formula funciton, it always says 'Formula Function did not return value" when we start the plan process ..as error details given blow..

                                FROM COMP 2 ELIG: Errored For Assignment_ID:100000002574125, with error message as :User-Defined Exception
                                Check_eligibility errored for Assignment_ID:100000002574125, with error message as :User-Defined Exception
                                Process_component errored for Person_Id: 100000000291263Assignment_Id: 100000002574125Component_Id: 300000000756886Error Message:ORA-06503: PL/SQL: Function returned without value
                                Process_employee errored with error message as :ORA-06503: PL/SQL: Function returned without value
                                Process_rows person errored with Assignment_Id: 100000002574125, PersonId: 100000000291263 Error Message :ORA-06503: PL/SQL: Function returned without value


                                ________________________________

                                Formula Function for your rerefene..

                                FUNCTION GET_CAREER_BAND_NEW ( p_effective_date DATE
                                , p_HR_ASSIGNMENT_ID NUMBER
                                ,P_COMPONENT_ID NUMBER
                                )
                                RETURN VARCHAR2
                                IS
                                l_HR_ASSIGNMENT_ID NUMBER;
                                l_component_ID NUMBER;
                                l_eligible VARCHAR2(1);
                                BEGIN
                                l_eligible := 'Y';

                                return l_eligible;

                                END;

                                Can you help me to find out why Im getting this error "User-Defined Exception " as well as PL SQl Error, Formula Did not returned value?

                                Thanks and regards,
                                Murali

                                Edited by: 991667 on Apr 22, 2013 5:11 AM
                                • 13. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
                                  Jani Rautiainen-Oracle
                                  Please use a separate thread for separate issue, this way we will keep the threads shorter and cleaner for other readers interested in the topic.
                                  I am not expert on FF, so will ask colleagues to comment, can you clarify the following:
                                  <li> The logic works without the call to GET_CAREER_BAND_NEW ?
                                  <li> When GET_CAREER_BAND_NEW is called you always get ORA-06503 ?
                                  <li> Does the call to GET_CAREER_BAND_NEW work outside the formula. E.g. if you call it from SQLPlus does it work as expected ?
                                  <li> Did you [url http://docs.oracle.com/cd/A60725_05/html/comnls/us/per/ffugax03.htm]register (note this refers to old EBS documentation however I think the flow is similar in Fusion also) the GET_CAREER_BAND_NEW through the UI to be used in the formula ?

                                  --
                                  Jani Rautiainen
                                  Fusion Applications Developer Relations
                                  https://blogs.oracle.com/fadevrel/
                                  • 14. Re: context or Input Val for Comp ID in "Participation & Rate Eligibiliy" FF
                                    994670
                                    Hi Jani,

                                    I suspect the issue continues with the patch as this is happening only with this type (Participationand Rate Eligibility)of Fast Formula.

                                    The logic works without the call to GET_CAREER_BAND_NEW ?
                                    Murali : Yes If we hard code the require value with in FF itself instead of call to GET_CAREER_BAND_NEW, it works fine.

                                    When GET_CAREER_BAND_NEW is called you always get ORA-06503 ?
                                    Yes.. Always I get this ORA-06503 when Im actually returning appropriate values.

                                    Does the call to GET_CAREER_BAND_NEW work outside the formula. E.g. if you call it from SQLPlus does it work as expected ?
                                    Yes, check with in toad to see what value im returning.. Its returning "N" as expected.

                                    Did you register (note this refers to old EBS documentation however I think the flow is similar in Fusion also) the GET_CAREER_BAND_NEW through the UI to be used in the formula ?
                                    Yes, registered the GET_CAREER_BAND_NEW forumula function as other ff in fusion.

                                    Thanks and regards,
                                    Murali
                                    1 2 Previous Next