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.PAY_GB_RTI_FPS_ARCHIVE dependencies on PAY_ASSIGNMENT_ACTIONS

Line 1570: FROM pay_assignment_actions

1566:
1567: CURSOR csr_child_act_id(c_lst_act_id NUMBER)
1568: IS
1569: SELECT MAX(ASSIGNMENT_ACTION_ID)
1570: FROM pay_assignment_actions
1571: WHERE SOURCE_ACTION_ID = c_lst_act_id;
1572:
1573: BEGIN
1574: hr_utility.set_location('Entering: '||l_proc,1);

Line 1658: from pay_assignment_actions

1654:
1655: --To fetch assignment id of the payroll run
1656: cursor csr_get_asg_id is
1657: select assignment_id
1658: from pay_assignment_actions
1659: where assignment_action_id = p_last_asg_action_id;
1660:
1661: BEGIN
1662:

Line 1768: FROM pay_assignment_actions act,

1764: pap.date_of_birth date_of_birth,
1765: SUBSTR(pap.sex,1,1) sex ,
1766: DECODE(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
1767: DECODE(pap.per_information9,'Y','Y',NULL) multiple_asg_flag
1768: FROM pay_assignment_actions act,
1769: per_all_assignments_f paa,
1770: per_all_people_f pap
1771: WHERE act.assignment_action_id = p_assactid
1772: AND act.assignment_id = paa.assignment_id

Line 1944: pay_assignment_actions paa,

1940: SELECT ptp.regular_payment_date,
1941: ptp.period_num,
1942: ptp.period_type
1943: FROM pay_payroll_actions ppa,
1944: pay_assignment_actions paa,
1945: per_time_periods ptp
1946: WHERE assignment_action_id = p_last_asg_action_id
1947: AND ppa.time_period_id = ptp.time_period_id
1948: AND ppa.payroll_id = ptp.payroll_id

Line 1982: FROM pay_assignment_actions paa ,

1978: --Below cursor fetches further ni details of the current assignment
1979: CURSOR csr_cat_NI_Details
1980: IS
1981: SELECT MAX(DECODE(name,'Periods',result_value,NULL))
1982: FROM pay_assignment_actions paa ,
1983: pay_run_results prr,
1984: pay_run_result_values prrv,
1985: pay_element_types_f petf ,
1986: pay_input_values_f pivf

Line 2000: FROM pay_assignment_actions paa ,

1996: --Below cursor fetches further ni details of the current assignment
1997: CURSOR csr_cat_NI
1998: IS
1999: SELECT MAX(DECODE(name,'Periods',result_value,NULL))
2000: FROM pay_assignment_actions paa ,
2001: pay_run_results prr,
2002: pay_run_result_values prrv,
2003: pay_element_types_f petf ,
2004: pay_input_values_f pivf

Line 2022: pay_assignment_actions paa

2018: trim(NVL(pap.per_information10,'N')) per_agg_flag,
2019: asg.assignment_number assignment_number
2020: FROM per_all_people_f pap,
2021: per_all_assignments_f asg,
2022: pay_assignment_actions paa
2023: WHERE paa.assignment_action_id = c_asg_act_id
2024: AND paa.assignment_id = asg.assignment_id
2025: AND pap.person_id = asg.person_id
2026: AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date

Line 2042: FROM pay_assignment_actions paa,

2038: SELECT
2039: /*+ USE_NL(paa, pact, ptp) */
2040: MIN(ptp.start_date) payroll_period_start_date,
2041: MAX(ptp.end_date) payroll_period_end_date
2042: FROM pay_assignment_actions paa,
2043: pay_payroll_actions pact,
2044: per_time_periods ptp,
2045: pay_assignment_actions paa1, --Prepayments
2046: pay_payroll_actions ppa1, --Prepayments

Line 2045: pay_assignment_actions paa1, --Prepayments

2041: MAX(ptp.end_date) payroll_period_end_date
2042: FROM pay_assignment_actions paa,
2043: pay_payroll_actions pact,
2044: per_time_periods ptp,
2045: pay_assignment_actions paa1, --Prepayments
2046: pay_payroll_actions ppa1, --Prepayments
2047: pay_action_interlocks pai --Prepayments
2048: WHERE paa.assignment_id = p_asgid
2049: AND paa.payroll_action_id = pact.payroll_action_id

Line 2095: pay_assignment_actions paa_max

2091: and ppa_current.payroll_action_id = g_pre_pact_id
2092: and ppa_prev.payroll_action_id =
2093: (select max(ppa_max.payroll_action_id)
2094: from pay_payroll_actions ppa_max,
2095: pay_assignment_actions paa_max
2096: where ppa_max.payroll_id = ppa_current.payroll_id
2097: and ppa_max.payroll_action_id < ppa_current.payroll_action_id
2098: and ppa_max.action_type IN ('P','U')
2099: and ppa_max.payroll_action_id = paa_max.payroll_action_id

Line 2106: from pay_assignment_actions paa_prepay,

2102: --Below cursor fetches the Previous run FPS assignment_action_id
2103: CURSOR csr_last_fps_asg_act_id (c_last_pre_pay_id NUMBER, c_asg_id NUMBER)
2104: IS
2105: select paa_fps.assignment_action_id
2106: from pay_assignment_actions paa_prepay,
2107: pay_payroll_actions ppa_prepay,
2108: pay_assignment_actions paa_fps,
2109: pay_payroll_actions ppa_fps,
2110: pay_action_interlocks pai

Line 2108: pay_assignment_actions paa_fps,

2104: IS
2105: select paa_fps.assignment_action_id
2106: from pay_assignment_actions paa_prepay,
2107: pay_payroll_actions ppa_prepay,
2108: pay_assignment_actions paa_fps,
2109: pay_payroll_actions ppa_fps,
2110: pay_action_interlocks pai
2111: where ppa_prepay.payroll_action_id = c_last_pre_pay_id
2112: and ppa_prepay.payroll_action_id = paa_prepay.payroll_action_id

Line 2730: pay_assignment_actions paa,

2726: SELECT ptp.regular_payment_date,
2727: ptp.period_num,
2728: ptp.period_type
2729: FROM pay_payroll_actions ppa,
2730: pay_assignment_actions paa,
2731: per_time_periods ptp
2732: WHERE assignment_action_id = l_last_asg_action_id
2733: AND ppa.time_period_id = ptp.time_period_id
2734: AND ppa.payroll_id = ptp.payroll_id

Line 2763: FROM pay_assignment_actions paa ,

2759: --Below cursor fetches further ni details of the current assignment
2760: CURSOR csr_cat_NI_Details
2761: IS
2762: SELECT MAX(DECODE(name,'Periods',result_value,NULL))
2763: FROM pay_assignment_actions paa ,
2764: pay_run_results prr,
2765: pay_run_result_values prrv,
2766: pay_element_types_f petf ,
2767: pay_input_values_f pivf

Line 2781: FROM pay_assignment_actions paa ,

2777: --Below cursor fetches further ni details of the current assignment
2778: CURSOR csr_cat_NI
2779: IS
2780: SELECT MAX(DECODE(name,'Periods',result_value,NULL))
2781: FROM pay_assignment_actions paa ,
2782: pay_run_results prr,
2783: pay_run_result_values prrv,
2784: pay_element_types_f petf ,
2785: pay_input_values_f pivf

Line 2818: FROM pay_assignment_actions paa,

2814: SELECT
2815: /*+ USE_NL(paa, pact, ptp) */
2816: MIN(ptp.start_date) payroll_period_start_date,
2817: MAX(ptp.end_date) payroll_period_end_date
2818: FROM pay_assignment_actions paa,
2819: pay_payroll_actions pact,
2820: per_time_periods ptp,
2821: pay_assignment_actions paa1, --Prepayments
2822: pay_payroll_actions ppa1, --Prepayments

Line 2821: pay_assignment_actions paa1, --Prepayments

2817: MAX(ptp.end_date) payroll_period_end_date
2818: FROM pay_assignment_actions paa,
2819: pay_payroll_actions pact,
2820: per_time_periods ptp,
2821: pay_assignment_actions paa1, --Prepayments
2822: pay_payroll_actions ppa1, --Prepayments
2823: pay_action_interlocks pai --Prepayments
2824: WHERE paa.assignment_id = p_asgid
2825: AND paa.payroll_action_id = pact.payroll_action_id

Line 2871: pay_assignment_actions paa_max

2867: and ppa_current.payroll_action_id = g_pre_pact_id
2868: and ppa_prev.payroll_action_id =
2869: (select max(ppa_max.payroll_action_id)
2870: from pay_payroll_actions ppa_max,
2871: pay_assignment_actions paa_max
2872: where ppa_max.payroll_id = ppa_current.payroll_id
2873: and ppa_max.payroll_action_id < ppa_current.payroll_action_id
2874: and ppa_max.action_type IN ('P','U')
2875: and ppa_max.payroll_action_id = paa_max.payroll_action_id

Line 2882: from pay_assignment_actions paa_prepay,

2878: --Below cursor fetches the Previous run FPS assignment_action_id
2879: CURSOR csr_last_fps_asg_act_id (c_last_pre_pay_id NUMBER, c_asg_id NUMBER)
2880: IS
2881: select paa_fps.assignment_action_id
2882: from pay_assignment_actions paa_prepay,
2883: pay_payroll_actions ppa_prepay,
2884: pay_assignment_actions paa_fps,
2885: pay_payroll_actions ppa_fps,
2886: pay_action_interlocks pai

Line 2884: pay_assignment_actions paa_fps,

2880: IS
2881: select paa_fps.assignment_action_id
2882: from pay_assignment_actions paa_prepay,
2883: pay_payroll_actions ppa_prepay,
2884: pay_assignment_actions paa_fps,
2885: pay_payroll_actions ppa_fps,
2886: pay_action_interlocks pai
2887: where ppa_prepay.payroll_action_id = c_last_pre_pay_id
2888: and ppa_prepay.payroll_action_id = paa_prepay.payroll_action_id

Line 2929: pay_assignment_actions paa,

2925: to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
2926: || paa.assignment_action_id),16)) max_asg_act_id,
2927: MAX(pact.effective_date) effective_date
2928: FROM per_all_assignments_f paaf,
2929: pay_assignment_actions paa,
2930: pay_payroll_actions pact,
2931: per_time_periods ptp,
2932: pay_assignment_actions paa1, --Prepayments
2933: pay_payroll_actions ppa1, --Prepayments

