[Home] [Help]
PACKAGE: APPS.PAY_KR_YEA_DON_EFILE_PKG
Source
1 package pay_kr_yea_don_efile_pkg as
2 /* $Header: pykrydef.pkh 120.2.12010000.2 2008/08/06 07:41:54 ubhat ship $ */
3 --
4 level_cnt number;
5 g_business_place_id number;
6 g_target_year number;
7 g_b_records number;
8 g_normal_yea varchar2(1) := 'X';
9 g_interim_yea varchar2(1) := 'X';
10 g_re_yea varchar2(1) := 'X';
11 g_payroll_action_id number;
12 g_assignment_set_id number;
13 g_donation_exem_archive number;
14 ------------------------------------------------------------------------
15 cursor csr_header is
16 select
17 'TAX_OFFICE_CODE=P', ihoi.org_information9,
18 'PRIMARY_BP_NUMBER=P', bhoi_primary.org_information2,
19 'BR_NUMBER=P', bhoi.org_information2,
20 'NATIONAL_IDENTIFIER=P', pp.national_identifier,
21 'FULL_NAME=P', pp.full_name,
22 'CORP_NAME=P', choi.org_information1,
23 'ASSIGN_ID=P', paa.assignment_id,
24 'NATIONALITY=P', decode(pay_kr_ff_functions_pkg.ni_nationality(pp.national_identifier),
25 'K', '1',
26 '9'
27 ),
28 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id /* Bug 6764369 */
29 from
30 per_people_f pp,
31 per_assignments_f pa,
32 pay_assignment_actions paa,
33 pay_payroll_actions ppa,
34 hr_organization_units hou1,
35 hr_organization_information bhoi_primary,
36 hr_organization_information bhoi,
37 hr_organization_information choi,
38 hr_organization_information ihoi,
39 ff_archive_items ar
40 where
41 bhoi_primary.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
42 and bhoi_primary.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
43 and choi.organization_id = to_number(bhoi_primary.org_information10)
44 and choi.org_information_context = 'KR_CORPORATE_INFORMATION'
45 and ihoi.organization_id = bhoi_primary.organization_id
46 and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE'
47 and hou1.business_group_id = pay_magtape_generic.get_parameter_value('BG_ID')
48 and bhoi.organization_id = hou1.organization_id
49 --Bug 5069923
50 and ( (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
51 or ( (bhoi.organization_id in (select posev.ORGANIZATION_ID_child
52 from PER_ORG_STRUCTURE_ELEMENTS posev
53 where posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
54 and exists ( select null
55 from hr_organization_information
56 where organization_id = posev.ORGANIZATION_ID_child
57 and org_information_context = 'CLASS'
58 and org_information1 = 'KR_BUSINESS_PLACE'
59 )
60 start with posev.ORGANIZATION_ID_PARENT = (decode(pay_magtape_generic.get_parameter_value('REPORT_FOR'),'S',null,'SUB',pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')))
61 connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
62 )
63 )
64 or (bhoi.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
65 )
66 )
67 )
68 and bhoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
69 and choi.organization_id = to_number(bhoi.org_information10)
70 and ppa.business_group_id = pay_magtape_generic.get_parameter_value('BG_ID')
71 and ppa.report_type = 'YEA'
72 and ppa.report_qualifier = 'KR'
73 and (
74 (ppa.report_category in (g_normal_yea, g_interim_yea, g_re_yea))
75 or (ppa.payroll_action_id = g_payroll_action_id)
76 )
77 and to_number(to_char(ppa.effective_date, 'YYYY'))
78 = g_target_year
79 --
80 and ppa.action_type = 'X'
81 and paa.payroll_action_id = ppa.payroll_action_id
82 and (
83 (to_number(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID')) is null)
84 or
85 (hr_assignment_set.assignment_in_set(
86 to_number(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID')), paa.assignment_id)
87 = 'Y')
88 )
89 and (
90 (g_re_yea <> 'R')
91 or
92 (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, g_payroll_action_id, g_target_year) = 'Y')
93 )
94 and paa.tax_unit_id = bhoi.organization_id
95 and paa.action_status = 'C'
96 and pa.assignment_id = paa.assignment_id
97 and ppa.effective_date between pa.effective_start_date and pa.effective_end_date
98 and pp.person_id = pa.person_id
99 and ppa.effective_date between pp.effective_start_date and pp.effective_end_date
100 and ar.context1 = paa.assignment_action_id
101 and ar.user_entity_id = g_donation_exem_archive
102 and ar.value >= 2000000
103 order by
104 bhoi.org_information2 ;
105 ------------------------------------------------------------------------
106
107 cursor csr_donation is
108 select
109 'DON_RECIPIENT_NO=P', don_recipient_no,
110 'DON_RECIPIENT_NAME=P', don_recipient_name ,
111 'NO_OF_DONATIONS=P', no_of_donations ,
112 'DONATION_AMOUNT=P', donation_amount ,
113 'DONATION_CODE=P', donation_code ,
114 'RECEIPT_NUMBER=P', receipt_number /* Bug 6764369 */
115 from
116 (
117 select
118 pae.assignment_id assignment_id,
119 nvl(aei_information7, ' ') don_recipient_no ,
120 nvl(aei_information8, ' ') don_recipient_name,
121 sum(nvl(aei_information4, 1)) no_of_donations,
122 sum(aei_information3) donation_amount,
123 aei_information5 donation_code,
124 nvl(aei_information11, ' ') receipt_number /* Bug 6764369 */
125 from
126 per_assignment_extra_info pae
127 where
128 pae.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGN_ID')
129 and pae.information_type = 'KR_YEA_DETAIL_DONATION_INFO'
130 and to_char(fnd_date.canonical_to_date(pae.aei_information1),'yyyy')
131 = g_target_year
132 group by
133 pae.assignment_id,
134 aei_information7,
135 aei_information8,
136 aei_information5,
137 aei_information11 /* Bug 6764369 */
138 ) ;
139
140
141 ------------------------------------------------------------------------
142 end pay_kr_yea_don_efile_pkg;