[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_PAYKRYTS_PKG
Source
1 package body pay_kr_paykryts_pkg as
2 /* $Header: paykryts.pkb 115.5 2003/08/26 02:32:58 viagarwa ship $ */
3 --
4 -- Global Variables
5 --
6 g_taxable_id number;
7 g_non_taxable_id number;
8 g_med_exp_tax_exem_id number;
9 g_donation_tax_exem_id number;
10 g_annual_itax_id number;
11 g_prev_itax_id number;
12 g_cur_itax_id number;
13 g_itax_adj_id number;
14 ------------------------------------------------------------------------
15 procedure data(
16 p_tax_unit_id in number,
17 p_target_year in number,
18 p_count out nocopy number,
19 p_earnings out nocopy number,
20 p_non_taxable_count out nocopy number,
21 p_non_taxable out nocopy number,
22 p_med_exp_tax_exem_count out nocopy number,
23 p_med_exp_tax_exem out nocopy number,
24 p_donation_tax_exem_count out nocopy number,
25 p_donation_tax_exem out nocopy number,
26 p_annual_itax out nocopy number,
27 p_prev_itax out nocopy number,
28 p_cur_itax out nocopy number,
29 p_itax_adj_pay out nocopy number,
30 p_itax_adj_refund out nocopy number,
31 p_itax_adj out nocopy number)
32 ------------------------------------------------------------------------
33 is
34 cursor csr is
35 select
36 count(paa.assignment_action_id) COUNT,
37 nvl(sum(to_number(i1.value) + to_number(i2.value)), 0) EARNINGS,
38 count(decode(to_number(i2.value), 0, null, 1)) NON_TAXABLE_COUNT,
39 nvl(sum(to_number(i2.value)), 0) NON_TAXABLE,
40 count(decode(to_number(i3.value), 0, null, 1)) MED_EXP_TAX_EXEM_COUNT,
41 nvl(sum(to_number(i3.value)), 0) MED_EXP_TAX_EXEM,
42 count(decode(to_number(i4.value), 0, null, 1)) DONATION_TAX_EXEM_COUNT,
43 nvl(sum(to_number(i4.value)), 0) DONATION_TAX_EXEM,
44 nvl(sum(to_number(i5.value)), 0) ANNUAL_ITAX,
45 nvl(sum(to_number(i6.value)), 0) PREV_ITAX,
46 nvl(sum(to_number(i7.value)), 0) CUR_ITAX,
47 nvl(sum(decode(sign(to_number(i8.value)), 1, to_number(i8.value),null)), 0) ITAX_ADJ_PAY,
48 abs(nvl(sum(decode(sign(to_number(i8.value)), -1, to_number(i8.value),null)), 0)) ITAX_ADJ_REFUND
49 from ff_archive_items i8, /* X_YEA_ITAX_ADJ */
50 ff_archive_items i7, /* X_YEA_CUR_ITAX */
51 ff_archive_items i6, /* X_YEA_PREV_ITAX */
52 ff_archive_items i5, /* X_YEA_ANNUAL_ITAX */
53 ff_archive_items i4, /* X_YEA_DONATION_TAX_EXEM */
54 ff_archive_items i3, /* X_YEA_MED_EXP_TAX_EXEM */
55 ff_archive_items i2, /* X_YEA_NON_TAXABLE */
56 ff_archive_items i1, /* X_YEA_TAXABLE */
57 pay_assignment_actions paa,
58 pay_payroll_actions ppa,
59 hr_organization_units hou
60 where hou.organization_id = p_tax_unit_id
61 and ppa.report_type = 'YEA'
62 and ppa.report_qualifier = 'KR'
63 and ppa.report_category in ('N', 'I')
64 and ppa.business_group_id + 0 = hou.business_group_id
65 and to_number(to_char(ppa.effective_date, 'YYYY')) = p_target_year
66 and ppa.action_type in ('B','X')
67 and paa.payroll_action_id = ppa.payroll_action_id
68 and paa.tax_unit_id = hou.organization_id
69 and paa.action_status = 'C'
70 and i1.context1 = paa.assignment_action_id
71 and i1.user_entity_id = nvl(g_taxable_id, paa.assignment_action_id)
72 and i2.context1 = paa.assignment_action_id
73 and i2.user_entity_id = nvl(g_non_taxable_id, paa.assignment_action_id)
74 and i3.context1 = paa.assignment_action_id
75 and i3.user_entity_id = nvl(g_med_exp_tax_exem_id, paa.assignment_action_id)
76 and i4.context1 = paa.assignment_action_id
77 and i4.user_entity_id = nvl(g_donation_tax_exem_id, paa.assignment_action_id)
78 and i5.context1 = paa.assignment_action_id
79 and i5.user_entity_id = nvl(g_annual_itax_id, paa.assignment_action_id)
80 and i6.context1(+) = paa.assignment_action_id
81 and i6.user_entity_id(+) = nvl(g_prev_itax_id, paa.assignment_action_id)
82 and i7.context1 = paa.assignment_action_id
83 and i7.user_entity_id = nvl(g_cur_itax_id, paa.assignment_action_id)
84 and i8.context1 = paa.assignment_action_id
85 and i8.user_entity_id = nvl(g_itax_adj_id, paa.assignment_action_id);
86 begin
87 open csr;
88 fetch csr into
89 p_count,
90 p_earnings,
91 p_non_taxable_count,
92 p_non_taxable,
93 p_med_exp_tax_exem_count,
94 p_med_exp_tax_exem,
95 p_donation_tax_exem_count,
96 p_donation_tax_exem,
97 p_annual_itax,
98 p_prev_itax,
99 p_cur_itax,
100 p_itax_adj_pay,
101 p_itax_adj_refund;
102 close csr;
103 --
104 p_itax_adj := p_itax_adj_pay - p_itax_adj_refund;
105 end data;
106 ------------------------------------------------------------------------
107 function user_entity_id(p_user_entity_name in varchar2) return number
108 ------------------------------------------------------------------------
109 is
110 l_user_entity_id number;
111 begin
112 select user_entity_id
113 into l_user_entity_id
114 from ff_user_entities
115 where user_entity_name = p_user_entity_name
116 and legislation_code = 'KR'
117 and creator_type = 'X';
118 --
119 return l_user_entity_id;
120 end user_entity_id;
121 ------------------------------------------------------------------------
122 ------------------------------------------------------------------------
123 begin
124 --
125 -- Derive user_entity_id for YEA archive items.
126 --
127 g_taxable_id := user_entity_id('X_YEA_TAXABLE');
128 g_non_taxable_id := user_entity_id('X_YEA_NON_TAXABLE');
129 g_med_exp_tax_exem_id := user_entity_id('X_YEA_MED_EXP_TAX_EXEM');
130 g_donation_tax_exem_id := user_entity_id('X_YEA_DONATION_TAX_EXEM');
131 g_annual_itax_id := user_entity_id('X_YEA_ANNUAL_ITAX');
132 g_prev_itax_id := user_entity_id('X_YEA_PREV_ITAX');
133 g_cur_itax_id := user_entity_id('X_YEA_CUR_ITAX');
134 g_itax_adj_id := user_entity_id('X_YEA_ITAX_ADJ');
135 end pay_kr_paykryts_pkg;