Line 2932: pay_assignment_actions paa1, --Prepayments

2928: FROM per_all_assignments_f paaf,
2929: pay_assignment_actions paa,
2930: pay_payroll_actions pact,
2931: per_time_periods ptp,
2932: pay_assignment_actions paa1, --Prepayments
2933: pay_payroll_actions ppa1, --Prepayments
2934: pay_action_interlocks pai --Prepayments
2935: WHERE paaf.person_id = p_person_rec.person_id--59437
2936: AND sysdate between paaf.effective_start_date and paaf.effective_end_date

Line 3532: pay_assignment_actions paa,

3528: SELECT ptp.regular_payment_date,
3529: ptp.period_num,
3530: ptp.period_type
3531: FROM pay_payroll_actions ppa,
3532: pay_assignment_actions paa,
3533: per_time_periods ptp
3534: WHERE assignment_action_id = p_last_asg_action_id
3535: AND ppa.time_period_id = ptp.time_period_id
3536: AND ppa.payroll_id = ptp.payroll_id

Line 3564: FROM pay_assignment_actions paa ,

3560: --Below cursor fetches further ni details of the current assignment
3561: CURSOR csr_cat_NI_Details
3562: IS
3563: SELECT MAX(DECODE(name,'Periods',result_value,NULL))
3564: FROM pay_assignment_actions paa ,
3565: pay_run_results prr,
3566: pay_run_result_values prrv,
3567: pay_element_types_f petf ,
3568: pay_input_values_f pivf

Line 3580: FROM pay_assignment_actions paa ,

3576: --Below cursor fetches further ni details of the current assignment
3577: CURSOR csr_cat_NI
3578: IS
3579: SELECT MAX(DECODE(name,'Periods',result_value,NULL))
3580: FROM pay_assignment_actions paa ,
3581: pay_run_results prr,
3582: pay_run_result_values prrv,
3583: pay_element_types_f petf ,
3584: pay_input_values_f pivf

Line 3601: pay_assignment_actions paa

3597: asg.assignment_number assignment_number,
3598: asg.assignment_id assignment_id
3599: FROM per_all_people_f pap,
3600: per_all_assignments_f asg,
3601: pay_assignment_actions paa
3602: WHERE paa.assignment_action_id = c_asg_act_id
3603: AND paa.assignment_id = asg.assignment_id
3604: AND pap.person_id = asg.person_id
3605: AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date

Line 3621: FROM pay_assignment_actions paa,

3617: SELECT
3618: /*+ USE_NL(paa, pact, ptp) */
3619: MIN(ptp.start_date) payroll_period_start_date,
3620: MAX(ptp.end_date) payroll_period_end_date
3621: FROM pay_assignment_actions paa,
3622: pay_payroll_actions pact,
3623: per_time_periods ptp,
3624: pay_assignment_actions paa1, --Prepayments
3625: pay_payroll_actions ppa1, --Prepayments

Line 3624: pay_assignment_actions paa1, --Prepayments

3620: MAX(ptp.end_date) payroll_period_end_date
3621: FROM pay_assignment_actions paa,
3622: pay_payroll_actions pact,
3623: per_time_periods ptp,
3624: pay_assignment_actions paa1, --Prepayments
3625: pay_payroll_actions ppa1, --Prepayments
3626: pay_action_interlocks pai --Prepayments
3627: WHERE paa.assignment_id = p_asgid
3628: AND paa.payroll_action_id = pact.payroll_action_id

Line 3674: pay_assignment_actions paa_max

