eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PQP_NL_PENSION_EXTRACTS dependencies on BEN_EXT_CHG_EVT_LOG

Line 447: FROM ben_ext_chg_evt_log bec

443: CURSOR c_get_revt_rows (p_business_group_id IN NUMBER
444: ,p_effective_date IN DATE
445: ,p_assignment_id IN NUMBER ) IS
446: SELECT old_val1,new_val1,ext_chg_evt_log_id
447: FROM ben_ext_chg_evt_log bec
448: WHERE chg_evt_cd = 'DAT'
449: AND person_id = g_person_id
450: AND bec.business_group_id = p_business_group_id
451: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN

Line 528: FROM ben_ext_chg_evt_log bec

524: SELECT old_val1
525: ,to_char(pps.actual_termination_date,'DD/MM/YYYY') term_date
526: ,ext_chg_evt_log_id
527: ,fnd_number.canonical_to_number(prmtr_01)
528: FROM ben_ext_chg_evt_log bec
529: ,per_periods_of_service pps
530: ,per_all_assignments_f asg
531: WHERE bec.chg_evt_cd = 'AAT'
532: AND bec.person_id = g_person_id

Line 563: FROM ben_ext_chg_evt_log bec

559: AND effective_start_date BETWEEN
560: g_extract_params(p_business_group_id).extract_start_date
561: AND g_extract_params(p_business_group_id).extract_end_date
562: AND NOT EXISTS ( SELECT 1
563: FROM ben_ext_chg_evt_log bec
564: WHERE chg_evt_cd = 'AAT'
565: AND person_id = g_person_id
566: AND bec.business_group_id = p_business_group_id
567: AND fnd_date.canonical_to_date(prmtr_09)

Line 608: FROM ben_ext_chg_evt_log bec

604: FROM per_assignment_status_types
605: WHERE per_system_status = 'TERM_ASSIGN'
606: AND active_flag = 'Y'))
607: AND NOT EXISTS ( SELECT 1
608: FROM ben_ext_chg_evt_log bec
609: WHERE chg_evt_cd = 'AAT'
610: AND person_id = g_person_id
611: AND bec.business_group_id = p_business_group_id
612: AND fnd_date.canonical_to_date(prmtr_09)

Line 2359: FROM ben_ext_chg_evt_log

2355:
2356: CURSOR c_hire_dt_chg(c_person_id IN NUMBER) IS
2357: SELECT old_val1 old_date,
2358: new_val1 new_date
2359: FROM ben_ext_chg_evt_log
2360: WHERE person_id = p_person_id
2361: AND chg_evt_cd = 'COPOS'
2362: AND fnd_date.canonical_to_date(prmtr_09)
2363: BETWEEN g_extract_params(p_business_group_id).extract_start_date AND

Line 2367: l_old_hire_can ben_ext_chg_evt_log.old_val1%TYPE;

2363: BETWEEN g_extract_params(p_business_group_id).extract_start_date AND
2364: g_extract_params(p_business_group_id).extract_end_date
2365: ORDER BY ext_chg_evt_log_id DESC;
2366:
2367: l_old_hire_can ben_ext_chg_evt_log.old_val1%TYPE;
2368: l_new_hire_can ben_ext_chg_evt_log.new_val1%TYPE;
2369: l_ret_val NUMBER := 0;
2370:
2371: BEGIN

Line 2368: l_new_hire_can ben_ext_chg_evt_log.new_val1%TYPE;

2364: g_extract_params(p_business_group_id).extract_end_date
2365: ORDER BY ext_chg_evt_log_id DESC;
2366:
2367: l_old_hire_can ben_ext_chg_evt_log.old_val1%TYPE;
2368: l_new_hire_can ben_ext_chg_evt_log.new_val1%TYPE;
2369: l_ret_val NUMBER := 0;
2370:
2371: BEGIN
2372:

Line 3179: FROM ben_ext_chg_evt_log

3175: ,c_start_date IN DATE
3176: ,c_end_date IN DATE) IS
3177: SELECT old_val1 old_date,
3178: new_val1 new_date
3179: FROM ben_ext_chg_evt_log
3180: WHERE person_id = c_person_id
3181: AND chg_evt_cd = 'COPOS'
3182: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
3183: ORDER BY ext_chg_evt_log_id desc;

