[Home] [Help]
95: and prr.source_type = 'E';
96: --
97: begin
98: --
99: hr_utility.set_location('Entered '||l_proc,5);
100: hr_utility.set_location('. Asg_ID:'||p_assignment_id||'. BG_ID:'||p_business_group_id||'. Date:'||p_date_earned,20);
101:
102: open pay_asg_act_csr;
103: fetch pay_asg_act_csr into l_asg_action_id, l_pay_action_date;
96: --
97: begin
98: --
99: hr_utility.set_location('Entered '||l_proc,5);
100: hr_utility.set_location('. Asg_ID:'||p_assignment_id||'. BG_ID:'||p_business_group_id||'. Date:'||p_date_earned,20);
101:
102: open pay_asg_act_csr;
103: fetch pay_asg_act_csr into l_asg_action_id, l_pay_action_date;
104:
102: open pay_asg_act_csr;
103: fetch pay_asg_act_csr into l_asg_action_id, l_pay_action_date;
104:
105: if pay_asg_act_csr%notfound then
106: hr_utility.set_location('Could not find Previous payroll run this period',40);
107:
108: close pay_asg_act_csr;
109: g_prior_asg_action_id := -1;
110: g_prior_pay_action_date := NULL;
109: g_prior_asg_action_id := -1;
110: g_prior_pay_action_date := NULL;
111:
112: else
113: hr_utility.set_location('Found previous payroll run this period',60);
114:
115: close pay_asg_act_csr;
116: g_prior_asg_action_id := l_asg_action_id;
117: g_prior_pay_action_date := l_pay_action_date;
157: and prrv.run_result_id = prr.run_result_id;
158: --
159: begin
160: --
161: hr_utility.set_location('Entered '||l_proc,5);
162: hr_utility.set_location('. Element='||p_element_name||'. IV='||p_input_value_name,20);
163:
164: if g_prior_asg_action_id <> -1 THEN
165: /* Assignment Action was found in initalize_payroll function.
158: --
159: begin
160: --
161: hr_utility.set_location('Entered '||l_proc,5);
162: hr_utility.set_location('. Element='||p_element_name||'. IV='||p_input_value_name,20);
163:
164: if g_prior_asg_action_id <> -1 THEN
165: /* Assignment Action was found in initalize_payroll function.
166: Run cursor to get record using assignment action stored in global */
167: open pay_run_result_csr;
168: fetch pay_run_result_csr into l_run_result_value;
169:
170: if pay_run_result_csr%found then
171: hr_utility.set_location('. Found run result value='||l_run_result_value,40);
172:
173: close pay_run_result_csr;
174: return l_run_result_value;
175: else
172:
173: close pay_run_result_csr;
174: return l_run_result_value;
175: else
176: hr_utility.set_location('. Run Result Not found',60);
177: close pay_run_result_csr;
178: l_run_result_value:= NULL;
179: end if;
180: else /* no prior runs were found this period */
202:
203: --
204: begin
205: --
206: hr_utility.set_location('Entered '||l_proc,5);
207: hr_utility.set_location('. Finding base code for element:'||p_base_element,20);
208:
209: l_base_code_val := get_prior_run_result(p_element_name => p_base_element
210: ,p_input_value_name => 'Base_Code'
203: --
204: begin
205: --
206: hr_utility.set_location('Entered '||l_proc,5);
207: hr_utility.set_location('. Finding base code for element:'||p_base_element,20);
208:
209: l_base_code_val := get_prior_run_result(p_element_name => p_base_element
210: ,p_input_value_name => 'Base_Code'
211: ,p_date_earned => p_date_earned
231: l_proc varchar2(72) := g_package||'.get_base_name';
232: --
233: begin
234: --
235: hr_utility.set_location('Entered '||l_proc,5);
236:
237: l_base_type := hruserdt.get_table_value(p_bus_group_id => p_business_group_id
238: ,p_table_name => 'FR_DEDUCTION_GROUPS'
239: ,p_col_name=> 'Base Balance'
238: ,p_table_name => 'FR_DEDUCTION_GROUPS'
239: ,p_col_name=> 'Base Balance'
240: ,p_row_value => p_group_code);
241:
242: hr_utility.set_location('. Base Type:'||l_base_type,20);
243:
244: return l_base_type;
245:
246: exception
265: l_proc varchar2(72) := g_package||'.get_base_name_cu';
266: --
267: begin
268: --
269: hr_utility.set_location('Entered '||l_proc,5);
270: --
271: l_group_code := get_group_code(p_cu_id);
272: --
273: l_base_type := hruserdt.get_table_value(p_bus_group_id => p_business_group_id
274: ,p_table_name => 'FR_DEDUCTION_GROUPS'
275: ,p_col_name=> 'Base Balance'
276: ,p_row_value => l_group_code);
277:
278: hr_utility.set_location('. Base Type:'||l_base_type,20);
279: hr_utility.set_location('Leaving '||l_proc,25);
280: return l_base_type;
281: exception
282: when no_data_found then
275: ,p_col_name=> 'Base Balance'
276: ,p_row_value => l_group_code);
277:
278: hr_utility.set_location('. Base Type:'||l_base_type,20);
279: hr_utility.set_location('Leaving '||l_proc,25);
280: return l_base_type;
281: exception
282: when no_data_found then
283: fnd_message.set_name('PAY','PAY_74938_SD_NO_BASE_DUCT_GRP');
296: l_proc varchar2(72) := g_package||'.get_group_code';
297: --
298: begin
299: --
300: hr_utility.set_location('Entered '||l_proc,5);
301: select group_code
302: into l_group_code
303: from pay_fr_contribution_usages
304: where contribution_usage_id = p_cu_id;
302: into l_group_code
303: from pay_fr_contribution_usages
304: where contribution_usage_id = p_cu_id;
305: --
306: hr_utility.set_location('. Group Code:'||l_group_code,20);
307: hr_utility.set_location('Leaving '||l_proc,25);
308: return l_group_code;
309: exception
310: when no_data_found then null; /* calling fn handles error */
303: from pay_fr_contribution_usages
304: where contribution_usage_id = p_cu_id;
305: --
306: hr_utility.set_location('. Group Code:'||l_group_code,20);
307: hr_utility.set_location('Leaving '||l_proc,25);
308: return l_group_code;
309: exception
310: when no_data_found then null; /* calling fn handles error */
311: end get_group_code;
321: l_proc varchar2(72) := g_package||'.get_base_value';
322: --
323: begin
324: --
325: hr_utility.set_location('Entered '||l_proc,5);
326:
327: hr_utility.set_location('Base Type='||p_base_type,10);
328:
329: l_base_value := g_band_table(get_table_index(p_base_type));
323: begin
324: --
325: hr_utility.set_location('Entered '||l_proc,5);
326:
327: hr_utility.set_location('Base Type='||p_base_type,10);
328:
329: l_base_value := g_band_table(get_table_index(p_base_type));
330:
331: hr_utility.set_location('leaving: '||l_proc||' Value='||l_base_value,30);
327: hr_utility.set_location('Base Type='||p_base_type,10);
328:
329: l_base_value := g_band_table(get_table_index(p_base_type));
330:
331: hr_utility.set_location('leaving: '||l_proc||' Value='||l_base_value,30);
332:
333: return l_base_value;
334: exception
335: when no_data_found then
352: l_proc varchar2(72) := g_package||'.get_rate_value';
353: --
354: begin
355: --
356: hr_utility.set_location('Entered '||l_proc,5);
357: hr_utility.set_location('. Rate Type='||p_rate_type,10);
358:
359: l_rate_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
360: ,p_table_name => 'FR_CONTRIBUTION_RATES'
353: --
354: begin
355: --
356: hr_utility.set_location('Entered '||l_proc,5);
357: hr_utility.set_location('. Rate Type='||p_rate_type,10);
358:
359: l_rate_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
360: ,p_table_name => 'FR_CONTRIBUTION_RATES'
361: ,p_col_name=> 'Value (EUR)'
360: ,p_table_name => 'FR_CONTRIBUTION_RATES'
361: ,p_col_name=> 'Value (EUR)'
362: ,p_row_value => p_rate_type));
363:
364: hr_utility.set_location('Leaving: '||l_proc||'. Rate='||l_rate_value,40);
365:
366: return l_rate_value;
367: exception
368: when no_data_found then
402: and (cu.business_group_id is NULL or cu.business_group_id = p_business_group_id);
403:
404:
405: begin
406: hr_utility.set_location('Entered pay_fr_general.get_contribution_usage',10);
407: hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
408: hr_utility.set_location('. p_process_type='||p_process_type,12);
409: hr_utility.set_location('. p_usage_type='||p_usage_type,13);
410:
403:
404:
405: begin
406: hr_utility.set_location('Entered pay_fr_general.get_contribution_usage',10);
407: hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
408: hr_utility.set_location('. p_process_type='||p_process_type,12);
409: hr_utility.set_location('. p_usage_type='||p_usage_type,13);
410:
411: OPEN contribution_usages_csr;
404:
405: begin
406: hr_utility.set_location('Entered pay_fr_general.get_contribution_usage',10);
407: hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
408: hr_utility.set_location('. p_process_type='||p_process_type,12);
409: hr_utility.set_location('. p_usage_type='||p_usage_type,13);
410:
411: OPEN contribution_usages_csr;
412: FETCH contribution_usages_csr INTO l_contribution_row;
405: begin
406: hr_utility.set_location('Entered pay_fr_general.get_contribution_usage',10);
407: hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
408: hr_utility.set_location('. p_process_type='||p_process_type,12);
409: hr_utility.set_location('. p_usage_type='||p_usage_type,13);
410:
411: OPEN contribution_usages_csr;
412: FETCH contribution_usages_csr INTO l_contribution_row;
413:
417: ELSE
418: close contribution_usages_csr;
419: end if;
420:
421: hr_utility.set_location('Leaving get_contribution_usage, ID:'||l_contribution_row.contribution_usage_id,100);
422: return l_contribution_row;
423:
424: exception
425: when no_data_found then
423:
424: exception
425: when no_data_found then
426: begin
427: hr_utility.set_location('ERROR: Contribution Usage missing for:'||p_element_name,100);
428: hr_utility.set_location('. Process Type:'||p_process_type||', Usage:'||p_usage_type,110);
429: fnd_message.set_name('PAY','PAY_74918_SD_NO_CNU_DATA');
430: fnd_message.set_token('ET',p_element_name);
431: fnd_message.set_token('PT',p_process_type);
424: exception
425: when no_data_found then
426: begin
427: hr_utility.set_location('ERROR: Contribution Usage missing for:'||p_element_name,100);
428: hr_utility.set_location('. Process Type:'||p_process_type||', Usage:'||p_usage_type,110);
429: fnd_message.set_name('PAY','PAY_74918_SD_NO_CNU_DATA');
430: fnd_message.set_token('ET',p_element_name);
431: fnd_message.set_token('PT',p_process_type);
432: fnd_message.set_token('UT',p_usage_type);
465:
466: l_proc varchar2(72) := g_package||'.GET_RATE_VALUE';
467:
468: begin
469: hr_utility.set_location('Entered pay_fr_general.get_rate_type',10);
470: hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
471: hr_utility.set_location('. p_process_type='||g_process_type,12);
472: hr_utility.set_location('. p_usage_type='||p_usage_type,13);
473: hr_utility.set_location('. p_date_earnd='||p_date_earned,14);
466: l_proc varchar2(72) := g_package||'.GET_RATE_VALUE';
467:
468: begin
469: hr_utility.set_location('Entered pay_fr_general.get_rate_type',10);
470: hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
471: hr_utility.set_location('. p_process_type='||g_process_type,12);
472: hr_utility.set_location('. p_usage_type='||p_usage_type,13);
473: hr_utility.set_location('. p_date_earnd='||p_date_earned,14);
474: hr_utility.set_location('. p_assignt_id='||p_assignment_id,15);
467:
468: begin
469: hr_utility.set_location('Entered pay_fr_general.get_rate_type',10);
470: hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
471: hr_utility.set_location('. p_process_type='||g_process_type,12);
472: hr_utility.set_location('. p_usage_type='||p_usage_type,13);
473: hr_utility.set_location('. p_date_earnd='||p_date_earned,14);
474: hr_utility.set_location('. p_assignt_id='||p_assignment_id,15);
475: hr_utility.set_location('. p_bus_grp_id='||p_business_group_id,16);
468: begin
469: hr_utility.set_location('Entered pay_fr_general.get_rate_type',10);
470: hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
471: hr_utility.set_location('. p_process_type='||g_process_type,12);
472: hr_utility.set_location('. p_usage_type='||p_usage_type,13);
473: hr_utility.set_location('. p_date_earnd='||p_date_earned,14);
474: hr_utility.set_location('. p_assignt_id='||p_assignment_id,15);
475: hr_utility.set_location('. p_bus_grp_id='||p_business_group_id,16);
476: l_contribution_usage_row:= get_contribution_usage(
469: hr_utility.set_location('Entered pay_fr_general.get_rate_type',10);
470: hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
471: hr_utility.set_location('. p_process_type='||g_process_type,12);
472: hr_utility.set_location('. p_usage_type='||p_usage_type,13);
473: hr_utility.set_location('. p_date_earnd='||p_date_earned,14);
474: hr_utility.set_location('. p_assignt_id='||p_assignment_id,15);
475: hr_utility.set_location('. p_bus_grp_id='||p_business_group_id,16);
476: l_contribution_usage_row:= get_contribution_usage(
477: p_process_type => g_process_type
470: hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
471: hr_utility.set_location('. p_process_type='||g_process_type,12);
472: hr_utility.set_location('. p_usage_type='||p_usage_type,13);
473: hr_utility.set_location('. p_date_earnd='||p_date_earned,14);
474: hr_utility.set_location('. p_assignt_id='||p_assignment_id,15);
475: hr_utility.set_location('. p_bus_grp_id='||p_business_group_id,16);
476: l_contribution_usage_row:= get_contribution_usage(
477: p_process_type => g_process_type
478: ,p_element_name => p_element_name
471: hr_utility.set_location('. p_process_type='||g_process_type,12);
472: hr_utility.set_location('. p_usage_type='||p_usage_type,13);
473: hr_utility.set_location('. p_date_earnd='||p_date_earned,14);
474: hr_utility.set_location('. p_assignt_id='||p_assignment_id,15);
475: hr_utility.set_location('. p_bus_grp_id='||p_business_group_id,16);
476: l_contribution_usage_row:= get_contribution_usage(
477: p_process_type => g_process_type
478: ,p_element_name => p_element_name
479: ,p_usage_type => p_usage_type
491:
492: l_rate_value := get_cached_rate(p_assignment_id,l_contribution_usage_id, p_tax_unit_id,
493: l_contribution_code, l_code_rate_id, l_rate_type);
494: if l_rate_value is null then -- { no cached rate
495: hr_utility.set_location('Entered '||l_proc,40);
496: if p_override_rate is null then
497: l_rate_value := get_rate_value(p_business_group_id,l_rate_type);
498: else
499: l_rate_value := p_override_rate;
496: if p_override_rate is null then
497: l_rate_value := get_rate_value(p_business_group_id,l_rate_type);
498: else
499: l_rate_value := p_override_rate;
500: hr_utility.set_location('Using Override Rate Value:'||p_override_rate,80);
501: end if;
502: maintain_rate_cache(l_contribution_usage_id, p_tax_unit_id, l_contribution_code,
503: l_rate_value, l_code_rate_id, l_rate_type);
504: end if;
502: maintain_rate_cache(l_contribution_usage_id, p_tax_unit_id, l_contribution_code,
503: l_rate_value, l_code_rate_id, l_rate_type);
504: end if;
505:
506: hr_utility.set_location('Leaving get_contribution_usage rate value:'||l_rate_value,100);
507: return l_rate_value;
508:
509: end get_rate_value;
510:
570: l_proc varchar2(72) := g_package||'.sub_contrib_code';
571: --
572: begin
573: --
574: hr_utility.set_location('Entered '||l_proc,5);
575:
576: /* Some contributions do not have a contribution code, therefore do not substitute */
577: if p_contribution_code is NULL then
578: return null;
591: ELSE /* Must be ARRCO */
592: l_full_code := '4'|| l_base_code || substr(p_contribution_code,6,2);
593: END IF;
594:
595: hr_utility.set_location('Leaving, code found='||l_full_code,50);
596: return l_full_code;
597:
598: ELSE
599: /* Contribution Type was not one of the four valid values */
629: l_proc varchar2(72) := g_package||'.get_payroll_name';
630: --
631: begin
632: --
633: hr_utility.set_location('Entered '||l_proc,5);
634: hr_utility.set_location('. Message Name: '||p_message_name,40);
635:
636: fnd_message.set_name('PAY', p_message_name);
637:
630: --
631: begin
632: --
633: hr_utility.set_location('Entered '||l_proc,5);
634: hr_utility.set_location('. Message Name: '||p_message_name,40);
635:
636: fnd_message.set_name('PAY', p_message_name);
637:
638: if p_token1 is not null then
640: l_colon_position := instr(p_token1,':');
641: l_token_name := substr(p_token1,1,l_colon_position-1);
642: l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
643: fnd_message.set_token(l_token_name, l_token_value);
644: hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
645: end if;
646:
647: if p_token2 is not null then
648: /* Obtain token 2 name and value */
649: l_colon_position := instr(p_token2,':');
650: l_token_name := substr(p_token2,1,l_colon_position-1);
651: l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
652: fnd_message.set_token(l_token_name, l_token_value);
653: hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
654: end if;
655:
656: if p_token3 is not null then
657: /* Obtain token 3 name and value */
658: l_colon_position := instr(p_token3,':');
659: l_token_name := substr(p_token3,1,l_colon_position-1);
660: l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
661: fnd_message.set_token(l_token_name, l_token_value);
662: hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
663: end if;
664:
665: l_message := substrb(fnd_message.get,1,250);
666:
663: end if;
664:
665: l_message := substrb(fnd_message.get,1,250);
666:
667: hr_utility.set_location('leaving '||l_proc,100);
668:
669: return l_message;
670: end get_payroll_message;
671:
699: where act_child.assignment_action_id = p_asg_action_id
700: and act_parent.assignment_action_id (+) = act_child.source_action_id;
701: begin
702: --
703: hr_utility.set_location('Entered '||l_proc,5);
704: -- First check if this is a supplementary run. Call consumed entry
705: -- only on change of (grand) parent action.
706: open csr_parent_action;
707: fetch csr_parent_action into l_parent_action_id;
715: ,p_payroll_id => p_payroll_id
716: ,p_ele_entry_id => p_orig_entry_id) = 'Y'
717: then
718: -- return with error status
719: hr_utility.set_location(' Leaving '||l_proc,10);
720: return 1;
721: end if;
722: -- 115.46 Also clear rate cache on change of (grand) parent action
723: g_deduction_rates.delete;
746: ,p_business_group_id => p_business_group_id
747: ,p_tax_unit_id => p_tax_unit_id -- Tax_Unit_id is Establishment_ID
748: ,p_orig_entry_id => p_orig_entry_id );
749:
750: hr_utility.set_location('leaving pay_fr_general.initialize_payroll',50);
751: return 0;
752: end initialize_payroll;
753:
754:
770: l_proc varchar2(72) := g_package||'.get_urssaf_base_code';
771: --
772: begin
773: --
774: hr_utility.set_location('Entered '||l_proc,5);
775:
776: /* Check to see if run already exists this period and if it does obtain base_code from the input value
777: of the URSSAF_BASES element */
778:
781: ,p_assignment_id => p_assignment_id
782: ,p_business_group_id => p_business_group_id);
783:
784: IF l_base_code is null then
785: hr_utility.set_location('Estab Format No:'''||p_estab_format_number||''' ',50);
786: hr_utility.set_location('Estab Wrk Accident Ord No:'''||p_estab_work_accident_order_no||''' ',51);
787: l_base_code := P_ESTAB_FORMAT_NUMBER || P_ESTAB_WORK_ACCIDENT_ORDER_NO;
788: ELSE
789: l_base_code := substr(l_base_code,1,1)||P_ESTAB_WORK_ACCIDENT_ORDER_NO;
782: ,p_business_group_id => p_business_group_id);
783:
784: IF l_base_code is null then
785: hr_utility.set_location('Estab Format No:'''||p_estab_format_number||''' ',50);
786: hr_utility.set_location('Estab Wrk Accident Ord No:'''||p_estab_work_accident_order_no||''' ',51);
787: l_base_code := P_ESTAB_FORMAT_NUMBER || P_ESTAB_WORK_ACCIDENT_ORDER_NO;
788: ELSE
789: l_base_code := substr(l_base_code,1,1)||P_ESTAB_WORK_ACCIDENT_ORDER_NO;
790: END IF;
788: ELSE
789: l_base_code := substr(l_base_code,1,1)||P_ESTAB_WORK_ACCIDENT_ORDER_NO;
790: END IF;
791:
792: hr_utility.set_location('URSSAF base code:'||l_base_code,20);
793:
794:
795: /* WRITE VALUE TO PL/SQL TABLE TO BE USED BY CONTRIBUTIONS PROCESS */
796: l_index := get_table_index('URSSAF');
810: end;
811:
812: g_base_code_table(l_index) := l_base_code;
813:
814: hr_utility.set_location('URSSAF base code written to PL/SQL table',50);
815:
816: return l_base_code;
817:
818: end get_urssaf_base_code;
835: l_proc varchar2(72) := g_package||'.get_assedic_base_code';
836: --
837: begin
838: --
839: hr_utility.set_location('Entered '||l_proc,5);
840:
841: /* Check to see if run already exists this period and if it does obtain base_code
842: from the input value of the ASSEDIC_BASES element */
843:
850: l_base_code := P_ESTAB_ASSEDIC_ORDER_NUMBER;
851:
852: END IF;
853:
854: hr_utility.set_location('ASSEDIC base code:'||l_base_code,20);
855:
856:
857: /* WRITE VALUE TO PL/SQL TABLE TO BE USED BY CONTRIBUTIONS PROCESS */
858: l_index := get_table_index('ASSEDIC');
872: end;
873:
874: g_base_code_table(l_index) := l_base_code;
875:
876: hr_utility.set_location('ASSEDIC base code written to PL/SQL table',50);
877:
878:
879: return l_base_code;
880:
922: l_existing_row varchar2(4);
923: l_index number;
924: --
925: begin
926: hr_utility.set_location('Entered get_pension_base_code, TYPE='||p_provider_type,10);
927:
928: /* Check to see if run already exists this period and if it does obtain base_code from the input value
929: of the BASES element */
930:
936: IF l_base_code is null then
937: /* No previous run found so obtain the latest information */
938:
939: IF p_emp_pension_provider_id <> -1 THEN
940: hr_utility.set_location('Provider set on Pension Element is:'||p_emp_pension_provider_id,15);
941: hr_utility.set_location('Establishment ='||p_establishment_id,15);
942: /* The provider has been set on the Pension Information Element
943: therefore obtain the estab info for that provider */
944:
937: /* No previous run found so obtain the latest information */
938:
939: IF p_emp_pension_provider_id <> -1 THEN
940: hr_utility.set_location('Provider set on Pension Element is:'||p_emp_pension_provider_id,15);
941: hr_utility.set_location('Establishment ='||p_establishment_id,15);
942: /* The provider has been set on the Pension Information Element
943: therefore obtain the estab info for that provider */
944:
945: open estab_pen_prvs_csr;
952: fnd_message.raise_error;
953: else
954: close estab_pen_prvs_csr;
955: end if;
956: hr_utility.set_location('Order Number Found:'||l_order_number,20);
957:
958: ELSE /* find the default pension provider from the estab eit i.e. Provider not set on Pension element*/
959:
960: hr_utility.set_location('About to obtain default pension provider from Estab:'||p_establishment_id,30);
956: hr_utility.set_location('Order Number Found:'||l_order_number,20);
957:
958: ELSE /* find the default pension provider from the estab eit i.e. Provider not set on Pension element*/
959:
960: hr_utility.set_location('About to obtain default pension provider from Estab:'||p_establishment_id,30);
961:
962: open estab_default_pen_prvs_csr;
963: fetch estab_default_pen_prvs_csr into l_order_number;
964:
978: fnd_message.set_name('PAY','PAY_74916_SD_BAD_DFLT_PEN_PRVS');
979: fnd_message.set_token('TYPE',p_provider_type);
980: fnd_message.raise_error;
981: ELSE
982: hr_utility.set_location('Order Number Found:'||l_order_number,39);
983: close estab_default_pen_prvs_csr;
984: end if;
985: end if;
986: END IF; /* end of obtaining default provider */
1007: end;
1008:
1009: g_base_code_table(l_index) := l_base_code;
1010:
1011: hr_utility.set_location(p_provider_type||' base code written to PL/SQL table',50);
1012:
1013:
1014: return l_base_code;
1015:
1035: l_ytd_high_value number;
1036: l_run_band number;
1037: --
1038: begin
1039: hr_utility.set_location('Entered get_contribution_band, band_type= '||p_band_type,0);
1040: hr_utility.set_location('SS_ceiling_YTD: '||p_ytd_ss_ceiling||', ytd_base:'||p_ytd_base||', ytd_band: '||p_ytd_band,1);
1041:
1042: l_band_low_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1043: ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1036: l_run_band number;
1037: --
1038: begin
1039: hr_utility.set_location('Entered get_contribution_band, band_type= '||p_band_type,0);
1040: hr_utility.set_location('SS_ceiling_YTD: '||p_ytd_ss_ceiling||', ytd_base:'||p_ytd_base||', ytd_band: '||p_ytd_band,1);
1041:
1042: l_band_low_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1043: ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1044: ,p_col_name=> 'LOW_VALUE'
1042: l_band_low_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1043: ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1044: ,p_col_name=> 'LOW_VALUE'
1045: ,p_row_value => p_band_type));
1046: hr_utility.set_location('Low value found= '||l_band_low_value,4);
1047:
1048: l_band_high_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1049: ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1050: ,p_col_name=> 'HIGH_VALUE'
1048: l_band_high_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1049: ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1050: ,p_col_name=> 'HIGH_VALUE'
1051: ,p_row_value => p_band_type));
1052: hr_utility.set_location('High value found= '||l_band_high_value,6);
1053:
1054: --
1055: l_ytd_low_value := p_ytd_ss_ceiling * l_band_low_value;
1056: l_ytd_high_value := p_ytd_ss_ceiling * l_band_high_value;
1066: end if;
1067:
1068: l_run_band := round(l_run_band,2);
1069:
1070: hr_utility.set_location('Band: '||p_band_type||' Value calculated= '||l_run_band,8);
1071: --
1072: return l_run_band;
1073: end get_contribution_band;
1074:
1086: l_new_ytd_p3_band number;
1087: l_ytd_gmp_value number;
1088: l_run_gmp_band number;
1089: begin
1090: hr_utility.set_location('Entered pay_fr_general.get_gmp_band',10);
1091:
1092: hr_utility.set_location('YTD GMP ceiling:'||p_ytd_gmp_ceiling,25);
1093: l_new_ytd_p3_band := p_ytd_p3_band + p_run_p3_band;
1094: hr_utility.set_location('YTD P3 band:'||l_new_ytd_p3_band,30);
1088: l_run_gmp_band number;
1089: begin
1090: hr_utility.set_location('Entered pay_fr_general.get_gmp_band',10);
1091:
1092: hr_utility.set_location('YTD GMP ceiling:'||p_ytd_gmp_ceiling,25);
1093: l_new_ytd_p3_band := p_ytd_p3_band + p_run_p3_band;
1094: hr_utility.set_location('YTD P3 band:'||l_new_ytd_p3_band,30);
1095:
1096: if p_ytd_gmp_ceiling > l_new_ytd_p3_band then
1090: hr_utility.set_location('Entered pay_fr_general.get_gmp_band',10);
1091:
1092: hr_utility.set_location('YTD GMP ceiling:'||p_ytd_gmp_ceiling,25);
1093: l_new_ytd_p3_band := p_ytd_p3_band + p_run_p3_band;
1094: hr_utility.set_location('YTD P3 band:'||l_new_ytd_p3_band,30);
1095:
1096: if p_ytd_gmp_ceiling > l_new_ytd_p3_band then
1097: l_ytd_gmp_value := p_ytd_gmp_ceiling - l_new_ytd_p3_band;
1098: else
1098: else
1099: l_ytd_gmp_value := 0;
1100: end if;
1101:
1102: hr_utility.set_location('YTD GMP band:'||l_ytd_gmp_value,35);
1103:
1104: l_run_gmp_band := l_ytd_gmp_value - p_ytd_gmp_band;
1105:
1106: l_run_gmp_band := round(l_run_gmp_band,2);
1104: l_run_gmp_band := l_ytd_gmp_value - p_ytd_gmp_band;
1105:
1106: l_run_gmp_band := round(l_run_gmp_band,2);
1107:
1108: hr_utility.set_location('GMP Band Run Value calculated: '||l_run_gmp_band,50);
1109:
1110: return l_run_gmp_band;
1111: end get_gmp_band;
1112:
1125: l_run_band number;
1126:
1127: begin
1128: --
1129: hr_utility.set_location('Entered fr_get_salary_tax_band, band_type= '||p_band_type,10);
1130: hr_utility.set_location('Base PTD='||p_ptd_base||', Band PTD='||p_ptd_band,15);
1131:
1132: l_low_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1133: ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1126:
1127: begin
1128: --
1129: hr_utility.set_location('Entered fr_get_salary_tax_band, band_type= '||p_band_type,10);
1130: hr_utility.set_location('Base PTD='||p_ptd_base||', Band PTD='||p_ptd_band,15);
1131:
1132: l_low_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1133: ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1134: ,p_col_name=> 'LOW_VALUE'
1136: l_high_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1137: ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1138: ,p_col_name=> 'HIGH_VALUE'
1139: ,p_row_value => p_band_type));
1140: hr_utility.set_location('low band='||l_low_value||', high_band='||l_high_value,20);
1141:
1142:
1143: l_low_value := l_low_value / 12;
1144: l_high_value := l_high_value / 12;
1142:
1143: l_low_value := l_low_value / 12;
1144: l_high_value := l_high_value / 12;
1145: --
1146: hr_utility.set_location('low band='||l_low_value||', high_band='||l_high_value,20);
1147: if p_ptd_base < l_low_value then
1148: l_run_band :=0;
1149: elsif p_ptd_base < l_high_value then
1150: l_run_band := p_ptd_base - p_ptd_band - l_low_value;
1153: end if;
1154:
1155: l_run_band := round(l_run_band,2);
1156:
1157: hr_utility.set_location('Band Value: '||l_run_band,50);
1158:
1159: return l_run_band;
1160: --
1161: end get_salary_tax_band;
1190: end;
1191:
1192: g_band_table(l_index) := p_value;
1193:
1194: hr_utility.set_location('WRITE_BASE_BAND: '||p_name||', value: '||p_value||', Index: '||l_index,500);
1195:
1196: return 0; -- Return 0 to indicate success
1197: end write_base_bands;
1198:
1288: open first_sickness_abs_csr;
1289: fetch first_sickness_abs_csr into l_first_absence_day;
1290:
1291: if first_sickness_abs_csr%notfound then
1292: hr_utility.set_location('Could not find any absence in year starting:'||to_char(p_start_of_year),10);
1293:
1294: close first_sickness_abs_csr;
1295: return p_start_of_year;
1296: else
1293:
1294: close first_sickness_abs_csr;
1295: return p_start_of_year;
1296: else
1297: hr_utility.set_location('First Absence found was:'||to_char(l_first_absence_day),20);
1298: l_first_absence_day := l_first_absence_day -1;
1299: hr_utility.set_location('Prior day was:'||to_char(l_first_absence_day),30);
1300:
1301: close first_sickness_abs_csr;
1295: return p_start_of_year;
1296: else
1297: hr_utility.set_location('First Absence found was:'||to_char(l_first_absence_day),20);
1298: l_first_absence_day := l_first_absence_day -1;
1299: hr_utility.set_location('Prior day was:'||to_char(l_first_absence_day),30);
1300:
1301: close first_sickness_abs_csr;
1302: return l_first_absence_day;
1303: end if;
1359: , 'FR_APEC_AGIRC', 'AGIRC'
1360: , pee2.entry_information1, paa.date_start);
1361:
1362: begin
1363: hr_utility.set_location('Entered get_abs_days, ASG_ID:'||p_assignment_id||', BG_ID:'||p_business_group_id,10);
1364: hr_utility.set_location('. From:'||p_from_date||', To:'||p_to_date,20);
1365:
1366: open count_sickness_abs_csr;
1367: fetch count_sickness_abs_csr into l_abs_days;
1360: , pee2.entry_information1, paa.date_start);
1361:
1362: begin
1363: hr_utility.set_location('Entered get_abs_days, ASG_ID:'||p_assignment_id||', BG_ID:'||p_business_group_id,10);
1364: hr_utility.set_location('. From:'||p_from_date||', To:'||p_to_date,20);
1365:
1366: open count_sickness_abs_csr;
1367: fetch count_sickness_abs_csr into l_abs_days;
1368:
1366: open count_sickness_abs_csr;
1367: fetch count_sickness_abs_csr into l_abs_days;
1368:
1369: if count_sickness_abs_csr%notfound then
1370: hr_utility.set_location('Could not find any absence in period:'||p_from_date||', To:'||p_to_date,10);
1371:
1372: close count_sickness_abs_csr;
1373: l_abs_days := 0;
1374: else
1371:
1372: close count_sickness_abs_csr;
1373: l_abs_days := 0;
1374: else
1375: hr_utility.set_location('Found Absence days='||l_abs_days,20);
1376: close count_sickness_abs_csr;
1377: end if;
1378:
1379: return l_abs_days;
1381: end get_abs_days;
1382: --
1383: begin
1384: --
1385: hr_utility.set_location('Entered '||l_proc,5);
1386: hr_utility.set_location('Abs Days Limit:'||p_abs_days_limit,10);
1387:
1388: /* Get the start of the year and the day where no absences exist up to */
1389: l_start_cal_year := trunc(p_pay_period_start_date,'YEAR');
1382: --
1383: begin
1384: --
1385: hr_utility.set_location('Entered '||l_proc,5);
1386: hr_utility.set_location('Abs Days Limit:'||p_abs_days_limit,10);
1387:
1388: /* Get the start of the year and the day where no absences exist up to */
1389: l_start_cal_year := trunc(p_pay_period_start_date,'YEAR');
1390:
1387:
1388: /* Get the start of the year and the day where no absences exist up to */
1389: l_start_cal_year := trunc(p_pay_period_start_date,'YEAR');
1390:
1391: hr_utility.set_location('Start of Payroll Year: '||l_start_cal_year,10);
1392:
1393: l_non_abs_day := get_non_absence_day(p_start_of_year => l_start_cal_year,
1394: p_assignment_id => p_assignment_id,
1395: p_business_group_id => p_business_group_id);
1392:
1393: l_non_abs_day := get_non_absence_day(p_start_of_year => l_start_cal_year,
1394: p_assignment_id => p_assignment_id,
1395: p_business_group_id => p_business_group_id);
1396: hr_utility.set_location('First non absence day: '||l_non_abs_day,20);
1397:
1398: if l_non_abs_day < p_pay_period_start_date then
1399: hr_utility.set_location('Rolling Year Has been rest',30);
1400: /* The rolling year has been reset prior to this period. Therefore get the
1395: p_business_group_id => p_business_group_id);
1396: hr_utility.set_location('First non absence day: '||l_non_abs_day,20);
1397:
1398: if l_non_abs_day < p_pay_period_start_date then
1399: hr_utility.set_location('Rolling Year Has been rest',30);
1400: /* The rolling year has been reset prior to this period. Therefore get the
1401: absence day count from the sart of the new rolling period to the end
1402: of this month */
1403: l_num_rolling_abs_days := get_abs_days(p_from_date => l_non_abs_day,
1404: p_to_date => p_pay_period_end_date,
1405: p_assignment_id => p_assignment_id,
1406: p_business_group_id => p_business_group_id);
1407: else
1408: hr_utility.set_location('Still in Rolling year',40);
1409: /* Otherwise the rolling year is still continuing from the previous year.
1410: Therefore get the rolling absence count start from the previous years first
1411: absence. Up to whichever occurs first of, the end of the payroll period or
1412: the first non absence day this year. */
1414:
1415: l_non_abs_day_prev_yr := get_non_absence_day(p_start_of_year => l_start_prev_cal_year
1416: ,p_assignment_id => p_assignment_id
1417: ,p_business_group_id => p_business_group_id);
1418: hr_utility.set_location('Previous year first non absence day: '||l_non_abs_day_prev_yr,50);
1419:
1420: l_num_rolling_abs_days := get_abs_days(p_from_date => l_non_abs_day_prev_yr
1421: ,p_to_date => least(l_non_abs_day,p_pay_period_end_date)
1422: ,p_assignment_id => p_assignment_id
1420: l_num_rolling_abs_days := get_abs_days(p_from_date => l_non_abs_day_prev_yr
1421: ,p_to_date => least(l_non_abs_day,p_pay_period_end_date)
1422: ,p_assignment_id => p_assignment_id
1423: ,p_business_group_id => p_business_group_id);
1424: hr_utility.set_location('Rolling year absence days: '||l_num_rolling_abs_days,60);
1425:
1426: end if;
1427:
1428:
1434: else
1435: l_days_over_limit := 0;
1436: end if;
1437:
1438: hr_utility.set_location('Days over Limit: '||l_days_over_limit,200);
1439: return l_days_over_limit;
1440:
1441: end get_days_over_pension_limit;
1442:
1482: l_proc varchar2(72) := g_package||'.GET_CONTRIBUTION_INFO';
1483: --
1484: begin
1485: --
1486: hr_utility.set_location('Entered '||l_proc,5);
1487: hr_utility.set_location('. Element: '||p_element_name,10);
1488:
1489: l_group_code := l_contribution_usage_row.group_code;
1490: l_rate_type := l_contribution_usage_row.rate_type;
1483: --
1484: begin
1485: --
1486: hr_utility.set_location('Entered '||l_proc,5);
1487: hr_utility.set_location('. Element: '||p_element_name,10);
1488:
1489: l_group_code := l_contribution_usage_row.group_code;
1490: l_rate_type := l_contribution_usage_row.rate_type;
1491: l_contribution_code := l_contribution_usage_row.contribution_code;
1494: l_code_rate_id := l_contribution_usage_row.code_rate_id;
1495: l_rate_category := l_contribution_usage_row.rate_category;
1496: l_element_name := l_contribution_usage_row.element_name;
1497:
1498: hr_utility.set_location('. group code:'||l_group_code,20);
1499: hr_utility.set_location('. rate type:'||l_rate_type,25);
1500:
1501: /* Call function to substitute base code into contribution code */
1502: if l_contribution_code is not null then
1495: l_rate_category := l_contribution_usage_row.rate_category;
1496: l_element_name := l_contribution_usage_row.element_name;
1497:
1498: hr_utility.set_location('. group code:'||l_group_code,20);
1499: hr_utility.set_location('. rate type:'||l_rate_type,25);
1500:
1501: /* Call function to substitute base code into contribution code */
1502: if l_contribution_code is not null then
1503: l_contribution_code := sub_contrib_code(
1506: elsif p_contribution_code is not null then
1507: -- use template code passed in, with extra validation
1508: l_contribution_code := substitute_code(p_contribution_code);
1509: end if;
1510: hr_utility.set_location('. Contribution code:'||l_contribution_code,28);
1511:
1512: l_base_name := get_base_name(p_business_group_id, l_group_code);
1513:
1514: hr_utility.set_location('. Base_name ='||l_base_name,35);
1510: hr_utility.set_location('. Contribution code:'||l_contribution_code,28);
1511:
1512: l_base_name := get_base_name(p_business_group_id, l_group_code);
1513:
1514: hr_utility.set_location('. Base_name ='||l_base_name,35);
1515:
1516: p_base := get_base_value(l_base_name);
1517:
1518: -- check to see if this rate has been used for this assignment in previous subruns
1521:
1522: l_rate_value := get_cached_rate(p_assignment_id,l_contribution_usage_id, p_tax_unit_id,
1523: l_contribution_code, l_code_rate_id, l_rate_type);
1524: if l_rate_value is null then -- { no cached rate
1525: hr_utility.set_location('Entered '||l_proc,40);
1526: if p_override_rate is null then
1527: l_rate_value := get_rate_value(p_business_group_id,l_rate_type);
1528: else
1529: l_rate_value := p_override_rate;
1526: if p_override_rate is null then
1527: l_rate_value := get_rate_value(p_business_group_id,l_rate_type);
1528: else
1529: l_rate_value := p_override_rate;
1530: hr_utility.set_location('Using Override Rate Value:'||p_override_rate,80);
1531: end if;
1532: maintain_rate_cache(l_contribution_usage_id, p_tax_unit_id, l_contribution_code,
1533: l_rate_value, l_code_rate_id, l_rate_type);
1534: end if;
1541: p_contribution_usage_id := l_contribution_usage_id;
1542: p_contribution_code := l_contribution_code;
1543:
1544: -- output this line to g_summary_deductions plsql table
1545: hr_utility.set_location('calling maintain_summary_deduction. code_rate_id:'||to_char(l_code_rate_id),90);
1546: maintain_summary_deduction(
1547: p_rate => l_rate_value
1548: , p_base_type => ltrim(l_base_name)
1549: , p_base => p_base
1557: , p_element_name => l_element_name
1558: );
1559:
1560:
1561: hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1562: --hr_utility.set_location('Leaving '||l_proc||'. base='||to_char(p_base),101);
1563: --hr_utility.set_location('Leaving '||l_proc||'. rate='||to_char(p_rate),102);
1564: --hr_utility.set_location('Leaving '||l_proc||'. contribution_code='||p_contribution_code,103);
1565: --hr_utility.set_location('Leaving '||l_proc||'. contribution_usage_id='||to_char(p_contribution_usage_id),104);
1558: );
1559:
1560:
1561: hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1562: --hr_utility.set_location('Leaving '||l_proc||'. base='||to_char(p_base),101);
1563: --hr_utility.set_location('Leaving '||l_proc||'. rate='||to_char(p_rate),102);
1564: --hr_utility.set_location('Leaving '||l_proc||'. contribution_code='||p_contribution_code,103);
1565: --hr_utility.set_location('Leaving '||l_proc||'. contribution_usage_id='||to_char(p_contribution_usage_id),104);
1566: --hr_utility.set_location('Leaving '||l_proc||'. asg_id='||to_char(p_assignment_id),105);
1559:
1560:
1561: hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1562: --hr_utility.set_location('Leaving '||l_proc||'. base='||to_char(p_base),101);
1563: --hr_utility.set_location('Leaving '||l_proc||'. rate='||to_char(p_rate),102);
1564: --hr_utility.set_location('Leaving '||l_proc||'. contribution_code='||p_contribution_code,103);
1565: --hr_utility.set_location('Leaving '||l_proc||'. contribution_usage_id='||to_char(p_contribution_usage_id),104);
1566: --hr_utility.set_location('Leaving '||l_proc||'. asg_id='||to_char(p_assignment_id),105);
1567: --hr_utility.set_location('Leaving '||l_proc||'. bg_id='||to_char(p_business_group_id),106);
1560:
1561: hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1562: --hr_utility.set_location('Leaving '||l_proc||'. base='||to_char(p_base),101);
1563: --hr_utility.set_location('Leaving '||l_proc||'. rate='||to_char(p_rate),102);
1564: --hr_utility.set_location('Leaving '||l_proc||'. contribution_code='||p_contribution_code,103);
1565: --hr_utility.set_location('Leaving '||l_proc||'. contribution_usage_id='||to_char(p_contribution_usage_id),104);
1566: --hr_utility.set_location('Leaving '||l_proc||'. asg_id='||to_char(p_assignment_id),105);
1567: --hr_utility.set_location('Leaving '||l_proc||'. bg_id='||to_char(p_business_group_id),106);
1568: --hr_utility.set_location('Leaving '||l_proc||'. date_earned='||to_char(p_date_earned),107);
1561: hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1562: --hr_utility.set_location('Leaving '||l_proc||'. base='||to_char(p_base),101);
1563: --hr_utility.set_location('Leaving '||l_proc||'. rate='||to_char(p_rate),102);
1564: --hr_utility.set_location('Leaving '||l_proc||'. contribution_code='||p_contribution_code,103);
1565: --hr_utility.set_location('Leaving '||l_proc||'. contribution_usage_id='||to_char(p_contribution_usage_id),104);
1566: --hr_utility.set_location('Leaving '||l_proc||'. asg_id='||to_char(p_assignment_id),105);
1567: --hr_utility.set_location('Leaving '||l_proc||'. bg_id='||to_char(p_business_group_id),106);
1568: --hr_utility.set_location('Leaving '||l_proc||'. date_earned='||to_char(p_date_earned),107);
1569: --hr_utility.set_location('Leaving '||l_proc||'. element='||p_element_name,108);
1562: --hr_utility.set_location('Leaving '||l_proc||'. base='||to_char(p_base),101);
1563: --hr_utility.set_location('Leaving '||l_proc||'. rate='||to_char(p_rate),102);
1564: --hr_utility.set_location('Leaving '||l_proc||'. contribution_code='||p_contribution_code,103);
1565: --hr_utility.set_location('Leaving '||l_proc||'. contribution_usage_id='||to_char(p_contribution_usage_id),104);
1566: --hr_utility.set_location('Leaving '||l_proc||'. asg_id='||to_char(p_assignment_id),105);
1567: --hr_utility.set_location('Leaving '||l_proc||'. bg_id='||to_char(p_business_group_id),106);
1568: --hr_utility.set_location('Leaving '||l_proc||'. date_earned='||to_char(p_date_earned),107);
1569: --hr_utility.set_location('Leaving '||l_proc||'. element='||p_element_name,108);
1570: --hr_utility.set_location('Leaving '||l_proc||'. usage_type='||p_usage_type,109);
1563: --hr_utility.set_location('Leaving '||l_proc||'. rate='||to_char(p_rate),102);
1564: --hr_utility.set_location('Leaving '||l_proc||'. contribution_code='||p_contribution_code,103);
1565: --hr_utility.set_location('Leaving '||l_proc||'. contribution_usage_id='||to_char(p_contribution_usage_id),104);
1566: --hr_utility.set_location('Leaving '||l_proc||'. asg_id='||to_char(p_assignment_id),105);
1567: --hr_utility.set_location('Leaving '||l_proc||'. bg_id='||to_char(p_business_group_id),106);
1568: --hr_utility.set_location('Leaving '||l_proc||'. date_earned='||to_char(p_date_earned),107);
1569: --hr_utility.set_location('Leaving '||l_proc||'. element='||p_element_name,108);
1570: --hr_utility.set_location('Leaving '||l_proc||'. usage_type='||p_usage_type,109);
1571:
1564: --hr_utility.set_location('Leaving '||l_proc||'. contribution_code='||p_contribution_code,103);
1565: --hr_utility.set_location('Leaving '||l_proc||'. contribution_usage_id='||to_char(p_contribution_usage_id),104);
1566: --hr_utility.set_location('Leaving '||l_proc||'. asg_id='||to_char(p_assignment_id),105);
1567: --hr_utility.set_location('Leaving '||l_proc||'. bg_id='||to_char(p_business_group_id),106);
1568: --hr_utility.set_location('Leaving '||l_proc||'. date_earned='||to_char(p_date_earned),107);
1569: --hr_utility.set_location('Leaving '||l_proc||'. element='||p_element_name,108);
1570: --hr_utility.set_location('Leaving '||l_proc||'. usage_type='||p_usage_type,109);
1571:
1572: return l_contribution_value;
1565: --hr_utility.set_location('Leaving '||l_proc||'. contribution_usage_id='||to_char(p_contribution_usage_id),104);
1566: --hr_utility.set_location('Leaving '||l_proc||'. asg_id='||to_char(p_assignment_id),105);
1567: --hr_utility.set_location('Leaving '||l_proc||'. bg_id='||to_char(p_business_group_id),106);
1568: --hr_utility.set_location('Leaving '||l_proc||'. date_earned='||to_char(p_date_earned),107);
1569: --hr_utility.set_location('Leaving '||l_proc||'. element='||p_element_name,108);
1570: --hr_utility.set_location('Leaving '||l_proc||'. usage_type='||p_usage_type,109);
1571:
1572: return l_contribution_value;
1573: end get_contribution_info;
1566: --hr_utility.set_location('Leaving '||l_proc||'. asg_id='||to_char(p_assignment_id),105);
1567: --hr_utility.set_location('Leaving '||l_proc||'. bg_id='||to_char(p_business_group_id),106);
1568: --hr_utility.set_location('Leaving '||l_proc||'. date_earned='||to_char(p_date_earned),107);
1569: --hr_utility.set_location('Leaving '||l_proc||'. element='||p_element_name,108);
1570: --hr_utility.set_location('Leaving '||l_proc||'. usage_type='||p_usage_type,109);
1571:
1572: return l_contribution_value;
1573: end get_contribution_info;
1574:
1643: l_proc varchar2(72) := g_package||'.GET_WORK_ACCIDENT_CONTRIBUTION';
1644: --
1645: begin
1646: --
1647: hr_utility.set_location('Entered '||l_proc,5);
1648:
1649: hr_utility.set_location('. element name='||p_element_name,10);
1650:
1651: l_group_code := l_contribution_usage_row.group_code;
1645: begin
1646: --
1647: hr_utility.set_location('Entered '||l_proc,5);
1648:
1649: hr_utility.set_location('. element name='||p_element_name,10);
1650:
1651: l_group_code := l_contribution_usage_row.group_code;
1652: l_contribution_code := l_contribution_usage_row.contribution_code;
1653: l_contribution_usage_id := l_contribution_usage_row.contribution_usage_id;
1659: /* Call function to substitute base code into contribution code */
1660: l_contribution_code := sub_contrib_code(p_contribution_type => l_contribution_type
1661: ,p_contribution_code => l_contribution_code);
1662:
1663: hr_utility.set_location('Found element info.code:'||l_contribution_code,20);
1664: hr_utility.set_location('. group code:'||l_group_code,30);
1665:
1666: -- check to see if this rate has been used for this assignment in previous subruns
1667: -- for this assignment, establishment, process_type if so use that rate rather
1660: l_contribution_code := sub_contrib_code(p_contribution_type => l_contribution_type
1661: ,p_contribution_code => l_contribution_code);
1662:
1663: hr_utility.set_location('Found element info.code:'||l_contribution_code,20);
1664: hr_utility.set_location('. group code:'||l_group_code,30);
1665:
1666: -- check to see if this rate has been used for this assignment in previous subruns
1667: -- for this assignment, establishment, process_type if so use that rate rather
1668: -- rederiving the rate for the current contribution.
1669:
1670: l_rate_value := get_cached_rate(p_assignment_id,l_contribution_usage_id, p_tax_unit_id,
1671: l_contribution_code, l_user_column_instance_id, l_risk_code);
1672: if l_rate_value is null then -- { no cached rate
1673: hr_utility.set_location('Entered '||l_proc,40);
1674:
1675: begin
1676: l_rate_value := get_table_rate(p_bus_group_id => p_business_group_id
1677: ,p_table_name => 'FR_WORK_ACCIDENT_RATES'
1686: end;
1687: maintain_rate_cache(l_contribution_usage_id, p_tax_unit_id, l_contribution_code,
1688: l_rate_value,l_user_column_instance_id, l_risk_code);
1689: end if; -- } no cached rate
1690: hr_utility.set_location('Rate value: '||l_rate_value,40);
1691: hr_utility.set_location('l_contribution_code: '||l_contribution_code,40);
1692:
1693: l_base_name := get_base_name(p_business_group_id, l_group_code);
1694:
1687: maintain_rate_cache(l_contribution_usage_id, p_tax_unit_id, l_contribution_code,
1688: l_rate_value,l_user_column_instance_id, l_risk_code);
1689: end if; -- } no cached rate
1690: hr_utility.set_location('Rate value: '||l_rate_value,40);
1691: hr_utility.set_location('l_contribution_code: '||l_contribution_code,40);
1692:
1693: l_base_name := get_base_name(p_business_group_id, l_group_code);
1694:
1695: p_base := get_base_value(l_base_name);
1696:
1697: /* Reduce the rate value by the reduction amount. Used for Part Time Rebate reduction */
1698: if p_reduction_percent is not null then
1699: l_rate_value := l_rate_value * ((100-p_reduction_percent) /100);
1700: hr_utility.set_location('reduced Rate value: '||l_rate_value,42);
1701: end if;
1702:
1703: l_contribution_value := round(p_base * (l_rate_value /100),2);
1704:
1707: p_contribution_usage_id := l_contribution_usage_id;
1708: p_contribution_code := l_contribution_code;
1709:
1710: -- output this line to g_summary_deductions plsql table
1711: hr_utility.set_location('calling maintain_summary_deduction. user_col_instance_id:'||to_char(l_user_column_instance_id),90);
1712: maintain_summary_deduction(
1713: p_rate => l_rate_value
1714: , p_base_type => l_base_name
1715: , p_base => p_base
1722: , p_code_rate_id => null
1723: , p_element_name => l_element_name
1724: );
1725:
1726: hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1727:
1728: return l_contribution_value;
1729:
1730: end get_work_accident_contribution;
1771: l_proc varchar2(72) := g_package||'.GET_TRANSPORT_TAX_CONTRIBUTION';
1772: --
1773: begin
1774: --
1775: hr_utility.set_location('Entered '||l_proc,5);
1776:
1777: l_group_code := l_contribution_usage_row.group_code;
1778: l_contribution_code := l_contribution_usage_row.contribution_code;
1779: l_contribution_usage_id := l_contribution_usage_row.contribution_usage_id;
1781: l_rate_category := l_contribution_usage_row.rate_category;
1782: l_element_name := l_contribution_usage_row.element_name;
1783: l_transport_tax_region := p_transport_tax_region;
1784:
1785: hr_utility.set_location('element info code:'||l_contribution_code,20);
1786: hr_utility.set_location('. group code:'||l_group_code,30);
1787: hr_utility.set_location('Transport Tax Region:'||p_transport_tax_region,40);
1788: hr_utility.set_location('Transport Tax Reduction:'||p_reduction,41);
1789:
1782: l_element_name := l_contribution_usage_row.element_name;
1783: l_transport_tax_region := p_transport_tax_region;
1784:
1785: hr_utility.set_location('element info code:'||l_contribution_code,20);
1786: hr_utility.set_location('. group code:'||l_group_code,30);
1787: hr_utility.set_location('Transport Tax Region:'||p_transport_tax_region,40);
1788: hr_utility.set_location('Transport Tax Reduction:'||p_reduction,41);
1789:
1790: /* Call function to substitute base code into contribution code */
1783: l_transport_tax_region := p_transport_tax_region;
1784:
1785: hr_utility.set_location('element info code:'||l_contribution_code,20);
1786: hr_utility.set_location('. group code:'||l_group_code,30);
1787: hr_utility.set_location('Transport Tax Region:'||p_transport_tax_region,40);
1788: hr_utility.set_location('Transport Tax Reduction:'||p_reduction,41);
1789:
1790: /* Call function to substitute base code into contribution code */
1791: l_contribution_code := sub_contrib_code(p_contribution_type => l_contribution_type
1784:
1785: hr_utility.set_location('element info code:'||l_contribution_code,20);
1786: hr_utility.set_location('. group code:'||l_group_code,30);
1787: hr_utility.set_location('Transport Tax Region:'||p_transport_tax_region,40);
1788: hr_utility.set_location('Transport Tax Reduction:'||p_reduction,41);
1789:
1790: /* Call function to substitute base code into contribution code */
1791: l_contribution_code := sub_contrib_code(p_contribution_type => l_contribution_type
1792: ,p_contribution_code => l_contribution_code);
1797:
1798: l_rate_value := get_cached_rate(p_assignment_id,l_contribution_usage_id, p_tax_unit_id,
1799: l_contribution_code, l_user_column_instance_id, l_transport_tax_region);
1800: if l_rate_value is null then -- { no cached rate
1801: hr_utility.set_location('Entered '||l_proc,50);
1802:
1803:
1804: begin
1805: l_rate_value := get_table_rate(p_bus_group_id => p_business_group_id
1816: maintain_rate_cache(l_contribution_usage_id, p_tax_unit_id, l_contribution_code,
1817: l_rate_value,l_user_column_instance_id, l_transport_tax_region);
1818: end if; -- } no cached rate
1819:
1820: hr_utility.set_location('Rate value: '||l_rate_value,60);
1821:
1822: if p_reduction <> 0 then
1823: l_rate_value := l_rate_value * ((100-p_reduction) /100);
1824: hr_utility.set_location('reduced Rate value: '||l_rate_value,65);
1820: hr_utility.set_location('Rate value: '||l_rate_value,60);
1821:
1822: if p_reduction <> 0 then
1823: l_rate_value := l_rate_value * ((100-p_reduction) /100);
1824: hr_utility.set_location('reduced Rate value: '||l_rate_value,65);
1825: end if;
1826:
1827: l_base_name := get_base_name(p_business_group_id, l_group_code);
1828:
1833: p_rate := l_rate_value;
1834: p_contribution_usage_id := l_contribution_usage_id;
1835: p_contribution_code := l_contribution_code;
1836:
1837: hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1838:
1839: -- output this line to g_summary_deductions plsql table
1840: hr_utility.set_location('calling maintain_summary_deduction. user_col_instance_id:'||to_char(l_user_column_instance_id),90);
1841: maintain_summary_deduction(
1836:
1837: hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1838:
1839: -- output this line to g_summary_deductions plsql table
1840: hr_utility.set_location('calling maintain_summary_deduction. user_col_instance_id:'||to_char(l_user_column_instance_id),90);
1841: maintain_summary_deduction(
1842: p_rate => l_rate_value
1843: , p_base_type => l_base_name
1844: , p_base => p_base
1884: l_proc varchar2(72) := g_package||'.get_fixed_value_contribution';
1885: --
1886: begin
1887: --
1888: hr_utility.set_location('Entered '||l_proc,5);
1889:
1890: l_rate_type := l_contribution_usage_row.rate_type;
1891: l_contribution_code := l_contribution_usage_row.contribution_code;
1892: l_contribution_usage_id := l_contribution_usage_row.contribution_usage_id;
1895: /* Call function to substitute base code into contribution code */
1896: l_contribution_code := sub_contrib_code(p_contribution_type => l_contribution_type
1897: ,p_contribution_code => l_contribution_code);
1898:
1899: hr_utility.set_location('Found element info.code:'||l_contribution_code,15);
1900: hr_utility.set_location('. rate type:'||l_rate_type,25);
1901:
1902: l_rate_value := get_rate_value(p_business_group_id,l_rate_type);
1903:
1896: l_contribution_code := sub_contrib_code(p_contribution_type => l_contribution_type
1897: ,p_contribution_code => l_contribution_code);
1898:
1899: hr_utility.set_location('Found element info.code:'||l_contribution_code,15);
1900: hr_utility.set_location('. rate type:'||l_rate_type,25);
1901:
1902: l_rate_value := get_rate_value(p_business_group_id,l_rate_type);
1903:
1904: p_contribution_usage_id := l_contribution_usage_id;
1903:
1904: p_contribution_usage_id := l_contribution_usage_id;
1905: p_contribution_code := l_contribution_code;
1906:
1907: hr_utility.set_location('Leaving '||l_proc||'. Value='||l_rate_value,100);
1908:
1909: return l_rate_value;
1910: end get_fixed_value_contribution;
1911:
1942: l_proc varchar2(72) := g_package||'.get_reduced_contribution';
1943: --
1944: begin
1945: --
1946: hr_utility.set_location('Entered '||l_proc,5);
1947:
1948: hr_utility.set_location('. element:'||p_element_name,10);
1949:
1950: l_group_code := l_contribution_usage_row.group_code;
1944: begin
1945: --
1946: hr_utility.set_location('Entered '||l_proc,5);
1947:
1948: hr_utility.set_location('. element:'||p_element_name,10);
1949:
1950: l_group_code := l_contribution_usage_row.group_code;
1951: l_rate_type := l_contribution_usage_row.rate_type;
1952: l_contribution_code := l_contribution_usage_row.contribution_code;
1962: -- use template code passed in, with extra validation
1963: l_contribution_code := substitute_code(p_contribution_code);
1964: end if;
1965:
1966: hr_utility.set_location('Found element info.code:'||l_contribution_code,20);
1967: hr_utility.set_location('. Rate type:'||l_rate_type,25);
1968: hr_utility.set_location('. Group code:'||l_group_code,30);
1969:
1970:
1963: l_contribution_code := substitute_code(p_contribution_code);
1964: end if;
1965:
1966: hr_utility.set_location('Found element info.code:'||l_contribution_code,20);
1967: hr_utility.set_location('. Rate type:'||l_rate_type,25);
1968: hr_utility.set_location('. Group code:'||l_group_code,30);
1969:
1970:
1971: l_base_name := get_base_name(p_business_group_id, l_group_code);
1964: end if;
1965:
1966: hr_utility.set_location('Found element info.code:'||l_contribution_code,20);
1967: hr_utility.set_location('. Rate type:'||l_rate_type,25);
1968: hr_utility.set_location('. Group code:'||l_group_code,30);
1969:
1970:
1971: l_base_name := get_base_name(p_business_group_id, l_group_code);
1972:
1976:
1977: /* Reduce the rate value by the reduction amount i.e. Post the reduced rate value */
1978: if p_reduction <> 0 then
1979: l_rate_value := l_rate_value * ((100-p_reduction) /100);
1980: hr_utility.set_location('reduced Rate value: '||l_rate_value,45);
1981: end if;
1982:
1983: l_contribution_value := round(p_base * (l_rate_value /100),2);
1984:
1985: p_rate := l_rate_value;
1986: p_contribution_usage_id := l_contribution_usage_id;
1987: p_contribution_code := l_contribution_code;
1988:
1989: hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1990:
1991: return l_contribution_value;
1992:
1993: end get_reduced_contribution;
2044: --
2045: --
2046: begin
2047: --
2048: hr_utility.set_location('Entered '||l_proc,8);
2049: --
2050: /* Check if a user formula exists - if it does then use values from that, otherwise use defaults
2051: set above */
2052: --
2076: l_inputs(i).value := fnd_date.date_to_canonical(p_effective_date);
2077: elsif l_inputs(i).name = 'ASSIGNMENT_ID' then
2078: l_inputs(i).value := p_assignment_id;
2079: else
2080: hr_utility.set_location('ERROR value = '||l_inputs(i).name ,7);
2081: end if;
2082: end loop;
2083: --
2084: hr_utility.set_location(' Prior to execute the formula',8);
2080: hr_utility.set_location('ERROR value = '||l_inputs(i).name ,7);
2081: end if;
2082: end loop;
2083: --
2084: hr_utility.set_location(' Prior to execute the formula',8);
2085: ff_exec.run_formula (l_inputs
2086: ,l_outputs);
2087: --
2088: hr_utility.set_location(' End run formula',9);
2084: hr_utility.set_location(' Prior to execute the formula',8);
2085: ff_exec.run_formula (l_inputs
2086: ,l_outputs);
2087: --
2088: hr_utility.set_location(' End run formula',9);
2089: --
2090: for l_out_cnt in l_outputs.first..l_outputs.last loop
2091: if l_outputs(l_out_cnt).name = 'P_HOURLY_VALUE' then
2092: l_hourly_value := l_outputs(l_out_cnt).value;
2120: else
2121: fnd_message.set_name('PAY','PAY_74922_SD_BAD_CONV_HRS_FREQ');
2122: fnd_message.set_token('FREQ',p_from_freq_code);
2123: fnd_message.raise_error;
2124: hr_utility.set_location('Error - p_from_freq_code not recognised',10);
2125: end if;
2126: --
2127: End If;
2128: --
2125: end if;
2126: --
2127: End If;
2128: --
2129: hr_utility.set_location('Leaving '||l_proc,100);
2130: --
2131: if p_to_freq_code = 'D' then
2132: return l_hours_in_day;
2133: elsif p_to_freq_code = 'W' then
2224: --
2225: and p_effective_date between pcf.effective_start_date and pcf.effective_end_date;
2226: --
2227: begin
2228: hr_utility.set_location(l_proc,10);
2229: --
2230: /* If there is a formula in the business group named FR_USER_HOURLY_RATE then this will be used */
2231: --
2232: /* This function call returns -1 if the formula was not found */
2236: ,p_business_group_id => p_business_group_id
2237: ,p_effective_start_date => l_start_date);
2238:
2239: If l_formula_id <> -1 then
2240: hr_utility.set_location(l_proc,20);
2241: -- IF parameter list is not null Extract parameters from parameter list
2242: -- Parameters are in the format P1=P1_VALUE,P2=P2_VALUE
2243: --
2244: if p_parameter_list is not null then
2257: l_param := substr(l_param,1,length(l_param)-1);
2258: param_tab(j).name := substr(l_param,1,instr(l_param,'=')-1);
2259: param_tab(j).value :=
2260: substr(l_param,instr(l_param,'=')+1,length(l_param));
2261: hr_utility.trace(p_formula||' '|| param_tab(j).name||' '||param_tab(j).value);
2262: else
2263: exit;
2264: end if;
2265: j := j + 1;
2291: end if;
2292: end loop;
2293: end if;
2294: --
2295: hr_utility.set_location(' Prior to execute the formula',8);
2296: hr_utility.set_location(l_proc,30);
2297: ff_exec.run_formula (l_inputs
2298: ,l_outputs);
2299: --
2292: end loop;
2293: end if;
2294: --
2295: hr_utility.set_location(' Prior to execute the formula',8);
2296: hr_utility.set_location(l_proc,30);
2297: ff_exec.run_formula (l_inputs
2298: ,l_outputs);
2299: --
2300: hr_utility.set_location(l_proc,40);
2296: hr_utility.set_location(l_proc,30);
2297: ff_exec.run_formula (l_inputs
2298: ,l_outputs);
2299: --
2300: hr_utility.set_location(l_proc,40);
2301: hr_utility.set_location(' End run formula',9);
2302: --
2303: for l_out_cnt in l_outputs.first..l_outputs.last loop
2304: if l_outputs(l_out_cnt).name = 'HOURLY_PAY_RATE' then
2297: ff_exec.run_formula (l_inputs
2298: ,l_outputs);
2299: --
2300: hr_utility.set_location(l_proc,40);
2301: hr_utility.set_location(' End run formula',9);
2302: --
2303: for l_out_cnt in l_outputs.first..l_outputs.last loop
2304: if l_outputs(l_out_cnt).name = 'HOURLY_PAY_RATE' then
2305: l_hourly_rate := l_outputs(l_out_cnt).value;
2308: --
2309: else
2310: /* User formula not found so try to derive the hourly rate from Salary Admin */
2311: --
2312: hr_utility.set_location(l_proc,50);
2313: --
2314: open c_pay_rate;
2315: fetch c_pay_rate into l_pay_rate,
2316: l_pay_basis,
2319: if c_pay_rate%found then
2320: close c_pay_rate;
2321: if l_pay_basis = 'HOURLY' then
2322: --
2323: hr_utility.set_location(l_proc,60);
2324: --
2325: l_hourly_rate := l_pay_rate;
2326: --
2327: elsif l_pay_basis = 'MONTHLY' then
2325: l_hourly_rate := l_pay_rate;
2326: --
2327: elsif l_pay_basis = 'MONTHLY' then
2328: --
2329: hr_utility.set_location(l_proc,70);
2330: --
2331: /* Convert the normal working hours into a monthly figure so that
2332: it can be compared with the Monthly salary basis */
2333: --
2342: end if;
2343: --
2344: elsif l_pay_basis = 'ANNUAL' then
2345: --
2346: hr_utility.set_location(l_proc,80);
2347: --
2348: /* Convert the normal working hours into an annual figure so that
2349: it can be compared with the Annual salary basis */
2350: --
2359: end if;
2360: end if;
2361: --
2362: else -- c_pay_rate%notfound
2363: hr_utility.set_location(l_proc,90);
2364: close c_pay_rate;
2365: end if;
2366: end if; -- Formula_ID <> -1
2367: --
2369: --
2370: if l_hourly_rate is not null then
2371: return l_hourly_rate;
2372: else
2373: hr_utility.set_location(l_proc,100);
2374: fnd_message.set_name('PAY','PAY_HOURLY_RATE_NOT_DERIVED');
2375: fnd_message.raise_error;
2376: end if;
2377: end get_pay_rate;
2401: l_proc varchar2(72) := g_package||'.get_prev_start_end';
2402: --
2403: begin
2404: --
2405: hr_utility.set_location('Entering '||l_proc,10);
2406: --
2407: if p_payroll_action_id <> nvl(pay_fr_general.g_payroll_action_id,-1) then
2408: --
2409: open csr_get_time_period;
2438: l_proc varchar2(72) := g_package||'.substitute_code';
2439: --
2440: begin
2441: --
2442: hr_utility.set_location('Entered '||l_proc,5);
2443: --
2444: l_code_prefix := SUBSTR(p_contribution_code,1,1);
2445:
2446: if l_code_prefix = '3' then
2478:
2479: if l_code_error_mesg = 'VALID' then
2480: return l_contrib_code;
2481: else
2482: hr_utility.set_location('Error:'||l_code_error_mesg,100);
2483: fnd_message.set_name('PAY',l_code_error_mesg);
2484: fnd_message.raise_error;
2485: end if;
2486:
2562: l_value number := 0;
2563: l_proc varchar2(72) := g_package||'fr_rolling_balance';
2564:
2565: BEGIN
2566: hr_utility.set_location('Entering:'|| l_proc,10);
2567: open csr_def_bal_id;
2568: fetch csr_def_bal_id into l_defined_balance_id;
2569: close csr_def_bal_id;
2570: --
2580: WHEN NO_DATA_FOUND THEN --Bug #2651568
2581: l_value := 0;
2582: END;
2583: i := i + 1;
2584: hr_utility.set_location(' BAL VAL='||l_value, 60);
2585: end loop;
2586: hr_utility.set_location(' FINAL BAL VAL='||l_value, 60);
2587: hr_utility.set_location(' Leaving:'||l_proc, 70);
2588: return l_value;
2582: END;
2583: i := i + 1;
2584: hr_utility.set_location(' BAL VAL='||l_value, 60);
2585: end loop;
2586: hr_utility.set_location(' FINAL BAL VAL='||l_value, 60);
2587: hr_utility.set_location(' Leaving:'||l_proc, 70);
2588: return l_value;
2589:
2590: END;
2583: i := i + 1;
2584: hr_utility.set_location(' BAL VAL='||l_value, 60);
2585: end loop;
2586: hr_utility.set_location(' FINAL BAL VAL='||l_value, 60);
2587: hr_utility.set_location(' Leaving:'||l_proc, 70);
2588: return l_value;
2589:
2590: END;
2591: ---------------------------------------------------------------------
2600: -- function called from formula to fetch the next row from t_summary_deductions table
2601: -- to return as results
2602: l_proc varchar2(72) := g_package||'.get_summary_deduction';
2603: begin
2604: hr_utility.set_location('Entering:'|| l_proc, 10);
2605: begin
2606: if g_summary_idx = 0 then p_pay_value := 0;
2607: else p_pay_value := nvl(g_summary_deductions(g_summary_idx).pay_value,0);
2608: end if;
2611: p_base := g_summary_deductions(g_summary_idx).base;
2612: p_contribution_code := g_summary_deductions(g_summary_idx).contribution_code;
2613: p_contribution_usage_id := g_summary_deductions(g_summary_idx).contribution_usage_id;
2614: exception when no_data_found then
2615: hr_utility.set_location('No data found '|| l_proc, 20);
2616: p_rate := 0 ;
2617: p_base := 0;
2618: p_contribution_code := ' ';
2619: p_contribution_usage_id := 0;
2618: p_contribution_code := ' ';
2619: p_contribution_usage_id := 0;
2620: end;
2621:
2622: hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_idx:'||to_char(g_summary_idx));
2623: hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_deductions.last:'||to_char(g_summary_deductions.last));
2624: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_rate:'||to_char(p_rate));
2625: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_base:'||to_char(p_base));
2626: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_code:'||p_contribution_code);
2619: p_contribution_usage_id := 0;
2620: end;
2621:
2622: hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_idx:'||to_char(g_summary_idx));
2623: hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_deductions.last:'||to_char(g_summary_deductions.last));
2624: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_rate:'||to_char(p_rate));
2625: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_base:'||to_char(p_base));
2626: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_code:'||p_contribution_code);
2627: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_usage_id:'||to_char(p_contribution_usage_id));
2620: end;
2621:
2622: hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_idx:'||to_char(g_summary_idx));
2623: hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_deductions.last:'||to_char(g_summary_deductions.last));
2624: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_rate:'||to_char(p_rate));
2625: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_base:'||to_char(p_base));
2626: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_code:'||p_contribution_code);
2627: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_usage_id:'||to_char(p_contribution_usage_id));
2628: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_pay_value:'||to_char(p_pay_value));
2621:
2622: hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_idx:'||to_char(g_summary_idx));
2623: hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_deductions.last:'||to_char(g_summary_deductions.last));
2624: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_rate:'||to_char(p_rate));
2625: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_base:'||to_char(p_base));
2626: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_code:'||p_contribution_code);
2627: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_usage_id:'||to_char(p_contribution_usage_id));
2628: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_pay_value:'||to_char(p_pay_value));
2629: -- if this is the last row in the table return a Y to stop processing, the indirect return is for the
2622: hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_idx:'||to_char(g_summary_idx));
2623: hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_deductions.last:'||to_char(g_summary_deductions.last));
2624: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_rate:'||to_char(p_rate));
2625: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_base:'||to_char(p_base));
2626: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_code:'||p_contribution_code);
2627: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_usage_id:'||to_char(p_contribution_usage_id));
2628: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_pay_value:'||to_char(p_pay_value));
2629: -- if this is the last row in the table return a Y to stop processing, the indirect return is for the
2630: -- next row so that context aren't changed the direct return is for index - 1 so test that
2623: hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_deductions.last:'||to_char(g_summary_deductions.last));
2624: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_rate:'||to_char(p_rate));
2625: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_base:'||to_char(p_base));
2626: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_code:'||p_contribution_code);
2627: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_usage_id:'||to_char(p_contribution_usage_id));
2628: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_pay_value:'||to_char(p_pay_value));
2629: -- if this is the last row in the table return a Y to stop processing, the indirect return is for the
2630: -- next row so that context aren't changed the direct return is for index - 1 so test that
2631: if g_summary_idx = nvl(g_summary_deductions.last + 1,g_summary_idx ) then return 'Y';
2624: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_rate:'||to_char(p_rate));
2625: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_base:'||to_char(p_base));
2626: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_code:'||p_contribution_code);
2627: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_usage_id:'||to_char(p_contribution_usage_id));
2628: hr_utility.trace('GET_SUMMARY_DEDUCTION.p_pay_value:'||to_char(p_pay_value));
2629: -- if this is the last row in the table return a Y to stop processing, the indirect return is for the
2630: -- next row so that context aren't changed the direct return is for index - 1 so test that
2631: if g_summary_idx = nvl(g_summary_deductions.last + 1,g_summary_idx ) then return 'Y';
2632: else return 'N';
2632: else return 'N';
2633: end if;
2634:
2635:
2636: hr_utility.set_location('Entering:'|| l_proc, 90);
2637:
2638: end GET_SUMMARY_DEDUCTION;
2639:
2640: PROCEDURE MAINTAIN_SUMMARY_DEDUCTION
2676: --
2677: -- the only deductions going into summary table are rate based deductions
2678: -- also T(ransport tax) and W(ork accident) are just inserted on the table - don't need to check
2679: -- whether a row exists already
2680: hr_utility.set_location('Entering:'|| l_proc, 10);
2681:
2682: if p_rate <> 0 and p_base <> 0 and p_contribution_code is not null then -- { record summary
2683:
2684: if p_rate_category = 'C' then
2685: -- 115.48 This means the contribution code is not held on
2686: -- pay_fr_contribution_usages. Insert into the table with
2687: -- contribution_usage_id set to -1*contribution_usage_id
2688: -- to allow the pay_fr_contribution_usages row to be identified later.
2689: hr_utility.set_location(l_proc, 20);
2690: l_idx := nvl(g_summary_deductions.last,0) + 1;
2691: g_summary_deductions(l_idx).base_type := p_base_type;
2692: g_summary_deductions(l_idx).base := p_base;
2693: g_summary_deductions(l_idx).Contribution_usage_id_type := p_rate_category;
2698: g_summary_deductions(l_idx).rate := p_rate;
2699: g_summary_deductions(l_idx).contribution_code := p_contribution_code;
2700: g_summary_deductions(l_idx).retro := null;
2701: elsif p_rate_category not in ('T','W') then -- { code_rate_id deductions
2702: hr_utility.set_location('Entering:'|| l_proc, 30);
2703: if p_code_rate_id is null then
2704: hr_utility.trace('No CODE_RATE_ID for p_contribution_usage_id:'||to_char(p_contribution_usage_id));
2705: fnd_message.set_name('PAY','PAY_75069_NO_CODE_RATE_ID');
2706: fnd_message.set_token('ELEMENT_NAME',p_element_name);
2700: g_summary_deductions(l_idx).retro := null;
2701: elsif p_rate_category not in ('T','W') then -- { code_rate_id deductions
2702: hr_utility.set_location('Entering:'|| l_proc, 30);
2703: if p_code_rate_id is null then
2704: hr_utility.trace('No CODE_RATE_ID for p_contribution_usage_id:'||to_char(p_contribution_usage_id));
2705: fnd_message.set_name('PAY','PAY_75069_NO_CODE_RATE_ID');
2706: fnd_message.set_token('ELEMENT_NAME',p_element_name);
2707: fnd_message.raise_error;
2708: else -- { all columns exists
2708: else -- { all columns exists
2709: --FOR 1 to nvl(g_summary_deductions.last,0)
2710: if g_summary_deductions.FIRST is not null then -- { empty table
2711: LOOP
2712: hr_utility.set_location('Entering:'|| l_proc, 40);
2713: l_idx := l_idx + 1;
2714:
2715: exit when l_action = 'U' or l_idx > nvl(g_summary_deductions.last,0);
2716:
2719: and g_summary_deductions(l_idx).contribution_code = p_contribution_code
2720: and g_summary_deductions(l_idx).Contribution_usage_id_type =
2721: p_rate_category
2722: then -- { matched row
2723: hr_utility.set_location('Entering:'|| l_proc, 50);
2724: l_action := 'U' ;
2725: g_summary_deductions(l_idx).rate := g_summary_deductions(l_idx).rate + p_rate;
2726: g_summary_deductions(l_idx).pay_value := g_summary_deductions(l_idx).pay_value + p_pay_value;
2727: g_summary_deductions(l_idx).contribution_usage_id := g_summary_deductions(l_idx).contribution_usage_id
2732:
2733: end if; -- } empty table
2734: -- if there wasn't a row to update then insert
2735: if l_action <> 'U' then -- { insert row
2736: hr_utility.set_location(l_proc, 60);
2737: l_idx := nvl(g_summary_deductions.last,0) + 1;
2738: g_summary_deductions(l_idx).base_type := p_base_type;
2739: g_summary_deductions(l_idx).base := p_base;
2740: g_summary_deductions(l_idx).Contribution_usage_id_type:=p_rate_category;
2748: end if; -- } columns exist
2749: else -- }{ end code_rate_id, start user_column_instance_id
2750: -- for T(ransport tax) and W(ork accident) deductions insert into the table with
2751: -- contribution_usage_id set to user_column_instance_id
2752: hr_utility.set_location(l_proc, 70);
2753: l_idx := nvl(g_summary_deductions.last,0) + 1;
2754: hr_utility.trace('l_idx:'||to_char(l_idx));
2755: g_summary_deductions(l_idx).base_type := p_base_type;
2756: g_summary_deductions(l_idx).base := p_base;
2750: -- for T(ransport tax) and W(ork accident) deductions insert into the table with
2751: -- contribution_usage_id set to user_column_instance_id
2752: hr_utility.set_location(l_proc, 70);
2753: l_idx := nvl(g_summary_deductions.last,0) + 1;
2754: hr_utility.trace('l_idx:'||to_char(l_idx));
2755: g_summary_deductions(l_idx).base_type := p_base_type;
2756: g_summary_deductions(l_idx).base := p_base;
2757: g_summary_deductions(l_idx).Contribution_usage_id_type := p_rate_category;
2758: g_summary_deductions(l_idx).contribution_usage_id := p_user_column_instance_id;
2763: g_summary_deductions(l_idx).retro := null;
2764: end if; -- } code_rate_id deduction
2765: end if; -- } record summary
2766:
2767: hr_utility.set_location('Leaving:'|| l_proc, 90);
2768:
2769: end maintain_summary_deduction;
2770:
2771: PROCEDURE MAINTAIN_RATE_CACHE
2785: l_proc varchar2(72) := g_package||'.maintain_rate_cache';
2786: l_idx NUMBER := 0;
2787:
2788: begin
2789: hr_utility.set_location('Entering:'|| l_proc, 10);
2790:
2791: l_idx := nvl(g_deduction_rates.last,0) + 1;
2792:
2793: hr_utility.trace('p_contribution_usage_id:'||to_char(p_contribution_usage_id)||' l_idx:'||to_char(l_idx));
2789: hr_utility.set_location('Entering:'|| l_proc, 10);
2790:
2791: l_idx := nvl(g_deduction_rates.last,0) + 1;
2792:
2793: hr_utility.trace('p_contribution_usage_id:'||to_char(p_contribution_usage_id)||' l_idx:'||to_char(l_idx));
2794:
2795: g_deduction_rates(l_idx).contribution_usage_id := p_contribution_usage_id;
2796: g_deduction_rates(l_idx).tax_unit_id := p_tax_unit_id;
2797: g_deduction_rates(l_idx).contribution_code := p_contribution_code;
2798: g_deduction_rates(l_idx).rate := p_rate_value ;
2799: g_deduction_rates(l_idx).user_column_instance_id := p_user_column_instance_id ;
2800: g_deduction_rates(l_idx).risk_code := p_rate_value ;
2801:
2802: hr_utility.set_location('Leaving:'|| l_proc, 90);
2803:
2804: end maintain_rate_cache;
2805:
2806: FUNCTION GET_CACHED_RATE
2823: begin
2824: -- the contribution_usage_id parameter is
2825: -- pay_fr_contribution_usages.contribution_usage_id
2826:
2827: hr_utility.set_location('Entering:'|| l_proc, 10);
2828: -- 115.46 Rate cache is now cleared on change of (grand) parent action
2829: -- within initialize_payroll()
2830: l_rate := NULL;
2831:
2830: l_rate := NULL;
2831:
2832: if g_deduction_rates.FIRST is not null then -- { empty table
2833: LOOP
2834: -- hr_utility.set_location(l_proc||' loop:'||to_char(l_idx+1)||' thru deduction_rates table', 40);
2835: l_idx := l_idx + 1;
2836:
2837: exit when l_idx > nvl(g_deduction_rates.last,0) or l_rate is not null;
2838:
2838:
2839: if g_deduction_rates(l_idx).contribution_usage_id = p_contribution_usage_id and
2840: g_deduction_rates(l_idx).tax_unit_id = p_tax_unit_id
2841: then -- { matched row
2842: hr_utility.set_location(l_proc||' rate in cache', 50);
2843: l_rate := g_deduction_rates(l_idx).rate ;
2844: p_contribution_code := g_deduction_rates(l_idx).contribution_code;
2845: p_user_column_instance_id := g_deduction_rates(l_idx).user_column_instance_id;
2846: p_risk_code := g_deduction_rates(l_idx).risk_code;
2849:
2850: end LOOP;
2851:
2852: end if; -- } empty table
2853: hr_utility.set_location('leaving:'|| l_proc, 90);
2854: RETURN l_rate;
2855: end get_cached_rate;
2856: --
2857: Function get_table_rate (p_bus_group_id in number,
2917: l_proc varchar2(72) := g_package||'.count_summary_deductions';
2918: l_count number;
2919:
2920: begin
2921: hr_utility.set_location('Entering:'|| l_proc, 10);
2922: l_count := nvl(g_summary_deductions.count,0);
2923: hr_utility.trace('g_summary_deductions.count: '|| to_char(l_count));
2924: return l_count;
2925:
2919:
2920: begin
2921: hr_utility.set_location('Entering:'|| l_proc, 10);
2922: l_count := nvl(g_summary_deductions.count,0);
2923: hr_utility.trace('g_summary_deductions.count: '|| to_char(l_count));
2924: return l_count;
2925:
2926: hr_utility.set_location('leaving:'|| l_proc, 90);
2927:
2922: l_count := nvl(g_summary_deductions.count,0);
2923: hr_utility.trace('g_summary_deductions.count: '|| to_char(l_count));
2924: return l_count;
2925:
2926: hr_utility.set_location('leaving:'|| l_proc, 90);
2927:
2928: end COUNT_SUMMARY_DEDUCTIONS;
2929:
2930: ------------------------------------------------------------------------