3670: and ppa_current.payroll_action_id = g_pre_pact_id
3671: and ppa_prev.payroll_action_id =
3672: (select max(ppa_max.payroll_action_id)
3673: from pay_payroll_actions ppa_max,
3674: pay_assignment_actions paa_max
3675: where ppa_max.payroll_id = ppa_current.payroll_id
3676: and ppa_max.payroll_action_id < ppa_current.payroll_action_id
3677: and ppa_max.action_type IN ('P','U')
3678: and ppa_max.payroll_action_id = paa_max.payroll_action_id

Line 3685: from pay_assignment_actions paa_prepay,

3681: --Below cursor fetches the Previous run FPS assignment_action_id
3682: CURSOR csr_last_fps_asg_act_id (c_last_pre_pay_id NUMBER, c_asg_id NUMBER)
3683: IS
3684: select paa_fps.assignment_action_id
3685: from pay_assignment_actions paa_prepay,
3686: pay_payroll_actions ppa_prepay,
3687: pay_assignment_actions paa_fps,
3688: pay_payroll_actions ppa_fps,
3689: pay_action_interlocks pai

Line 3687: pay_assignment_actions paa_fps,

3683: IS
3684: select paa_fps.assignment_action_id
3685: from pay_assignment_actions paa_prepay,
3686: pay_payroll_actions ppa_prepay,
3687: pay_assignment_actions paa_fps,
3688: pay_payroll_actions ppa_fps,
3689: pay_action_interlocks pai
3690: where ppa_prepay.payroll_action_id = c_last_pre_pay_id
3691: and ppa_prepay.payroll_action_id = paa_prepay.payroll_action_id

Line 4242: FROM pay_assignment_actions paa ,

4238:
4239: CURSOR csr_cat_NI_Details
4240: IS
4241: SELECT MAX(DECODE(name,'Category',result_value,NULL))
4242: FROM pay_assignment_actions paa ,
4243: pay_run_results prr,
4244: pay_run_result_values prrv,
4245: pay_element_types_f petf ,
4246: pay_input_values_f pivf

Line 4257: FROM pay_assignment_actions paa ,

4253:
4254: CURSOR csr_cat_NI
4255: IS
4256: SELECT MAX(DECODE(name,'Category',result_value,NULL))
4257: FROM pay_assignment_actions paa ,
4258: pay_run_results prr,
4259: pay_run_result_values prrv,
4260: pay_element_types_f petf ,
4261: pay_input_values_f pivf

Line 4583: FROM pay_assignment_actions

4579:
4580: CURSOR csr_child_act_id(c_lst_act_id NUMBER) -- kvinayku
4581: IS
4582: SELECT NVL(MAX(ASSIGNMENT_ACTION_ID),c_lst_act_id)
4583: FROM pay_assignment_actions
4584: WHERE SOURCE_ACTION_ID = c_lst_act_id;
4585:
4586: CURSOR csr_tax_paye_result(p_action_id NUMBER) -- kvinayku
4587: IS

Line 4856: FROM pay_assignment_actions

4852:
4853: CURSOR csr_child_act_id(c_lst_act_id NUMBER) -- kvinayku
4854: IS
4855: SELECT NVL(MAX(ASSIGNMENT_ACTION_ID),c_lst_act_id)
4856: FROM pay_assignment_actions
4857: WHERE SOURCE_ACTION_ID = c_lst_act_id;
4858:
4859: CURSOR csr_tax_paye_result(p_action_id NUMBER) -- kvinayku
4860: IS

Line 5084: FROM pay_assignment_actions

5080:
5081: CURSOR csr_child_act_id(c_lst_act_id NUMBER)
5082: IS
5083: SELECT assignment_action_id
5084: FROM pay_assignment_actions
5085: WHERE source_action_id = c_lst_act_id;
5086:
5087: CURSOR csr_tax_paye_result(p_action_id NUMBER)
5088: IS

Line 5138: FROM pay_assignment_actions

5134:
5135: CURSOR csr_max_child_act_id(c_lst_act_id NUMBER)
5136: IS
5137: SELECT max(assignment_action_id)
5138: FROM pay_assignment_actions
5139: WHERE source_action_id = c_lst_act_id;
5140:
5141: BEGIN
5142: hr_utility.set_location('Entering: '||l_proc,1);

Line 5766: FROM pay_assignment_actions

5762:
5763: CURSOR csr_child_act_id(c_lst_act_id NUMBER)
5764: IS
5765: SELECT ASSIGNMENT_ACTION_ID
5766: FROM pay_assignment_actions
5767: WHERE SOURCE_ACTION_ID = c_lst_act_id;
5768:
5769: CURSOR csr_tax_paye_result(p_action_id NUMBER)
5770: IS

Line 5821: FROM pay_assignment_actions

5817:
5818: CURSOR csr_max_child_act_id(c_lst_act_id NUMBER)
5819: IS
5820: SELECT max(assignment_action_id)
5821: FROM pay_assignment_actions
5822: WHERE source_action_id = c_lst_act_id;
5823:
5824: BEGIN
5825: hr_utility.set_location('Entering: '||l_proc,1);

Line 6577: select paa.assignment_action_id into l_assact_id from pay_assignment_actions paa

6573: ;
6574: hr_utility.trace('action_context_id = '|| p_assactid);
6575: IF p_tab_rec_data(i).action_info_category IS NOT NULL THEN
6576: -- get the assignment action id for the each assignment which are created in action creation
6577: select paa.assignment_action_id into l_assact_id from pay_assignment_actions paa
6578: where paa.payroll_action_id = g_payroll_action_id
6579: and paa.assignment_id = p_tab_rec_data(i).assignment_id;
6580:
6581: pay_action_information_api.create_action_information( p_action_information_id => l_action_id,

Line 6871: pay_assignment_actions act, --Payroll Run

6867: act.assignment_action_id asg_act_id
6868: FROM pay_payroll_actions ppa, --Current pactid
6869: pay_payroll_actions appa, --Payroll Run
6870: pay_payroll_actions appa2, --Prepayments
6871: pay_assignment_actions act, --Payroll Run
6872: pay_assignment_actions act1, --Prepayments
6873: pay_action_interlocks pai, --Prepayments
6874: per_all_assignments_f as1
6875: WHERE ppa.payroll_action_id = pactid

Line 6872: pay_assignment_actions act1, --Prepayments

6868: FROM pay_payroll_actions ppa, --Current pactid
6869: pay_payroll_actions appa, --Payroll Run
6870: pay_payroll_actions appa2, --Prepayments
6871: pay_assignment_actions act, --Payroll Run
6872: pay_assignment_actions act1, --Prepayments
6873: pay_action_interlocks pai, --Prepayments
6874: per_all_assignments_f as1
6875: WHERE ppa.payroll_action_id = pactid
6876: AND as1.person_id BETWEEN stperson AND endperson

Line 6946: FROM pay_assignment_actions paa_pre

6942:
6943: -- cursor to check whether this assignment processed in prepayment or not
6944: cursor csr_prepayment_made(pre_pay_id number,p_asg_id number) is
6945: SELECT paa_pre.assignment_id
6946: FROM pay_assignment_actions paa_pre
6947: , pay_assignment_actions paa_arcv
6948: WHERE paa_arcv.assignment_id = p_asg_id
6949: AND paa_pre.payroll_action_id = pre_pay_id
6950: AND paa_pre.assignment_id = paa_arcv.assignment_id;

Line 6947: , pay_assignment_actions paa_arcv

6943: -- cursor to check whether this assignment processed in prepayment or not
6944: cursor csr_prepayment_made(pre_pay_id number,p_asg_id number) is
6945: SELECT paa_pre.assignment_id
6946: FROM pay_assignment_actions paa_pre
6947: , pay_assignment_actions paa_arcv
6948: WHERE paa_arcv.assignment_id = p_asg_id
6949: AND paa_pre.payroll_action_id = pre_pay_id
6950: AND paa_pre.assignment_id = paa_arcv.assignment_id;
6951:

Line 6973: FROM pay_assignment_actions paa

6969: to_number (substr (max (lpad (paa.action_sequence, 15
6970: , '0')
6971: || paa.assignment_action_id), 16))
6972: , max (pact.effective_date) effective_date
6973: FROM pay_assignment_actions paa
6974: , pay_payroll_actions pact
6975: , per_time_periods ptp
6976: WHERE paa.assignment_id = p_asgid
6977: AND paa.payroll_action_id = pact.payroll_action_id

Line 6999: pay_assignment_actions act, --Payroll Run

6995: -- SELECT max (act.assignment_id) assignment_id
6996: FROM pay_payroll_actions ppa, --Current pactid
6997: pay_payroll_actions appa, --Payroll Run
6998: pay_payroll_actions appa2, --Prepayments
6999: pay_assignment_actions act, --Payroll Run
7000: pay_assignment_actions act1, --Prepayments
7001: pay_action_interlocks pai, --Prepayments
7002: per_all_assignments_f as1
7003: WHERE as1.person_id = p_person_id --45885

Line 7000: pay_assignment_actions act1, --Prepayments

6996: FROM pay_payroll_actions ppa, --Current pactid
6997: pay_payroll_actions appa, --Payroll Run
6998: pay_payroll_actions appa2, --Prepayments
6999: pay_assignment_actions act, --Payroll Run
7000: pay_assignment_actions act1, --Prepayments
7001: pay_action_interlocks pai, --Prepayments
7002: per_all_assignments_f as1
7003: WHERE as1.person_id = p_person_id --45885
7004: AND appa.action_type IN ('R','Q','V','B') -- Payroll Run or Quickpay Run

Line 7034: pay_assignment_actions act, --Payroll Run

7030: -- SELECT max (act.assignment_id) assignment_id
7031: FROM pay_payroll_actions ppa, --Current pactid
7032: pay_payroll_actions appa, --Payroll Run
7033: pay_payroll_actions appa2, --Prepayments
7034: pay_assignment_actions act, --Payroll Run
7035: pay_assignment_actions act1, --Prepayments
7036: pay_action_interlocks pai, --Prepayments
7037: per_all_assignments_f as1
7038: WHERE as1.person_id = p_person_id --45885

Line 7035: pay_assignment_actions act1, --Prepayments

7031: FROM pay_payroll_actions ppa, --Current pactid
7032: pay_payroll_actions appa, --Payroll Run
7033: pay_payroll_actions appa2, --Prepayments
7034: pay_assignment_actions act, --Payroll Run
7035: pay_assignment_actions act1, --Prepayments
7036: pay_action_interlocks pai, --Prepayments
7037: per_all_assignments_f as1
7038: WHERE as1.person_id = p_person_id --45885
7039: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run

Line 7074: FROM pay_assignment_actions paa

7070: || paa.assignment_action_id),16)) max_asg_act_id*/
7071: distinct paa.assignment_id
7072: /* ,max (pact.effective_date) effective_date
7073: ,max(paa1.assignment_action_id)*/
7074: FROM pay_assignment_actions paa
7075: ,pay_assignment_actions paa1
7076: ,per_all_assignments_f paaf
7077: ,pay_payroll_actions pact
7078: ,pay_payroll_actions pact1

Line 7075: ,pay_assignment_actions paa1

7071: distinct paa.assignment_id
7072: /* ,max (pact.effective_date) effective_date
7073: ,max(paa1.assignment_action_id)*/
7074: FROM pay_assignment_actions paa
7075: ,pay_assignment_actions paa1
7076: ,per_all_assignments_f paaf
7077: ,pay_payroll_actions pact
7078: ,pay_payroll_actions pact1
7079: ,per_time_periods ptp

Line 7132: FROM pay_assignment_actions paa

7128: || paa.assignment_action_id),16)) max_asg_act_id
7129: ,max(paa.assignment_id)
7130: ,max (pact.effective_date) effective_date*/
7131: distinct max(paa1.assignment_action_id) max_pre_act_id
7132: FROM pay_assignment_actions paa
7133: ,pay_assignment_actions paa1
7134: ,per_all_assignments_f paaf
7135: ,pay_payroll_actions pact
7136: ,pay_payroll_actions pact1

Line 7133: ,pay_assignment_actions paa1