Line 3194: ben_ext_chg_evt_log log

3190: ,effective_start_date start_date
3191: ,effective_end_date end_date
3192: ,fnd_number.canonical_to_number(new_val1) ptp
3193: FROM per_all_assignments_f asg,
3194: ben_ext_chg_evt_log log
3195: ,per_assignment_status_types past
3196: ,hr_soft_coding_keyflex sck
3197: WHERE asg.assignment_id = p_assignment_id
3198: AND asg.assignment_status_type_id = past.assignment_status_type_id

Line 3480: l_new_start_can ben_ext_chg_evt_log.new_val1%TYPE;

3476: l_code NUMBER;
3477: l_date date := hr_api.g_eot;
3478: l_new_start date;
3479: l_old_start date;
3480: l_new_start_can ben_ext_chg_evt_log.new_val1%TYPE;
3481: l_old_start_can ben_ext_chg_evt_log.old_val1%TYPE;
3482: l_beg_new_st date;
3483: l_end_new_st date;
3484:

Line 3481: l_old_start_can ben_ext_chg_evt_log.old_val1%TYPE;

3477: l_date date := hr_api.g_eot;
3478: l_new_start date;
3479: l_old_start date;
3480: l_new_start_can ben_ext_chg_evt_log.new_val1%TYPE;
3481: l_old_start_can ben_ext_chg_evt_log.old_val1%TYPE;
3482: l_beg_new_st date;
3483: l_end_new_st date;
3484:
3485: l_get_count_ptp_changes NUMBER := 0;

Line 3505: l_old_date1_xx ben_ext_chg_evt_log.old_val1%TYPE;

3501: l_grp_index NUMBER:=0;
3502: l_tax_org_flag VARCHAR2(1);
3503: l_reversal_term NUMBER := 0;
3504: l_normal_term NUMBER := 0;
3505: l_old_date1_xx ben_ext_chg_evt_log.old_val1%TYPE;
3506: l_new_date1_xx ben_ext_chg_evt_log.new_val1%TYPE;
3507: l_old_date2_xx ben_ext_chg_evt_log.old_val1%TYPE;
3508: l_new_date2_xx ben_ext_chg_evt_log.new_val1%TYPE;
3509: l_term_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;

Line 3506: l_new_date1_xx ben_ext_chg_evt_log.new_val1%TYPE;

3502: l_tax_org_flag VARCHAR2(1);
3503: l_reversal_term NUMBER := 0;
3504: l_normal_term NUMBER := 0;
3505: l_old_date1_xx ben_ext_chg_evt_log.old_val1%TYPE;
3506: l_new_date1_xx ben_ext_chg_evt_log.new_val1%TYPE;
3507: l_old_date2_xx ben_ext_chg_evt_log.old_val1%TYPE;
3508: l_new_date2_xx ben_ext_chg_evt_log.new_val1%TYPE;
3509: l_term_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
3510: l_revt_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;

Line 3507: l_old_date2_xx ben_ext_chg_evt_log.old_val1%TYPE;

3503: l_reversal_term NUMBER := 0;
3504: l_normal_term NUMBER := 0;
3505: l_old_date1_xx ben_ext_chg_evt_log.old_val1%TYPE;
3506: l_new_date1_xx ben_ext_chg_evt_log.new_val1%TYPE;
3507: l_old_date2_xx ben_ext_chg_evt_log.old_val1%TYPE;
3508: l_new_date2_xx ben_ext_chg_evt_log.new_val1%TYPE;
3509: l_term_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
3510: l_revt_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
3511: l_term_pos_id_XX NUMBER;

Line 3508: l_new_date2_xx ben_ext_chg_evt_log.new_val1%TYPE;

