[Home] [Help]
PACKAGE: APPS.PER_EVS_MAG_REPORT
Source
1 package per_evs_mag_report AUTHID CURRENT_USER as
2 /* $Header: peevsmag.pkh 120.3.12010000.2 2009/06/01 11:55:43 kagangul ship $ */
3 --
4
5 level_cnt number ;
6
7 FUNCTION f_evs_rem_spl_char(p_input_string IN VARCHAR2)
8 RETURN VARCHAR2;
9
10 /*
11 ** Cursor to retrieve Westpac Direct Entry system Header info
12 */
13 cursor c_evs is
14 SELECT -- Bug # 8528862 : Need to remove characters like <'. ->
15 --'LAST_NAME=P' ,substr(ppf.last_name,1,13)
16 'LAST_NAME=P' ,substr(per_evs_mag_report.f_evs_rem_spl_char(ppf.last_name),1,13)
17 -- Bug # 8528862 : Need to remove characters like <'. ->
18 --,'MIDDLE_NAME=P' ,nvl(substr(ppf.middle_names,1,7),' ')
19 ,'MIDDLE_NAME=P' ,nvl(substr(per_evs_mag_report.f_evs_rem_spl_char(ppf.middle_names),1,7),' ')
20 -- Bug # 8528862 : Need to remove characters like <'. ->
21 --,'FIRST_NAME=P' ,nvl(substr(ppf.first_name,1,10),' ')
22 ,'FIRST_NAME=P' ,nvl(substr(per_evs_mag_report.f_evs_rem_spl_char(ppf.first_name),1,10),' ')
23 ,'SSN=P' ,nvl((substr(ppf.national_identifier,1,3) || substr(ppf.national_identifier,5,2) || substr(ppf.national_identifier,8,4)),' ')
24 ,'DATE_OF_BIRTH=P' ,nvl(to_char(ppf.date_of_birth,'MMDDYYYY'),' ')
25 ,'GENDER=P' ,nvl(substr(ppf.sex,1,1),' ')
26 ,'REQUESTER_ID_CODE=P' ,nvl(hoi.org_information1,' ')
27 ,'USER_CONTROL_DATA=P' ,nvl(hoi.org_information2,' ')
28 ,'MULTI_REQ_INDICATOR=P' ,nvl(hoi.org_information3,' ')
29
30 FROM pay_assignment_actions paa
31 ,per_all_assignments_f paf
32 ,per_all_people_f ppf
33 ,hr_organization_information hoi
34
35 WHERE paa.payroll_action_id = fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(3))
36 AND paa.assignment_id = paf.assignment_id
37 AND paf.person_id = ppf.person_id
38 AND paf.primary_flag = 'Y'
39 AND paf.effective_start_date = (SELECT max(paf2.effective_start_date)
40 FROM per_assignments_f paf2
41 WHERE paf.person_id = paf2.person_id
42 AND paf2.primary_flag = 'Y'
43 )
44 AND ppf.effective_start_date = (SELECT max(ppf2.effective_start_date)
45 FROM per_all_people_f ppf2
46 WHERE ppf.person_id = ppf2.person_id
47 )
48 AND paa.tax_unit_id = hoi.organization_id(+)
49 AND hoi.org_information_context(+) = 'EVS Filing'
50 ORDER BY national_identifier ;
51
52 /* Replaced by the above query to avoid duplicate persons being reported */
53 /* Select
54 'LAST_NAME=P' ,substr(ppf.last_name,1,13)
55 , 'MIDDLE_NAME=P' ,nvl(substr(ppf.middle_names,1,7),' ')
56 , 'FIRST_NAME=P' ,nvl(substr(ppf.first_name,1,10),' ')
57 , 'SSN=P' ,nvl((substr(ppf.national_identifier,1,3) || substr(ppf.national_identifier,5,2) || substr(ppf.national_identifier,8,4)),' ')
58 , 'DATE_OF_BIRTH=P' ,nvl(to_char(ppf.date_of_birth,'MMDDYYYY'),' ')
59 , 'GENDER=P' ,nvl(substr(ppf.sex,1,1),' ')
60 , 'REQUESTER_ID_CODE=P' ,nvl(hoi.org_information1,' ')
61 , 'USER_CONTROL_DATA=P' ,nvl(hoi.org_information2,' ')
62 , 'MULTI_REQ_INDICATOR=P' ,nvl(hoi.org_information3,' ')
63 From
64 pay_assignment_actions paa
65 ,per_all_assignments_f paf
66 ,per_all_people_f ppf
67 ,hr_organization_information hoi
68 where
69 paa.payroll_action_id =
70 fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(3))
71 and paa.assignment_id = paf.assignment_id
72 and paf.person_id = ppf.person_id
73 and paf.effective_start_date =
74 (select max(paf2.effective_start_date)
75 from per_assignments_f paf2
76 where paf.assignment_id = paf2.assignment_id
77 )
78 -- and ppf.effective_start_date = paf.effective_start_date
79 and ppf.effective_start_date =
80 (select max(ppf2.effective_start_date)
81 from per_all_people_f ppf2
82 where ppf.person_id = ppf2.person_id
83 )
84 and paa.tax_unit_id = hoi.organization_id(+)
85 and hoi.org_information_context(+) = 'EVS Filing'
86 Order by national_identifier ; */
87
88
89 ----------------------------------------------------------------------------
90 -- For PYUGEN
91 ----------------------------------------------------------------------------
92 procedure get_parameters
93 (p_payroll_action_id in number
94 );
95 --
96 procedure range_cursor
97 (pactid in number
98 ,sqlstr out nocopy varchar2
99 );
100
101 PROCEDURE action_creation(
102 pactid IN NUMBER,
103 stperson IN NUMBER,
104 endperson IN NUMBER,
105 chunk IN NUMBER
106 );
107
108 PROCEDURE init_code(
109 p_payroll_action_id in number
110 );
111
112 ----------------------------------------------------------------------------
113 procedure evs_mag_report_main
114 (errbuf out nocopy varchar2
115 ,retcode out nocopy number
116 --
117 ,p_start_date in varchar2
118 ,p_end_date in varchar2
119 ,p_tax_unit_id in number
120 ,p_business_group_id in number
121 ,p_report_category in varchar2
122 ,p_media_type in varchar2
123 );
124 --
125 procedure evs_put_record
126 (p_file_id in utl_file.file_type
127 ,p_ssn in varchar2
128 ,p_last_name in varchar2
129 ,p_first_name in varchar2
130 ,p_middle_name in varchar2
131 ,p_date_of_birth in date
132 ,p_gender in varchar2
133 ,p_user_control_data in varchar2
134 ,p_requester_id_code in varchar2
135 ,p_multiple_req_indicator in varchar2
136 );
137 --
138 end per_evs_mag_report;