1 PACKAGE Okl_Aeh_Pvt AS
2 /* $Header: OKLSAEHS.pls 120.2 2006/07/11 10:08:21 dkagrawa noship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL DATASTRUCTURES
5 ---------------------------------------------------------------------------
6 TYPE aeh_rec_type IS RECORD (
7 ae_header_id NUMBER := Okc_Api.G_MISS_NUM,
8 post_to_gl_flag OKL_AE_HEADERS.POST_TO_GL_FLAG%TYPE := Okc_Api.G_MISS_CHAR,
9 set_of_books_id NUMBER := Okc_Api.G_MISS_NUM,
10 org_id NUMBER := Okc_Api.G_MISS_NUM,
11 ACCOUNTING_EVENT_ID NUMBER := Okc_Api.G_MISS_NUM,
12 ae_category OKL_AE_HEADERS.AE_CATEGORY%TYPE := Okc_Api.G_MISS_CHAR,
13 period_name OKL_AE_HEADERS.PERIOD_NAME%TYPE := Okc_Api.G_MISS_CHAR,
14 accounting_date OKL_AE_HEADERS.ACCOUNTING_DATE%TYPE := Okc_Api.G_MISS_DATE,
15 cross_currency_flag OKL_AE_HEADERS.CROSS_CURRENCY_FLAG%TYPE := Okc_Api.G_MISS_CHAR,
16 gl_transfer_flag OKL_AE_HEADERS.GL_TRANSFER_FLAG%TYPE := Okc_Api.G_MISS_CHAR,
17 gl_transfer_run_id NUMBER := Okc_Api.G_MISS_NUM,
18 object_version_number NUMBER := Okc_Api.G_MISS_NUM,
19 sequence_id NUMBER := Okc_Api.G_MISS_NUM,
20 sequence_value NUMBER := Okc_Api.G_MISS_NUM,
21 description OKL_AE_HEADERS.DESCRIPTION%TYPE := Okc_Api.G_MISS_CHAR,
22 accounting_error_code OKL_AE_HEADERS.ACCOUNTING_ERROR_CODE%TYPE := Okc_Api.G_MISS_CHAR,
23 gl_transfer_error_code OKL_AE_HEADERS.GL_TRANSFER_ERROR_CODE%TYPE,
24 gl_reversal_flag OKL_AE_HEADERS.GL_REVERSAL_FLAG%TYPE := Okc_Api.G_MISS_CHAR,
25 program_id NUMBER := Okc_Api.G_MISS_NUM,
26 program_application_id NUMBER := Okc_Api.G_MISS_NUM,
27 program_update_date OKL_AE_HEADERS.PROGRAM_UPDATE_DATE%TYPE := Okc_Api.G_MISS_DATE,
28 request_id NUMBER := Okc_Api.G_MISS_NUM,
29 created_by NUMBER := Okc_Api.G_MISS_NUM,
30 creation_date OKL_AE_HEADERS.CREATION_DATE%TYPE := Okc_Api.G_MISS_DATE,
31 last_updated_by NUMBER := Okc_Api.G_MISS_NUM,
32 last_update_date OKL_AE_HEADERS.LAST_UPDATE_DATE%TYPE := Okc_Api.G_MISS_DATE,
33 last_update_login NUMBER := Okc_Api.G_MISS_NUM);
34
35 g_miss_aeh_rec aeh_rec_type;
36 TYPE aeh_tbl_type IS TABLE OF aeh_rec_type
37 INDEX BY BINARY_INTEGER;
38 TYPE aehv_rec_type IS RECORD (
39 post_to_gl_flag OKL_AE_HEADERS.POST_TO_GL_FLAG%TYPE := Okc_Api.G_MISS_CHAR,
40 ae_header_id NUMBER := Okc_Api.G_MISS_NUM,
41 object_version_number NUMBER := Okc_Api.G_MISS_NUM,
42 ACCOUNTING_EVENT_ID NUMBER := Okc_Api.G_MISS_NUM,
43 set_of_books_id NUMBER := Okc_Api.G_MISS_NUM,
44 org_id NUMBER := Okc_Api.G_MISS_NUM,
45 AE_CATEGORY OKL_AE_HEADERS.AE_CATEGORY%TYPE := Okc_Api.G_MISS_CHAR,
46 sequence_id NUMBER := Okc_Api.G_MISS_NUM,
47 sequence_value NUMBER := Okc_Api.G_MISS_NUM,
48 period_name OKL_AE_HEADERS.PERIOD_NAME%TYPE := Okc_Api.G_MISS_CHAR,
49 accounting_date OKL_AE_HEADERS.ACCOUNTING_DATE%TYPE := Okc_Api.G_MISS_DATE,
50 description OKL_AE_HEADERS.DESCRIPTION%TYPE := Okc_Api.G_MISS_CHAR,
51 accounting_error_code OKL_AE_HEADERS.ACCOUNTING_ERROR_CODE%TYPE := Okc_Api.G_MISS_CHAR,
52 cross_currency_flag OKL_AE_HEADERS.CROSS_CURRENCY_FLAG%TYPE := Okc_Api.G_MISS_CHAR,
53 gl_transfer_flag OKL_AE_HEADERS.GL_TRANSFER_FLAG%TYPE := Okc_Api.G_MISS_CHAR,
54 gl_transfer_error_code OKL_AE_HEADERS.GL_TRANSFER_ERROR_CODE%TYPE := Okc_Api.G_MISS_CHAR,
55 gl_transfer_run_id NUMBER := Okc_Api.G_MISS_NUM,
56 gl_reversal_flag OKL_AE_HEADERS.GL_REVERSAL_FLAG%TYPE := Okc_Api.G_MISS_CHAR,
57 program_id NUMBER := Okc_Api.G_MISS_NUM,
58 program_application_id NUMBER := Okc_Api.G_MISS_NUM,
59 program_update_date OKL_AE_HEADERS.PROGRAM_UPDATE_DATE%TYPE := Okc_Api.G_MISS_DATE,
60 request_id NUMBER := Okc_Api.G_MISS_NUM,
61 created_by NUMBER := Okc_Api.G_MISS_NUM,
62 creation_date OKL_AE_HEADERS.CREATION_DATE%TYPE := Okc_Api.G_MISS_DATE,
63 last_updated_by NUMBER := Okc_Api.G_MISS_NUM,
64 last_update_date OKL_AE_HEADERS.LAST_UPDATE_DATE%TYPE := Okc_Api.G_MISS_DATE,
65 last_update_login NUMBER := Okc_Api.G_MISS_NUM);
66
67 g_miss_aehv_rec aehv_rec_type;
68 TYPE aehv_tbl_type IS TABLE OF aehv_rec_type
69 INDEX BY BINARY_INTEGER;
70 --gboomina bug#4648697..changes for perf start
71 --Added column arrarys for bulk insert
72 TYPE ae_header_id_typ IS TABLE OF okl_ae_headers.ae_header_id%TYPE
73 INDEX BY BINARY_INTEGER;
74 TYPE post_to_gl_flag_typ IS TABLE OF okl_ae_headers.post_to_gl_flag%TYPE
75 INDEX BY BINARY_INTEGER;
76 TYPE set_of_books_id_typ IS TABLE OF okl_ae_headers.set_of_books_id%TYPE
77 INDEX BY BINARY_INTEGER;
78 TYPE accounting_event_id_typ IS TABLE OF okl_ae_headers.accounting_event_id%TYPE
79 INDEX BY BINARY_INTEGER;
80 TYPE object_version_number_typ IS TABLE OF okl_ae_headers.object_version_number%TYPE
81 INDEX BY BINARY_INTEGER;
82 TYPE ae_category_typ IS TABLE OF okl_ae_headers.ae_category%TYPE
83 INDEX BY BINARY_INTEGER;
84 TYPE period_name_typ IS TABLE OF okl_ae_headers.period_name%TYPE
85 INDEX BY BINARY_INTEGER;
86 TYPE accounting_date_typ IS TABLE OF okl_ae_headers.accounting_date%TYPE
87 INDEX BY BINARY_INTEGER;
88 TYPE gl_transfer_run_id_typ IS TABLE OF okl_ae_headers.gl_transfer_run_id%TYPE
89 INDEX BY BINARY_INTEGER;
90 TYPE cross_currency_flag_typ IS TABLE OF okl_ae_headers.cross_currency_flag%TYPE
91 INDEX BY BINARY_INTEGER;
92 TYPE gl_transfer_flag_typ IS TABLE OF okl_ae_headers.gl_transfer_flag%TYPE
93 INDEX BY BINARY_INTEGER;
94 TYPE sequence_id_typ IS TABLE OF okl_ae_headers.sequence_id%TYPE
95 INDEX BY BINARY_INTEGER;
96 TYPE sequence_value_typ IS TABLE OF okl_ae_headers.sequence_value%TYPE
97 INDEX BY BINARY_INTEGER;
98 TYPE description_typ IS TABLE OF okl_ae_headers.description%TYPE
99 INDEX BY BINARY_INTEGER;
100 TYPE accounting_error_code_typ IS TABLE OF okl_ae_headers.accounting_error_code%TYPE
101 INDEX BY BINARY_INTEGER;
102 TYPE gl_transfer_error_code_typ IS TABLE OF okl_ae_headers.gl_transfer_error_code%TYPE
103 INDEX BY BINARY_INTEGER;
104 TYPE gl_reversal_flag_typ IS TABLE OF okl_ae_headers.gl_reversal_flag%TYPE
105 INDEX BY BINARY_INTEGER;
106 TYPE org_id_typ IS TABLE OF okl_ae_headers.org_id%TYPE
107 INDEX BY BINARY_INTEGER;
108 TYPE program_id_typ IS TABLE OF okl_ae_headers.program_id%TYPE
109 INDEX BY BINARY_INTEGER;
110 TYPE program_application_id_typ IS TABLE OF okl_ae_headers.program_application_id%TYPE
111 INDEX BY BINARY_INTEGER;
112 TYPE program_update_date_typ IS TABLE OF okl_ae_headers.program_update_date%TYPE
113 INDEX BY BINARY_INTEGER;
114 TYPE request_id_typ IS TABLE OF okl_ae_headers.request_id%TYPE
115 INDEX BY BINARY_INTEGER;
116 TYPE created_by_typ IS TABLE OF okl_ae_headers.created_by%TYPE
117 INDEX BY BINARY_INTEGER;
118 TYPE creation_date_typ IS TABLE OF okl_ae_headers.creation_date%TYPE
119 INDEX BY BINARY_INTEGER;
120 TYPE last_updated_by_typ IS TABLE OF okl_ae_headers.last_updated_by%TYPE
121 INDEX BY BINARY_INTEGER;
122 TYPE last_update_date_typ IS TABLE OF okl_ae_headers.last_update_date%TYPE
123 INDEX BY BINARY_INTEGER;
124 TYPE last_update_login_typ IS TABLE OF okl_ae_headers.last_update_login%TYPE
125 INDEX BY BINARY_INTEGER;
126 --gboomina bug#4648697..changes for perf end
127
128 ---------------------------------------------------------------------------
129 -- GLOBAL MESSAGE CONSTANTS
130 ---------------------------------------------------------------------------
131 G_FND_APP CONSTANT VARCHAR2(200) := Okc_Api.G_FND_APP;
132 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := Okc_Api.G_FORM_UNABLE_TO_RESERVE_REC;
133 G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := Okc_Api.G_FORM_RECORD_DELETED;
134 G_FORM_RECORD_CHANGED CONSTANT VARCHAR2(200) := Okc_Api.G_FORM_RECORD_CHANGED;
135 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := Okc_Api.G_RECORD_LOGICALLY_DELETED;
136 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := Okc_Api.G_REQUIRED_VALUE;
137 G_INVALID_VALUE CONSTANT VARCHAR2(200) := Okc_Api.G_INVALID_VALUE;
138 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := Okc_Api.G_COL_NAME_TOKEN;
139 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := Okc_Api.G_PARENT_TABLE_TOKEN;
140 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := Okc_Api.G_CHILD_TABLE_TOKEN;
141 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLerrm';
142 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLcode';
143 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXPECTED_ERROR';
144 G_UPPERCASE_REQUIRED CONSTANT VARCHAR2(200) := 'OKL_UPPER_CASE_REQUIRED';
145 G_UNQS CONSTANT VARCHAR2(200) := 'OKL_UNIQUE_KEY_VALIDATION_FAILED';
146
147 ---------------------------------------------------------------------------
148 -- GLOBAL VARIABLES
149 ---------------------------------------------------------------------------
150 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_AEH_PVT';
151 G_APP_NAME CONSTANT VARCHAR2(3) := Okc_Api.G_APP_NAME;
152 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
153
154 ---------------------------------------------------------------------------
155 -- Procedures and Functions
156 ---------------------------------------------------------------------------
157
158 PROCEDURE qc;
159 PROCEDURE change_version;
160 PROCEDURE api_copy;
161 PROCEDURE insert_row(
162 p_api_version IN NUMBER,
163 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY VARCHAR2,
167 p_aehv_rec IN aehv_rec_type,
168 x_aehv_rec OUT NOCOPY aehv_rec_type);
169
170 PROCEDURE insert_row(
171 p_api_version IN NUMBER,
172 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
173 x_return_status OUT NOCOPY VARCHAR2,
174 x_msg_count OUT NOCOPY NUMBER,
175 x_msg_data OUT NOCOPY VARCHAR2,
176 p_aehv_tbl IN aehv_tbl_type,
177 x_aehv_tbl OUT NOCOPY aehv_tbl_type);
178
179 --gboomina bug#4648697..changes for perf start
180 --added new procedure for bulk insert
181 PROCEDURE insert_row_perf(
182 p_api_version IN NUMBER,
183 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
184 x_return_status OUT NOCOPY VARCHAR2,
185 x_msg_count OUT NOCOPY NUMBER,
186 x_msg_data OUT NOCOPY VARCHAR2,
187 p_aehv_tbl IN aehv_tbl_type,
188 x_aehv_tbl OUT NOCOPY aehv_tbl_type);
189 --gboomina bug#4648697..changes for perf end
190
191 PROCEDURE lock_row(
192 p_api_version IN NUMBER,
193 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
194 x_return_status OUT NOCOPY VARCHAR2,
195 x_msg_count OUT NOCOPY NUMBER,
196 x_msg_data OUT NOCOPY VARCHAR2,
197 p_aehv_rec IN aehv_rec_type);
198
199 PROCEDURE lock_row(
200 p_api_version IN NUMBER,
201 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_msg_count OUT NOCOPY NUMBER,
204 x_msg_data OUT NOCOPY VARCHAR2,
205 p_aehv_tbl IN aehv_tbl_type);
206
207 PROCEDURE update_row(
208 p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2,
213 p_aehv_rec IN aehv_rec_type,
214 x_aehv_rec OUT NOCOPY aehv_rec_type);
215
216 PROCEDURE update_row(
217 p_api_version IN NUMBER,
218 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER,
221 x_msg_data OUT NOCOPY VARCHAR2,
222 p_aehv_tbl IN aehv_tbl_type,
223 x_aehv_tbl OUT NOCOPY aehv_tbl_type);
224
225 PROCEDURE delete_row(
226 p_api_version IN NUMBER,
227 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
228 x_return_status OUT NOCOPY VARCHAR2,
229 x_msg_count OUT NOCOPY NUMBER,
230 x_msg_data OUT NOCOPY VARCHAR2,
231 p_aehv_rec IN aehv_rec_type);
232
233 PROCEDURE delete_row(
234 p_api_version IN NUMBER,
235 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2,
239 p_aehv_tbl IN aehv_tbl_type);
240
241 PROCEDURE validate_row(
242 p_api_version IN NUMBER,
243 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 p_aehv_rec IN aehv_rec_type);
248
249 PROCEDURE validate_row(
250 p_api_version IN NUMBER,
251 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_count OUT NOCOPY NUMBER,
254 x_msg_data OUT NOCOPY VARCHAR2,
255 p_aehv_tbl IN aehv_tbl_type);
256
257 END Okl_Aeh_Pvt;