[Home] [Help]
PACKAGE: APPS.PAY_NL_NSI_PROCESS
Source
1 PACKAGE PAY_NL_NSI_PROCESS AUTHID CURRENT_USER as
2 /* $Header: pynlnsia.pkh 115.1 2004/05/10 05:01:31 rtadikam noship $ */
3 level_cnt NUMBER;
4 /*-------------------------------------------------------------------------------
5 |Name : RANGE_CODE |
6 |Type : Procedure |
7 |Description : This procedure returns a sql string to select a range of |
8 | assignments eligible for archival |
9 -------------------------------------------------------------------------------*/
10
11 Procedure RANGE_CODE (pactid IN NUMBER
12 ,sqlstr OUT NOCOPY VARCHAR2);
13
14
15 /*-------------------------------------------------------------------------------
16 |NAME : ASSIGNMENT_ACTION_CODE |
17 |TYPE : PROCEDURE |
18 |DESCRIPTION : THIS PROCEDURE FURTHER RESTRICTS THE ASSIGNMENT ID'S RETURNED |
19 | BY THE RANGE CODE. |
20 -------------------------------------------------------------------------------*/
21
22 Procedure ASSIGNMENT_ACTION_CODE (p_payroll_action_id in number
23 ,p_start_person_id in number
24 ,p_end_person_id in number
25 ,p_chunk in number);
26
27
28 /*----------------------------------------------------------------------------------
29 |Name : ARCHIVE_INIT |
30 |Type : Procedure |
31 |Description : Procedure sets the global tables g_statutory_balance_table, |
32 | g_stat_element_table,g_user_balance_table,g_element_table|
33 -----------------------------------------------------------------------------------*/
34
35 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER);
36
37 /*-------------------------------------------------------------------------------
38 |Name : ARCHIVE_CODE |
39 |Type : Procedure |
40 |Description : This is the main procedure which calls the several procedures |
41 | to archive the data. |
42 -------------------------------------------------------------------------------*/
43
44 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER
45 ,p_effective_date IN DATE);
46
47
48
49 /*------------------------------------------------------------------------------
50 |Name : GET_PARAMETER |
51 |Type : Function |
52 |Description : Funtion to get the parameters of the archive process |
53 -------------------------------------------------------------------------------*/
54
55 Function get_parameter(
56 p_parameter_string in varchar2
57 ,p_token in varchar2
58 ,p_segment_number in number default null )RETURN varchar2;
59
60 /*-----------------------------------------------------------------------------
61 |Name : GET_ALL_PARAMETERS |
62 |Type : Procedure |
63 |Description: Procedure which returns all the parameters of the archive process|
64 -------------------------------------------------------------------------------*/
65
66 PROCEDURE get_all_parameters(
67 p_payroll_action_id IN NUMBER
68 ,p_business_group_id OUT NOCOPY NUMBER
69 ,p_employer_id OUT NOCOPY VARCHAR2
70 ,p_si_provider_id OUT NOCOPY VARCHAR2
71 ,p_nsi_month OUT NOCOPY VARCHAR2
72 ,p_output_media_type OUT NOCOPY VARCHAR2
73 ,p_payroll_id OUT NOCOPY VARCHAR2
74 ,p_withdraw_asg_set_id OUT NOCOPY VARCHAR2
75 ,p_report_type OUT NOCOPY VARCHAR2) ;
76
77
78 /********************************************************
79 * Cursor to fetch header record information *
80 ********************************************************/
81 CURSOR CSR_NL_NSIFILE_HEADER IS
82 SELECT 'SENDER_REGISTRATION_NUMBER=P'
83 ,nl_er_nsi.action_information3
84 ,'SENDER_NAME=P'
85 ,nl_er_nsi.action_information4
86 ,'SENDER_ADDRESS=P'
87 ,nl_er_nsi.action_information5
88 ,'EMPLOYER_NAME=P'
89 ,nl_er_nsi.action_information6
90 ,'EMPLOYER_ADDRESS=P'
91 ,nl_er_nsi.action_information7
92 ,'EMPLOYER_REGISTRATION_NUMBER=P'
93 ,nl_er_nsi.action_information8
94 ,'OUTPUT_MEDIA_TYPE=P'
95 ,pay_magtape_generic.get_parameter_value('OUTPUT_MEDIA_TYPE')
96 FROM pay_action_information nl_er_nsi
97 ,pay_payroll_actions ppa
98 WHERE nl_er_nsi.action_context_id=ppa.payroll_action_id
99 AND ppa.payroll_action_id=pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
100 AND nl_er_nsi.ACTION_CONTEXT_TYPE='PA'
101 AND nl_er_nsi.ACTION_INFORMATION_CATEGORY='NL NSI EMPLOYER DETAILS'
102 AND nl_er_nsi.effective_date between ppa.start_date
103 and last_day(to_date('01'||pay_magtape_generic.get_parameter_value('NSI_MONTH'),'DDMMYYYY'));
104
105
106
107 /********************************************************
108 * Cursor to fetch batch/payment record information *
109 ********************************************************/
110
111 CURSOR CSR_NL_NSIFILE_BODY IS
112 SELECT
113 'NSI_PERSON_ID=P'
114 ,nl_ee_nsi.action_information2
115 ,'NSI_ASSIGNMENT_ID=P'
116 ,nl_ee_nsi.action_information3
117 ,'TYPE_NOTIFICATION_A=P'
118 ,nvl(nl_ee_nsi.action_information9,'0')
119 ,'DATE_OF_NOTIFICATION_A=P'
120 ,nvl(nl_ee_nsi.action_information10,'00000000')
121 ,'TYPE_NOTIFICATION_B=P'
122 ,nvl(nl_ee_nsi.action_information11,'0')
123 ,'DATE_OF_NOTIFICATION_B=P'
124 ,nvl(nl_ee_nsi.action_information12,'00000000')
125 ,'CODE_INSURANCE=P'
126 ,nl_ee_nsi.action_information13
127 ,'CODE_INSURANCE_BASIS=P'
128 ,nl_ee_nsi.action_information14
129 ,'CODE_OCCUPATION=P'
130 ,nl_ee_nsi.action_information15
131 ,'WORK_PATTERN=P'
132 ,nl_ee_nsi.action_information16
133 ,'START_DATE_LABOUR_RELATION=P'
134 ,nl_ee_nsi.action_information17
135 ,'SOFI_NUMBER=P'
136 ,nl_ee_nsi.action_information18
137 ,'EMPLOYEE_NAME=P'
138 ,nl_ee_nsi.action_information19
139 ,'EMPLOYEE_PRIMARY_ADDRESS=P'
140 ,nl_ee_nsi.action_information20
141 ,'EMPLOYEE_PR_ADDRESS=P'
142 ,nl_ee_nsi.action_information21
143 ,'GAK_REPORTING_INFO=P'
144 ,nl_ee_nsi.action_information22
145 ,'CADANS_REPORTING_INFO=P'
146 ,nl_ee_nsi.action_information23
147 ,'EMPLOYEE_DETAILS=P'
148 ,nl_ee_nsi.action_information24
149 FROM pay_action_information nl_ee_nsi
150 ,pay_payroll_actions ppa
151 ,pay_assignment_actions paa
152 where nl_ee_nsi.ACTION_CONTEXT_ID= paa.assignment_Action_id
153 AND ppa.payroll_Action_id=paa.payroll_Action_id
154 AND ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
155 AND nl_ee_nsi.action_context_type='AAP'
156 AND nl_ee_nsi.action_information_category = 'NL NSI EMPLOYEE DETAILS'
157 AND nl_ee_nsi.effective_date BETWEEN ppa.start_date
158 AND last_day(to_date('01'||pay_magtape_generic.get_parameter_value('NSI_MONTH'),'DDMMYYYY'))
159 order by nl_ee_nsi.action_information_id; --Bug No : 3612117
160 END PAY_NL_NSI_PROCESS;