4 /*
1 PACKAGE pay_au_foreign_workers AUTHID CURRENT_USER AS
2 /* $Header: pyaufwrp.pkh 120.4 2010/11/23 12:19:20 avenkatk noship $*/
3
5 ******************************************************************
6 * *
7 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
8 * Chertsey, England. *
9 * *
10 * All rights reserved. *
11 * *
12 * This material has been provided pursuant to an agreement *
13 * containing restrictions on its use. The material is also *
14 * protected by copyright law. No part of this material may *
15 * be copied or distributed, transmitted or transcribed, in *
16 * any form or by any means, electronic, mechanical, magnetic, *
17 * manual, or otherwise, or disclosed to third parties without *
18 * the express written permission of Oracle Corporation UK Ltd, *
19 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
20 * England. *
21 * *
22 ******************************************************************
23
24 ------------------------------------------------------------------------+
25 *** Program: pay_au_foreign_workers (Package Specification)
26 ***
27 ***
28 *** Change History
29 ***
30 *** Date Changed By Version Bug No Description of Change
31 *** --------- ---------- ------- ------ --------------------------------+
32 *** 05-May-10 avenkatk 115.0 9147421 Initial Version
33 *** 10-May-10 avenkatk 115.1 9147421 Resolved gscc errors
34 *** 23-Jun-10 avenkatk 115.2 9147430 Added Foreign Worker Phase II changes
35 *** 22-Jul-10 avenkatk 115.3 9147430 Resolved gscc errors
36 *** 12-Aug-10 avenkatk 115.4 10019629 Made procedure populate_asg_status_id public
37 *** 18-Nov-10 avenkatk 115.5 9950136 Added new function get_fw_between_dates
38 */
39
40
41
42
43 /* The Foreign Worker Constant Types.
44 These constants would be used everywhere in the package to check the
45 Foreign Worker Type for which details are fetched.
46 */
47
51 /* Define the Global variables and Variable types */
48 g_fw_f_type CONSTANT VARCHAR2(80) := 'Foreign Worker F';
49 g_fw_j_type CONSTANT VARCHAR2(80) := 'Foreign Worker J';
50
52
53 cached boolean := FALSE;
54 g_debug boolean;
55 g_fw_asg_f_status_id per_assignment_status_types.assignment_status_type_id%TYPE := -1;
56 g_fw_asg_j_status_id per_assignment_status_types.assignment_status_type_id%TYPE := -1;
57 g_fw_def_bal_id pay_defined_balances.defined_balance_id%TYPE := -1;
58 g_package VARCHAR2(100) := 'pay_au_foreign_workers';
59
60
61 TYPE fw_asg_data_type IS RECORD(
62 assignment_id per_assignments_f.assignment_id%TYPE
63 ,start_date per_assignments_f.effective_start_date%TYPE
64 ,end_date per_assignments_f.effective_end_date%TYPE
65 ,payroll_id per_assignments_f.payroll_id%TYPE
66 ,tax_unit_id pay_assignment_actions.tax_unit_id%TYPE
67 );
68
69 TYPE tab_fw_dates IS TABLE OF fw_asg_data_type INDEX BY BINARY_INTEGER;
70
71 TYPE bal_type IS RECORD(
72 balance_type_id NUMBER
73 );
74
75 TYPE tab_bal_type IS TABLE OF bal_type INDEX BY BINARY_INTEGER;
76
77 PROCEDURE populate_asg_status_id;
78
79 FUNCTION check_foreign_worker(p_assignment_id IN NUMBER
80 ,p_tax_unit_id IN NUMBER
81 ,p_start_date IN DATE
82 ,p_end_date IN DATE)
83 RETURN VARCHAR2;
84
85 FUNCTION check_foreign_worker(p_assignment_id IN NUMBER
86 ,p_tax_unit_id IN NUMBER
87 ,p_start_date IN DATE
88 ,p_end_date IN DATE
89 ,p_fw_type IN VARCHAR2)
90 RETURN VARCHAR2;
91
92
93 PROCEDURE get_foreign_worker_dates (p_assignment_id IN per_assignments_f.assignment_id%TYPE
94 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
95 ,p_start_date IN DATE
96 ,p_end_date IN DATE
97 ,p_fw_type IN VARCHAR2
98 ,p_tab_fw_dates OUT NOCOPY pay_au_foreign_workers.tab_fw_dates);
99
100
101 FUNCTION check_fw_terminated(p_assignment_id IN per_assignments_f.assignment_id%TYPE
102 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
103 ,p_start_date IN DATE
104 ,p_end_date IN DATE
105 ,p_fw_type IN VARCHAR2
106 ,p_act_term_date OUT NOCOPY per_periods_of_service.actual_termination_date%TYPE)
107 RETURN VARCHAR2;
108
109
110 PROCEDURE get_defined_balances(p_balances_tab IN pay_au_foreign_workers.tab_bal_type
111 ,p_dimension_name IN pay_balance_dimensions.dimension_name%TYPE
112 ,p_def_bal_tab OUT NOCOPY pay_balance_pkg.t_balance_value_tab
113 );
114
115 FUNCTION is_le_changed(p_assignment_id IN per_assignments_f.assignment_id%TYPE
116 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
117 ,p_payroll_id IN per_time_periods.payroll_id%TYPE
118 ,p_time_period_id IN per_time_periods.time_period_id%TYPE)
119 RETURN VARCHAR2;
120
121
122 PROCEDURE get_foreign_payment_amounts(p_assignment_id IN per_assignments_f.assignment_id%TYPE
123 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
124 ,p_tab_fw_dates IN pay_au_foreign_workers.tab_fw_dates
125 ,p_start_date IN DATE
126 ,p_end_date IN DATE
127 ,p_fw_type IN VARCHAR2
128 ,p_balances_tab IN pay_au_foreign_workers.tab_bal_type
129 ,p_output_table OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab);
130
131
132 PROCEDURE get_fw_between_dates(p_fw_tab_dates IN pay_au_foreign_workers.tab_fw_dates
133 ,p_start_date IN DATE
134 ,p_end_date IN DATE
135 ,p_result_tab_dates OUT NOCOPY pay_au_foreign_workers.tab_fw_dates);
136
137 END pay_au_foreign_workers;
[Home] [Help]