3504: l_normal_term NUMBER := 0;
3505: l_old_date1_xx ben_ext_chg_evt_log.old_val1%TYPE;
3506: l_new_date1_xx ben_ext_chg_evt_log.new_val1%TYPE;
3507: l_old_date2_xx ben_ext_chg_evt_log.old_val1%TYPE;
3508: l_new_date2_xx ben_ext_chg_evt_log.new_val1%TYPE;
3509: l_term_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
3510: l_revt_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
3511: l_term_pos_id_XX NUMBER;
3512: l_org_index NUMBER;

Line 3509: l_term_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;

3505: l_old_date1_xx ben_ext_chg_evt_log.old_val1%TYPE;
3506: l_new_date1_xx ben_ext_chg_evt_log.new_val1%TYPE;
3507: l_old_date2_xx ben_ext_chg_evt_log.old_val1%TYPE;
3508: l_new_date2_xx ben_ext_chg_evt_log.new_val1%TYPE;
3509: l_term_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
3510: l_revt_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
3511: l_term_pos_id_XX NUMBER;
3512: l_org_index NUMBER;
3513: l_fp_hire_dt DATE;

Line 3510: l_revt_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;

3506: l_new_date1_xx ben_ext_chg_evt_log.new_val1%TYPE;
3507: l_old_date2_xx ben_ext_chg_evt_log.old_val1%TYPE;
3508: l_new_date2_xx ben_ext_chg_evt_log.new_val1%TYPE;
3509: l_term_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
3510: l_revt_log_id_xx ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
3511: l_term_pos_id_XX NUMBER;
3512: l_org_index NUMBER;
3513: l_fp_hire_dt DATE;
3514: l_fp_new_hire NUMBER := 0 ;

Line 6576: l_old_er_num ben_ext_chg_evt_log.old_val1%TYPE := '-1';

6572: WHERE assignment_id = p_assignment_id
6573: AND business_group_id = p_business_group_id
6574: ORDER BY effective_end_date DESC;
6575:
6576: l_old_er_num ben_ext_chg_evt_log.old_val1%TYPE := '-1';
6577: l_new_er_num hr_organization_information.org_information2%TYPE := '-1';
6578: l_org_info_id hr_organization_information.org_information_id%TYPE;
6579: l_version_id per_org_structure_versions_v.org_structure_version_id%TYPE DEFAULT NULL;
6580: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_ABP_ER_Num';

Line 7301: l_override_value ben_ext_chg_evt_log.new_val1%TYPE;

7297: l_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
7298: l_bal_exists NUMBER;
7299: l_balance_id pay_balance_types.balance_type_id%TYPE;
7300: l_balance_amount NUMBER := 0;
7301: l_override_value ben_ext_chg_evt_log.new_val1%TYPE;
7302: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Pension_Salary';
7303: l_eff_dt DATE;
7304: l_dim_name VARCHAR2(100);
7305:

Line 7508: FROM ben_ext_chg_evt_log

7504: -- Cursor to check if there are any changes in the pension salary
7505: --
7506: CURSOR c_get_override_start_date IS
7507: SELECT fnd_date.canonical_to_date(prmtr_02)
7508: FROM ben_ext_chg_evt_log
7509: WHERE person_id = g_person_id
7510: AND Fnd_Number.canonical_to_number(prmtr_01) = p_assignment_id
7511: AND chg_eff_dt BETWEEN p_start_date AND p_end_date
7512: AND chg_evt_cd = 'COAPS'

Line 7515: FROM ben_ext_chg_evt_log

7511: AND chg_eff_dt BETWEEN p_start_date AND p_end_date
7512: AND chg_evt_cd = 'COAPS'
7513: AND ext_chg_evt_log_id =
7514: (SELECT Max(ext_chg_evt_log_id)
7515: FROM ben_ext_chg_evt_log
7516: WHERE person_id = g_person_id
7517: AND Fnd_Number.canonical_to_number(prmtr_01) = p_assignment_id
7518: AND chg_eff_dt BETWEEN p_start_date AND p_end_date
7519: AND chg_evt_cd = 'COAPS');

Line 7925: FROM ben_ext_chg_evt_log

