[Home] [Help]
PACKAGE: APPS.PAY_PAYSLIP_REPORT
Source
1 PACKAGE pay_payslip_report AUTHID CURRENT_USER AS
2 /* $Header: pygpsrep.pkh 120.7.12020000.2 2013/03/08 09:39:38 bklingam ship $ */
3 --
4 level_cnt NUMBER;
5 g_pa_token VARCHAR2(50);
6 g_cs_token VARCHAR2(50);
7 --
8 FUNCTION get_parameter ( p_parameter_string IN VARCHAR2
9 ,p_token IN VARCHAR2
10 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2;
11 --
12 FUNCTION get_sort_order( p_type IN VARCHAR2
13 ,p_legislation_code IN VARCHAR2 ) RETURN VARCHAR2 ;
14 --
15 PROCEDURE get_all_parameters(p_payroll_action_id IN NUMBER
16 ,p_payroll_id OUT NOCOPY NUMBER
17 ,p_consolidation_set_id OUT NOCOPY NUMBER
18 ,p_start_date OUT NOCOPY VARCHAR2
19 ,p_end_date OUT NOCOPY VARCHAR2
20 ,p_rep_group OUT NOCOPY VARCHAR2
21 ,p_rep_category OUT NOCOPY VARCHAR2
22 ,p_assignment_set_id OUT NOCOPY NUMBER
23 ,p_assignment_id OUT NOCOPY NUMBER
24 ,p_effective_date OUT NOCOPY DATE
25 ,p_business_group_id OUT NOCOPY NUMBER
26 ,p_legislation_code OUT NOCOPY VARCHAR2 ) ;
27 --
28 CURSOR csr_asg IS
29 SELECT /*+ ORDERED */ 'TRANSFER_ACT_ID=P',ptoa.object_Action_id
30 FROM pay_temp_object_actions ptoa
31 ,pay_payroll_actions ppa
32 ,per_all_assignments_f paaf
33 ,per_all_people_f papf
34 ,hr_soft_coding_keyflex hsck
35 ,per_business_groups pbg
36 ,hr_organization_units org
37 ,per_person_types ppt
38 ,per_assignment_status_types past
39 WHERE ptoa.object_id = paaf.assignment_id
40 AND ptoa.object_type = 'ASG'
41 AND paaf.person_id = papf.person_id
42 AND ptoa.payroll_action_id = TO_NUMBER(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'))
43 AND ptoa.payroll_action_id = ppa.payroll_action_id
44 AND paaf.effective_start_date = (SELECT MAX(paf_inner.effective_start_date)
45 FROM per_all_assignments_f paf_inner
46 WHERE paf_inner.assignment_id = paaf.assignment_id
47 AND paf_inner.effective_start_date <=
48 TO_DATE(pay_payslip_report.get_parameter(ppa.legislative_parameters,'END_DATE'),'YYYY/MM/DD')
49 )
50 --AND paaf.effective_start_date between papf.effective_start_date and papf.effective_end_date
51 AND papf.effective_start_date = (SELECT MAX(papf_inner.effective_start_date)
52 FROM per_all_people_f papf_inner
53 WHERE papf_inner.person_id = papf.person_id
54 AND papf_inner.effective_start_date <=
55 TO_DATE(pay_payslip_report.get_parameter(ppa.legislative_parameters,'END_DATE'),'YYYY/MM/DD')
56 )
57 AND papf.business_group_id = pbg.business_group_id
58 AND pbg.legislation_code = NVL(past.legislation_code,pbg.legislation_code)
59 /* 7195040 - Condition to be Added to Prevent Duplicates for Benefit,
60 Applicant, or Contractor Assignments*/
61 AND paaf.assignment_type = 'E'
62 AND past.active_flag = 'Y'
63 AND past.assignment_status_type_id = paaf.assignment_status_type_id
64 AND ppt.person_type_id = papf.person_type_id
65 AND ppt.business_group_id = pbg.business_group_id
66 AND ppt.active_flag = 'Y'
67 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
68 AND org.organization_id = paaf.organization_id
69 AND org.business_group_id = paaf.business_group_id
70 ORDER BY DECODE(pay_payslip_report.get_sort_order('LE',pbg.legislation_code)
71 ,'SEGMENT1',hsck.segment1
72 ,'SEGMENT2',hsck.segment2
73 ,'SEGMENT3',hsck.segment3
74 ,'SEGMENT4',hsck.segment4
75 ,'SEGMENT5',hsck.segment5
76 ,'SEGMENT6',hsck.segment6
77 ,'SEGMENT7',hsck.segment7
78 ,'SEGMENT8',hsck.segment8
79 ,'SEGMENT9',hsck.segment9
80 ,'SEGMENT10',hsck.segment10
81 ,'SEGMENT11',hsck.segment11
82 ,'SEGMENT12',hsck.segment12
83 ,'SEGMENT13',hsck.segment13
84 ,'SEGMENT14',hsck.segment14
85 ,'SEGMENT15',hsck.segment15
86 ,'SEGMENT16',hsck.segment16
87 ,'SEGMENT17',hsck.segment17
88 ,'SEGMENT18',hsck.segment18
89 ,'SEGMENT19',hsck.segment19
90 ,'SEGMENT20',hsck.segment20
91 ,'SEGMENT21',hsck.segment21
92 ,'SEGMENT22',hsck.segment22
93 ,'SEGMENT23',hsck.segment23
94 ,'SEGMENT24',hsck.segment24
95 ,'SEGMENT25',hsck.segment25
96 ,'SEGMENT26',hsck.segment26
97 ,'SEGMENT27',hsck.segment27
98 ,'SEGMENT28',hsck.segment28
99 ,'SEGMENT29',hsck.segment29
100 ,'SEGMENT30',hsck.segment30)
101 ,DECODE(pay_payslip_report.get_sort_order('ORG',pbg.legislation_code)
102 ,'ORGANIZATION_NAME', org.name
103 ,'ORGANIZATION_ID',paaf.organization_id
104 ,'ASSIGNMENT_NUMBER',paaf.assignment_number)
105 ,DECODE(pay_payslip_report.get_sort_order('NAME',pbg.legislation_code)
106 ,'LAST_NAME',papf.last_name
107 ,'FIRST_NAME',papf.first_name
108 ,'FULL_NAME',papf.full_name
109 ,papf.last_name)
110 ,paaf.assignment_number ;
111 --
112 CURSOR csr_curr_act IS
113 SELECT 'TRANSFER_ACT_ID=P',
114 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
115 FROM DUAL;
116 --
117 PROCEDURE xml_asg;
118 --
119 PROCEDURE qualifying_proc(p_assignment_id IN NUMBER
120 ,p_qualifier OUT NOCOPY VARCHAR2 );
121
122 --
123 END pay_payslip_report;