Customizing HCM seeded notifications

If you want to change a notification format in Cloud Oracle HCM, go to the BIP path using the admin login(use xmlpserver in URL), Shared folders/Human Capital Management/ Workflow Notifications and click customize, so that the copy gets created in Shared folders/Custom/Human Capital Management/ Workflow Notifications. But before doing this, there doesnt exist a copy of the report being customized.

/Shared folders/Human Capital Management/ Workflow Notifications

OIC iCal scheduling syntax

source: rfc5545 (ietf.org)  

 recur = recur-rule-part *( ";" recur-rule-part )

                       ;
                       ; The rule parts are not ordered in any
                       ; particular sequence.
                       ;
                       ; The FREQ rule part is REQUIRED,
                       ; but MUST NOT occur more than once.
                       ;
                       ; The UNTIL or COUNT rule parts are OPTIONAL,
                       ; but they MUST NOT occur in the same 'recur'.
                       ;



Desruisseaux                Standards Track                    [Page 38]

---

RFC 5545                       iCalendar                  September 2009


                       ; The other rule parts are OPTIONAL,
                       ; but MUST NOT occur more than once.

       recur-rule-part = ( "FREQ" "=" freq )
                       / ( "UNTIL" "=" enddate )
                       / ( "COUNT" "=" 1*DIGIT )
                       / ( "INTERVAL" "=" 1*DIGIT )
                       / ( "BYSECOND" "=" byseclist )
                       / ( "BYMINUTE" "=" byminlist )
                       / ( "BYHOUR" "=" byhrlist )
                       / ( "BYDAY" "=" bywdaylist )
                       / ( "BYMONTHDAY" "=" bymodaylist )
                       / ( "BYYEARDAY" "=" byyrdaylist )
                       / ( "BYWEEKNO" "=" bywknolist )
                       / ( "BYMONTH" "=" bymolist )
                       / ( "BYSETPOS" "=" bysplist )
                       / ( "WKST" "=" weekday )

       freq        = "SECONDLY" / "MINUTELY" / "HOURLY" / "DAILY"
                   / "WEEKLY" / "MONTHLY" / "YEARLY"

       enddate     = date / date-time

       byseclist   = ( seconds *("," seconds) )

       seconds     = 1*2DIGIT       ;0 to 60

       byminlist   = ( minutes *("," minutes) )

       minutes     = 1*2DIGIT       ;0 to 59

       byhrlist    = ( hour *("," hour) )

       hour        = 1*2DIGIT       ;0 to 23

       bywdaylist  = ( weekdaynum *("," weekdaynum) )

       weekdaynum  = [[plus / minus] ordwk] weekday

       plus        = "+"

       minus       = "-"

       ordwk       = 1*2DIGIT       ;1 to 53

       weekday     = "SU" / "MO" / "TU" / "WE" / "TH" / "FR" / "SA"
       ;Corresponding to SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,
       ;FRIDAY, and SATURDAY days of the week.



Desruisseaux                Standards Track                    [Page 39]

---

RFC 5545                       iCalendar                  September 2009


       bymodaylist = ( monthdaynum *("," monthdaynum) )

       monthdaynum = [plus / minus] ordmoday

       ordmoday    = 1*2DIGIT       ;1 to 31

       byyrdaylist = ( yeardaynum *("," yeardaynum) )

       yeardaynum  = [plus / minus] ordyrday

       ordyrday    = 1*3DIGIT      ;1 to 366

       bywknolist  = ( weeknum *("," weeknum) )

       weeknum     = [plus / minus] ordwk

       bymolist    = ( monthnum *("," monthnum) )

       monthnum    = 1*2DIGIT       ;1 to 12

       bysplist    = ( setposday *("," setposday) )

       setposday   = yeardaynum

example:

FREQ=MONTHLY;BYMONTH=1,3,5,7,8,10,12;BYMONTHDAY=15,31;

&FREQ=MONTHLY;BYMONTH=4,6,9,11;BYMONTHDAY=15,30;

&FREQ=MONTHLY;BYMONTH=2;BYMONTHDAY=15,28,29;

OIC iCal scheduling example

Schedule an integration from sunday to thursday 8 AM to 8PM for every 30 minutes

Use iCal expression as below.

FREQ=WEEKLY;BYDAY=SU,MO,TU,WE,TH;BYHOUR=8,9,10,11,12,13,14,15,16,17,18,19,20;BYMINUTE=0,30;

Query to get contexts for a DBI

 select * from FF_CONTEXTS_TL where LANGUAGE='US' AND context_id in
   (select CONTEXT_ID from FF_CONTEXTS_B where CONTEXT_ID in
      (select CONTEXT_ID from ff_route_context_usages where route_id in
         (select route_id from ff_user_entities_b where user_entity_id in
            (select user_entity_id from ff_database_items_b where base_user_name like 'ANC_ABS_ENTRS_ATTRIBUTE1')
         )
      )
   )

EBS Bank accounts query

 select
