[Home] [Help]
PACKAGE: APPS.OKL_PTC_PVT
Source
1 PACKAGE OKL_PTC_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKLSPTCS.pls 120.2 2005/10/30 04:43:55 appldev noship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL DATASTRUCTURES
5 ---------------------------------------------------------------------------
6 TYPE ptcv_rec_type IS RECORD (
7 id NUMBER := OKC_API.G_MISS_NUM
8 ,sequence_number NUMBER := OKC_API.G_MISS_NUM
9 ,asset_id NUMBER := OKC_API.G_MISS_NUM
10 ,asset_number OKL_PROPERTY_TAX_V.ASSET_NUMBER%TYPE := OKC_API.G_MISS_CHAR
11 ,contract_number OKL_PROPERTY_TAX_V.contract_number%TYPE := OKC_API.G_MISS_CHAR
12 ,sty_name OKL_PROPERTY_TAX_V.STY_NAME%TYPE := OKC_API.G_MISS_CHAR
13 ,sty_id NUMBER := OKC_API.G_MISS_NUM
14 ,invoice_date OKL_PROPERTY_TAX_V.INVOICE_DATE%TYPE := OKC_API.G_MISS_DATE
15 ,amount NUMBER := OKC_API.G_MISS_NUM
16 ,org_id NUMBER := OKC_API.G_MISS_NUM
17 ,asset_description OKL_PROPERTY_TAX_V.ASSET_DESCRIPTION%TYPE := OKC_API.G_MISS_CHAR
18 ,khr_id NUMBER := OKC_API.G_MISS_NUM
19 ,kle_id NUMBER := OKC_API.G_MISS_NUM
20 ,asset_units NUMBER := OKC_API.G_MISS_NUM
21 ,language OKL_PROPERTY_TAX_V.LANGUAGE%TYPE := OKC_API.G_MISS_CHAR
22 ,source_lang OKL_PROPERTY_TAX_V.SOURCE_LANG%TYPE := OKC_API.G_MISS_CHAR
23 ,sfwt_flag OKL_PROPERTY_TAX_V.SFWT_FLAG%TYPE := OKC_API.G_MISS_CHAR
24 ,asset_address_1 OKL_PROPERTY_TAX_V.ASSET_ADDRESS_1%TYPE := OKC_API.G_MISS_CHAR
25 ,asset_address_2 OKL_PROPERTY_TAX_V.ASSET_ADDRESS_2%TYPE := OKC_API.G_MISS_CHAR
26 ,asset_address_3 OKL_PROPERTY_TAX_V.ASSET_ADDRESS_3%TYPE := OKC_API.G_MISS_CHAR
27 ,asset_address_4 OKL_PROPERTY_TAX_V.ASSET_ADDRESS_4%TYPE := OKC_API.G_MISS_CHAR
28 ,asset_city OKL_PROPERTY_TAX_V.ASSET_CITY%TYPE := OKC_API.G_MISS_CHAR
29 ,asset_state OKL_PROPERTY_TAX_V.ASSET_STATE%TYPE := OKC_API.G_MISS_CHAR
30 ,asset_country OKL_PROPERTY_TAX_V.ASSET_COUNTRY%TYPE := OKC_API.G_MISS_CHAR
31 ,tax_assessment_amount NUMBER := OKC_API.G_MISS_NUM
32 ,tax_jurisdiction_city OKL_PROPERTY_TAX_V.TAX_JURISDICTION_CITY%TYPE := OKC_API.G_MISS_CHAR
33 -- End Addition for Est Property Tax
34 ,JURSDCTN_TYPE OKL_PROPERTY_TAX_V.JURSDCTN_TYPE%TYPE := OKC_API.G_MISS_CHAR
35 ,JURSDCTN_NAME OKL_PROPERTY_TAX_V.JURSDCTN_NAME%TYPE := OKC_API.G_MISS_CHAR
36 ,MLRT_TAX NUMBER := OKC_API.G_MISS_NUM
37 ,TAX_VENDOR_ID NUMBER := OKC_API.G_MISS_NUM
38 ,TAX_VENDOR_NAME OKL_PROPERTY_TAX_V.TAX_VENDOR_NAME%TYPE := OKC_API.G_MISS_CHAR
39 ,TAX_VENDOR_SITE_ID NUMBER := OKC_API.G_MISS_NUM
40 ,TAX_VENDOR_SITE_NAME OKL_PROPERTY_TAX_V.TAX_VENDOR_SITE_NAME%TYPE := OKC_API.G_MISS_CHAR
41 -- End Addition for Est Property Tax
42 ,tax_jurisdiction_city_rate NUMBER := OKC_API.G_MISS_NUM
43 ,tax_jurisdiction_county OKL_PROPERTY_TAX_V.TAX_JURISDICTION_COUNTY%TYPE := OKC_API.G_MISS_CHAR
44 ,tax_jurisdiction_county_rate NUMBER := OKC_API.G_MISS_NUM
45 ,tax_jurisdiction_state OKL_PROPERTY_TAX_V.TAX_JURISDICTION_STATE%TYPE := OKC_API.G_MISS_CHAR
46 ,tax_jurisdiction_state_rate NUMBER := OKC_API.G_MISS_NUM
47 ,tax_jurisdiction_school OKL_PROPERTY_TAX_V.TAX_JURISDICTION_SCHOOL%TYPE := OKC_API.G_MISS_CHAR
48 ,tax_jurisdiction_school_rate NUMBER := OKC_API.G_MISS_NUM
49 ,tax_jurisdiction_country OKL_PROPERTY_TAX_V.TAX_JURISDICTION_COUNTRY%TYPE := OKC_API.G_MISS_CHAR
50 ,tax_jurisdiction_country_rate NUMBER := OKC_API.G_MISS_NUM
51 ,tax_assessment_date OKL_PROPERTY_TAX_V.TAX_ASSESSMENT_DATE%TYPE := OKC_API.G_MISS_DATE
52 ,milrate NUMBER := OKC_API.G_MISS_NUM
53 ,property_tax_amount NUMBER := OKC_API.G_MISS_NUM
54 ,oec NUMBER := OKC_API.G_MISS_NUM
55 ,created_by NUMBER := OKC_API.G_MISS_NUM
56 ,creation_date OKL_PROPERTY_TAX_V.CREATION_DATE%TYPE := OKC_API.G_MISS_DATE
57 ,last_updated_by NUMBER := OKC_API.G_MISS_NUM
58 ,last_update_date OKL_PROPERTY_TAX_V.LAST_UPDATE_DATE%TYPE := OKC_API.G_MISS_DATE
59 ,last_update_login NUMBER := OKC_API.G_MISS_NUM);
60 G_MISS_ptcv_rec ptcv_rec_type;
61 TYPE ptcv_tbl_type IS TABLE OF ptcv_rec_type
62 INDEX BY BINARY_INTEGER;
63 TYPE ptc_rec_type IS RECORD (
64 id NUMBER := OKC_API.G_MISS_NUM
65 ,sequence_number NUMBER := OKC_API.G_MISS_NUM
66 ,asset_id NUMBER := OKC_API.G_MISS_NUM
67 ,asset_number OKL_PROPERTY_TAX_B.ASSET_NUMBER%TYPE := OKC_API.G_MISS_CHAR
68 ,khr_id NUMBER := OKC_API.G_MISS_NUM
69 ,kle_id NUMBER := OKC_API.G_MISS_NUM
70 ,asset_units NUMBER := OKC_API.G_MISS_NUM
71 ,asset_address_1 OKL_PROPERTY_TAX_B.ASSET_ADDRESS_1%TYPE := OKC_API.G_MISS_CHAR
72 ,asset_address_2 OKL_PROPERTY_TAX_B.ASSET_ADDRESS_2%TYPE := OKC_API.G_MISS_CHAR
73 ,asset_address_3 OKL_PROPERTY_TAX_B.ASSET_ADDRESS_3%TYPE := OKC_API.G_MISS_CHAR
74 ,asset_address_4 OKL_PROPERTY_TAX_B.ASSET_ADDRESS_4%TYPE := OKC_API.G_MISS_CHAR
75 ,asset_city OKL_PROPERTY_TAX_B.ASSET_CITY%TYPE := OKC_API.G_MISS_CHAR
76 ,asset_state OKL_PROPERTY_TAX_B.ASSET_STATE%TYPE := OKC_API.G_MISS_CHAR
77 ,asset_country OKL_PROPERTY_TAX_B.ASSET_COUNTRY%TYPE := OKC_API.G_MISS_CHAR
78 ,tax_assessment_amount NUMBER := OKC_API.G_MISS_NUM
79 ,tax_jurisdiction_city OKL_PROPERTY_TAX_B.TAX_JURISDICTION_CITY%TYPE := OKC_API.G_MISS_CHAR
80 -- Addition for Est Property Tax
81 ,JURSDCTN_TYPE OKL_PROPERTY_TAX_B.JURSDCTN_TYPE%TYPE := OKC_API.G_MISS_CHAR
82 ,JURSDCTN_NAME OKL_PROPERTY_TAX_B.JURSDCTN_NAME%TYPE := OKC_API.G_MISS_CHAR
83 ,MLRT_TAX NUMBER := OKC_API.G_MISS_NUM
84 ,TAX_VENDOR_ID NUMBER := OKC_API.G_MISS_NUM
85 ,TAX_VENDOR_NAME OKL_PROPERTY_TAX_B.TAX_VENDOR_NAME%TYPE := OKC_API.G_MISS_CHAR
86 ,TAX_VENDOR_SITE_ID NUMBER := OKC_API.G_MISS_NUM
87 ,TAX_VENDOR_SITE_NAME OKL_PROPERTY_TAX_B.TAX_VENDOR_SITE_NAME%TYPE := OKC_API.G_MISS_CHAR
88 -- End Addition for Est Property Tax
89 ,tax_jurisdiction_city_rate NUMBER := OKC_API.G_MISS_NUM
90 ,tax_jurisdiction_county OKL_PROPERTY_TAX_B.TAX_JURISDICTION_COUNTY%TYPE := OKC_API.G_MISS_CHAR
91 ,tax_jurisdiction_county_rate NUMBER := OKC_API.G_MISS_NUM
92 ,tax_jurisdiction_state OKL_PROPERTY_TAX_B.TAX_JURISDICTION_STATE%TYPE := OKC_API.G_MISS_CHAR
93 ,tax_jurisdiction_state_rate NUMBER := OKC_API.G_MISS_NUM
94 ,tax_jurisdiction_school OKL_PROPERTY_TAX_B.TAX_JURISDICTION_SCHOOL%TYPE := OKC_API.G_MISS_CHAR
95 ,tax_jurisdiction_school_rate NUMBER := OKC_API.G_MISS_NUM
96 ,tax_jurisdiction_country OKL_PROPERTY_TAX_B.TAX_JURISDICTION_COUNTRY%TYPE := OKC_API.G_MISS_CHAR
97 ,tax_jurisdiction_country_rate NUMBER := OKC_API.G_MISS_NUM
98 ,tax_assessment_date OKL_PROPERTY_TAX_B.TAX_ASSESSMENT_DATE%TYPE := OKC_API.G_MISS_DATE
99 ,milrate NUMBER := OKC_API.G_MISS_NUM
100 ,property_tax_amount NUMBER := OKC_API.G_MISS_NUM
101 ,oec NUMBER := OKC_API.G_MISS_NUM
102 ,created_by NUMBER := OKC_API.G_MISS_NUM
103 ,creation_date OKL_PROPERTY_TAX_B.CREATION_DATE%TYPE := OKC_API.G_MISS_DATE
104 ,last_updated_by NUMBER := OKC_API.G_MISS_NUM
105 ,last_update_date OKL_PROPERTY_TAX_B.LAST_UPDATE_DATE%TYPE := OKC_API.G_MISS_DATE
106 ,last_update_login NUMBER := OKC_API.G_MISS_NUM
107 ,contract_number OKL_PROPERTY_TAX_B.contract_number%TYPE := OKC_API.G_MISS_CHAR
108 ,sty_name OKL_PROPERTY_TAX_B.STY_NAME%TYPE := OKC_API.G_MISS_CHAR
109 ,sty_id NUMBER := OKC_API.G_MISS_NUM
110 ,invoice_date OKL_PROPERTY_TAX_B.INVOICE_DATE%TYPE := OKC_API.G_MISS_DATE
111 ,amount NUMBER := OKC_API.G_MISS_NUM
112 ,org_id NUMBER := OKC_API.G_MISS_NUM);
113 G_MISS_ptc_rec ptc_rec_type;
114 TYPE ptc_tbl_type IS TABLE OF ptc_rec_type
115 INDEX BY BINARY_INTEGER;
116 TYPE ptct_rec_type IS RECORD (
117 id NUMBER := OKC_API.G_MISS_NUM
118 ,language OKL_PROPERTY_TAX_TL.LANGUAGE%TYPE := OKC_API.G_MISS_CHAR
119 ,source_lang OKL_PROPERTY_TAX_TL.SOURCE_LANG%TYPE := OKC_API.G_MISS_CHAR
120 ,sfwt_flag OKL_PROPERTY_TAX_TL.SFWT_FLAG%TYPE := OKC_API.G_MISS_CHAR
121 ,asset_description OKL_PROPERTY_TAX_TL.ASSET_DESCRIPTION%TYPE := OKC_API.G_MISS_CHAR
122 ,created_by NUMBER := OKC_API.G_MISS_NUM
123 ,creation_date OKL_PROPERTY_TAX_TL.CREATION_DATE%TYPE := OKC_API.G_MISS_DATE
124 ,last_updated_by NUMBER := OKC_API.G_MISS_NUM
125 ,last_update_date OKL_PROPERTY_TAX_TL.LAST_UPDATE_DATE%TYPE := OKC_API.G_MISS_DATE
126 ,last_update_login NUMBER := OKC_API.G_MISS_NUM);
127 G_MISS_ptct_rec ptct_rec_type;
128 TYPE ptct_tbl_type IS TABLE OF ptct_rec_type
129 INDEX BY BINARY_INTEGER;
130 ---------------------------------------------------------------------------
131 -- GLOBAL MESSAGE CONSTANTS
132 ---------------------------------------------------------------------------
133 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
134 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_FORM_UNABLE_TO_RESERVE_REC;
135 G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_FORM_RECORD_DELETED;
136 G_FORM_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_FORM_RECORD_CHANGED;
137 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
138 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
139 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
140 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
141 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
142 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
143 --G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKS_SERVICE_AVAILABILITY_UNEXPECTED_ERROR';
144 --G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
145 --G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
146
147 ---------------------------------------------------------------------------
148 -- GLOBAL EXCEPTIONS
149 ---------------------------------------------------------------------------
150 --G_EXCEPTION_HALT_VALIDATION EXCEPTION;
151
152 ---------------------------------------------------------------------------
153 -- GLOBAL VARIABLES
154 ---------------------------------------------------------------------------
155 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_PTC_PVT';
156 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
157
158 -------------------------------------------------------------------------------
159 --Post change to TAPI code
160 -------------------------------------------------------------------------------
161 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
162 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXPECTED_ERROR';
163 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
164 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
165 g_no_parent_record CONSTANT VARCHAR2(200) := 'OKL_NO_PARENT_RECORD';
166 ---------------------------------------------------------------------------
167
168 ---------------------------------------------------------------------------
169 -- Procedures and Functions
170 ---------------------------------------------------------------------------
171
172 PROCEDURE qc;
173 PROCEDURE change_version;
174 PROCEDURE api_copy;
175 PROCEDURE add_language;
176 PROCEDURE insert_row(
177 p_api_version IN NUMBER,
178 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
179 x_return_status OUT NOCOPY VARCHAR2,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2,
182 p_ptcv_rec IN ptcv_rec_type,
183 x_ptcv_rec OUT NOCOPY ptcv_rec_type);
184 PROCEDURE insert_row(
185 p_api_version IN NUMBER,
186 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
187 x_return_status OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2,
190 p_ptcv_tbl IN ptcv_tbl_type,
191 x_ptcv_tbl OUT NOCOPY ptcv_tbl_type,
192 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE);
193 PROCEDURE insert_row(
194 p_api_version IN NUMBER,
195 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
196 x_return_status OUT NOCOPY VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_msg_data OUT NOCOPY VARCHAR2,
199 p_ptcv_tbl IN ptcv_tbl_type,
200 x_ptcv_tbl OUT NOCOPY ptcv_tbl_type);
201 PROCEDURE lock_row(
202 p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2,
207 p_ptcv_rec IN ptcv_rec_type);
208 PROCEDURE lock_row(
209 p_api_version IN NUMBER,
210 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
211 x_return_status OUT NOCOPY VARCHAR2,
212 x_msg_count OUT NOCOPY NUMBER,
213 x_msg_data OUT NOCOPY VARCHAR2,
214 p_ptcv_tbl IN ptcv_tbl_type,
215 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE);
216 PROCEDURE lock_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_ptcv_tbl IN ptcv_tbl_type);
223 PROCEDURE update_row(
224 p_api_version IN NUMBER,
225 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2,
229 p_ptcv_rec IN ptcv_rec_type,
230 x_ptcv_rec OUT NOCOPY ptcv_rec_type);
231 PROCEDURE update_row(
232 p_api_version IN NUMBER,
233 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2,
237 p_ptcv_tbl IN ptcv_tbl_type,
238 x_ptcv_tbl OUT NOCOPY ptcv_tbl_type,
239 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE);
240 PROCEDURE update_row(
241 p_api_version IN NUMBER,
242 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
243 x_return_status OUT NOCOPY VARCHAR2,
244 x_msg_count OUT NOCOPY NUMBER,
245 x_msg_data OUT NOCOPY VARCHAR2,
246 p_ptcv_tbl IN ptcv_tbl_type,
247 x_ptcv_tbl OUT NOCOPY ptcv_tbl_type);
248 PROCEDURE delete_row(
249 p_api_version IN NUMBER,
250 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_count OUT NOCOPY NUMBER,
253 x_msg_data OUT NOCOPY VARCHAR2,
254 p_ptcv_rec IN ptcv_rec_type);
255 PROCEDURE delete_row(
256 p_api_version IN NUMBER,
257 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
258 x_return_status OUT NOCOPY VARCHAR2,
259 x_msg_count OUT NOCOPY NUMBER,
260 x_msg_data OUT NOCOPY VARCHAR2,
261 p_ptcv_tbl IN ptcv_tbl_type,
262 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE);
263 PROCEDURE delete_row(
264 p_api_version IN NUMBER,
265 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
266 x_return_status OUT NOCOPY VARCHAR2,
267 x_msg_count OUT NOCOPY NUMBER,
268 x_msg_data OUT NOCOPY VARCHAR2,
269 p_ptcv_tbl IN ptcv_tbl_type);
270 PROCEDURE validate_row(
271 p_api_version IN NUMBER,
272 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
273 x_return_status OUT NOCOPY VARCHAR2,
274 x_msg_count OUT NOCOPY NUMBER,
275 x_msg_data OUT NOCOPY VARCHAR2,
276 p_ptcv_rec IN ptcv_rec_type);
277 PROCEDURE validate_row(
278 p_api_version IN NUMBER,
279 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
280 x_return_status OUT NOCOPY VARCHAR2,
281 x_msg_count OUT NOCOPY NUMBER,
282 x_msg_data OUT NOCOPY VARCHAR2,
283 p_ptcv_tbl IN ptcv_tbl_type,
284 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE);
285 PROCEDURE validate_row(
286 p_api_version IN NUMBER,
287 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2,
291 p_ptcv_tbl IN ptcv_tbl_type);
292 END OKL_PTC_PVT;