1 PACKAGE pay_gb_eoy_archive AUTHID CURRENT_USER AS
2 /* $Header: pygbeoya.pkh 120.3 2006/11/06 22:34:28 rmakhija noship $ */
3 --
4 ------------------------------- FUNCTIONS -------------------------------------
5 --
6 -- FUNCTION get_nearest_scon
7 -- This function searches for a SCON number to associate with the SCON balance
8 -- Balance initialization creates run results prior to the NI row that records
9 -- the SCON number. So find a row for the same category after the effective
10 -- date of the owning payroll action.
11 -- Priority is next latest SCON input with the same Category
12 -- down to next latest SCON input regardless of Category
13 FUNCTION get_nearest_scon(p_element_entry_id IN number ,
14 p_assignment_action_id IN number,
15 p_category IN varchar2 ,
16 p_effective_date IN date)
17 return varchar2;
18 pragma restrict_references (get_nearest_scon, WNDS);
19 --
20 -- FUNCTION canonical_to_date
21 -- Cover on the fnd_date function, but with exception handling
22 FUNCTION canonical_to_date(p_chardate IN VARCHAR2)
23 RETURN DATE;
24 PRAGMA restrict_references(canonical_to_date, WNDS, WNPS);
25 --
26 -- FUNCTION canonical_to_number
27 -- Cover on the fnd_number function, but with exception handling
28 FUNCTION canonical_to_number(p_charnum IN VARCHAR2)
29 RETURN NUMBER;
30 PRAGMA restrict_references(canonical_to_number, WNDS, WNPS);
31 --
32 -- FUNCTION get_arch_str
33 -- Overloaded Pure Public Function which returns a value from the archive,
34 -- given the action id (ff_archive_items.context1), user entity name or id
35 -- and up to three additional contexts.
36 -- No validation is performed on the input parameters.
37 -- If a matching item does not exist, null is returned.
38 -- The additional context parameters must be populated in order.
39 FUNCTION get_arch_str(p_action_id IN NUMBER,
40 p_user_entity_id IN NUMBER,
41 p_context_value1 IN VARCHAR2 DEFAULT NULL,
42 p_context_value2 IN VARCHAR2 DEFAULT NULL,
43 p_context_value3 IN VARCHAR2 DEFAULT NULL)
44 RETURN VARCHAR2;
45 PRAGMA restrict_references(get_arch_str, WNDS, WNPS);
46 FUNCTION get_arch_str(p_action_id IN NUMBER,
47 p_user_entity_name IN VARCHAR2,
48 p_context_value1 IN VARCHAR2 DEFAULT NULL,
49 p_context_value2 IN VARCHAR2 DEFAULT NULL,
50 p_context_value3 IN VARCHAR2 DEFAULT NULL)
51 RETURN VARCHAR2;
52 PRAGMA restrict_references(get_arch_str, WNDS, WNPS);
53 --
54 -- FUNCTION get_arch_num
55 -- Pure Public Function which returns a value from the archive
56 -- using get_arch_str, then formats it to a number
57 FUNCTION get_arch_num(p_action_id IN NUMBER,
58 p_user_entity_name IN VARCHAR2,
59 p_context_value1 IN VARCHAR2 DEFAULT NULL,
60 p_context_value2 IN VARCHAR2 DEFAULT NULL,
61 p_context_value3 IN VARCHAR2 DEFAULT NULL)
62 RETURN NUMBER;
63 PRAGMA restrict_references(get_arch_num, WNDS, WNPS);
64 --
65 -- FUNCTION get_arch_date
66 -- Pure Public Function which returns a value from the archive
67 -- using get_arch_str, then formats it to a date
68 FUNCTION get_arch_date(p_action_id IN NUMBER,
69 p_user_entity_name IN VARCHAR2,
70 p_context_value1 IN VARCHAR2 DEFAULT NULL,
71 p_context_value2 IN VARCHAR2 DEFAULT NULL,
72 p_context_value3 IN VARCHAR2 DEFAULT NULL)
73 RETURN DATE;
74 PRAGMA restrict_references(get_arch_date, WNDS, WNPS);
75 --
76 --
77 -- FUNCTION get_parameter
78 -- Pure Public Function which returns a specific legislative parameter,
79 -- given a string of parameters and a token.
80 -- Optional segment_number parameter indicates which segment of the parameter
81 -- to return where the parameter contains segments separated by colons
82 -- eg. SORT_OPTIONS=segment1:segment2:segment3
83 -- Now caters for spaces in parameter values (so can be used to retrieve
84 -- canonical dates) where the parameter is delimited with pipe chars
85 -- eg. |START_DATE=1999/04/06 00:00:00|
86 FUNCTION get_parameter(p_parameter_string IN VARCHAR2,
87 p_token IN VARCHAR2,
88 p_segment_number IN NUMBER DEFAULT NULL)
89 RETURN VARCHAR2;
90 PRAGMA restrict_references(get_parameter, WNDS);
91 FUNCTION get_cached_value(p_payroll_action_id IN NUMBER,
92 p_user_entity_name IN VARCHAR2,
93 p_payroll_id IN NUMBER)
94 RETURN VARCHAR2;
95 PRAGMA restrict_references(get_cached_value, WNDS, WNPS);
96 --
97 FUNCTION get_agg_active_start(p_asg_id IN NUMBER,
98 p_tax_ref IN VARCHAR2,
99 p_proll_eff_date IN DATE)
100 RETURN DATE;
101 PRAGMA restrict_references(get_agg_active_start, WNDS, WNPS);
102 --
103 FUNCTION get_agg_active_end(p_asg_id IN NUMBER,
104 p_tax_ref IN VARCHAR2,
105 p_proll_eff_date IN DATE)
106 RETURN DATE;
107 PRAGMA restrict_references(get_agg_active_end, WNDS, WNPS);
108 ------------------------------- PROCEDURES ----------------------------------
109 --
110 -- PROCEDURE range_cursor
111 -- Procedure which archives the payroll information, then returns a
112 -- varchar2 defining a SQL Statement to select all the people that may be
113 -- eligible for Year End reporting.
114 -- The archiver uses this cursor to split the people into chunks for parallel
115 -- processing.
116 PROCEDURE range_cursor (pactid IN NUMBER,
117 sqlstr OUT NOCOPY VARCHAR2);
118 --
119 PROCEDURE action_creation(pactid IN NUMBER,
120 stperson IN NUMBER,
121 endperson IN NUMBER,
122 chunk IN NUMBER);
123 --
124 PROCEDURE archinit(p_payroll_action_id IN NUMBER);
125 --
126 PROCEDURE archive_code(p_assactid IN NUMBER, p_effective_date IN DATE);
127 --
128 -- PROCEDURE extract_item_report_format
129 -- This procedure inserts the necessary data into the
130 -- PAY_REPORT_FORMAT_ITEMS_F table, FOR GB EXTRACT ARCHIVE ITEMS ONLY.
131 -- This distinction must be made as the procedure contains hard-
132 -- coded data, only relevant for extract items, ie those DBI/
133 -- User Entities starting 'X_'. Do not use this utility for
134 -- entering other data into these tables.
135 -- The Datetracking is 'handled' in this case by entering
136 -- start of time and end of time for all records. Again, this
137 -- is specific to GB Extract Items.
138 PROCEDURE extract_item_report_format(p_user_entity_name IN VARCHAR2,
139 p_archive_type IN VARCHAR2);
140 --
141 -- Function to write error or warning messages to the output and the log files
142 FUNCTION write_output(p_assignment_number IN VARCHAR2,
143 p_full_name IN VARCHAR2,
144 p_message_type IN VARCHAR2,
145 p_message IN VARCHAR2) RETURN NUMBER;
146 --
147 FUNCTION write_output_footer RETURN NUMBER;
148 --
149 END pay_gb_eoy_archive;