ppr.payroll_relationship_id payrollrelationshipid,
ppr.person_id personid,
h.party_id partyid,
eba.bank_account_id bankaccountid,
eba.bank_account_num bankaccountnumber,
eba.bank_id bankid,
eba.bank_name bankname,
eba.bank_number banknumber,
eba.branch_number branchnumber,
eba.branch_id branchid,
eba.bank_branch_name branchname,
eba.eft_swift_code eftswiftcode,
eba.bank_home_country homecountry,
eba.bank_account_type bankaccounttype
FROM
pay_bank_accounts eba,
iby_account_owners ebao,
hz_parties h,
pay_pay_relationships_dn ppr,
per_persons p,
hz_orig_sys_references hosp
WHERE
eba.bank_account_id = ebao.ext_bank_account_id
AND ebao.account_owner_party_id = h.party_id
AND hosp.owner_table_id = h.party_id
AND hosp.orig_system_reference = TO_CHAR(ppr.person_id)
AND hosp.owner_table_name = 'HZ_PARTIES'
AND hosp.orig_system = 'FUSION_HCM'
AND ppr.person_id = p.person_id
AND h.status = 'A'
--and eba.bank_account_id= fill_value
and ppr.payroll_relationship_number='123'

EBS Run Results Query

 SELECT papf.employee_number,
       papf.full_name,
       ppa.effective_date,
       pp.payroll_name,
       pet.element_name,
       piv.name input_value,
       prrv.result_value
FROM pay_payroll_actions ppa,
     pay_assignment_actions paa,
     pay_payrolls_f pp,
     pay_run_results prr,
     pay_run_result_values prrv,
     pay_input_values_f piv,
     pay_element_types_f pet,
     per_all_assignments_f paaf,
     per_all_people_f papf
WHERE 1=1 and ppa.payroll_action_id = :payroll_action_id
  AND ppa.payroll_action_id = paa.payroll_action_id
  AND ppa.payroll_id = pp.payroll_id
  AND paa.assignment_action_id = prr.assignment_action_id
  AND prr.run_result_id= prrv.run_result_id
  AND prrv.input_value_id = piv.input_value_id
  AND piv.element_type_id = pet.element_type_id
  AND paaf.assignment_id = paa.assignment_id
  AND paaf.person_id = papf.person_id
  and paaf.business_group_id=3444
  and pet.element_name='Regular Salary'
  AND trunc(sysdate) BETWEEN pp.effective_start_date AND pp.effective_end_date
  AND trunc(sysdate) BETWEEN pet.effective_start_date AND pet.effective_end_date
  AND trunc(sysdate) BETWEEN piv.effective_start_date AND piv.effective_end_date
  AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
  AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
  --and ppa.effective_date=trunc(sysdate-11)
  and piv.name='Pay Value'
ORDER BY employee_number;

Personal Payment Method and Bank account details Query in EBS

 SELECT
    papf.employee_number,
    acc.segment1,
    acc.segment2,
    acc.segment3,
    acc.segment4,
    acc.segment5,
    pppmf.last_update_date
FROM
    pay_personal_payment_methods_f  pppmf,
    per_all_assignments_f           paaf,
    per_all_people_f                papf,
    pay_external_accounts           acc
WHERE
        1 = 1
    AND pppmf.assignment_id = paaf.assignment_id
    AND paaf.person_id = papf.person_id
    AND acc.external_account_id = pppmf.external_account_id
    AND paaf.assignment_type = 'E'
    AND paaf.primary_flag = 'Y'
    AND paaf.business_group_id = 3444
    AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND trunc(sysdate) BETWEEN pppmf.effective_start_date AND pppmf.effective_end_date
    AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
    AND papf.employee_number IN ( '')

Query to fetch Absence Plan Accrual balance

SELECT
    papf.person_number,
    aapft.name,
    (
        SELECT
            a.end_bal
        FROM
            anc_per_accrual_entries  a,
            anc_per_plan_enrollment  b
        WHERE
                a.per_plan_enrt_id = b.per_plan_enrt_id
            AND a.accrual_period = b.last_accrual_run
            AND b.work_term_asg_id = paaf.work_terms_assignment_id
            AND b.plan_id = appe.plan_id
            AND b.person_id = paaf.person_id
            AND trunc(sysdate) BETWEEN b.enrt_st_dt AND b.enrt_end_dt
    ) accrual_balance
FROM
    per_all_assignments_m    paaf,
    per_all_people_f         papf,
    anc_per_plan_enrollment  appe,
    anc_absence_plans_f_tl   aapft
WHERE
        paaf.person_id = papf.person_id
    AND appe.person_id = papf.person_id
    AND aapft.absence_plan_id = appe.plan_id
    AND aapft.language = 'US'
    AND aapft.name = 'Holiday - Days'
    AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND trunc(sysdate) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
    AND papf.person_number = '11111'
    AND paaf.primary_flag = 'Y'
    AND paaf.assignment_type = 'E'