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_DK_MIA_REPORT_PKG

Source


1 PACKAGE PAY_DK_MIA_REPORT_PKG AUTHID CURRENT_USER as
2 /* $Header: pydkmiar.pkh 120.1 2006/01/19 22:19:15 pgopal noship $ */
3 
4 level_cnt NUMBER;
5 
6 PROCEDURE range_cursor(
7                 p_payroll_action_id     IN  NUMBER,
8                 p_sqlstr                OUT NOCOPY VARCHAR2);
9 
10 PROCEDURE assignment_action_code(
11                           pactid    IN NUMBER,
12                           stperson  IN NUMBER,
13                           endperson IN NUMBER,
14                           chunk     IN NUMBER);
15 
16 FUNCTION get_parameter(
17                  p_parameter_string  IN VARCHAR2
18                 ,p_token             IN VARCHAR2
19                 ,p_segment_number    IN NUMBER DEFAULT NULL )RETURN VARCHAR2;
20 
21 FUNCTION get_cp_parameter(
22                           p_payroll_action_id   NUMBER
23 	  	         ,p_token_name          VARCHAR2) RETURN VARCHAR2;
24 
25 FUNCTION get_period_dates(
26                  p_payroll_id          IN VARCHAR2
27 		,p_payroll_action_id   IN VARCHAR2
28                 ,p_start_date          OUT NOCOPY VARCHAR2
29 		,p_end_date            OUT NOCOPY VARCHAR2
30                 ,p_direct_dd_date      OUT NOCOPY VARCHAR2)RETURN VARCHAR2;
31 
32 /*FUNCTION get_payroll_period(
33                  p_payroll_id          IN VARCHAR2
34 		,p_effective_date      IN DATE)RETURN VARCHAR2;*/
35 
36 FUNCTION get_taxable_pay
37    (p_assignment_action_id     IN  VARCHAR2) RETURN NUMBER;
38 
39 
40 FUNCTION get_sp_name(p_business_group_id IN NUMBER) RETURN varchar2;
41 
42 FUNCTION get_sp_details (p_payroll_action_id IN number
43 			,p_cvr_no OUT NOCOPY varchar2
44 			,p_sp_name OUT NOCOPY varchar2
45 			,p_org_address OUT NOCOPY varchar2
46 			,p_town OUT NOCOPY varchar2) RETURN varchar2;
47 
48 
49 FUNCTION get_dd_date(p_payroll_id IN NUMBER,
50                      p_effective_date IN DATE) RETURN varchar2;
51 
52 FUNCTION get_business_group_id(p_payroll_action_id IN number) RETURN number;
53 
54 FUNCTION check_termination_date(p_start_date varchar2,
55 				p_end_date varchar2,
56 				p_termination_date varchar2) RETURN varchar2;
57 
58 CURSOR get_org_details IS
59 SELECT 'ORGANIZATION_NAME=P',hou1.name
60       ,'TRANSFER_CVRNO=P',hoi2.org_information1
61       ,'ORGANIZATION_ADDR=P', substr((loc.ADDRESS_LINE_1||' '||loc.ADDRESS_LINE_2||' '||loc.ADDRESS_LINE_3),1,40)
62       ,'ORGANIZATION_TOWN=P' ,substr((loc.POSTAL_CODE ||' ' || loc.TOWN_OR_CITY),1,40)
63       ,'PAY_PERIOD=P' , to_char(ppa.effective_date,'YYYYMM')
64       ,'PAYROLL_ACTION_ID=C' , ppa.payroll_action_id
65 
66 FROM    HR_ORGANIZATION_UNITS hou1
67       , HR_ORGANIZATION_INFORMATION hoi1
68       , HR_ORGANIZATION_INFORMATION hoi2
69       , HR_LOCATIONS loc
70       , PAY_PAYROLL_ACTIONS ppa
71 WHERE
72 ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
73 and hou1.business_group_id = ppa.BUSINESS_GROUP_ID
74 and hou1.organization_id = nvl(PAY_DK_MIA_REPORT_PKG.get_parameter(legislative_parameters,'LEGAL_EMPLOYER_NAME',null),hou1.organization_id)
75 and hou1.location_id = loc.LOCATION_ID(+)
76 and hoi1.organization_id = hou1.organization_id
77 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
78 and hoi1.org_information1 = nvl2(PAY_DK_MIA_REPORT_PKG.get_parameter(legislative_parameters,'LEGAL_EMPLOYER_NAME',null),'HR_LEGAL_EMPLOYER','DK_SERVICE_PROVIDER')
79 and hoi1.ORG_INFORMATION2 ='Y'
80 and hoi2.ORG_INFORMATION_CONTEXT= nvl2(PAY_DK_MIA_REPORT_PKG.get_parameter(legislative_parameters,'LEGAL_EMPLOYER_NAME',null)
81                                       ,'DK_LEGAL_ENTITY_DETAILS','DK_SERVICE_PROVIDER_DETAILS')
82 and hoi2.organization_id =  hoi1.organization_id
83 and ppa.EFFECTIVE_DATE BETWEEN hou1.DATE_FROM and nvl(hou1.DATE_TO, ppa.EFFECTIVE_DATE);
84 
85 
86 
87 
88 CURSOR get_employee_details IS
89 SELECT distinct('CPR_NO=P'), to_char(PAP.NATIONAL_IDENTIFIER),
90 	'PERSON_ID=P', to_char(PAP.PERSON_ID),
91 	'PAYROLL_ID=P',to_char(PAA.PAYROLL_ID),
92 	'LEGAL_EMPLR_CVRNO=P', hoi2.ORG_INFORMATION1,
93 	'ASGMT_START_DATE=P' , to_char(PAA.EFFECTIVE_START_DATE,'YYYYMMDD'),
94 	'ASGMT_END_DATE=P' , nvl(to_char(PPS.ACTUAL_TERMINATION_DATE,'YYYYMMDD'),'-1'),
95 	'TERM_DATE=P', nvl(to_char(to_date(SCL.SEGMENT8,'DD/MM/YYYYY'),'YYYYMMDD'),'-1'),
96 	'DISPOSAL_DATE=P', PAY_DK_MIA_REPORT_PKG.get_dd_date(PPA.payroll_id,ppa.effective_date),
97 	'TAXABLE_PAY=P' , to_char(PAY_DK_MIA_REPORT_PKG.get_taxable_pay(pact.assignment_action_id)),
98 	'EMP_NO=P', PAP.EMPLOYEE_NUMBER,
99 	'ASGMNT_ID=P', PAA.ASSIGNMENT_ID
100 	--'PAYROLL_ACTION_ID=C' , ppa.payroll_action_id
101 FROM
102      per_all_people_f                  PAP
103    , per_all_assignments_f             PAA
104    , pay_assignment_actions            pact
105    , pay_payrolls_f                    PPF
106    , pay_payroll_actions               PPA
107    , per_periods_of_service	       PPS
108    , hr_soft_coding_keyflex SCL
109    , HR_ORGANIZATION_UNITS hou
110    , HR_ORGANIZATION_INFORMATION hoi1
111    , HR_ORGANIZATION_INFORMATION hoi2
112 WHERE
113 --ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
114 ppa.business_group_id = PAY_DK_MIA_REPORT_PKG.get_business_group_id(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'))
115 --AND PAA.business_group_id = ppa.business_group_id
116 AND PAP.per_information_category ='DK'
117 AND PAA.PERSON_ID = PAP.PERSON_ID
118 AND PPS.person_id = PAP.person_id
119 AND PAA.payroll_id = PPF.payroll_id
120 AND PAA.PAYROLL_ID > 0
121 AND PAA.payroll_id = PPA.payroll_id
122 AND pact.payroll_action_id = (select max(payroll_action_id) from pay_payroll_actions
123 			      where payroll_id=PPA.payroll_id
124 			      and action_type='R'
125 			      and action_status ='C'
126 			      and effective_date BETWEEN ppa.start_date AND ppa.effective_date)
127 			      and exists
128 			      (select '1' from pay_payroll_actions
129 			      where payroll_id=PPA.payroll_id
130 			      and action_type='P'
131   			      and action_status = 'C'
132 			      and effective_date BETWEEN ppa.start_date AND ppa.effective_date)
133 AND PAA.assignment_id = pact.assignment_id
134 AND PAA.assignment_status_type_id = 1
135 AND PAA.soft_coding_keyflex_id = SCL.soft_coding_keyflex_id
136 --AND PPA.EFFECTIVE_DATE  between  PAA.EFFECTIVE_START_DATE  and PAA.EFFECTIVE_END_DATE
137 AND PPA.EFFECTIVE_DATE  between  PAP.EFFECTIVE_START_DATE  and PAP.EFFECTIVE_END_DATE
138 AND to_char(PPA.effective_date,'YYYYMMDD') =
139 			to_char((select effective_date from pay_payroll_actions
140 			where payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')),'YYYYMMDD')
141 AND PPA.EFFECTIVE_DATE BETWEEN PPS.DATE_START AND NVL(PPS.ACTUAL_TERMINATION_DATE,TO_DATE('31/12/4712','dd/mm/yyyy'))--Check added by pgopal for bug fix-4499107
142 and hou.business_group_id =  PPA.business_group_id
143 and hoi1.organization_id = hou.organization_id
144 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
145 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
146 and hoi1.ORG_INFORMATION2 = 'Y'
147 and hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS'
148 and hoi2.organization_id =  hoi1.organization_id
149 and nvl(hoi2.org_information1,0)= nvl2(PAY_DK_MIA_REPORT_PKG.get_parameter(legislative_parameters,'LEGAL_EMPLOYER_NAME',null),pay_magtape_generic.get_parameter_value('TRANSFER_CVRNO'),nvl(hoi2.org_information1,0) )
150 and ppa.EFFECTIVE_DATE BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, ppa.EFFECTIVE_DATE)
151 AND SCL.ENABLED_FLAG = 'Y'
152 AND SCL.SEGMENT1 =to_char(hou.ORGANIZATION_ID)
153 ORDER BY hoi2.ORG_INFORMATION1,PAA.payroll_id,PAP.PERSON_ID;
154 
155 END PAY_DK_MIA_REPORT_PKG;
156