1 Package PAY_NO_TC_REQ AUTHID CURRENT_USER as
2 /* $Header: pynotcrq.pkh 120.0 2005/05/29 07:02:31 appldev noship $ */
3 --
4 level_cnt NUMBER;
5 --
6
7 FUNCTION get_parameter(p_payroll_action_id NUMBER,
8 p_token_name VARCHAR2) RETURN VARCHAR2;
9
10 --
11 -- PROCEDURE range_cursor
12 -- Procedure which stamps the payroll action with the PAYROLL_ID (if
13 -- supplied), then returns a varchar2 defining a SQL Stateent to select
14 -- all the people in the business group.
15 -- The archiver uses this cursor to split the people into chunks for parallel
16 -- processing.
17 --
18 -- to return parameter values from legislative parameters in pay_payroll_actions
19 --
20
21 PROCEDURE range_cursor(
22 p_payroll_action_id IN NUMBER,
23 p_sqlstr OUT NOCOPY VARCHAR2);
24
25
26 PROCEDURE assignment_action_code(
27 p_payroll_action_id IN NUMBER,
28 p_start_person_id IN NUMBER,
29 p_end_person_id IN NUMBER,
30 p_chunk_number IN NUMBER);
31
32
33
34
35
36 CURSOR CSR_NO_TC_REQ IS
37
38 select
39 'NATIONAL_IDENTIFIER=P'
40 ,pef.NATIONAL_IDENTIFIER
41 ,'ORG_NUMBER=P'
42 ,hoi1.org_information1
43
44 from HR_ORGANIZATION_UNITS o1
45 , HR_ORGANIZATION_INFORMATION hoi1
46 , HR_ORGANIZATION_INFORMATION hoi2
47 , HR_ORGANIZATION_INFORMATION hoi3
48 , hr_soft_coding_keyflex hsk
49 , per_all_assignments_f paf
50 , per_all_people_f pef
51 , per_periods_of_service serv
52 , pay_payroll_actions paa
53
54 WHERE
55 paa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
56 and hoi1.organization_id = o1.organization_id
57 and hoi1.ORG_INFORMATION_CONTEXT='NO_LEGAL_EMPLOYER_DETAILS'
58 and hoi1.organization_id = hoi2.organization_id
59 and hoi2.ORG_INFORMATION_CONTEXT='CLASS'
60 and hoi2.org_information1 = 'HR_LEGAL_EMPLOYER'
61 and hoi3.organization_id = hoi2.organization_id
62 and hoi3.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
63 and hoi3.org_information1=hsk.segment2
64 and hsk.SOFT_CODING_KEYFLEX_ID = paf.SOFT_CODING_KEYFLEX_ID
65 and paf.PRIMARY_FLAG = 'Y'
66 and paf.person_id=pef.person_id
67 and pef.business_group_id = paa.business_group_id
68 and serv.person_id = pef.person_id
69 and serv.period_of_service_id = paf.period_of_service_id
70 and serv.date_start = (select max(s.date_start)
71 from per_periods_of_service s
72 where s.person_id = pef.person_id
73 and paa.effective_date >= s.date_start)
74 and pef.current_employee_flag = 'Y'
75 and paa.effective_date between paf.effective_start_date and paf.effective_end_date
76 and paa.effective_date between pef.effective_start_date and pef.effective_end_date
77 order by hoi1.org_information1;
78
79
80
81 end PAY_NO_TC_REQ;
[Home] [Help]