7129: ,max(paa.assignment_id)
7130: ,max (pact.effective_date) effective_date*/
7131: distinct max(paa1.assignment_action_id) max_pre_act_id
7132: FROM pay_assignment_actions paa
7133: ,pay_assignment_actions paa1
7134: ,per_all_assignments_f paaf
7135: ,pay_payroll_actions pact
7136: ,pay_payroll_actions pact1
7137: ,per_time_periods ptp

Line 7182: pay_assignment_actions paa

7178: select pai.locking_action_id
7179: from
7180: pay_action_interlocks pai,
7181: pay_payroll_actions ppa,
7182: pay_assignment_actions paa
7183: where pai.locked_action_id = c_cur_asg_action_id
7184: and ppa.action_type in ('P','U')
7185: and paa.payroll_action_id = ppa.payroll_action_id
7186: and paa.assignment_action_id = pai.locking_action_id;

Line 7193: FROM pay_assignment_actions paa,

7189: IS
7190: SELECT paa.assignment_action_id,
7191: ppa.payroll_action_id,
7192: ppa.effective_date
7193: FROM pay_assignment_actions paa,
7194: pay_payroll_actions ppa
7195: WHERE paa.assignment_id = p_asgid
7196: AND ppa.payroll_action_id = p_pre_pay_id
7197: AND paa.payroll_action_id = ppa.payroll_action_id;

Line 7234: pay_assignment_actions paa,

7230: -- cursor to fetch the prepayment asg_action id for the given payroll asg_action id
7231: cursor csr_ni_prepay_asg_act_details(c_payroll_asg_act_id number) is
7232: select pai.locking_action_id prepay_asg_act_id from
7233: pay_action_interlocks pai,
7234: pay_assignment_actions paa,
7235: pay_payroll_actions ppa
7236: where pai.locked_action_id = c_payroll_asg_act_id
7237: and pai.locking_action_id = paa.assignment_action_id
7238: and paa.action_status in ('C','S')

Line 7244: select 'Y' from pay_assignment_actions paa,

7240: and paa.payroll_action_id = ppa.payroll_action_id;
7241:
7242: -- to check whether the given assignment is processed in given prepay payroll_action id
7243: cursor csr_rti_rpt_proc_prepay(c_ni_rpt_asg_id number,c_pre_pay_action_id number) is
7244: select 'Y' from pay_assignment_actions paa,
7245: pay_payroll_actions ppa
7246: where
7247: ppa.payroll_action_id = c_pre_pay_action_id
7248: and ppa.payroll_action_id = paa.payroll_action_id

Line 7267: FROM pay_assignment_actions paa,

7263: --Below cursor fetches the latest payroll run details of the current assignment
7264: CURSOR csr_aggr_asgs_processed(p_person_id NUMBER, p_pre_pay_id NUMBER, p_start_year DATE, p_end_year DATE)
7265: IS
7266: SELECT count(distinct paa1.assignment_id)
7267: FROM pay_assignment_actions paa,
7268: per_all_assignments_f paaf,
7269: pay_payroll_actions pact,
7270: per_time_periods ptp,
7271: pay_assignment_actions paa1, --Prepayments

Line 7271: pay_assignment_actions paa1, --Prepayments

7267: FROM pay_assignment_actions paa,
7268: per_all_assignments_f paaf,
7269: pay_payroll_actions pact,
7270: per_time_periods ptp,
7271: pay_assignment_actions paa1, --Prepayments
7272: pay_payroll_actions ppa1, --Prepayments
7273: pay_action_interlocks pai --Prepayments
7274: WHERE paa.assignment_id = paaf.assignment_id
7275: AND paaf.person_id = p_person_id

Line 7292: pay_assignment_actions act, --Payroll Run

7288: IS
7289: SELECT max(act.assignment_action_id) assignment_action_id
7290: FROM pay_payroll_actions appa, --Payroll Run
7291: pay_payroll_actions appa2, --Prepayments
7292: pay_assignment_actions act, --Payroll Run
7293: pay_assignment_actions act1, --Prepayments
7294: pay_action_interlocks pai, --Prepayments
7295: per_all_assignments_f as1,
7296: pay_all_payrolls_f papf ,

Line 7293: pay_assignment_actions act1, --Prepayments

7289: SELECT max(act.assignment_action_id) assignment_action_id
7290: FROM pay_payroll_actions appa, --Payroll Run
7291: pay_payroll_actions appa2, --Prepayments
7292: pay_assignment_actions act, --Payroll Run
7293: pay_assignment_actions act1, --Prepayments
7294: pay_action_interlocks pai, --Prepayments
7295: per_all_assignments_f as1,
7296: pay_all_payrolls_f papf ,
7297: hr_soft_coding_keyflex flex

Line 7318: FROM pay_assignment_actions

7314:
7315: CURSOR csr_asg_id(c_aggr_max_act_id number)
7316: IS
7317: SELECT assignment_id
7318: FROM pay_assignment_actions
7319: WHERE assignment_action_id = c_aggr_max_act_id;
7320:
7321: -- Declarations for First FPS
7322: l_last_asg_action_id number;

Line 7555: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;

7551: end if;
7552: end if; --if (l_flag = 'Y') then
7553:
7554: if l_flag = 'Y' and l_first_fps_prepay_asg = 'N' Then
7555: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
7556: -- Insert assignment into pay_assignment_actions
7557: hr_nonrun_asact.insact ( lockingactid, asg_record.assignment_id, pactid, chunk, NULL );
7558: End if;
7559:

Line 7556: -- Insert assignment into pay_assignment_actions

7552: end if; --if (l_flag = 'Y') then
7553:
7554: if l_flag = 'Y' and l_first_fps_prepay_asg = 'N' Then
7555: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
7556: -- Insert assignment into pay_assignment_actions
7557: hr_nonrun_asact.insact ( lockingactid, asg_record.assignment_id, pactid, chunk, NULL );
7558: End if;
7559:
7560: END LOOP;

Line 7613: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;

7609:
7610: if ((asg_rec.asg_act_id = l_aggr_min_act_id) and (l_earlier_processed = 0)) then
7611: hr_utility.set_location('Creating Assignment Action for assignment_id '||asg_rec.assignment_id, 50);
7612:
7613: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
7614: hr_nonrun_asact.insact ( lockingactid, asg_rec.assignment_id, pactid, chunk, NULL );
7615: -- get the assigment action id of the prepayment payroll action id for the current assignment.
7616: OPEN csr_prepay_asg_act_details(asg_rec.assignment_id, l_pre_pact_id);
7617: FETCH csr_prepay_asg_act_details

Line 7630: /* SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;

7626: hr_utility.set_location('Creating other Assignment Action ids for assignment_id '||asg_rec.assignment_id, 50);
7627:
7628: /*for c_asg in csr_get_other_asg(asg_rec.assignment_id)
7629: loop*/
7630: /* SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
7631: hr_utility.set_location('Creating action for asg '||c_asg.asg_id, 50);
7632: hr_nonrun_asact.insact(lockingactid, c_asg.asg_id, pactid, chunk, NULL );
7633: hr_utility.set_location('Created action for asg '||c_asg.asg_id, 50);*/
7634:

Line 7646: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;

7642: FETCH csr_agg_all_asg_act_creat
7643: INTO l_asg_id;
7644:
7645: EXIT WHEN csr_agg_all_asg_act_creat%NOTFOUND;
7646: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
7647: hr_utility.set_location('Creating action for asg '||l_asg_id, 50);
7648: hr_nonrun_asact.insact(lockingactid, l_asg_id, pactid, chunk, NULL );
7649: hr_utility.set_location('Created action for asg '||l_asg_id, 50);
7650:

Line 7691: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;

7687:
7688: ELSIF l_flag1 = 'Y' THEN
7689: -- Create one assignment action for every assignment
7690: hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
7691: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
7692:
7693: -- Insert assignment into pay_assignment_actions
7694: hr_nonrun_asact.insact ( lockingactid, asg_rec.assignment_id, pactid, chunk, NULL );
7695: -- get the assigment action id of the prepayment payroll action id for the current assignment.

Line 7693: -- Insert assignment into pay_assignment_actions

7689: -- Create one assignment action for every assignment
7690: hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
7691: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
7692:
7693: -- Insert assignment into pay_assignment_actions
7694: hr_nonrun_asact.insact ( lockingactid, asg_rec.assignment_id, pactid, chunk, NULL );
7695: -- get the assigment action id of the prepayment payroll action id for the current assignment.
7696: OPEN csr_prepay_asg_act_details(asg_rec.assignment_id, l_pre_pact_id);
7697: FETCH csr_prepay_asg_act_details

Line 7735: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;

