[Home] [Help]
PACKAGE: APPS.PAY_NL_ANNUAL_SI_FILE
Source
1 PACKAGE PAY_NL_ANNUAL_SI_FILE as
2 /* $Header: pynlasif.pkh 120.0.12000000.1 2007/01/17 22:54:35 appldev noship $ */
3 level_cnt NUMBER;
4
5
6 Cursor Csr_NL_Annual_SI_Header IS
7
8 SELECT
9 'TAX_YEAR=P',pay_magtape_generic.get_parameter_value('TAX_YEAR'),
10 'BUSINESS_GROUP_ID=P',ppa.business_group_id,
11 'EMPLOYER_ID=P',pay_magtape_generic.get_parameter_value('EMPLOYER_ID'),
12 'ORG_HEIRARCHY_ID=P',pay_magtape_generic.get_parameter_value('ORG_HEIRARCHY'),
13 'SI_PROVIDER_ID=P',pay_magtape_generic.get_parameter_value('SI_PROVIDER_ID')
14 FROM pay_payroll_actions ppa
15 WHERE ppa.payroll_action_id
16 =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
17 AND EXISTS
18 (select * from pay_assignment_actions paa
19 where paa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
20
21
22 Cursor Csr_NL_Annual_SI_Body IS
23 SELECT
24 'ASI_EMPLOYER_ID=P' ,ee_asi.action_information1 ,
25 'ASI_SIP_ID=P' ,ee_asi.action_information3 ,
26 'ASI_ASSIGNMENT_ID=P' ,ee_asi.action_information2 ,
27 'ASI_PERSON_ID=P' ,ee_asi.action_information4 ,
28 'SOFI_NUMBER=P' ,pap.National_Identifier,
29 'ASI_NOD=P' ,ee_asi.action_information5 ,
30 'ASI_SI_WAGE=P' ,ee_asi.action_information6 ,
31 'ASI_SUP_DAYS=P' ,ee_asi.action_information7 ,
32 'ASI_AMT_ALLOWANCE=P' ,ee_asi.action_information8 ,
33 'ASI_SPL_IND=P' ,ee_asi.action_information9
34 FROM
35 pay_assignment_actions pay_act,
36 pay_action_interlocks arc_lck,
37 pay_action_information ee_asi,
38 per_all_people_f pap
39 WHERE pay_act.payroll_action_id
40 =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
41 AND pay_act.assignment_action_id = arc_lck.locking_action_id
42 AND arc_lck.locking_action_id = ee_asi.action_context_id
43 AND ee_asi.action_context_type='AAP'
44 AND ee_asi.action_information_category = 'NL ASI EMPLOYEE DETAILS'
45 AND ee_asi.action_information4 = pap.person_id
46 AND ee_asi.effective_date between pap.effective_start_date and pap.effective_end_date;
47
48
49 /*-------------------------------------------------------------------------------
50 |Name : RANGE_CODE |
51 |Type : Procedure |
52 |Description : This procedure returns a sql string to select a range of |
53 | assignments eligible for archival |
54 -------------------------------------------------------------------------------*/
55
56 Procedure RANGE_CODE (pactid IN NUMBER
57 ,sqlstr OUT NOCOPY VARCHAR2);
58
59
60 /*--------------------------------------------------------------------
61 |Name : ASSIGNMENT_ACTION_CODE |
62 |Type : Procedure |
63 |Description: This procedure Fetches,validates and archives |
64 | information in the newly created context |
65 | NL ATS EMPLOYEE DETAILS |
66 ----------------------------------------------------------------------*/
67
68 Procedure ASSIGNMENT_ACTION_CODE (p_payroll_action_id in number
69 ,p_start_person_id in number
70 ,p_end_person_id in number
71 ,p_chunk in number);
72
73
74 /*----------------------------------------------------------------------------------
75 |Name : ARCHIVE_INIT |
76 |Type : Procedure |
77 |Description : Initialization Code for Archiver |
78 -----------------------------------------------------------------------------------*/
79
80 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER);
81
82 /*-------------------------------------------------------------------------------
83 |Name : ARCHIVE_CODE |
84 |Type : Procedure |
85 |Description : Archival code for archiver |
86 -------------------------------------------------------------------------------*/
87
88 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER
89 ,p_effective_date IN DATE);
90
91
92
93 /*-----------------------------------------------------------------------------
94 |Name : GET_ALL_PARAMETERS |
95 |Type : Procedure |
96 |Description: Procedure which returns all the parameters of the archive process|
97 -------------------------------------------------------------------------------*/
98
99 PROCEDURE get_all_parameters (
100 p_payroll_action_id IN NUMBER
101 ,p_business_group_id OUT NOCOPY NUMBER
102 ,p_si_provider_id OUT NOCOPY NUMBER
103 ,p_effective_date OUT NOCOPY DATE
104 ,p_tax_year OUT NOCOPY DATE
105 ,p_employer OUT NOCOPY NUMBER
106 ,p_org_struct_id OUT NOCOPY NUMBER ) ;
107
108
109 /*-----------------------------------------------------------------------------
110 |Name : Get_SIP_Details |
111 |Type : Function |
112 |Description: Procedure gets Reg Number , reporting name details at the |
113 | SIP level |
114 -------------------------------------------------------------------------------*/
115 FUNCTION Get_SIP_Details
116 ( P_Employer_ID IN NUMBER
117 ,P_SI_PROVIDER_ID IN NUMBER
118 ,P_PROCESS_DATE IN DATE
119 ,p_Sender_Rep_Name_sip OUT NOCOPY VARCHAR2
120 ,p_Sender_Reg_Number_sip OUT NOCOPY VARCHAR2
121 ,p_Employer_Rep_Name_sip OUT NOCOPY VARCHAR2
122 ,p_Employer_Reg_Number_sip OUT NOCOPY VARCHAR2
123 )RETURN NUMBER ;
124
125 /*-----------------------------------------------------------------------------
126 |Name : GET_SI_WAGE |
127 |Type : Function |
128 |Description: Function returns SI Wage - sum of SI_INCOME_STANDARD_TAX, |
129 | SI_INCOME_SPECIAL_TAX , RETRO_SI_INCOME_STANDARD_TAX , |
130 | RETRO_SI_INCOME_SPECIAL_TAX |
131 | SIP,ORG levels |
132 -------------------------------------------------------------------------------*/
133
134 function get_si_wage(p_assgt_act_id number)RETURN number;
135
136 /*-----------------------------------------------------------------------------
137 |Name : GET_SI_SUPPLEMENTARY_DAYS |
138 |Type : Function |
139 |Description: Function returns SI Supplementary Days - |
140 | balance SI_SUPPLEMENATRY_DAYS |
141 -------------------------------------------------------------------------------*/
142
143 function get_si_supplementary_days(p_assgt_act_id number)RETURN number;
144
145 /*-----------------------------------------------------------------------------
146 |Name : GET_SI_AMOUNT_ALLOWANCE |
147 |Type : Function |
148 |Description: Function returns SI Amount Allowance - |
149 | balance SI_AMOUNT_ALLOWANCE |
150 -------------------------------------------------------------------------------*/
151
152 function get_si_amount_allowance(p_assgt_act_id number)RETURN number;
153
154 /*-----------------------------------------------------------------------------
155 |Name : GET_SI_SPECIAL_INDICATOR |
156 |Type : Procedure |
157 |Description: Function fetches the SI Special Indicator |
158 -------------------------------------------------------------------------------*/
159
160 PROCEDURE get_si_special_indicator(p_assignment_id IN NUMBER,
161 l_si_special_indicator OUT NOCOPY VARCHAR2 );
162
163 /*-----------------------------------------------------------------------------
164 |Name : GET_NUMBER_OF_DAYS |
165 |Type : Function |
166 |Description: Function returns Number Of Days - |
167 | balance REAL_SOCIAL_INSURANCE_DAYS |
168 -------------------------------------------------------------------------------*/
169
170 function get_number_of_days(p_assgt_act_id number)RETURN number;
171
172 function get_org_name(p_org_id number
173 ,l_org_name OUT NOCOPY VARCHAR2)RETURN NUMBER;
174
175 END PAY_NL_ANNUAL_SI_FILE;