[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_NONSTAT_SPAY_EFILE
Source
1 package body pay_kr_nonstat_spay_efile as
2 /*$Header: pykrnspef.pkb 120.8.12020000.2 2013/03/06 10:45:48 scireddy ship $ */
3
4 /*************************************************************************
5 * Procedure to submit e-file request indirectly
6 *************************************************************************/
7
8 procedure submit_efile (errbuf out nocopy varchar2,
9 retcode out nocopy varchar2,
10 p_effective_date in varchar2,
11 p_business_place in varchar2,
12 p_report_for in varchar2, --5069923
13 p_magnetic_file_name in varchar2,
14 p_report_file_name in varchar2,
15 p_target_year in varchar2,
16 p_payroll_action_id in varchar2, --5069923
17 p_assignment_set_id in varchar2, --5069923
18 p_reported_date in varchar2,
19 p_reporting_period in varchar2,
20 p_characterset in varchar2, --5069923
21 p_business_group_id_hd in varchar2,
22 p_tax_reporting_org in varchar2, --4095229
23 p_tax_reporter in varchar2,
24 p_cont_phone_no in varchar2,
25 p_home_tax_id in varchar2,
26 p_org_struc_version_id in varchar2 --5069923
27
28 )
29 is
30
31 l_req_id number;
32 l_message varchar2(2000);
33 l_phase varchar2(100);
34 l_status varchar2(100);
35 l_action_completed boolean;
36
37 begin
38
39 l_req_id := fnd_request.submit_request (
40 APPLICATION => 'PAY'
41 ,PROGRAM => 'PAYKRSEF_NS_B'
42 ,DESCRIPTION => 'KR Separation Pay E-File (MAGTAPE)'
43 ,ARGUMENT1 => 'pay_magtape_generic.new_formula'
44 ,ARGUMENT2 => p_magnetic_file_name
45 ,ARGUMENT3 => p_report_file_name
46 ,ARGUMENT4 => p_effective_date
47 ,ARGUMENT5 => 'MAGTAPE_REPORT_ID=KR_NS_SPAY_EFILE'
48 ,ARGUMENT6 => 'PRIMARY_BP_ID=' || p_business_place
49 ,ARGUMENT7 => 'TARGET_YEAR=' || p_target_year
50 ,ARGUMENT8 => 'REPORTED_DATE=' || p_reported_date
51 ,ARGUMENT9 => 'BUSINESS_GROUP_ID=' || p_business_group_id_hd
52 ,ARGUMENT10 => 'HOME_TAX_ID=' || nvl(p_home_tax_id, ' ')
53 ,ARGUMENT11 => 'TAX_REPORTING_ORG=' || p_tax_reporting_org
54 ,ARGUMENT12 => 'TAX_REPORTER=' || p_tax_reporter
55 ,ARGUMENT13 => 'CONT_PHONE_NO=' || p_cont_phone_no
56 ,ARGUMENT14 => 'REPORT_FOR=' || p_report_for
57 ,ARGUMENT15 => 'PAYROLL_ACTION_ID=' || p_payroll_action_id
58 ,ARGUMENT16 => 'ASSIGNMENT_SET_ID=' || p_assignment_set_id
59 ,ARGUMENT17 => 'CHARACTERSET=' || p_characterset
60 ,ARGUMENT18 => 'ORG_STRUC_VERSION_ID=' || p_org_struc_version_id
61 ,ARGUMENT19 => 'REPORTING_PERIOD=' || p_reporting_period
62 );
63
64 if (l_req_id = 0) then
65 retcode := 2;
66 fnd_message.retrieve(errbuf);
67 else
68 commit;
69 end if;
70 end submit_efile;
71
72 /*************************************************************************
73 * Function that count the BP's under the Business Group.
74 * This function needs TARGET_YEAR coming from SRS
75 *************************************************************************/
76 function get_bp_count( primary_business_place_id IN Number) Return Number
77 is
78 l_bp_count Number;
79
80 cursor c_business_place_count
81 is
82 select count(distinct hoi.org_information2||ihoi.org_information9) bp_count
83 from hr_organization_information hoi
84 ,hr_organization_units hou
85 ,hr_organization_units phou
86 ,hr_organization_information phoi
87 ,hr_organization_information ihoi
88 where hou.organization_id = hoi.organization_id
89 and hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
90 and hoi.org_information10 = phoi.org_information10
91 and phou.organization_id = primary_business_place_id
92 and phou.organization_id = phoi.organization_id
93 --Bug 5069923
94 and ( (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
95 or ( (hoi.organization_id in (select posev.ORGANIZATION_ID_child
96 from PER_ORG_STRUCTURE_ELEMENTS posev
97 where posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
98 and exists ( select null
99 from hr_organization_information
100 where organization_id = posev.ORGANIZATION_ID_child
101 and org_information_context = 'CLASS'
102 and org_information1 = 'KR_BUSINESS_PLACE'
103 )
104 start with ORGANIZATION_ID_PARENT = (decode(pay_magtape_generic.get_parameter_value('REPORT_FOR'),'S',null,'SUB',primary_business_place_id))
105 connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
106 )
107 )
108 or (hoi.organization_id = primary_business_place_id
109 )
110 )
111 )
112 and phoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
113 and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE'
114 and ihoi.organization_id = hou.organization_id
115 and exists( select 'x'
116 from ff_user_entities fue
117 ,ff_archive_items fai
118 ,pay_assignment_actions xpaa
119 ,pay_payroll_actions xppa
120 where xppa.action_type = 'X'
121 and xppa.action_status = 'C'
122 --Bug 5069923
123 and xppa.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
124 and xppa.effective_date between
125 to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'0101','YYYYMMDD')
126 and to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'1231','YYYYMMDD')
127 and xppa.payroll_action_id = xpaa.payroll_action_id
128 and xpaa.action_status = 'C'
129 --Bug 5069923
130 and ( (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
131 or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
132 )
133 and xppa.report_type = 'KR_SEP'
134 --Bug 5069923
135 and xppa.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
136 and xpaa.tax_unit_id = hou.organization_id
137 and fue.user_entity_name in ('A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN','A_RECEIVABLE_NON_STAT_SEP_PAY_ASG_RUN','A_RECEIVABLE_SEPARATION_PAY_ASG_RUN',
138 'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN' ) --Bug 9409509
139 and fue.legislation_code = 'KR'
140 and fai.user_entity_id = fue.user_entity_id
141 and fai.context1 = xpaa.assignment_action_id
142 and fai.value > '0'
143
144
145 )
146 group by hoi.org_information10;
147 begin
148 open c_business_place_count;
149 fetch c_business_place_count into l_bp_count;
150 close c_business_place_count;
151
152 return nvl(l_bp_count, 0);
153 end get_bp_count;
154
155 function get_record_d_context(p_assignment_action_id number) return number
156 is
157 l_element_entry_id number;
158 begin
159 select
160 fac.context into l_element_entry_id
161 from
162 ff_contexts fc,
163 ff_user_entities fue,
164 ff_route_context_usages frc,
165 ff_archive_item_contexts fac,
166 ff_archive_items fai
167 where
168 fue.user_entity_name = 'X_KR_PREV_BP_NUMBER'
169 and fue.legislation_code = 'KR'
170 and fue.route_id = frc.route_id
171 and frc.context_id = fc.context_id
172 and fc.context_name = 'ELEMENT_ENTRY_ID'
173 and fai.context1 = p_assignment_action_id
174 and fai.user_entity_id = fue.user_entity_id
175 and fac.archive_item_id = fai.archive_item_id
176 and fac.context_id = fc.context_id
177 order by
178 fai.value, fac.context ;
179 IF l_element_entry_id is NULL THEN
180 l_element_entry_id := 1;
181 END IF;
182 return l_element_entry_id;
183 end;
184
185 begin
186
187 g_bp_count := get_bp_count(pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID'));
188
189 end pay_kr_nonstat_spay_efile;