1 Package PAY_IE_WNU_EDI AUTHID CURRENT_USER as
2 /* $Header: pyiewnue.pkh 120.1 2006/03/23 21:56:27 vikgupta noship $ */
3 --
4 level_cnt NUMBER;
5 --
6 /* Procedure wnu_update_extra_info calls apis to insert/update records in
7 PER_EXTRA_ASSIGNMENT_INFO */
8
9 Procedure wnu_update_extra_info
10 (p_assignment_id in number,
11 p_effective_date in date,
12 p_include_in_wnu in varchar2 default null);
13 --
14
15 --
16 --
17 -- CURSORS
18 CURSOR CSR_WNU_HEADER_FOOTER IS
19 SELECT
20 ('TAX_YEAR=P') , to_char(ppa.effective_date , 'RRRR'),
21 ('EMPLOYER_NUMBER=P') , nvl(trim(rpad(hoi.org_information1,30)),' '), --Bug 4069789 --Bug 4369280
22 --('EMPLOYER_NAME=P') , nvl(trim(rpad(hou.name,30)),' '),
23 --Modified the source of Employer name for bug fix 3567562
24 ('EMPLOYER_NAME=P') , nvl(trim(rpad(hou.name,30)),' '),
25 ('EFFECTIVE_DATE=P') , to_char(ppa.effective_date, 'DDMMYY'),
26 ('EMPLOYER_ADDRESS1=P') , nvl(trim(rpad(hlo.address_line_1,30)),' '),
27 ('EMPLOYER_ADDRESS2=P') , nvl(trim(rpad(hlo.address_line_2,30)),' '),
28 ('EMPLOYER_ADDRESS3=P') , nvl(trim(rpad(hlo.address_line_3,30)),' '),
29 ('CONTACT_NAME=P') , nvl(trim(rpad(hoi.org_information4,20)),' '),
30 ('CONTACT_NUMBER=P') , nvl(trim(rpad(hlo.telephone_number_1,12)),' ')
31 FROM pay_payroll_actions ppa
32 ,hr_organization_units hou
33 ,hr_organization_information hoi
34 ,hr_locations_all hlo
35 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
36 AND hou.business_group_id= ppa.business_group_id
37 AND hoi.organization_id=pay_ie_archive_detail_pkg.get_parameter(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),'EMP_REF')
38 AND hou.organization_id =hoi.organization_id
39 AND hou.location_id = hlo.location_id(+)
40 AND hoi.org_information_context = 'IE_EMPLOYER_INFO'; --Bug 4369280
41 -- AND hoi.organization_id =hou.organization_id
42 --For bug Fix 3567562 added join to filter record based on Tax District and PAYE Reference specified as parameters.
43 --AND hoi.org_information1=pay_ie_archive_detail_pkg.get_parameter(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),'TAX_REF')
44 --AND hoi.org_information2=pay_ie_archive_detail_pkg.get_parameter(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),'PAYE_REF');
45 --
46 CURSOR CSR_WNU_DETAIL IS
47 select
48 upper('RSI_NUMBER=P'),
49 nvl(SUBSTR(upper(people.national_identifier),1,9),' '),
50 upper('WORKS_NUMBER=P'),
51 upper(substr(assign.assignment_number,1,12)),
52 upper('SURNAME=P'),
53 upper(substr(people.last_name,1,20)),
54 upper('FIRST_NAME=P'),
55 nvl(upper(substr(people.first_name,1,20)),' '),
56 upper('DATE_OF_BIRTH=P'),
57 nvl(to_char(people.DATE_OF_BIRTH, 'DDMMYY'),' '),
58 upper('ADDRESS_LINE1=P'),
59 nvl(SUBSTR(trim(pad.ADDRESS_LINE1),1,30), ' '),
60 upper('ADDRESS_LINE2=P'),
61 nvl(SUBSTR(trim(pad.ADDRESS_LINE2),1,30), ' '),
62 upper('ADDRESS_LINE3=P'),
63 nvl(SUBSTR(trim(pad.ADDRESS_LINE3),1,30), ' '),
64 upper('PAYROLL_NAME=P'),
65 nvl(ppayf.payroll_name ,' ')
66 from
67 pay_assignment_actions act,
68 pay_payroll_actions ppa,
69 per_all_assignments_f assign,
70 per_all_people_f people,
71 per_addresses pad,
72 pay_all_payrolls_f ppayf
73 where act.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
74 and ppa.payroll_action_id = act.payroll_action_id
75 and act.assignment_id = assign.assignment_id
76 and ppayf.payroll_id = assign.payroll_id
77 and assign.person_id = people.person_id
78 and pad.person_id(+) = people.person_id
79 and NVL(pad.PRIMARY_FLAG,'Y') = 'Y'
80 -- For bug 5114019, added a join with per_addresses date as a person can have
81 -- datetrack primary addresses.
82 and ppa.effective_date between
83 nvl(pad.date_from,ppa.effective_date) and nvl(pad.date_to,to_date('31/12/4712','dd/mm/yyyy'))
84 -- End bug 5114019
85 and ppa.effective_date between
86 assign.effective_start_date and assign.effective_end_date
87 and ppa.effective_date between
88 people.effective_start_date and people.effective_end_date
89 and ppa.effective_date between
90 ppayf.effective_start_date and ppayf.effective_end_date
91 order by ppayf.payroll_name, people.last_name ;
92 --
93 --
94 -- PROCEDURE range_cursor
95 -- Procedure which stamps the payroll action with the PAYROLL_ID (if
96 -- supplied), then returns a varchar2 defining a SQL Stateent to select
97 -- all the people in the business group.
98 -- The archiver uses this cursor to split the people into chunks for parallel
99 -- processing.
100 -- This procedure is used for both the P45 Archive process and the P45 EDI
101 -- process.
102 --
103 -- to return parameter values from legislative parameters in pay_payroll_actions
104 --
105 PROCEDURE range_cursor (pactid IN NUMBER,
106 sqlstr OUT NOCOPY VARCHAR2);
107 --
108 /* PROCEDURE wnu_full_action_creation:
109 This PROC creates assignment actions when running the process in FULL Mode */
110
111 PROCEDURE wnu_full_action_creation(pactid IN NUMBER,
112 stperson IN NUMBER,
113 endperson IN NUMBER,
114 chunk IN NUMBER);
115 --
116 /* PROCEDURE wnu_update_action_creation:
117 This PROC creates assignment actions when running the process in UPDATE Mode */
118 --
119 PROCEDURE wnu_update_action_creation(pactid IN NUMBER,
120 stperson IN NUMBER,
121 endperson IN NUMBER,
122 chunk IN NUMBER);
123 --
124 end PAY_IE_WNU_EDI;
[Home] [Help]