[Home] [Help]
260: return varchar2
261: is
262:
263: l_base_type pay_user_Column_instances_f.value%type;
264: l_group_code pay_fr_contribution_usages.group_code%TYPE;
265: l_proc varchar2(72) := g_package||'.get_base_name_cu';
266: --
267: begin
268: --
286: end get_base_name_CU;
287: ------------------------------------------------------------------------
288: -- Function GET_GROUP_CODE
289: -- This function will be used to determine the group code of a given
290: -- row in pay_fr_contribution_usages
291: ------------------------------------------------------------------------
292: function get_group_code (p_cu_id in number)
293: return varchar2
294: is
291: ------------------------------------------------------------------------
292: function get_group_code (p_cu_id in number)
293: return varchar2
294: is
295: l_group_code pay_fr_contribution_usages.group_code%TYPE;
296: l_proc varchar2(72) := g_package||'.get_group_code';
297: --
298: begin
299: --
299: --
300: hr_utility.set_location('Entered '||l_proc,5);
301: select group_code
302: into l_group_code
303: from pay_fr_contribution_usages
304: where contribution_usage_id = p_cu_id;
305: --
306: hr_utility.set_location('. Group Code:'||l_group_code,20);
307: hr_utility.set_location('Leaving '||l_proc,25);
377: -- +********************************************************************+
378: --
379: ------------------------------------------------------------------------
380: -- Function GET_CONTRIBUTION_USAGE
381: -- This function obtains a row from the pay_fr_contribution_usages table
382: -- It will be called from other cover functions.
383: ------------------------------------------------------------------------
384: function get_contribution_usage
385: (p_process_type in varchar2
386: ,p_element_name in varchar2
387: ,p_usage_type in varchar2
388: ,p_effective_date in date
389: ,p_business_group_id in number default null)
390: return pay_fr_contribution_usages%rowtype
391: is
392:
393: l_contribution_row pay_fr_contribution_usages%rowtype;
394:
389: ,p_business_group_id in number default null)
390: return pay_fr_contribution_usages%rowtype
391: is
392:
393: l_contribution_row pay_fr_contribution_usages%rowtype;
394:
395: cursor contribution_usages_csr is
396: select *
397: from pay_fr_contribution_usages cu
393: l_contribution_row pay_fr_contribution_usages%rowtype;
394:
395: cursor contribution_usages_csr is
396: select *
397: from pay_fr_contribution_usages cu
398: where cu.process_type = p_process_type
399: and cu.contribution_usage_type = p_usage_type
400: and p_effective_date between cu.date_from and nvl(cu.date_to,to_date('31-12-4712','DD-MM-YYYY'))
401: and cu.element_name = p_element_name
458: l_contribution_type varchar2(10);
459: l_base_name pay_user_Column_instances_f.value%type;
460: l_code_rate_id number;
461: l_rate_category varchar2(1);
462: l_element_name pay_fr_contribution_usages.element_name%type;
463:
464: l_contribution_usage_row pay_fr_contribution_usages%rowtype;
465:
466: l_proc varchar2(72) := g_package||'.GET_RATE_VALUE';
460: l_code_rate_id number;
461: l_rate_category varchar2(1);
462: l_element_name pay_fr_contribution_usages.element_name%type;
463:
464: l_contribution_usage_row pay_fr_contribution_usages%rowtype;
465:
466: l_proc varchar2(72) := g_package||'.GET_RATE_VALUE';
467:
468: begin
1468: l_contribution_type varchar2(10);
1469: l_base_name pay_user_Column_instances_f.value%type;
1470: l_code_rate_id number;
1471: l_rate_category varchar2(1);
1472: l_element_name pay_fr_contribution_usages.element_name%type;
1473:
1474: l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1475: p_process_type => g_process_type
1476: ,p_element_name => p_element_name
1470: l_code_rate_id number;
1471: l_rate_category varchar2(1);
1472: l_element_name pay_fr_contribution_usages.element_name%type;
1473:
1474: l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1475: p_process_type => g_process_type
1476: ,p_element_name => p_element_name
1477: ,p_usage_type => p_usage_type
1478: ,p_effective_date => p_date_earned
1583: ,p_contribution_usage_id OUT NOCOPY number
1584: ,p_override_rate in number default null)
1585: return number
1586: is
1587: l_contribution_code pay_fr_contribution_usages.contribution_code%TYPE:=null;
1588: begin
1589: return get_contribution_info(p_assignment_id
1590: ,p_business_group_id
1591: ,p_date_earned
1631: l_risk_code varchar2(30);
1632: l_user_column_instance_id number;
1633: l_user_row_id number;
1634: l_rate_category varchar2(1);
1635: l_element_name pay_fr_contribution_usages.element_name%type;
1636:
1637: l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1638: p_process_type => g_process_type
1639: ,p_element_name => p_element_name
1633: l_user_row_id number;
1634: l_rate_category varchar2(1);
1635: l_element_name pay_fr_contribution_usages.element_name%type;
1636:
1637: l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1638: p_process_type => g_process_type
1639: ,p_element_name => p_element_name
1640: ,p_usage_type => p_usage_type
1641: ,p_effective_date => p_date_earned);
1761: l_rate_category varchar2(1);
1762: l_element_name pay_element_types_f.element_name%type;
1763: l_transport_tax_region varchar2(80);
1764:
1765: l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1766: p_process_type => g_process_type
1767: ,p_element_name => p_element_name
1768: ,p_usage_type => p_usage_type
1769: ,p_effective_date => p_date_earned);
1873: l_contribution_usage_id number;
1874: l_contribution_code varchar2(30);
1875: l_contribution_type varchar2(10);
1876:
1877: l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1878: p_business_group_id => p_business_group_id
1879: ,p_process_type => g_process_type
1880: ,p_element_name => p_element_name
1881: ,p_usage_type => p_usage_type
1932: l_contribution_usage_id number;
1933: l_contribution_type varchar2(10);
1934: l_base_name varchar2(30);
1935:
1936: l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1937: p_process_type => g_process_type
1938: ,p_element_name => p_element_name
1939: ,p_usage_type => p_usage_type
1940: ,p_effective_date => p_date_earned);
2001: ,P_RATE out nocopy number
2002: ,P_CONTRIBUTION_USAGE_ID out nocopy number)
2003: return number
2004: is
2005: l_contribution_code pay_fr_contribution_usages.contribution_code%TYPE:=null;
2006: begin
2007: return get_reduced_contribution(p_business_group_id
2008: ,p_date_earned
2009: ,p_element_name
2655: -- matching on base_type, base and contribution code
2656: -- if it does exist add the pay_value, rate and contribution_usage_id to that row
2657: -- contribution_usage_id to add in the 2 to the power code_rate_id
2658: -- if it doesn't exist then add as a new row.
2659: -- special handling for Work Accident, Travel Tax rate identinfied by pay_fr_contribution_usages.rate_category
2660: -- 'W', 'A' the contribution_usage_id for
2661: -- work accident is set from the user_column_instance_id of udt -
2662: -- currently
2663: l_proc varchar2(72) := g_package||'.maintain_summary_deduction';
2664: l_idx NUMBER := 0;
2665: l_action varchar2(1) := 'I';
2666:
2667: begin
2668: -- the contribution_usage_id parameter is pay_fr_contribution_usages.contribution_usage_id
2669: -- fetch the rate_category and rate_code_id from pay_fr_contribution_usages
2670:
2671: -- loop through the table to see if the row already exists
2672: -- for the matching row add in the pay_value, rate and the exponent value
2665: l_action varchar2(1) := 'I';
2666:
2667: begin
2668: -- the contribution_usage_id parameter is pay_fr_contribution_usages.contribution_usage_id
2669: -- fetch the rate_category and rate_code_id from pay_fr_contribution_usages
2670:
2671: -- loop through the table to see if the row already exists
2672: -- for the matching row add in the pay_value, rate and the exponent value
2673: -- error condition ? analysis talks about trapping duplicate deduction
2682: if p_rate <> 0 and p_base <> 0 and p_contribution_code is not null then -- { record summary
2683:
2684: if p_rate_category = 'C' then
2685: -- 115.48 This means the contribution code is not held on
2686: -- pay_fr_contribution_usages. Insert into the table with
2687: -- contribution_usage_id set to -1*contribution_usage_id
2688: -- to allow the pay_fr_contribution_usages row to be identified later.
2689: hr_utility.set_location(l_proc, 20);
2690: l_idx := nvl(g_summary_deductions.last,0) + 1;
2684: if p_rate_category = 'C' then
2685: -- 115.48 This means the contribution code is not held on
2686: -- pay_fr_contribution_usages. Insert into the table with
2687: -- contribution_usage_id set to -1*contribution_usage_id
2688: -- to allow the pay_fr_contribution_usages row to be identified later.
2689: hr_utility.set_location(l_proc, 20);
2690: l_idx := nvl(g_summary_deductions.last,0) + 1;
2691: g_summary_deductions(l_idx).base_type := p_base_type;
2692: g_summary_deductions(l_idx).base := p_base;
2821: l_contribution_code VARCHAR2(30);
2822:
2823: begin
2824: -- the contribution_usage_id parameter is
2825: -- pay_fr_contribution_usages.contribution_usage_id
2826:
2827: hr_utility.set_location('Entering:'|| l_proc, 10);
2828: -- 115.46 Rate cache is now cleared on change of (grand) parent action
2829: -- within initialize_payroll()