1 PACKAGE PAY_US_PSD_GENERIC_EXTRACT AUTHID CURRENT_USER as
2 /* $Header: payuspsdgnxtract.pkh 120.0.12020000.7 2012/11/26 14:44:57 pkoduri noship $ */
3
4 /*===========================================================================+
5 | Copyright (c) 2001 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 Name
10 PAY_US_PSD_GENERIC_EXTRACT
11
12 File
13 payuspsdgnxtract.pkh
14
15 Purpose
16 The purpose of this package is to support the 'PSD Periodic Wage Listing Report' Process
17 This package is created to include generic database package components may be used for by
18 'PSD Periodic Wage Listing Report' process for Extracting archived Data, Validating
19 and constructing XML element.
20
21 Notes
22
23 History
24
25 Date User Id Version Bug Description
26 ============================================================================
27 09-jul-12 pracagra 115.0 11712075 Initial Version Created.
28 09-jul-12 pracagra 115.1 11712075 Removed GSCC errors.
29 16-jul-12 pracagra 115.2 11712075 Increased the number of employee xml tags.
30 06-aug-12 pracagra 115.3 14407407 Increased the number of employee xml tags.
31 24-aug-12 pracagra 115.4 14379256 Increased the number of employee xml tags.
32 07-sep-12 pracagra 115.5 14580715 and 14596652 Increased the number of employee xml tags.
33 22-nov-12 skchalla 115.6 14799833 Modified for the Yearly reporting.
34 26-nov-12 pkoduri 115.7 14286448 Corrections for GRE name length issue.
35 ============================================================================*/
36 -- Global Variables
37 -- Not any
38 --
39 -- This procedure would be used to populate the Tag used for RA Data Record
40 --
41 PROCEDURE populate_ra_data_tag;
42
43 PROCEDURE populate_arch_transmitter (
44 p_payroll_action_id IN NUMBER
45 ,p_tax_unit_id IN NUMBER
46 ,p_date_earned IN DATE
47 ,p_reporting_year IN VARCHAR2
48 ,p_jurisdiction_code IN VARCHAR2
49 ,p_state_code IN NUMBER
50 ,p_state_abbreviation IN VARCHAR2
51 ,status IN VARCHAR2
52 ,p_final_string OUT NOCOPY VARCHAR2
53 );
54 --
55 -- This procedure would be used to populate the Tag used for RE Record
56 --
57 PROCEDURE populate_re_data_tag;
58 --
59 -- This procedure would be used to populate the PL/Table used for
60 -- storing RE or Employer record Data
61
62 PROCEDURE populate_arch_employer (
63 p_payroll_action_id IN NUMBER
64 ,p_tax_unit_id IN NUMBER
65 ,p_date_earned IN DATE
66 ,p_reporting_year IN VARCHAR2
67 ,p_jurisdiction_code IN VARCHAR2
68 ,p_state_code IN NUMBER
69 ,p_state_abbreviation IN VARCHAR2
70 ,status IN VARCHAR2
71 ,p_final_string OUT NOCOPY VARCHAR2
72 );
73
74 --
75 -- This procedure would be used to populate the Tag used for RW, RO and RS Data Item Tags
76 --
77 PROCEDURE populate_ee_data_tag;
78
79 PROCEDURE populate_arch_employee(
80 p_payroll_action_id NUMBER
81 ,p_assignment_action_id NUMBER
82 ,p_tax_unit_id NUMBER
83 ,p_assignment_id NUMBER
84 ,p_date_earned DATE
85 ,p_reporting_year VARCHAR2
86 ,p_jurisdiction_code VARCHAR2
87 ,p_state_code NUMBER
88 ,p_state_abbreviation VARCHAR2
89 ,status VARCHAR2
90 ,p_final_string OUT NOCOPY VARCHAR2
91 );
92
93
94 FUNCTION convert_special_char( p_data varchar2)
95 RETURN varchar2;
96
97
98 TYPE submitter_rec IS RECORD
99 ( submitter_tag varchar2(200),
100 submitter_data varchar2(240)); -- Bug# 14286448 Gre length issue
101
102 /*******************************************************************
103 ** PL/SQL table of record to store the archived values of RA Record DataItems
104 *******************************************************************/
105 TYPE submitter_record IS TABLE OF submitter_rec
106 INDEX BY BINARY_INTEGER;
107 --
108 -- Table of Records with Data Type submitter_record
109 --
110 g_ra_record submitter_record;
111
112 --
113 --PL Table used for storing the RA record Data Item Tags
114 --
115 TYPE ra_dataitem_tag IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
116 ltr_ra_data_tag ra_dataitem_tag;
117
118 --
119 -- Nof of Tags used RA Records Data Items
120 --
121 g_ra_no_of_tag number := 33;
122 --
123 -- This is to store Submitter Employer Identification Number
124 --
125 g_submitter_ein varchar2(200);
126
127 /* ========================================================
128 ** PL/SQL table of record to store the archived values of RE Record DataItems
129 ========================================================= */
130 TYPE employer_rec IS RECORD
131 ( employer_tag varchar2(200),
132 employer_data varchar2(240)); -- Bug# 14286448 Gre length issue
133
134 TYPE employer_record IS TABLE OF employer_rec
135 INDEX BY BINARY_INTEGER;
136 --
137 -- Table of Records with Data Type employer_record
138 --
139 g_re_record employer_record;
140
141 --
142 --PL Table used for storing the RE record Data Item Tags
143 --
144 TYPE re_dataitem_tag IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
145 ltr_re_data_tag re_dataitem_tag;
146
147 --
148 -- Nof of Tags used RA Records Data Items
149 --
150 g_re_no_of_tag number := 28;
151
152
153 /* ==================================================================
154 ** PL/SQL table of record to store the archived values of RW, RO and RS Record DataItems
155 ================================================================== */
156 TYPE employee_rec IS RECORD
157 ( employee_tag varchar2(200),
158 employee_data varchar2(200));
159
160 TYPE employee_record IS TABLE OF employee_rec
161 INDEX BY BINARY_INTEGER;
162 --
163 -- Table of Records with Data Type employee_record
164 --
165 g_ee_record employee_record;
166
167 --
168 --PL Table used for storing the RE record Data Item Tags
169 --
170 TYPE ee_dataitem_tag IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
171 ltr_ee_data_tag ee_dataitem_tag;
172
173 --
174 -- Nof of Tags used RW, RO and RS Records Data Items
175 --
176 g_ee_no_of_tag number := 50;
177
178 END pay_us_psd_generic_extract;
[Home] [Help]