7921: ,c_business_group_id IN Number
7922: ,c_ext_start_date IN Date
7923: ,c_ext_end_date IN Date ) IS
7924: SELECT 'x'
7925: FROM ben_ext_chg_evt_log
7926: WHERE person_id = c_person_id
7927: AND business_group_id = c_business_group_id
7928: AND chg_evt_cd IN ('COLN','COSS','COUN','COG','CODB')
7929: AND fnd_date.canonical_to_date(prmtr_09)

Line 7948: FROM ben_ext_chg_evt_log

7944: -- Cursor to check if partner last name or prefix has changed
7945: --
7946: CURSOR c_chk_partner_log (c_person_id IN Number) IS
7947: SELECT 'X'
7948: FROM ben_ext_chg_evt_log
7949: WHERE person_id = c_person_id
7950: AND business_group_id = p_business_group_id
7951: AND chg_evt_cd IN ('COUN','COLN','CCFN')
7952: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN g_extract_params(p_business_group_id).extract_start_date

Line 8126: FROM ben_ext_chg_evt_log

8122: ,c_business_group_id IN NUMBER
8123: ,c_ext_start_date IN DATE
8124: ,c_ext_end_date IN DATE ) IS
8125: SELECT 'x'
8126: FROM ben_ext_chg_evt_log
8127: WHERE person_id = c_person_id
8128: AND business_group_id = c_business_group_id
8129: AND chg_evt_cd = 'COM'
8130: AND fnd_date.canonical_to_date(prmtr_09)

Line 8189: FROM ben_ext_chg_evt_log log

8185: AND country <> 'NL'
8186: AND EXISTS (SELECT 1
8187: -- exists change in address event logs
8188: -- for the primary address
8189: FROM ben_ext_chg_evt_log log
8190: WHERE padr.person_id = log.person_id
8191: AND chg_evt_cd = 'COPR'
8192: AND padr.address_id = log.prmtr_01
8193: AND fnd_date.canonical_to_date(prmtr_09)

Line 8214: FROM ben_ext_chg_evt_log log

8210: AND country = 'NL'
8211: AND EXISTS (SELECT 1
8212: -- exists change in address event logs
8213: -- for the primary address
8214: FROM ben_ext_chg_evt_log log
8215: WHERE padr.person_id = log.person_id
8216: AND chg_evt_cd = 'COPR'
8217: AND padr.address_id = log.prmtr_01
8218: -- there is a change in primary address

Line 8238: FROM ben_ext_chg_evt_log log

8234: ,c_business_group_id IN NUMBER
8235: ,c_ext_start_date IN DATE
8236: ,c_ext_end_date IN DATE ) IS
8237: SELECT TO_NUMBER(prmtr_01) addr_id,new_val1 country
8238: FROM ben_ext_chg_evt_log log
8239: WHERE person_id = c_person_id
8240: AND chg_evt_cd = 'COCN'
8241: AND fnd_date.canonical_to_date(prmtr_09)
8242: BETWEEN c_ext_start_date AND c_ext_end_date

Line 8533: FROM ben_ext_chg_evt_log

8529: -- Cursor to check if there are any changes in the ABP Pension Salary
8530: --
8531: CURSOR c_get_override_salary IS
8532: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))
8533: FROM ben_ext_chg_evt_log
8534: WHERE person_id = g_person_id
8535: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
8536: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
8537: AND g_extract_params(p_business_group_id).extract_end_date

Line 8541: FROM ben_ext_chg_evt_log