7731: FETCH csr_ni_aggr_max_act_id INTO l_ni_aggr_max_act_id;
7732: CLOSE csr_ni_aggr_max_act_id;
7733:
7734: if nvl(l_rti_ni_proc_flag,'N') <> 'Y' and asg_rec.asg_act_id = l_ni_aggr_max_act_id then
7735: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
7736: -- Insert assignment into pay_assignment_actions
7737: hr_nonrun_asact.insact ( lockingactid, l_ni_rpt_asg_id, pactid, chunk, NULL );
7738: /* -- get the assigment action id of the prepayment payroll action id for the current assignment.
7739: OPEN csr_ni_prepay_asg_act_details(asg_rec.asg_act_id);

Line 7736: -- Insert assignment into pay_assignment_actions

7732: CLOSE csr_ni_aggr_max_act_id;
7733:
7734: if nvl(l_rti_ni_proc_flag,'N') <> 'Y' and asg_rec.asg_act_id = l_ni_aggr_max_act_id then
7735: SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
7736: -- Insert assignment into pay_assignment_actions
7737: hr_nonrun_asact.insact ( lockingactid, l_ni_rpt_asg_id, pactid, chunk, NULL );
7738: /* -- get the assigment action id of the prepayment payroll action id for the current assignment.
7739: OPEN csr_ni_prepay_asg_act_details(asg_rec.asg_act_id);
7740: FETCH csr_ni_prepay_asg_act_details

Line 7799: FROM pay_assignment_actions paa,

7795: IS
7796: SELECT aei.assignment_extra_info_id,
7797: aei.object_version_number,
7798: aei.aei_information8
7799: FROM pay_assignment_actions paa,
7800: per_assignment_extra_info aei
7801: WHERE paa.assignment_action_id = p_assact
7802: AND aei.assignment_id = paa.assignment_id
7803: AND aei.information_type = p_type;

Line 7810: FROM pay_assignment_actions paa,

7806: IS
7807: SELECT aei.assignment_extra_info_id,
7808: aei.object_version_number,
7809: aei.aei_information9
7810: FROM pay_assignment_actions paa,
7811: per_assignment_extra_info aei
7812: WHERE paa.assignment_action_id = p_assact
7813: AND aei.assignment_id = paa.assignment_id
7814: AND aei.information_type = p_type;

Line 7821: FROM pay_assignment_actions paa,

7817: IS
7818: SELECT aei.assignment_extra_info_id,
7819: aei.object_version_number,
7820: aei.aei_information19
7821: FROM pay_assignment_actions paa,
7822: per_assignment_extra_info aei
7823: WHERE paa.assignment_action_id = p_assact
7824: AND aei.assignment_id = paa.assignment_id
7825: AND aei.information_type = p_type;

Line 8584: FROM pay_assignment_actions paa

8580: to_number (substr (max (lpad (paa.action_sequence ,15,'0')
8581: || paa.assignment_action_id),16)) max_asg_act_id
8582: , max(pact.effective_date) effective_date
8583:
8584: FROM pay_assignment_actions paa
8585: ,pay_payroll_actions pact
8586: ,per_time_periods ptp
8587: WHERE paa.assignment_id = c_asg_id
8588: AND paa.payroll_action_id = pact.payroll_action_id

Line 8603: FROM pay_assignment_actions paa

8599: to_number (substr (max (lpad (paa.action_sequence ,15,'0')
8600: || paa.assignment_action_id),16)) max_asg_act_id
8601: , max(pact.effective_date) effective_date
8602:
8603: FROM pay_assignment_actions paa
8604: ,pay_payroll_actions pact
8605: ,per_time_periods ptp
8606: WHERE paa.assignment_id = c_asg_id
8607: AND paa.payroll_action_id = pact.payroll_action_id

Line 8771: pay_assignment_actions paa,

8767: CURSOR csr_get_payment_date(p_last_asg_action_id NUMBER)
8768: IS
8769: SELECT ptp.regular_payment_date payment_date
8770: FROM pay_payroll_actions ppa,
8771: pay_assignment_actions paa,
8772: per_time_periods ptp
8773: WHERE assignment_action_id = p_last_asg_action_id
8774: AND ppa.time_period_id = ptp.time_period_id
8775: AND ppa.payroll_id = ptp.payroll_id

Line 8783: pay_assignment_actions paa1,

8779: cursor get_min_asg_actid(p_person_id IN Number,p_assactid IN number)
8780: is
8781: select min(paa2.assignment_action_id) asgactid
8782: from
8783: pay_assignment_actions paa1,
8784: pay_assignment_actions paa2,
8785: per_all_assignments_f asg
8786: where
8787: paa1.assignment_action_id = p_assactid

Line 8784: pay_assignment_actions paa2,

8780: is
8781: select min(paa2.assignment_action_id) asgactid
8782: from
8783: pay_assignment_actions paa1,
8784: pay_assignment_actions paa2,
8785: per_all_assignments_f asg
8786: where
8787: paa1.assignment_action_id = p_assactid
8788: and paa1.payroll_action_id = paa2.payroll_action_id

Line 8800: pay_assignment_actions paa,

8796: asg.EFFECTIVE_START_DATE asg_eff_start_date,
8797: asg.person_id,
8798: asg.assignment_number
8799: FROM per_all_assignments_f asg,
8800: pay_assignment_actions paa,
8801: pay_payroll_actions ppa
8802: WHERE paa.assignment_action_id = c_asg_act_id
8803: AND paa.assignment_id = asg.assignment_id
8804: AND ppa.payroll_action_id = g_pre_pact_id

Line 8818: pay_assignment_actions paa

8814: trim(NVL(pap.per_information10,'N')) per_paye_agg_flag,
8815: asg.payroll_id payroll_id
8816: FROM per_all_people_f pap,
8817: per_all_assignments_f asg,
8818: pay_assignment_actions paa
8819: WHERE paa.assignment_action_id = c_asg_act_id
8820: AND paa.assignment_id = asg.assignment_id
8821: AND pap.person_id = asg.person_id
8822: -- and asg.business_group_id = l_business_group_id

Line 8836: FROM pay_assignment_actions paa,

8832: /*+ USE_NL(paa, pact, ptp) */
8833: to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
8834: || paa.assignment_action_id),16)) max_asg_act_id,
8835: MAX(pact.effective_date) effective_date
8836: FROM pay_assignment_actions paa,
8837: pay_payroll_actions pact,
8838: per_time_periods ptp,
8839: pay_assignment_actions paa1, --Prepayments
8840: pay_payroll_actions ppa1, --Prepayments

Line 8839: pay_assignment_actions paa1, --Prepayments

8835: MAX(pact.effective_date) effective_date
8836: FROM pay_assignment_actions paa,
8837: pay_payroll_actions pact,
8838: per_time_periods ptp,
8839: pay_assignment_actions paa1, --Prepayments
8840: pay_payroll_actions ppa1, --Prepayments
8841: pay_action_interlocks pai --Prepayments
8842: WHERE paa.assignment_id = p_asgid
8843: AND paa.payroll_action_id = pact.payroll_action_id

Line 8862: FROM pay_assignment_actions paa,

8858: /*+ USE_NL(paa, pact, ptp) */
8859: to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
8860: || paa.assignment_action_id),16)) max_asg_act_id,
8861: MAX(pact.effective_date) effective_date
8862: FROM pay_assignment_actions paa,
8863: pay_payroll_actions pact,
8864: per_time_periods ptp,
8865: pay_assignment_actions paa1, --Prepayments
8866: pay_payroll_actions ppa1, --Prepayments

Line 8865: pay_assignment_actions paa1, --Prepayments

8861: MAX(pact.effective_date) effective_date
8862: FROM pay_assignment_actions paa,
8863: pay_payroll_actions pact,
8864: per_time_periods ptp,
8865: pay_assignment_actions paa1, --Prepayments
8866: pay_payroll_actions ppa1, --Prepayments
8867: pay_action_interlocks pai --Prepayments
8868: WHERE paa.assignment_id = p_asgid --46959
8869: AND paa.payroll_action_id = pact.payroll_action_id

Line 8888: FROM pay_assignment_actions paa,

8884: IS
8885: SELECT paa.assignment_action_id,
8886: ppa.payroll_action_id,
8887: ppa.effective_date
8888: FROM pay_assignment_actions paa,
8889: pay_payroll_actions ppa
8890: WHERE paa.assignment_id = p_asgid
8891: AND ppa.payroll_action_id = p_pre_pay_id
8892: AND paa.payroll_action_id = ppa.payroll_action_id;

Line 8897: FROM pay_assignment_actions paa

8893: ---
8894: CURSOR csr_pay_act_details(p_asg_actid NUMBER)
8895: IS
8896: SELECT paa.payroll_action_id
8897: FROM pay_assignment_actions paa
8898: WHERE paa.assignment_action_id = p_asg_actid;
8899:
8900: --Below cursor verifies whether current payment for this assignment is already archived
8901: CURSOR csr_earlier_archived(p_last_asg_action_id NUMBER)

