[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA_MAGTAPE_PKG
Source
1 package body pay_kr_yea_magtape_pkg as
2 /* $Header: pykryeam.pkb 120.2.12010000.1 2008/07/27 23:06:42 appldev ship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := ' pay_kr_yea_magtape_pkg.';
7 --
8 --Bug 5069923
9 report_for_var varchar2(50);
10
11 procedure populate_a
12 ------------------------------------------------------------------------
13 is
14 l_proc varchar2(61) := c_package || 'populate_a';
15 --
16 cursor csr_a is
17 select count(distinct hoi2.org_information2||hoi3.org_information9) --Bug# 2822459 count(*)
18 from hr_organization_information hoi2,
19 hr_organization_information hoi3,
20 hr_organization_units bp2,
21 hr_organization_information hoi1,
22 hr_organization_units bp1
23 where bp1.organization_id = g_business_place_id --Bug 5069923
24 and hoi1.organization_id = bp1.organization_id
25 and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
26 --Bug 5069923
27 and ( (report_for_var='A')
28 or ( (hoi2.organization_id in (select posev.ORGANIZATION_ID_child
29 from PER_ORG_STRUCTURE_ELEMENTS posev
30 where posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
31 and exists (select null
32 from hr_organization_information
33 where organization_id = posev.ORGANIZATION_ID_child
34 and org_information_context = 'CLASS'
35 and org_information1 = 'KR_BUSINESS_PLACE'
36 )
37 start with ORGANIZATION_ID_PARENT = (decode(report_for_var,'S',null,'SUB',g_business_place_id))
38 connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
39 )
40 )
41 or (hoi2.organization_id = g_business_place_id
42 )
43 )
44 )
45 and bp2.business_group_id = bp1.business_group_id
46 and hoi2.organization_id = bp2.organization_id
47 and hoi2.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
48 and hoi2.org_information10 = hoi1.org_information10
49 and hoi3.organization_id = hoi2.organization_id
50 and hoi3.org_information_context = 'KR_INCOME_TAX_OFFICE'
51 and exists(
52 select null
53 from pay_assignment_actions paa,
54 pay_payroll_actions ppa
55 where ppa.report_type = 'YEA'
56 and ppa.report_qualifier = 'KR'
57 and ppa.business_group_id = bp1.business_group_id
58 -- Bug 3248513
59 and ( (ppa.report_category in (g_normal_yea, g_interim_yea, g_re_yea)) or (ppa.payroll_action_id = g_payroll_action_id ))
60 and to_number(to_char(ppa.effective_date, 'YYYY')) = g_target_year
61 --
62 and ppa.action_type in ('B','X')
63 and paa.payroll_action_id = ppa.payroll_action_id
64 and paa.tax_unit_id = bp2.organization_id
65 and paa.action_status = 'C');
66 begin
67 if g_business_place_id is null then
68 g_business_place_id := to_number(pay_magtape_generic.get_parameter_value('BUSINESS_PLACE_ID'));
69 g_target_year := to_number(pay_magtape_generic.get_parameter_value('TARGET_YEAR'));
70 --Bug 5069923
71 report_for_var := pay_magtape_generic.get_parameter_value('REPORT_FOR');
72 --
73 open csr_a;
74 fetch csr_a into g_b_records;
75 close csr_a;
76 end if;
77 end populate_a;
78
79 ------------------------------------------------------------------------
80 -- Package initialization section
81 ------------------------------------------------------------------------
82 begin
83 declare
84 l_report_type varchar2(3);
85 --------------------------------------------------------
86 function user_entity_id(p_user_entity_name in varchar2) return number
87 --------------------------------------------------------
88 is
89 l_user_entity_id number;
90 begin
91 select user_entity_id
92 into l_user_entity_id
93 from ff_user_entities
94 where user_entity_name = p_user_entity_name
95 and legislation_code = 'KR'
96 and creator_type = 'X';
97 --
98 return l_user_entity_id;
99 end user_entity_id;
100 begin
101 -- Bug 3248513
102 g_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
103 g_assignment_set_id := to_number(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'));
104
105 if g_payroll_action_id is null then
106
107 l_report_type := pay_magtape_generic.get_parameter_value('REPORT_TYPE');
108
109 if l_report_type is null then
110 g_normal_yea := 'N';
111 g_interim_yea := 'I';
112
113 elsif l_report_type = 'N' then
114 g_normal_yea := 'N';
115
116 elsif l_report_type = 'I' then
117 g_interim_yea := 'I';
118
119 elsif l_report_type = 'R' then
120 g_re_yea := 'R';
121
122 elsif l_report_type = 'NI' then
123 g_normal_yea := 'N';
124 g_interim_yea := 'I';
125
126 elsif l_report_type = 'NR' then
127 g_normal_yea := 'N';
128 g_re_yea := 'R';
129
130 end if;
131 end if;
132 --
133 populate_a;
134
135 g_taxable_id := user_entity_id('X_YEA_TAXABLE');
136 g_annual_itax_id := user_entity_id('X_YEA_ANNUAL_ITAX');
137 g_annual_rtax_id := user_entity_id('X_YEA_ANNUAL_RTAX');
138 g_annual_stax_id := user_entity_id('X_YEA_ANNUAL_STAX');
139
140 end;
141 end pay_kr_yea_magtape_pkg;