8537: AND g_extract_params(p_business_group_id).extract_end_date
8538: AND chg_evt_cd = 'COAPS'
8539: AND ext_chg_evt_log_id =
8540: (SELECT MAX(ext_chg_evt_log_id)
8541: FROM ben_ext_chg_evt_log
8542: WHERE person_id = g_person_id
8543: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
8544: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
8545: AND g_extract_params(p_business_group_id).extract_end_date

Line 8946: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

8942:
8943: --cursor to check if ASG EIT rows exist
8944: CURSOR c_asg_rows_exist IS
8945: SELECT 1
8946: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
8947: WHERE chg_evt_cd = 'COAPPD'
8948: AND prmtr_01 = 'ASG'
8949: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
8950: AND pty.pension_sub_category = p_sub_cat

Line 8964: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

8960:
8961: --cursor to check if ORG EIT rows exist
8962: CURSOR c_org_rows_exist(c_org_id IN Number) IS
8963: SELECT 1
8964: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
8965: WHERE chg_evt_cd = 'COAPPD'
8966: AND prmtr_01 = 'ORG'
8967: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
8968: AND pty.pension_sub_category = p_sub_cat

Line 8976: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

8972:
8973: --cursor to get the ASG EIT log rows
8974: CURSOR c_get_asg_rows IS
8975: SELECT 1
8976: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
8977: WHERE chg_evt_cd = 'COAPPD'
8978: AND prmtr_01 = 'ASG'
8979: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
8980: AND prmtr_03 = 'Y'

Line 8997: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

8993:
8994: --cursor to get the ORG EIT log rows
8995: CURSOR c_get_org_rows(c_org_id IN Number,c_hire_date IN Date) IS
8996: SELECT 1
8997: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
8998: WHERE chg_evt_cd = 'COAPPD'
8999: AND prmtr_01 = 'ORG'
9000: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9001: AND prmtr_03 = 'Y'

Line 9472: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

9468:
9469: --cursor to check if ASG EIT rows exist
9470: CURSOR c_asg_rows_exist IS
9471: SELECT 1
9472: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9473: WHERE chg_evt_cd = 'COAPPD'
9474: AND prmtr_01 = 'ASG'
9475: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9476: AND pty.pension_sub_category = 'FUR_S'

Line 9490: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

9486:
9487: --cursor to check if ORG EIT rows exist
9488: CURSOR c_org_rows_exist(c_org_id IN Number) IS
9489: SELECT 1
9490: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9491: WHERE chg_evt_cd = 'COAPPD'
9492: AND prmtr_01 = 'ORG'
9493: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9494: AND pty.pension_sub_category = 'FUR_S'

Line 9502: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

9498:
9499: --cursor to get the old and new start and end dates from the ASG EIT
9500: CURSOR c_get_asg_rows IS
9501: SELECT old_val1,new_val1,old_val2,new_val2
9502: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9503: WHERE chg_evt_cd = 'COAPPD'
9504: AND prmtr_01 = 'ASG'
9505: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9506: AND prmtr_03 = 'Y'

Line 9523: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

9519:
9520: --cursor to get the old and new start and end dates from the ORG EIT
9521: CURSOR c_get_org_rows(c_org_id IN Number,c_hire_date IN Date) IS
9522: SELECT old_val1,new_val1,old_val2,new_val2
9523: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9524: WHERE chg_evt_cd = 'COAPPD'
9525: AND prmtr_01 = 'ORG'
9526: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9527: AND prmtr_03 = 'Y'

Line 9905: FROM ben_ext_chg_evt_log bec

9901: -- Cursor for ASG participation in PPP
9902: --
9903: CURSOR c_ppp_asg IS
9904: SELECT 1
9905: FROM ben_ext_chg_evt_log bec
9906: WHERE chg_evt_cd = 'COAPPD'
9907: AND prmtr_01 = 'ASG'
9908: AND prmtr_04 = 'PPP'
9909: AND prmtr_03 = 'Y'

Line 10311: ben_ext_chg_evt_log blog

10307: AND paei.information_type = 'NL_ABP_PAR_INFO'
10308: AND fnd_date.canonical_to_date(aei_information1)
10309: < g_extract_params(p_business_group_id).extract_start_date
10310: AND EXISTS ( SELECT 1 FROM
10311: ben_ext_chg_evt_log blog
10312: WHERE blog.person_id = g_person_id
10313: AND blog.chg_evt_cd = 'COAPKOP'
10314: AND blog.prmtr_10 = paei.assignment_id
10315: AND fnd_number.canonical_to_number(blog.prmtr_03) = paei.assignment_extra_info_id

Line 10446: ben_ext_chg_evt_log blog

10442: AND paei.information_type = 'NL_ABP_PI'
10443: AND fnd_date.canonical_to_date(aei_information1)
10444: < g_extract_params(p_business_group_id).extract_start_date
10445: AND EXISTS ( SELECT 1 FROM
10446: ben_ext_chg_evt_log blog
10447: WHERE blog.person_id = g_person_id
10448: AND blog.chg_evt_cd = 'COAPP'
10449: AND blog.prmtr_10 = paei.assignment_id
10450: AND fnd_number.canonical_to_number(blog.prmtr_03) = paei.assignment_extra_info_id

Line 10797: FROM ben_ext_chg_evt_log

10793: ,c_start_date IN DATE
10794: ,c_end_date IN DATE) IS
10795: SELECT old_val1 old_date,
10796: new_val1 new_date
10797: FROM ben_ext_chg_evt_log
10798: WHERE person_id = c_person_id
10799: AND chg_evt_cd = 'COPOS'
10800: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
10801: ORDER BY ext_chg_evt_log_id desc;

Line 10897: l_chg_eff_dt ben_ext_chg_evt_log.chg_eff_dt%TYPE;

10893: WHERE per_system_status = 'TERM_ASSIGN'
10894: AND active_flag = 'Y')
10895: AND assignment_type = 'E';*/
10896:
10897: l_chg_eff_dt ben_ext_chg_evt_log.chg_eff_dt%TYPE;
10898: l_chg_evt_cd ben_ext_chg_evt_log.chg_evt_cd%TYPE;
10899: l_active_assg NUMBER;
10900: l_rec05_sent NUMBER;
10901: l_prior_ptp NUMBER;

Line 10898: l_chg_evt_cd ben_ext_chg_evt_log.chg_evt_cd%TYPE;

10894: AND active_flag = 'Y')
10895: AND assignment_type = 'E';*/
10896:
10897: l_chg_eff_dt ben_ext_chg_evt_log.chg_eff_dt%TYPE;
10898: l_chg_evt_cd ben_ext_chg_evt_log.chg_evt_cd%TYPE;
10899: l_active_assg NUMBER;
10900: l_rec05_sent NUMBER;
10901: l_prior_ptp NUMBER;
10902: l_dummy NUMBER;

