[Home] [Help]
PACKAGE: APPS.PAY_KR_YEA_MED_EFILE_PKG
Source
1 package pay_kr_yea_med_efile_pkg as
2 /* $Header: pykrymef.pkh 120.6 2006/12/14 07:50:03 pdesu noship $ */
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_medical_exp_archive number;
14 ------------------------------------------------------------------------
15 cursor csr_a 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 'VALIDATION=P', pay_kr_yea_med_efile_conc_pkg.validate_det_medical_rec(
25 paa.assignment_id,
26 to_date(to_char(g_target_year),'yyyy') ),
27 'EMP_NATIONALITY=P', decode(pay_kr_ff_functions_pkg.ni_nationality(pp.national_identifier),'F','9'
28 ,'1')
29 from
30 per_people_f pp,
31 per_periods_of_service pds,
32 per_assignments_f pa,
33 pay_assignment_actions paa,
34 pay_payroll_actions ppa,
35 hr_organization_units hou1,
36 hr_organization_information bhoi_primary,
37 hr_organization_information bhoi,
38 hr_organization_information choi,
39 hr_organization_information ihoi,
40 ff_archive_items ar
41 where
42 bhoi_primary.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
43 and bhoi_primary.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
44 and choi.organization_id = to_number(bhoi_primary.org_information10)
45 and choi.org_information_context = 'KR_CORPORATE_INFORMATION'
46 and ihoi.organization_id = bhoi_primary.organization_id
47 and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE'
48 and hou1.business_group_id = pay_magtape_generic.get_parameter_value('BG_ID')
49 and bhoi.organization_id = hou1.organization_id
50 --Bug 5069923
51 and ( (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
52 or ( (bhoi.organization_id in ( select posev.ORGANIZATION_ID_child
53 from PER_ORG_STRUCTURE_ELEMENTS posev
54 where posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
55 and exists ( select null
56 from hr_organization_information
57 where organization_id = posev.ORGANIZATION_ID_child
58 and org_information_context = 'CLASS'
59 and org_information1 = 'KR_BUSINESS_PLACE'
60 )
61 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')))
62 connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
63 )
64 )
65 or (bhoi.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
66 )
67 )
68 )
69 and bhoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
70 and choi.organization_id = to_number(bhoi.org_information10)
71 and ppa.business_group_id = pay_magtape_generic.get_parameter_value('BG_ID')
72 and ppa.report_type = 'YEA'
73 and ppa.report_qualifier = 'KR'
74 and ( (ppa.report_category in (g_normal_yea, g_interim_yea, g_re_yea)) or
75 (ppa.payroll_action_id = g_payroll_action_id) )
76 and to_number(to_char(ppa.effective_date, 'YYYY')) = g_target_year
77 --
78 and ppa.action_type in ('B','X')
79 and paa.payroll_action_id = ppa.payroll_action_id
80 and ((to_number(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID')) is null) or
81 (hr_assignment_set.assignment_in_set(to_number(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID')),
82 paa.assignment_id) = 'Y'))
83 and (('X' <> 'R') or
84 (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, g_payroll_action_id, g_target_year) = 'Y')) -- Bug 4726974
85 and paa.tax_unit_id = bhoi.organization_id
86 and paa.action_status = 'C'
87 and pa.assignment_id = paa.assignment_id
88 and ppa.effective_date between pa.effective_start_date and pa.effective_end_date
89 and pds.period_of_service_id = pa.period_of_service_id
90 and pp.person_id = pds.person_id
91 and ppa.effective_date between pp.effective_start_date and pp.effective_end_date
92 and ar.context1(+) = paa.assignment_action_id
93 and ar.user_entity_id(+) = g_medical_exp_archive
94 and ar.value >= 2000000
95 order by bhoi.org_information2;
96 ------------------------------------------------------------------------
97
98 cursor csr_b is
99 select
100 'MED_REG_NO=P', pay_kr_yea_med_efile_conc_pkg.get_medical_reg_no(
101 assignment_id,
102 to_date(to_char(g_target_year),'yyyy'),
103 med_reg_no),
104 'MED_NAME=P', med_name ,
105 'CARD_MED_NO_OF_PAYMENTS=P', card_med_no_of_payments ,
106 'CARD_EXPENSE_AMOUNT=P', card_expense_amount ,
107 'CASH_MED_NO_OF_PAYMENTS=P', cash_med_no_of_payments ,
108 'CASH_EXPENSE_AMOUNT=P', cash_expense_amount ,
109 'RELATIONSHIP=P', relarionship ,
110 'RESIDENT_REG_NO=P', pay_kr_yea_med_efile_conc_pkg.get_resident_reg_no(
111 assignment_id,
112 to_date(to_char(g_target_year),'yyyy'),
113 resident_reg_no),
114 'DISABLED_OR_AGED=P', disabled_or_aged,
115 'DEPNT_NATIONALITY=P', decode(pay_kr_ff_functions_pkg.ni_nationality(resident_reg_no),'F','9','1')
116 from
117 (
118 select
119 pae.assignment_id assignment_id ,
120 aei_information5 med_reg_no ,
121 aei_information6 med_name,
122 sum(decode(aei_information12,
123 null,decode(aei_information11,null,0,0,
124 0,1),aei_information12)) card_med_no_of_payments,
125 sum(nvl(aei_information11,0)) card_expense_amount,
126 sum(decode(aei_information10,
127 null,decode(aei_information3,null,0,0,
128 0,1),aei_information10)) cash_med_no_of_payments,
129 sum(nvl(aei_information3,0)) cash_expense_amount,
130 aei_information7 relarionship,
131 aei_information8 resident_reg_no,
132 aei_information9 disabled_or_aged
133 from PER_ASSIGNMENT_EXTRA_INFO pae
134 where
135 pae.assignment_id =pay_magtape_generic.get_parameter_value('ASSIGN_ID')
136 and pae.information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
137 and to_char(fnd_date.canonical_to_date(pae.aei_information1),'yyyy') = g_target_year
138 group by pae.assignment_id, aei_information8,aei_information5,aei_information6,
139 aei_information7,aei_information8 ,aei_information9
140 );
141
142
143 ------------------------------------------------------------------------
144 end pay_kr_yea_med_efile_pkg;