[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.3 2006/11/07 11:49:38 vaisriva noship $ */
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_characterset in varchar2, --5069923
20 p_business_group_id_hd in varchar2,
21 p_tax_reporting_org in varchar2, --4095229
22 p_tax_reporter in varchar2,
23 p_cont_phone_no in varchar2,
24 p_home_tax_id in varchar2,
25 p_org_struc_version_id in varchar2 --5069923
26 )
27 is
28
29 l_req_id number;
30 l_message varchar2(2000);
31 l_phase varchar2(100);
32 l_status varchar2(100);
33 l_action_completed boolean;
34
35 begin
36
37 l_req_id := fnd_request.submit_request (
38 APPLICATION => 'PAY'
39 ,PROGRAM => 'PAYKRSEF_NS_B'
40 ,DESCRIPTION => 'KR Non-Statutory Separation Pay E-File (MAGTAPE)'
41 ,ARGUMENT1 => 'pay_magtape_generic.new_formula'
42 ,ARGUMENT2 => p_magnetic_file_name
43 ,ARGUMENT3 => p_report_file_name
44 ,ARGUMENT4 => p_effective_date
45 ,ARGUMENT5 => 'MAGTAPE_REPORT_ID=KR_NS_SPAY_EFILE'
46 ,ARGUMENT6 => 'PRIMARY_BP_ID=' || p_business_place
47 ,ARGUMENT7 => 'TARGET_YEAR=' || p_target_year
48 ,ARGUMENT8 => 'REPORTED_DATE=' || p_reported_date
49 ,ARGUMENT9 => 'BUSINESS_GROUP_ID=' || p_business_group_id_hd
50 ,ARGUMENT10 => 'HOME_TAX_ID=' || nvl(p_home_tax_id, ' ')
51 ,ARGUMENT11 => 'TAX_REPORTING_ORG=' || p_tax_reporting_org
52 ,ARGUMENT12 => 'TAX_REPORTER=' || p_tax_reporter
53 ,ARGUMENT13 => 'CONT_PHONE_NO=' || p_cont_phone_no
54 ,ARGUMENT14 => 'REPORT_FOR=' || p_report_for
55 ,ARGUMENT15 => 'PAYROLL_ACTION_ID=' || p_payroll_action_id
56 ,ARGUMENT16 => 'ASSIGNMENT_SET_ID=' || p_assignment_set_id
57 ,ARGUMENT17 => 'CHARACTERSET=' || p_characterset
58 ,ARGUMENT18 => 'ORG_STRUC_VERSION_ID=' || p_org_struc_version_id
59 );
60
61 if (l_req_id = 0) then
62 retcode := 2;
63 fnd_message.retrieve(errbuf);
64 else
65 commit;
66 end if;
67 end submit_efile;
68
69 /*************************************************************************
70 * Function that count the BP's under the Business Group.
71 * This function needs TARGET_YEAR coming from SRS
72 *************************************************************************/
73 function get_bp_count( primary_business_place_id IN Number) Return Number
74 is
75 l_bp_count Number;
76
77 cursor c_business_place_count
78 is
79 select count(distinct hoi.org_information2||ihoi.org_information9) bp_count
80 from hr_organization_information hoi
81 ,hr_organization_units hou
82 ,hr_organization_units phou
83 ,hr_organization_information phoi
84 ,hr_organization_information ihoi
85 where hou.organization_id = hoi.organization_id
86 and hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
87 and hoi.org_information10 = phoi.org_information10
88 and phou.organization_id = primary_business_place_id
89 and phou.organization_id = phoi.organization_id
90 --Bug 5069923
91 and ( (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
92 or ( (hoi.organization_id in (select posev.ORGANIZATION_ID_child
93 from PER_ORG_STRUCTURE_ELEMENTS posev
94 where posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
95 and exists ( select null
96 from hr_organization_information
97 where organization_id = posev.ORGANIZATION_ID_child
98 and org_information_context = 'CLASS'
99 and org_information1 = 'KR_BUSINESS_PLACE'
100 )
101 start with ORGANIZATION_ID_PARENT = (decode(pay_magtape_generic.get_parameter_value('REPORT_FOR'),'S',null,'SUB',primary_business_place_id))
102 connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
103 )
104 )
105 or (hoi.organization_id = primary_business_place_id
106 )
107 )
108 )
109 and phoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
110 and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE'
111 and ihoi.organization_id = hou.organization_id
112 and exists( select 'x'
113 from ff_user_entities fue
114 ,ff_archive_items fai
115 ,pay_assignment_actions xpaa
116 ,pay_payroll_actions xppa
117 where xppa.action_type = 'X'
118 and xppa.action_status = 'C'
119 --Bug 5069923
120 and xppa.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
121 and xppa.effective_date between
122 to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'0101','YYYYMMDD')
123 and to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'1231','YYYYMMDD')
124 and xppa.payroll_action_id = xpaa.payroll_action_id
125 and xpaa.action_status = 'C'
126 --Bug 5069923
127 and ( (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
128 or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
129 )
130 and xppa.report_type = 'KR_SEP'
131 --Bug 5069923
132 and xppa.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
133 and xpaa.tax_unit_id = hou.organization_id
134 and fue.user_entity_name = 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN'
135 and fue.legislation_code = 'KR'
136 and fai.user_entity_id = fue.user_entity_id
137 and fai.context1 = xpaa.assignment_action_id
138 and fai.value > '0'
139 )
140 group by hoi.org_information10;
141 begin
142 open c_business_place_count;
143 fetch c_business_place_count into l_bp_count;
144 close c_business_place_count;
145
146 return nvl(l_bp_count, 0);
147 end get_bp_count;
148
149 begin
150
151 g_bp_count := get_bp_count(pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID'));
152
153 end pay_kr_nonstat_spay_efile;