Line 10914: l_old_date1 ben_ext_chg_evt_log.old_val1%TYPE;

10910: l_org_rows_exist NUMBER;
10911: l_kind VARCHAR2(30) := 'WNE';
10912: l_value VARCHAR2(30) := '100';
10913: l_value_num NUMBER;
10914: l_old_date1 ben_ext_chg_evt_log.old_val1%TYPE;
10915: l_new_date1 ben_ext_chg_evt_log.new_val1%TYPE;
10916: l_old_date2 ben_ext_chg_evt_log.old_val1%TYPE;
10917: l_new_date2 ben_ext_chg_evt_log.new_val1%TYPE;
10918: l_term_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;

Line 10915: l_new_date1 ben_ext_chg_evt_log.new_val1%TYPE;

10911: l_kind VARCHAR2(30) := 'WNE';
10912: l_value VARCHAR2(30) := '100';
10913: l_value_num NUMBER;
10914: l_old_date1 ben_ext_chg_evt_log.old_val1%TYPE;
10915: l_new_date1 ben_ext_chg_evt_log.new_val1%TYPE;
10916: l_old_date2 ben_ext_chg_evt_log.old_val1%TYPE;
10917: l_new_date2 ben_ext_chg_evt_log.new_val1%TYPE;
10918: l_term_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
10919: l_revt_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;

Line 10916: l_old_date2 ben_ext_chg_evt_log.old_val1%TYPE;

10912: l_value VARCHAR2(30) := '100';
10913: l_value_num NUMBER;
10914: l_old_date1 ben_ext_chg_evt_log.old_val1%TYPE;
10915: l_new_date1 ben_ext_chg_evt_log.new_val1%TYPE;
10916: l_old_date2 ben_ext_chg_evt_log.old_val1%TYPE;
10917: l_new_date2 ben_ext_chg_evt_log.new_val1%TYPE;
10918: l_term_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
10919: l_revt_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
10920: l_reason VARCHAR2(1);

