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