Line 8967: PAY_ASSIGNMENT_ACTIONS PAA,

8963: pay_org_payment_methods_f popf,
8964: pay_personal_payment_methods_f pppf,
8965: pay_external_accounts Orgkey,
8966: pay_external_accounts deskey,
8967: PAY_ASSIGNMENT_ACTIONS PAA,
8968: pay_payroll_actions ppa
8969: WHERE popf.ORG_PAYMENT_METHOD_ID = ppp.ORG_PAYMENT_METHOD_ID
8970: AND pppf.personal_payment_method_ID = ppp.personal_payment_method_ID
8971: AND popf.PAYMENT_TYPE_ID = ppt.PAYMENT_TYPE_ID

Line 8988: pay_assignment_actions paa,

8984: IS
8985: select (sum(ppp.value) * 100) amount
8986: from pay_pre_payments ppp,
8987: pay_org_payment_methods_f popmf,
8988: pay_assignment_actions paa,
8989: pay_payroll_actions ppa
8990: where ppp.ASSIGNMENT_ACTION_ID = c_prepay_asg_action_id
8991: and popmf.ORG_PAYMENT_METHOD_ID = ppp.ORG_PAYMENT_METHOD_ID
8992: and popmf.defined_balance_id is not null

Line 9010: FROM pay_assignment_actions paa,

9006: /*+ USE_NL(paa, pact, ptp) */
9007: to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
9008: || paa.assignment_action_id),16)) max_asg_act_id,
9009: MAX(pact.effective_date) effective_date
9010: FROM pay_assignment_actions paa,
9011: per_all_assignments_f paaf,
9012: pay_payroll_actions pact,
9013: per_time_periods ptp,
9014: pay_assignment_actions paa1, --Prepayments

Line 9014: pay_assignment_actions paa1, --Prepayments

9010: FROM pay_assignment_actions paa,
9011: per_all_assignments_f paaf,
9012: pay_payroll_actions pact,
9013: per_time_periods ptp,
9014: pay_assignment_actions paa1, --Prepayments
9015: pay_payroll_actions ppa1, --Prepayments
9016: pay_action_interlocks pai --Prepayments
9017: WHERE paa.assignment_id = paaf.assignment_id
9018: AND paaf.person_id = p_person_id

Line 9051: FROM pay_assignment_actions paa

9047: SELECT /*+ USE_NL(paa, pact, ptp) */
9048: to_number (substr (max (lpad (paa.action_sequence ,15,'0')
9049: || paa.assignment_action_id),16)) max_asg_act_id
9050: ,max (pact.effective_date) effective_date
9051: FROM pay_assignment_actions paa
9052: ,per_all_assignments_f paaf
9053: ,pay_payroll_actions pact
9054: ,per_time_periods ptp
9055: WHERE paa.assignment_id = paaf.assignment_id

Line 9089: FROM pay_assignment_actions

9085:
9086: CURSOR csr_aggr_sequence_id(c_asg_act_id NUMBER)
9087: IS
9088: SELECT action_sequence
9089: FROM pay_assignment_actions
9090: WHERE assignment_action_id = c_asg_act_id;
9091:
9092: CURSOR csr_aggr_locking_asg_act_id(p_person_id NUMBER, p_action_sequence NUMBER)
9093: IS

Line 9103: ,pay_assignment_actions act

9099: */
9100: SELECT min (act.assignment_action_id)
9101: FROM pay_action_classifications class
9102: ,pay_payroll_actions pact
9103: ,pay_assignment_actions act
9104: ,per_all_assignments_f ass
9105: ,per_periods_of_service pos
9106: WHERE pos.person_id = p_person_id
9107: AND ass.period_of_service_id = pos.period_of_service_id

Line 9120: pay_assignment_actions paa,

9116: CURSOR csr_processed_in_prepayment(c_prepay_asgactid NUMBER, c_payroll_asgactid NUMBER, c_payroll_master_asgactid number)
9117: IS
9118: SELECT 'Y' this_prepayment
9119: FROM pay_action_interlocks pai,
9120: pay_assignment_actions paa,
9121: pay_payroll_actions ppa
9122: WHERE paa.payroll_action_id = ppa.payroll_action_id
9123: AND paa.assignment_action_id = pai.locking_action_id
9124: AND ppa.action_type in ('P','U')

Line 9139: from pay_assignment_actions

9135: */
9136:
9137: cursor csr_get_asg_id(c_asgactid number) is
9138: select distinct assignment_id
9139: from pay_assignment_actions
9140: where assignment_action_id = c_asgactid;
9141: -- Curosr to fetch the RTI Reporting flag and effective date of the assignment
9142: cursor csr_ni_rpt_flag(c_asg_id number) is
9143: select AEI_INFORMATION1, AEI_INFORMATION2 from per_assignment_extra_info where assignment_id = c_asg_id

Line 9212: pay_assignment_actions paa,

9208: ***/
9209: cursor csr_asg_act_oth_asgs(c_asg_id number) is
9210: select max(paa.assignment_action_id) asg_act_id from per_all_assignments_f paaf,
9211: per_all_assignments_f paaf1,
9212: pay_assignment_actions paa,
9213: pay_payroll_actions ppa
9214: where paaf.assignment_id = c_asg_id
9215: and paaf1.person_id = paaf.person_id
9216: and paaf1.assignment_id <> c_asg_id

Line 9228: FROM pay_assignment_actions paa_pre

9224: -- First FPS Cursors
9225: -- cursor to check whether this assignment processed in prepayment or not
9226: cursor csr_prepayment_made(pre_pay_id number) is
9227: SELECT paa_pre.assignment_id
9228: FROM pay_assignment_actions paa_pre
9229: , pay_assignment_actions paa_arcv
9230: WHERE paa_arcv.assignment_action_id = p_assactid
9231: AND paa_pre.payroll_action_id = pre_pay_id
9232: AND paa_pre.assignment_id = paa_arcv.assignment_id;

Line 9229: , pay_assignment_actions paa_arcv

9225: -- cursor to check whether this assignment processed in prepayment or not
9226: cursor csr_prepayment_made(pre_pay_id number) is
9227: SELECT paa_pre.assignment_id
9228: FROM pay_assignment_actions paa_pre
9229: , pay_assignment_actions paa_arcv
9230: WHERE paa_arcv.assignment_action_id = p_assactid
9231: AND paa_pre.payroll_action_id = pre_pay_id
9232: AND paa_pre.assignment_id = paa_arcv.assignment_id;
9233:

Line 9237: FROM pay_assignment_actions paa

9233:
9234: -- cursor to get assignment id using assignment action id parameter
9235: CURSOR csr_asg_id(c_asg_act_id NUMBER) IS
9236: SELECT paa.assignment_id
9237: FROM pay_assignment_actions paa
9238: WHERE paa.assignment_action_id = c_asg_act_id;
9239:
9240: -- To fetch First FPS parameter
9241: /*CURSOR csr_parameter_info IS

Line 9245: , pay_assignment_actions paa

9241: /*CURSOR csr_parameter_info IS
9242: SELECT substr (pay_gb_eoy_archive.get_parameter (legislative_parameters, 'FIRST_FPS'), 1,1) first_fps,
9243: SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref
9244: FROM pay_payroll_actions pact
9245: , pay_assignment_actions paa
9246: WHERE paa.assignment_action_id = p_assactid
9247: AND pact.payroll_action_id = paa.payroll_action_id;
9248: */
9249:

Line 9273: FROM pay_assignment_actions paa

9269: to_number (substr (max (lpad (paa.action_sequence, 15
9270: , '0')
9271: || paa.assignment_action_id), 16))
9272: , max (pact.effective_date) effective_date
9273: FROM pay_assignment_actions paa
9274: , pay_payroll_actions pact
9275: , per_time_periods ptp
9276: WHERE paa.assignment_id = p_asgid
9277: AND paa.payroll_action_id = pact.payroll_action_id

Line 9296: FROM pay_assignment_actions paa

9292: to_number (substr (max (lpad (paa.action_sequence, 15
9293: , '0')
9294: || paa.assignment_action_id), 16))
9295: , max (pact.effective_date) effective_date
9296: FROM pay_assignment_actions paa
9297: , pay_payroll_actions pact
9298: , per_time_periods ptp
9299: WHERE paa.assignment_id = p_asgid
9300: AND paa.payroll_action_id = pact.payroll_action_id

Line 9334: select count(*) from pay_assignment_actions paa, --run