Line 10917: l_new_date2 ben_ext_chg_evt_log.new_val1%TYPE;

10913: l_value_num NUMBER;
10914: l_old_date1 ben_ext_chg_evt_log.old_val1%TYPE;
10915: l_new_date1 ben_ext_chg_evt_log.new_val1%TYPE;
10916: l_old_date2 ben_ext_chg_evt_log.old_val1%TYPE;
10917: l_new_date2 ben_ext_chg_evt_log.new_val1%TYPE;
10918: l_term_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
10919: l_revt_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
10920: l_reason VARCHAR2(1);
10921: l_old_term_reason VARCHAR2(1);

Line 10918: l_term_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;

10914: l_old_date1 ben_ext_chg_evt_log.old_val1%TYPE;
10915: l_new_date1 ben_ext_chg_evt_log.new_val1%TYPE;
10916: l_old_date2 ben_ext_chg_evt_log.old_val1%TYPE;
10917: l_new_date2 ben_ext_chg_evt_log.new_val1%TYPE;
10918: l_term_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
10919: l_revt_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
10920: l_reason VARCHAR2(1);
10921: l_old_term_reason VARCHAR2(1);
10922: l_org_id NUMBER;

Line 10919: l_revt_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;

10915: l_new_date1 ben_ext_chg_evt_log.new_val1%TYPE;
10916: l_old_date2 ben_ext_chg_evt_log.old_val1%TYPE;
10917: l_new_date2 ben_ext_chg_evt_log.new_val1%TYPE;
10918: l_term_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
10919: l_revt_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
10920: l_reason VARCHAR2(1);
10921: l_old_term_reason VARCHAR2(1);
10922: l_org_id NUMBER;
10923: l_ppp_start ben_ext_chg_evt_log.new_val1%TYPE;

Line 10923: l_ppp_start ben_ext_chg_evt_log.new_val1%TYPE;

10919: l_revt_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%TYPE;
10920: l_reason VARCHAR2(1);
10921: l_old_term_reason VARCHAR2(1);
10922: l_org_id NUMBER;
10923: l_ppp_start ben_ext_chg_evt_log.new_val1%TYPE;
10924: l_ppp_end ben_ext_chg_evt_log.new_val2%TYPE;
10925: l_ppp_found NUMBER := 0;
10926: l_ppp_kind VARCHAR2(1) := ' ';
10927: l_ppp_ret NUMBER;

Line 10924: l_ppp_end ben_ext_chg_evt_log.new_val2%TYPE;

10920: l_reason VARCHAR2(1);
10921: l_old_term_reason VARCHAR2(1);
10922: l_org_id NUMBER;
10923: l_ppp_start ben_ext_chg_evt_log.new_val1%TYPE;
10924: l_ppp_end ben_ext_chg_evt_log.new_val2%TYPE;
10925: l_ppp_found NUMBER := 0;
10926: l_ppp_kind VARCHAR2(1) := ' ';
10927: l_ppp_ret NUMBER;
10928: l_ppp_err VARCHAR2(1000);

Line 10958: l_old_hire_dt_can ben_ext_chg_evt_log.old_val1%TYPE;

10954: l_opnp_ret_val NUMBER;
10955: l_prev_term_dt VARCHAR2(8);
10956: l_old_hire_dt DATE;
10957: l_new_hire_dt DATE;
10958: l_old_hire_dt_can ben_ext_chg_evt_log.old_val1%TYPE;
10959: l_new_hire_dt_can ben_ext_chg_evt_log.new_val1%TYPE;
10960: l_out_retro_ptpn t_retro_ptpn;
10961: l_out_retro_ptpn_kind t_retro_ptpn_kind;
10962: l_ge_retro_ptpn NUMBER;

Line 10959: l_new_hire_dt_can ben_ext_chg_evt_log.new_val1%TYPE;

10955: l_prev_term_dt VARCHAR2(8);
10956: l_old_hire_dt DATE;
10957: l_new_hire_dt DATE;
10958: l_old_hire_dt_can ben_ext_chg_evt_log.old_val1%TYPE;
10959: l_new_hire_dt_can ben_ext_chg_evt_log.new_val1%TYPE;
10960: l_out_retro_ptpn t_retro_ptpn;
10961: l_out_retro_ptpn_kind t_retro_ptpn_kind;
10962: l_ge_retro_ptpn NUMBER;
10963: l_ge_retro_ptpn_kind NUMBER;

