[Home] [Help]
71:
72: cursor csr_ele_entry (p_element_link number, p_inp_val number)is
73: select pee.element_entry_id
74: from PAY_ELEMENT_ENTRY_VALUES_F pev,
75: PAY_ELEMENT_ENTRIES_F pee
76: where pev.screen_entry_value = p_jurisdiction_code
77: and pev.input_value_id + 0 = p_inp_val
78: and p_session_date between pev.effective_start_date
79: and pev.effective_end_date
358: /* Cursor to get the worker's compensation element entry */
359:
360: cursor csr_wc_ele_entry (p_element_link number)is
361: select pee.element_entry_id
362: from PAY_ELEMENT_ENTRIES_F pee
363: where p_session_date between pee.effective_start_date
364: and pee.effective_end_date
365: and pee.element_link_id = p_element_link
366: and pee.assignment_id = p_assignment_id;
6126: cursor csr_get_jurisdiction is
6127: select peev.screen_entry_value jurisdiction,
6128: peef.effective_start_date start_date
6129: from PAY_ELEMENT_ENTRY_VALUES_F peev,
6130: PAY_ELEMENT_ENTRIES_F peef,
6131: PAY_INPUT_VALUES_F piv,
6132: PAY_ELEMENT_TYPES_F pet
6133: where pet.element_name = 'VERTEX'
6134: and pet.element_type_id = piv.element_type_id
6141: and peef.effective_start_date = p_effective_start_date
6142: and peef.effective_end_date = p_effective_end_date
6143: and peef.effective_start_date between pet.effective_start_date and pet.effective_end_date; -- Bug 3354060 added to remove MJC between
6144: -- PAY_INPUT_VALUES_F and
6145: -- PAY_ELEMENT_ENTRIES_F
6146:
6147: begin
6148:
6149: hr_utility.set_location('pay_us_emp_dt_tax_rules.zero_out_time',1);
7070:
7071: cursor csr_ele_entry (p_element_link number, p_inp_val number)is
7072: select pee.element_entry_id
7073: from PAY_ELEMENT_ENTRY_VALUES_F pev,
7074: PAY_ELEMENT_ENTRIES_F pee
7075: where pev.screen_entry_value = p_jurisdiction_code
7076: and pev.input_value_id + 0 = p_inp_val
7077: and p_session_date between pev.effective_start_date
7078: and pev.effective_end_date
7175: if p_new_start_date is not null
7176: then
7177:
7178: l_step := 4;
7179: update PAY_ELEMENT_ENTRIES_F
7180: set effective_start_date = p_new_start_date
7181: where element_entry_id = l_element_entry_id
7182: and p_session_date between effective_start_date
7183: and effective_end_date;
7193: if p_new_end_date is not null
7194: then
7195:
7196: l_step := 6;
7197: update PAY_ELEMENT_ENTRIES_F
7198: set effective_end_date = p_new_end_date
7199: where element_entry_id = l_element_entry_id
7200: and p_session_date between effective_start_date
7201: and effective_end_date;
7220:
7221: /* Delete the element entry values */
7222:
7223: l_step := 9;
7224: delete PAY_ELEMENT_ENTRIES_F
7225: where element_entry_id = l_element_entry_id
7226: and p_session_date between effective_start_date
7227: and effective_end_date;
7228:
7446: /* Cursor to get the element entry for the jurisdiction */
7447:
7448: cursor csr_wc_ele_entry (p_element_link number)is
7449: select pee.element_entry_id
7450: from PAY_ELEMENT_ENTRIES_F pee
7451: where pee.assignment_id = p_assignment_id
7452: and p_session_date between pee.effective_start_date
7453: and pee.effective_end_date
7454: and pee.element_link_id = p_element_link;
7516: if p_new_start_date is not null
7517: then
7518:
7519: l_step := 4;
7520: update PAY_ELEMENT_ENTRIES_F
7521: set effective_start_date = p_new_start_date
7522: where element_entry_id = l_element_entry_id
7523: and p_session_date between effective_start_date
7524: and effective_end_date;
7535: if p_new_end_date is not null
7536: then
7537:
7538: l_step := 6;
7539: update PAY_ELEMENT_ENTRIES_F
7540: set effective_end_date = p_new_end_date
7541: where element_entry_id = l_element_entry_id
7542: and p_session_date between effective_start_date
7543: and effective_end_date;
7562:
7563: /* Delete the element entry values */
7564:
7565: l_step := 9;
7566: delete PAY_ELEMENT_ENTRIES_F
7567: where element_entry_id = l_element_entry_id
7568: and p_session_date between effective_start_date
7569: and effective_end_date;
7570: end if;
7751:
7752: /* cursor to get the effective end date of the next element entry record */
7753: cursor csr_get_next_date (p_element_link number, p_date date)is
7754: select pee.effective_end_date
7755: from PAY_ELEMENT_ENTRIES_F pee
7756: where pee.assignment_id = p_assignment_id
7757: and p_date between pee.effective_start_date
7758: and pee.effective_end_date
7759: and pee.element_link_id = p_element_link
8032: /* Cursor to check for multiple date effective records of the vertex
8033: element entries */
8034: cursor csr_multiple_rec(p_def_date date, p_ele_link number) is
8035: select 'Y'
8036: from pay_element_entries_f pef
8037: where pef.assignment_id = p_assignment_id
8038: and pef.element_link_id = p_ele_link
8039: and pef.effective_start_date >= p_def_date
8040: and exists (select null
8037: where pef.assignment_id = p_assignment_id
8038: and pef.element_link_id = p_ele_link
8039: and pef.effective_start_date >= p_def_date
8040: and exists (select null
8041: from pay_element_entries_f pee
8042: where pee.assignment_id = p_assignment_id
8043: and pee.element_entry_id = pef.element_entry_id
8044: and pee.effective_start_date >= p_def_date
8045: and pee.effective_start_date <> pef.effective_start_date);
8049: cursor csr_ele_entry (p_element_link number)is
8050: select pee.effective_start_date,
8051: pee.effective_end_date,
8052: pee.effective_start_date -1
8053: from PAY_ELEMENT_ENTRIES_F pee
8054: where pee.assignment_id = p_assignment_id
8055: and p_session_date between pee.effective_start_date
8056: and pee.effective_end_date
8057: and pee.element_link_id = p_element_link
8070:
8071: /* cursor to get the effective end date of the next element entry record */
8072: cursor csr_get_next_date (p_element_link number, p_date date)is
8073: select pee.effective_end_date
8074: from PAY_ELEMENT_ENTRIES_F pee
8075: where pee.assignment_id = p_assignment_id
8076: and p_date between pee.effective_start_date
8077: and pee.effective_end_date
8078: and pee.element_link_id = p_element_link
8109:
8110: /* cursor to get the effective start date of the previous element entry record */
8111: cursor csr_get_prev_date (p_element_link number, p_date date)is
8112: select pee.effective_start_date
8113: from PAY_ELEMENT_ENTRIES_F pee
8114: where pee.assignment_id = p_assignment
8115: and pee.element_link_id = p_element_link
8116: and p_date between pee.effective_start_date
8117: and pee.effective_end_date
9079: cursor csr_get_dates(p_element_link number, p_session_dt date) is
9080: select pef.effective_start_date,
9081: pef.effective_end_date,
9082: pef.effective_end_date + 1
9083: from PAY_ELEMENT_ENTRIES_F pef
9084: where pef.assignment_id = p_assignment_id
9085: and p_session_dt between pef.effective_start_date
9086: and pef.effective_end_date
9087: and pef.element_link_id = p_element_link
9098:
9099: /* cursor to get the effective end date of the next element entry record */
9100: cursor csr_get_next_date (p_element_link number, p_date date)is
9101: select pee.effective_end_date
9102: from PAY_ELEMENT_ENTRIES_F pee
9103: where pee.assignment_id = p_assignment_id
9104: and p_date between pee.effective_start_date
9105: and pee.effective_end_date
9106: and pee.element_link_id = p_element_link