9330: and ASSIGNMENT_ID = c_asg_id
9331: and PAYROLL_ASG_ACT_ID = c_cur_asg_action_id;
9332:
9333: cursor csr_chk_prepayment_run(c_assignment_action_id number) is
9334: select count(*) from pay_assignment_actions paa, --run
9335: pay_assignment_actions paa1, --Prepayments
9336: pay_payroll_actions ppa1, --Prepayments
9337: pay_action_interlocks pai --Prepayments
9338: where paa.assignment_action_id = c_assignment_action_id --9917796

Line 9335: pay_assignment_actions paa1, --Prepayments

9331: and PAYROLL_ASG_ACT_ID = c_cur_asg_action_id;
9332:
9333: cursor csr_chk_prepayment_run(c_assignment_action_id number) is
9334: select count(*) from pay_assignment_actions paa, --run
9335: pay_assignment_actions paa1, --Prepayments
9336: pay_payroll_actions ppa1, --Prepayments
9337: pay_action_interlocks pai --Prepayments
9338: where paa.assignment_action_id = c_assignment_action_id --9917796
9339: AND paa.assignment_action_id = pai.locked_action_id

Line 9354: pay_assignment_actions act, --Payroll Run

9350: min(act.assignment_action_id) assignment_action_id
9351: FROM pay_payroll_actions ppa, --Current pactid
9352: pay_payroll_actions appa, --Payroll Run
9353: pay_payroll_actions appa2, --Prepayments
9354: pay_assignment_actions act, --Payroll Run
9355: pay_assignment_actions act1, --Prepayments
9356: pay_action_interlocks pai, --Prepayments
9357: per_all_assignments_f as1
9358: WHERE as1.person_id = p_person_id --45885

Line 9355: pay_assignment_actions act1, --Prepayments

9351: FROM pay_payroll_actions ppa, --Current pactid
9352: pay_payroll_actions appa, --Payroll Run
9353: pay_payroll_actions appa2, --Prepayments
9354: pay_assignment_actions act, --Payroll Run
9355: pay_assignment_actions act1, --Prepayments
9356: pay_action_interlocks pai, --Prepayments
9357: per_all_assignments_f as1
9358: WHERE as1.person_id = p_person_id --45885
9359: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run

Line 9381: pay_assignment_actions act, --Payroll Run

9377: SELECT max(act.assignment_action_id) assignment_action_id
9378: FROM pay_payroll_actions ppa, --Current pactid
9379: pay_payroll_actions appa, --Payroll Run
9380: pay_payroll_actions appa2, --Prepayments
9381: pay_assignment_actions act, --Payroll Run
9382: pay_assignment_actions act1, --Prepayments
9383: pay_action_interlocks pai, --Prepayments
9384: per_all_assignments_f as1
9385: WHERE as1.person_id = p_person_id --45885

Line 9382: pay_assignment_actions act1, --Prepayments

9378: FROM pay_payroll_actions ppa, --Current pactid
9379: pay_payroll_actions appa, --Payroll Run
9380: pay_payroll_actions appa2, --Prepayments
9381: pay_assignment_actions act, --Payroll Run
9382: pay_assignment_actions act1, --Prepayments
9383: pay_action_interlocks pai, --Prepayments
9384: per_all_assignments_f as1
9385: WHERE as1.person_id = p_person_id --45885
9386: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run

Line 9407: pay_assignment_actions act, --Payroll Run

9403: FROM
9404: pay_payroll_actions ppa, --Current pactid
9405: pay_payroll_actions appa, --Payroll Run
9406: pay_payroll_actions appa2, --Prepayments
9407: pay_assignment_actions act, --Payroll Run
9408: pay_assignment_actions act1, --Prepayments
9409: pay_action_interlocks pai, --Prepayments
9410: per_all_assignments_f as1
9411: WHERE

Line 9408: pay_assignment_actions act1, --Prepayments

9404: pay_payroll_actions ppa, --Current pactid
9405: pay_payroll_actions appa, --Payroll Run
9406: pay_payroll_actions appa2, --Prepayments
9407: pay_assignment_actions act, --Payroll Run
9408: pay_assignment_actions act1, --Prepayments
9409: pay_action_interlocks pai, --Prepayments
9410: per_all_assignments_f as1
9411: WHERE
9412: ppa.payroll_action_id = g_payroll_action_id

Line 9433: FROM pay_assignment_actions

