eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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;