Line 11832: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

11828:
11829: --cursor to check if ASG EIT rows exist
11830: CURSOR c_asg_rows_exist IS
11831: SELECT 1
11832: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
11833: WHERE chg_evt_cd = 'COAPPD'
11834: AND prmtr_01 = 'ASG'
11835: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11836: AND pty.pension_sub_category = 'IPAP'

Line 11850: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

11846:
11847: --cursor to check if ORG EIT rows exist
11848: CURSOR c_org_rows_exist(c_org_id IN Number) IS
11849: SELECT 1
11850: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
11851: WHERE chg_evt_cd = 'COAPPD'
11852: AND prmtr_01 = 'ORG'
11853: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11854: AND pty.pension_sub_category = 'IPAP'

Line 11862: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

11858:
11859: --cursor to get the old and new start and end dates from the ASG EIT
11860: CURSOR c_get_asg_rows IS
11861: SELECT old_val1,new_val1,old_val2,new_val2
11862: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
11863: WHERE chg_evt_cd = 'COAPPD'
11864: AND prmtr_01 = 'ASG'
11865: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11866: AND prmtr_03 = 'Y'

Line 11883: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty

11879:
11880: --cursor to get the old and new start and end dates from the ORG EIT
11881: CURSOR c_get_org_rows(c_org_id IN Number,c_hire_date IN Date) IS
11882: SELECT old_val1,new_val1,old_val2,new_val2
11883: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
11884: WHERE chg_evt_cd = 'COAPPD'
11885: AND prmtr_01 = 'ORG'
11886: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11887: AND prmtr_03 = 'Y'

Line 12471: FROM ben_ext_chg_evt_log

12467: ,c_start_date IN DATE
12468: ,c_end_date IN DATE) IS
12469: SELECT old_val1 old_date,
12470: new_val1 new_date
12471: FROM ben_ext_chg_evt_log
12472: WHERE person_id = c_person_id
12473: AND chg_evt_cd = 'COPOS'
12474: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
12475: ORDER BY ext_chg_evt_log_id desc;

Line 12491: FROM ben_ext_chg_evt_log

12487:
12488: CURSOR c_get_override_salary (c_start IN DATE, c_end IN DATE) IS
12489: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))
12490: ,fnd_date.canonical_to_date(prmtr_02)
12491: FROM ben_ext_chg_evt_log
12492: WHERE person_id = g_person_id
12493: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
12494: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
12495: AND g_extract_params(p_business_group_id).extract_end_date

Line 12510: l_old_date_can ben_ext_chg_evt_log.old_val1%TYPE;

12506: l_or_pen_sal NUMBER;
12507: l_ext_rslt_dtl_id NUMBER;
12508: l_main_rec csr_rslt_dtl%ROWTYPE;
12509: l_new_rec csr_rslt_dtl%ROWTYPE;
12510: l_old_date_can ben_ext_chg_evt_log.old_val1%TYPE;
12511: l_new_date_can ben_ext_chg_evt_log.new_val1%TYPE;
12512: l_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
12513: l_pension_sal_char VARCHAR2(15);
12514: l_pension_yr_char VARCHAR2(15);

Line 12511: l_new_date_can ben_ext_chg_evt_log.new_val1%TYPE;

12507: l_ext_rslt_dtl_id NUMBER;
12508: l_main_rec csr_rslt_dtl%ROWTYPE;
12509: l_new_rec csr_rslt_dtl%ROWTYPE;
12510: l_old_date_can ben_ext_chg_evt_log.old_val1%TYPE;
12511: l_new_date_can ben_ext_chg_evt_log.new_val1%TYPE;
12512: l_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
12513: l_pension_sal_char VARCHAR2(15);
12514: l_pension_yr_char VARCHAR2(15);
12515: l_get_ps_val NUMBER;