eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.PAY_KW_PIFSS

Source


1 PACKAGE PAY_KW_PIFSS  as
2 /* $Header: pykwpifs.pkh 120.5 2006/08/23 10:39:20 spendhar noship $ */
3  level_cnt NUMBER;
4  --
5  PROCEDURE range_cursor (pactid IN NUMBER,
6                          sqlstr OUT nocopy VARCHAR2);
7  --
8  procedure assignment_action_code
9       (p_payroll_action_id  in pay_payroll_actions.payroll_action_id%type,
10        p_start_person_id    in per_all_people_f.person_id%type,
11        p_end_person_id      in per_all_people_f.person_id%type,
12        p_chunk              in number);
13  --
14  procedure spawn_archive_reports;
15  --
16  CURSOR CSR_KW_PIFSS_HEADER IS
17  SELECT 'REPORT_TYPE=P'
18 	,pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Report_Type')
19 	,'EMPLOYER_KEY=P'
20 	,pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Employer_Key')
21 	,'FILE_ID=P'
22 	,pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'File_ID')
23 	,'CREATION_DATE=P'
24       ,to_char(ppa.effective_date, 'YYYYMMDD')
25 	,'ACTUAL_DATE=P'
26 ,to_char(trunc(to_date('01'||'-'||pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Month')||'-'||pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Year'),'dd-mm-yyyy'), 'MM'),'YYYYMMDD')
27 	,'TOT_EMPLOYEE=P'
28       ,pay_kw_pifss_report.get_total_count(pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Employer_Id'),
29                                            pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Month'),
30                                            pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Year'),
31 					   pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'LOCAL_NATIONALITY'))
32 	,'CHANGE_EMPLOYEE=P'
33       ,pay_kw_pifss_report.get_change_count(pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Employer_Id'),
34                                            pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Month'),
35                                            pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Year'),
36 					   pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'LOCAL_NATIONALITY'))
37 	,'TERM_EMPLOYEE=P'
38       ,pay_kw_pifss_report.get_term_count(pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Employer_Id'),
39                                            pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Month'),
40                                            pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Year'),
41 				           pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'LOCAL_NATIONALITY'))
42 	,'NEW_EMPLOYEE=P'
43       ,pay_kw_pifss_report.get_new_count(pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Employer_Id'),
44                                            pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Month'),
45                                            pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'Year'),
46     					   pay_kw_pifss_report.get_parameter(ppa.legislative_parameters,'LOCAL_NATIONALITY'))
47       ,'DATE_EARNED=C'
48       ,to_char(ppa.effective_date, 'YYYY/MM/DD HH24:MI:SS')
49       ,'ORG_PAY_METHOD_ID=C'
50       ,ppa.org_payment_method_id
51       ,'BUSINESS_GROUP_ID=C'
52       ,ppa.business_group_id
53       ,'PAYROLL_ID=C'
54       ,ppa.payroll_id
55       ,'PAYROLL_ACTION_ID=C'
56       ,ppa.payroll_action_id
57 FROM   pay_payroll_actions ppa
58 WHERE  ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
59 --
60 CURSOR CSR_KW_PIFSS_BODY IS
61 SELECT /*+ INDEX(hscl, HR_SOFT_CODING_KEYFLEX_PK) */ distinct 'REPORT_TYPE=P'
62 	   		,pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Report_Type')
63 	   		,'AMOUNT=P'
64 			, pay_kw_pifss_report.get_amount_cont(
65 			pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Employer_Id'),paa.assignment_action_id,pef.person_id , to_date('01'||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Month')
66 			||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Year'),'dd-mm-yyyy'))
67 			,'SSN=P'
68 			,nvl(hscl.segment2,' ')
69 			,'CIVIL_ID=P'
70 			,nvl(pef.national_identifier,' ')
71 			,'EMP_NO=P'
72 			,pef.employee_number
73 			,'EMP_NAME=P'
74 --			,pef.full_name
75 ,decode(FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),
76        'L',
77        NVL(hr_person_name.get_person_name
78                        (pef.person_id
79                        ,TRUNC(to_date('01'||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Month')||'-'||
80                                       pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Year'),'dd-mm-yyyy'), 'MM')
81                        ,'DISPLAY_NAME'
82                        ,FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT')),' '),
83        pef.full_name)
84 			,'EFFECTIVE_DATE=P'
85 			,to_char(ppa.effective_date,'YYYYMMDD')
86 			,'CHANGE_IND=P'
87 			,nvl(pay_kw_pifss_report.get_change_indicator(pef.person_id),' ')
88 			,'ASSIGNMENT_ID=C' , asg.assignment_id
89 			      ,'BUSINESS_GROUP_ID=C' , asg.business_group_id
90 			      ,'DATE_EARNED=C' , to_char(ppa.effective_date, 'YYYY/MM/DD HH24:MI:SS')
91 			      ,'ORGANIZATION_ID=C' , asg.organization_id
92       ,'TAX_UNIT_ID=C' , paa.tax_unit_id
93   FROM   per_assignments_f asg
94            ,pay_assignment_actions paa
95            ,pay_payroll_actions ppa
96            ,hr_soft_coding_keyflex hscl
97            ,per_people_f pef
98            ,pay_payroll_actions ppa1
99 	   ,pay_legislation_rules leg
100     WHERE  asg.assignment_id = paa.assignment_id
101     AND    pef.person_id = asg.person_id
102     AND    paa.payroll_action_id = ppa.payroll_action_id
103     AND    ppa.action_type in ('R','Q')
104     AND    ppa.action_status = 'C'
105     AND    paa.action_status = 'C'
106     AND    trunc(ppa.date_earned,'MM') = TRUNC(to_date('01'||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Month')||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Year'),'dd-mm-yyyy'), 'MM')
107     AND    trunc(to_date('01'||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Month')||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Year'),'dd-mm-yyyy'), 'MM')
108                between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
109     AND    trunc(to_date('01'||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Month')||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Year'),'dd-mm-yyyy'), 'MM')
110                between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
111     AND    ppa1.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
112     AND    hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
113     AND    hscl.segment1 = pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Employer_Id')
114     AND    leg.legislation_code = 'KW'
115     AND    leg.rule_type = 'S'
116     AND    leg.rule_mode = hscl.id_flex_num
117     AND    pef.nationality = pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'LOCAL_NATIONALITY');
118 --
119 CURSOR CSR_KW_PIFSS_FOOTER IS
120 SELECT /*+ INDEX(hscl,HR_SOFT_CODING_KEYFLEX_PK) */ distinct 'REPORT_TYPE=P'
121 	   		,pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Report_Type')
122 			,'EMPLOYER_KEY=P'
123 	   		,pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Employer_Key')
124 	   		,'DED_SSN=P'
125 			,nvl(hscl.segment2,' ')
126 			,'DED_CIVIL_ID=P'
127 			,nvl(pef.national_identifier,' ')
128 			,'DED_EMP_NO=P'
129 			,pef.employee_number
130 			,'DED_EMP_NAME=P'
131 --			,pef.full_name
132 ,decode(FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),
133        'L',
134        NVL(hr_person_name.get_person_name
135                        (pef.person_id
136                        ,TRUNC(to_date('01'||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Month')||'-'||
137                                       pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Year'),'dd-mm-yyyy'), 'MM')
138                        ,'DISPLAY_NAME'
139                        ,FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT')),' '),
140        pef.full_name)
141 			,'DED_DETAIL=P'
142 			,nvl(substr(pay_kw_pifss_report.get_deduction_detail(pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Report_Type'),paa.assignment_action_id,asg.assignment_id,ppa.effective_date),1,63),' ')
143 			,'DED_DETAIL2=P'
144 			,nvl(substr(pay_kw_pifss_report.get_deduction_detail(pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Report_Type'),paa.assignment_action_id,asg.assignment_id,ppa.effective_date),64,63),' ')
145 			,'DED_DETAIL3=P'
146 			,nvl(substr(pay_kw_pifss_report.get_deduction_detail(pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Report_Type'),paa.assignment_action_id,asg.assignment_id,ppa.effective_date),127,63),' ')
147 			,'DED_DETAIL4=P'
148 			,nvl(substr(pay_kw_pifss_report.get_deduction_detail(pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Report_Type'),paa.assignment_action_id,asg.assignment_id,ppa.effective_date),190,63),' ')
149 			,'DED_DETAIL5=P'
150 			,nvl(substr(pay_kw_pifss_report.get_deduction_detail(pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Report_Type'),paa.assignment_action_id,asg.assignment_id,ppa.effective_date),253,63),' ')
151 			,'ASSIGNMENT_ID=C' , asg.assignment_id
152 			      ,'BUSINESS_GROUP_ID=C' , asg.business_group_id
153 			      ,'DATE_EARNED=C' , to_char(ppa.effective_date, 'YYYY/MM/DD HH24:MI:SS')
154 			      ,'ORGANIZATION_ID=C' , asg.organization_id
155       ,'TAX_UNIT_ID=C' , paa.tax_unit_id
156   FROM   per_assignments_f asg
157            ,pay_assignment_actions paa
158            ,pay_payroll_actions ppa
159            ,hr_soft_coding_keyflex hscl
160            ,per_people_f pef
161            ,pay_payroll_actions ppa1
162 	   ,pay_legislation_rules leg
163     WHERE  asg.assignment_id = paa.assignment_id
164     AND    pef.person_id = asg.person_id
165     AND    paa.payroll_action_id = ppa.payroll_action_id
166     AND    ppa.action_type in ('R','Q')
167     AND    ppa.action_status = 'C'
168     AND    paa.action_status = 'C'
169     AND    trunc(ppa.date_earned,'MM') = TRUNC(to_date('01'||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Month')||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Year'),'dd-mm-yyyy'), 'MM')
170     AND    trunc(to_date('01'||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Month')||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Year'),'dd-mm-yyyy'), 'MM')
171 	 between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
172     AND    trunc(to_date('01'||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Month')||'-'||pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Year'),'dd-mm-yyyy'), 'MM')
173 	 between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
174     AND    ppa1.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
175     AND    hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
176     AND    hscl.segment1 = pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'Employer_Id')
177     AND    leg.legislation_code = 'KW'
178     AND    leg.rule_type = 'S'
179     AND    leg.rule_mode = hscl.id_flex_num
180     AND    pef.nationality = pay_kw_pifss_report.get_parameter(ppa1.legislative_parameters,'LOCAL_NATIONALITY');
181 --
182 END PAY_KW_PIFSS;