1 PACKAGE pqp_update_work_pattern_table AS
2 /* $Header: pquwprow.pkh 120.0 2005/05/29 02:15 appldev noship $ */
3
4 TYPE rec_rows_details IS RECORD(
5 --row_name pay_user_rows_f.row_low_range_or_name%TYPE
6 user_row_id pay_user_rows_f.user_row_id%TYPE
7 ,effective_start_date DATE
8 ,effective_end_date DATE );
9
10 TYPE t_row_details IS TABLE OF rec_rows_details
11 INDEX BY BINARY_INTEGER;
12
13 TYPE t_row_ids IS TABLE OF pay_user_rows_f.user_row_id%TYPE
14 INDEX BY BINARY_INTEGER;
15
16 TYPE t_row_names IS TABLE OF pay_user_rows_f.row_low_range_or_name%TYPE
17 INDEX BY BINARY_INTEGER;
18
19 TYPE t_row_values IS TABLE OF NUMBER
20 INDEX BY BINARY_INTEGER;
21
22
23
24
25 CURSOR csr_get_value (
26 p_user_column_id IN NUMBER
27 ,p_user_row_id IN NUMBER
28 ,p_effective_date IN DATE
29 ,p_business_group_id IN NUMBER
30 ,p_legislation_code IN VARCHAR2
31 ) IS
32 SELECT puci.value value
33 ,puci.user_column_instance_id user_column_instance_id
34 ,puci.rowid row_id
35 ,puci.effective_start_date effective_start_date
36 ,puci.effective_end_date effective_end_date
37 FROM pay_user_column_instances_f puci
38 WHERE ( puci.business_group_id = p_business_group_id
39 OR puci.legislation_code = p_legislation_code )
40 AND puci.user_column_id = p_user_column_id
41 AND puci.user_row_id = p_user_row_id;
42 --AND p_effective_date BETWEEN puci.effective_start_date
43 -- AND puci.effective_end_date ;
44 --commented out the above code as per the multiple entries in the
45 --Columns of the 'PQP_COMAPNY WORK_PATTERNS'issue raised in the Bug# 4078709
46
47
48 PROCEDURE update_working_days_in_week (
49 errbuf OUT NOCOPY VARCHAR2
50 ,retcode OUT NOCOPY NUMBER
51 ,p_column_name IN VARCHAR2
52 ,p_business_group_id IN NUMBER
53 ,p_overwrite_if_exists IN VARCHAR2
54 );
55
56 FUNCTION get_avg_working_days_in_week (
57 p_business_group_id IN NUMBER
58 ,p_effective_date IN DATE
59 ,p_user_column_id IN pay_user_columns.user_column_id%TYPE
60 ,p_user_table_id IN pay_user_tables.user_table_id%TYPE
61 ,p_total_days_defined OUT NOCOPY NUMBER
62 ,p_total_working_days_defined OUT NOCOPY NUMBER
63 ) RETURN NUMBER ;
64
65 PROCEDURE update_insert_row(
66 p_user_column_id IN NUMBER
67 ,p_user_row_id IN NUMBER
68 ,p_effective_date IN DATE
69 ,p_row_effective_start_date IN DATE
70 ,p_row_effective_end_date IN DATE
71 ,p_business_group_id IN NUMBER
72 ,p_value_to_update IN NUMBER
73 ,p_overwrite_if_exists IN VARCHAR2
74 ) ;
75
76
77 END pqp_update_work_pattern_table ;