9429:
9430: CURSOR csr_aggr_master_id(c_child_asg_act_id NUMBER)
9431: IS
9432: SELECT assignment_action_id
9433: FROM pay_assignment_actions
9434: WHERE source_action_id = c_child_asg_act_id;
9435:
9436: CURSOR csr_aggr_max_act_id( p_person_id NUMBER,
9437: p_pre_pact_id NUMBER,

Line 9446: pay_assignment_actions act, --Payroll Run

9442: max(act.assignment_action_id) assignment_action_id
9443: FROM pay_payroll_actions ppa, --Current pactid
9444: pay_payroll_actions appa, --Payroll Run
9445: pay_payroll_actions appa2, --Prepayments
9446: pay_assignment_actions act, --Payroll Run
9447: pay_assignment_actions act1, --Prepayments
9448: pay_action_interlocks pai, --Prepayments
9449: per_all_assignments_f as1
9450: WHERE as1.person_id = p_person_id --45885

Line 9447: pay_assignment_actions act1, --Prepayments

9443: FROM pay_payroll_actions ppa, --Current pactid
9444: pay_payroll_actions appa, --Payroll Run
9445: pay_payroll_actions appa2, --Prepayments
9446: pay_assignment_actions act, --Payroll Run
9447: pay_assignment_actions act1, --Prepayments
9448: pay_action_interlocks pai, --Prepayments
9449: per_all_assignments_f as1
9450: WHERE as1.person_id = p_person_id --45885
9451: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run

Line 9619: pay_assignment_actions paa

9615: select pai.locking_action_id
9616: from
9617: pay_action_interlocks pai,
9618: pay_payroll_actions ppa,
9619: pay_assignment_actions paa
9620: where pai.locked_action_id = c_cur_asg_action_id
9621: and ppa.action_type in ('P','U')
9622: and paa.payroll_action_id = ppa.payroll_action_id
9623: and paa.assignment_action_id = pai.locking_action_id;

Line 9629: pay_assignment_actions paa

9625: /* cursor csr_is_retry_request is
9626: select 'Y' from fnd_concurrent_programs fcp,
9627: fnd_concurrent_requests fcr,
9628: pay_payroll_actions ppa,
9629: pay_assignment_actions paa
9630: where paa.assignment_action_id = p_assactid
9631: and ppa.payroll_action_id = paa.payroll_action_id
9632: and ppa.request_id = fcr.request_id
9633: and fcr.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID

Line 9639: pay_assignment_actions

9635:
9636: -- Modified retry cursor
9637: cursor csr_is_retry_request is
9638: select 'Y' from
9639: pay_assignment_actions
9640: where assignment_action_id = p_assactid
9641: AND ACTION_STATUS = 'M';
9642:
9643: /* To get the payroll action id given assignment action id

Line 9649: pay_assignment_actions paa

9645: cursor csr_prepay_payrol_act(c_asg_act_id number)
9646: is
9647: select paa.payroll_action_id
9648: from
9649: pay_assignment_actions paa
9650: where paa.assignment_action_id = c_asg_act_id;
9651:
9652: -- to check whether the given assignment is processed in given prepay payroll_action id
9653: cursor csr_rti_rpt_proc_prepay(c_ni_rpt_asg_id number,c_pre_pay_action_id number) is

Line 9654: select 'Y' from pay_assignment_actions paa,

9650: where paa.assignment_action_id = c_asg_act_id;
9651:
9652: -- to check whether the given assignment is processed in given prepay payroll_action id
9653: cursor csr_rti_rpt_proc_prepay(c_ni_rpt_asg_id number,c_pre_pay_action_id number) is
9654: select 'Y' from pay_assignment_actions paa,
9655: pay_payroll_actions ppa
9656: where
9657: ppa.payroll_action_id = c_pre_pay_action_id
9658: and ppa.payroll_action_id = paa.payroll_action_id

Line 9671: FROM pay_assignment_actions paa

9667: to_number (substr (max (lpad (paa.action_sequence ,15,'0')
9668: || paa.assignment_action_id),16)) max_asg_act_id
9669: , max(pact.effective_date) effective_date
9670:
9671: FROM pay_assignment_actions paa
9672: ,pay_payroll_actions pact
9673: ,per_time_periods ptp
9674: WHERE paa.assignment_id = c_asg_id
9675: AND paa.payroll_action_id = pact.payroll_action_id

Line 9685: from pay_assignment_actions

9681: AND ptp.regular_payment_date <= g_effective_date;
9682:
9683: CURSOR csr_last_aggr_asg(c_last_asgact_id number) is
9684: select distinct assignment_id
9685: from pay_assignment_actions
9686: where assignment_action_id = c_last_asgact_id;
9687:
9688:
9689: --Below cursor fetches the number of assignments processed in current prepayment request

Line 9693: FROM pay_assignment_actions paa,

9689: --Below cursor fetches the number of assignments processed in current prepayment request
9690: CURSOR csr_aggr_asgs_processed(p_person_id NUMBER, p_pre_pay_id NUMBER, p_start_year DATE, p_end_year DATE)
9691: IS
9692: SELECT count(distinct paa1.assignment_id)
9693: FROM pay_assignment_actions paa,
9694: per_all_assignments_f paaf,
9695: pay_payroll_actions pact,
9696: per_time_periods ptp,
9697: pay_assignment_actions paa1, --Prepayments

Line 9697: pay_assignment_actions paa1, --Prepayments

9693: FROM pay_assignment_actions paa,
9694: per_all_assignments_f paaf,
9695: pay_payroll_actions pact,
9696: per_time_periods ptp,
9697: pay_assignment_actions paa1, --Prepayments
9698: pay_payroll_actions ppa1, --Prepayments
9699: pay_action_interlocks pai --Prepayments
9700: WHERE paa.assignment_id = paaf.assignment_id
9701: AND paaf.person_id = p_person_id

Line 9718: pay_assignment_actions paa

9714: CURSOR csr_parameter_info IS
9715: SELECT fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS_DATE'),1,10)) first_fps_date,
9716: to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id
9717: FROM pay_payroll_actions pact,
9718: pay_assignment_actions paa
9719: WHERE paa.assignment_action_id = p_assactid
9720: AND pact.payroll_action_id = paa.payroll_action_id;
9721:
9722: -- To get maximum assignment action id for the person

Line 9731: pay_assignment_actions paa

9727: || paa.assignment_action_id), 16))
9728: , max (pact.effective_date) effective_date
9729:
9730: FROM per_all_assignments_f paaf,
9731: pay_assignment_actions paa
9732: , pay_payroll_actions pact
9733: , per_time_periods ptp
9734: WHERE paa.assignment_id = paaf.assignment_id
9735: and paaf.person_id = person_id

Line 9757: pay_assignment_actions paa

9753: CURSOR csr_asg_num(c_asg_act_id NUMBER, c_cur_last_effective_date date)
9754: IS
9755: SELECT asg.assignment_number
9756: FROM per_all_assignments_f asg,
9757: pay_assignment_actions paa
9758: WHERE paa.assignment_action_id = c_asg_act_id
9759: AND paa.assignment_id = asg.assignment_id
9760: AND c_cur_last_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
9761:

Line 9775: from pay_assignment_actions paa,

9771: AND information_type = 'GB_RTI_ASG_DETAILS';
9772:
9773: cursor csr_fps_check(p_asg_id number) is
9774: select count(*)
9775: from pay_assignment_actions paa,
9776: pay_payroll_actions ppa
9777: where paa.assignment_id = p_asg_id
9778: and paa.action_status ='C'
9779: and paa.assignment_action_id <> p_assactid

Line 9785: from pay_assignment_actions paa

9781: and ppa.report_type in ( 'RTI_FPS_REP' , 'RTI_FPS_REP_13');
9782:
9783: cursor csr_fps_action_exists(c_cur_asg_id number, c_payroll_action_id number) is
9784: select 'Y'
9785: from pay_assignment_actions paa
9786: where paa.payroll_action_id = c_payroll_action_id
9787: and paa.assignment_id = c_cur_asg_id;
9788:
9789: l_aggr_asgs_processed number;

Line 9905: from pay_assignment_actions where assignment_action_id = p_assactid;

9901: --delete from pay_gb_fps_details in case of retry
9902: if l_retry_req_flag = 'Y' then
9903: hr_utility.trace('RETRY : l_retry_req_flag: '|| l_retry_req_flag);
9904: select payroll_action_id into l_pact_id
9905: from pay_assignment_actions where assignment_action_id = p_assactid;
9906: open get_min_asg_actid(l_person_id,p_assactid);
9907: fetch get_min_asg_actid into min_asgactid;
9908: close get_min_asg_actid;
9909:

Line 10871: pay_assignment_actions paa,

10867:
10868: -- should skip the aggregation logic for other assignments
10869: select max(paa.assignment_action_id) into l_asg_action_id
10870: from
10871: pay_assignment_actions paa,
10872: per_all_assignments_f paaf
10873: where
10874: paaf.person_id = l_person_id
10875: and paaf.assignment_id = paa.assignment_id

Line 10992: pay_assignment_actions paa

10988: begin
10989: select ppa.payroll_action_id into l_oth_min_pre_pay_id from
10990: pay_action_interlocks pai,
10991: pay_payroll_actions ppa,
10992: pay_assignment_actions paa
10993: where
10994: pai.locked_action_id = l_cur_asg_action_id
10995: and pai.locking_action_id = paa.assignment_action_id
10996: and ppa.payroll_action_id = paa.payroll_action_id

Line 11120: select paa.assignment_action_id into l_assact_id from pay_assignment_actions paa

11116: if (l_fps_action_exists = 'Y') then
11117: --Fix for the bug 16409794 ends here
11118:
11119: -- get the assignment action id for the current assginment id.
11120: select paa.assignment_action_id into l_assact_id from pay_assignment_actions paa
11121: where paa.payroll_action_id = g_payroll_action_id
11122: and paa.assignment_id = l_cur_asg_id;
11123:
11124: if (nvl(l_processed_in_prepayment, 'N') <> 'Y') then

Line 12817: select paa.assignment_action_id into l_ni_rpt_assact_id from pay_assignment_actions paa

12813: /*open csr_get_latest_asg_action(l_ni_rpt_asg_id);
12814: fetch csr_get_latest_asg_action into l_ni_max_asg_act_id, l_ni_max_asg_act_date;
12815: close csr_get_latest_asg_action;
12816: */
12817: select paa.assignment_action_id into l_ni_rpt_assact_id from pay_assignment_actions paa
12818: where paa.payroll_action_id = g_payroll_action_id
12819: and paa.assignment_id = l_ni_rpt_asg_id;
12820: hr_utility.set_location('Calling get_ni_only_asg_details:',1000);
12821: get_ni_only_asg_details(l_ni_rpt_asg_id,

Line 12972: select paa.assignment_action_id into l_ni_rpt_assact_id from pay_assignment_actions paa

12968: get_ni_only_asg_details(l_ni_rpt_asg_id,l_archive_tab_ni_rpt,
12969: end if; -- end if for l_archive_tab_ni_rpt.count > 0
12970: */
12971:
12972: select paa.assignment_action_id into l_ni_rpt_assact_id from pay_assignment_actions paa
12973: where paa.payroll_action_id = g_payroll_action_id
12974: and paa.assignment_id = l_ni_rpt_asg_id;
12975: hr_utility.set_location('l_ni_rpt_assact_id: ' || l_ni_rpt_assact_id ,1000);
12976: -- insert_archive_row(p_assactid, p_effective_date,l_archive_tab);

Line 13224: pay_assignment_actions paa

13220: IS
13221: SELECT DISTINCT paa.assignment_action_id asg_action_id,
13222: assignment_id
13223: FROM pay_payroll_actions ppa,
13224: pay_assignment_actions paa
13225: WHERE ppa.payroll_action_id = pactid -- pact_id
13226: AND paa.payroll_action_id = ppa.payroll_action_id
13227: AND paa.action_status = 'E';
13228:

Line 13392: pay_assignment_actions paa

13388: IS
13389: SELECT DISTINCT paa.assignment_action_id asg_action_id,
13390: assignment_id
13391: FROM pay_payroll_actions ppa,
13392: pay_assignment_actions paa
13393: WHERE ppa.payroll_action_id = pactid -- pact_id
13394: AND paa.payroll_action_id = ppa.payroll_action_id
13395: AND paa.action_status = 'C';
13396:

Line 13403: pay_assignment_actions paa

13399: IS
13400: SELECT DISTINCT paa.assignment_action_id asg_action_id,
13401: assignment_id
13402: FROM pay_payroll_actions ppa,
13403: pay_assignment_actions paa
13404: WHERE ppa.payroll_action_id = pactid -- pact_id
13405: AND paa.payroll_action_id = ppa.payroll_action_id
13406: AND paa.action_status = 'E';
13407:

Line 13598: pay_assignment_actions paa,

13594: CURSOR get_person_count
13595: IS
13596: SELECT COUNT(DISTINCT(paaf.person_id))
13597: FROM pay_payroll_actions ppa,
13598: pay_assignment_actions paa,
13599: per_all_assignments_f paaf
13600: WHERE ppa.payroll_action_id = pactid -- pact_id
13601: AND paa.payroll_action_id = ppa.payroll_action_id
13602: AND paa.action_status = 'C'