[Home] [Help]
PACKAGE BODY: APPS.OKL_BLK_AST_UPD_PVT
Source
1 PACKAGE BODY OKL_BLK_AST_UPD_PVT AS
2 /* $Header: OKLRBAUB.pls 120.18 2008/05/28 01:18:55 sechawla noship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8
9 SUBTYPE instance_rec IS CSI_DATASTRUCTURES_PUB.instance_rec;
10 SUBTYPE extend_attrib_values_tbl IS CSI_DATASTRUCTURES_PUB.extend_attrib_values_tbl;
11 SUBTYPE party_tbl IS CSI_DATASTRUCTURES_PUB.party_tbl;
12 SUBTYPE account_tbl IS CSI_DATASTRUCTURES_PUB.party_account_tbl;
13 SUBTYPE pricing_attribs_tbl IS CSI_DATASTRUCTURES_PUB.pricing_attribs_tbl;
14 SUBTYPE organization_units_tbl IS CSI_DATASTRUCTURES_PUB.organization_units_tbl;
15 SUBTYPE instance_asset_tbl IS CSI_DATASTRUCTURES_PUB.instance_asset_tbl;
16 SUBTYPE transaction_rec IS CSI_DATASTRUCTURES_PUB.transaction_rec;
17 SUBTYPE id_tbl IS CSI_DATASTRUCTURES_PUB.id_tbl;
18
19 l_instance_rec instance_rec;
20 l_ext_attrib_values_tbl extend_attrib_values_tbl;
21 l_party_tbl party_tbl;
22 l_account_tbl account_tbl;
23 l_pricing_attrib_tbl pricing_attribs_tbl;
24 l_org_assignments_tbl organization_units_tbl;
25 l_asset_assignment_tbl instance_asset_tbl;
26 l_txn_rec transaction_rec;
27 l_instance_id_lst id_tbl;
28
29
30 PROCEDURE create_txl_itm_insts(
31 p_api_version IN NUMBER,
32 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2,
36 p_date_from IN DATE,
37 p_itiv_rec IN itiv_rec_type,
38 p_request_id IN NUMBER,
39 x_trxv_rec OUT NOCOPY trxv_rec_type,
40 x_itiv_rec OUT NOCOPY itiv_rec_type);
41
42 PROCEDURE Create_asset_header(
43 p_api_version IN NUMBER,
44 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2,
48 p_trxv_rec IN trxv_rec_type,
49 x_trxv_rec OUT NOCOPY trxv_rec_type) ;
50
51 PROCEDURE Update_asset_header(
52 p_api_version IN NUMBER,
53 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2,
57 p_trxv_rec IN trxv_rec_type,
58 x_trxv_rec OUT NOCOPY trxv_rec_type);
59
60 FUNCTION get_try_id(p_try_name IN OKL_TRX_TYPES_V.NAME%TYPE,
61 x_try_id OUT NOCOPY OKC_LINE_STYLES_V.ID%TYPE)
62 RETURN VARCHAR2 ;
63
64 -----------------------------------------------------------------------------
65 --Start of comments
66 --
67 --Procedure Name : get_trx_rec
68 --Purpose : Gets source transaction record for IB interface
69 --Modification History :
70 --15-Jun-2001 ashish.singh Created
71 --Notes : Assigns values to transaction_type_id and source_line_ref_id
72 --End of Comments
73 ------------------------------------------------------------------------------
74 PROCEDURE get_trx_rec
75 (p_api_version IN NUMBER,
76 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_count OUT NOCOPY NUMBER,
79 x_msg_data OUT NOCOPY VARCHAR2,
80 p_cle_id IN NUMBER,
81 p_transaction_type IN VARCHAR2,
82 x_trx_rec OUT NOCOPY transaction_rec) IS
83
84 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
85 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRX_REC';
86 l_api_version CONSTANT NUMBER := 1.0;
87
88 --Following cursor assumes that a transaction type called
89 --'OKL LINE ACTIVATION' and 'OKL SPLIT ASSET' will be seeded in IB
90 Cursor okl_trx_type_curs(p_transaction_type IN VARCHAR2)is
91 select transaction_type_id
92 from CS_TRANSACTION_TYPES_V
93 where Name = p_transaction_type;
94 l_trx_type_id NUMBER;
95 begin
96 open okl_trx_type_curs(p_transaction_type);
97 Fetch okl_trx_type_curs
98 into l_trx_type_id;
99 If okl_trx_type_curs%NotFound Then
100 --OKL LINE ACTIVATION not seeded as a source transaction in IB
101 Raise OKC_API.G_EXCEPTION_ERROR;
102 End If;
103 close okl_trx_type_curs;
104 --Assign transaction Type id to seeded value in cs_lookups
105 x_trx_rec.transaction_type_id := l_trx_type_id;
106 --Assign Source Line Ref id to contract line id of IB instance line
107 x_trx_rec.source_line_ref_id := p_cle_id;
108 x_trx_rec.transaction_date := SYSDATE;
109 x_trx_rec.source_transaction_date := sysdate;
110 Exception
111 When OKC_API.G_EXCEPTION_ERROR Then
112 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
113 (
114 l_api_name,
115 G_PKG_NAME,
116 'OKC_API.G_RET_STS_ERROR',
117 x_msg_count,
118 x_msg_data,
119 '_PVT'
120 );
121 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
122 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
123 (
124 l_api_name,
125 G_PKG_NAME,
126 'OKC_API.G_RET_STS_UNEXP_ERROR',
127 x_msg_count,
128 x_msg_data,
129 '_PVT'
130 );
131 WHEN OTHERS THEN
132 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
133 (
134 l_api_name,
135 G_PKG_NAME,
136 'OTHERS',
137 x_msg_count,
138 x_msg_data,
139 '_PVT'
140 );
141 END get_trx_rec;
142
143 PROCEDURE update_location(
144 p_api_version IN NUMBER,
145 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
146 p_loc_rec IN OKL_LOC_REC_TYPE,
147 x_return_status OUT NOCOPY VARCHAR2,
148 x_msg_count OUT NOCOPY NUMBER,
149 x_msg_data OUT NOCOPY VARCHAR2) IS
150
151 CURSOR c_free_form2(p_parent_line_id IN NUMBER) IS
152 SELECT A.ID
153 FROM OKC_K_LINES_V A,
154 OKC_LINE_STYLES_B B
155 WHERE A.CLE_ID = p_parent_line_id
156 AND A.LSE_ID = B.ID
157 AND B.LTY_CODE = 'FREE_FORM2';
158
159 CURSOR c_inst_item(p_line_id IN NUMBER) IS
160 SELECT A.ID,
161 A.DNZ_CHR_ID
162 FROM OKC_K_LINES_V A,
163 OKC_LINE_STYLES_B B
164 WHERE A.CLE_ID = p_line_id
165 AND A.LSE_ID = B.ID
166 AND B.LTY_CODE = 'INST_ITEM';
167
168 CURSOR c_items(p_inst_itm_id IN NUMBER) is
169 SELECT *
170 FROM OKC_K_ITEMS_V
171 WHERE CLE_ID = p_inst_itm_id
172 AND JTOT_OBJECT1_CODE = 'OKX_IB_ITEM';
173
174
175 CURSOR c_ib_inst(p_object1_id1 IN VARCHAR2,p_object1_id2 IN VARCHAR2) IS
176 SELECT *
177 FROM OKX_INSTALL_ITEMS_V A
178 WHERE ID1 = p_object1_id1
179 AND ID2 = p_object1_id2;
180
181 --RKUTTIYA added for bug: 3569441
182 CURSOR c_loc_typecode(p_instance_id IN NUMBER) IS
183 SELECT LOCATION_TYPE_CODE,
184 INSTALL_LOCATION_TYPE_CODE
185 FROM csi_item_instances
186 WHERE INSTANCE_ID = p_instance_id;
187
188 --Added by rkuttiya for Sales Tax project
189 CURSOR c_bill_upfront_tax(p_contract_id IN NUMBER,
190 p_line_id IN NUMBER)
191 IS
192 SELECT RUL.RULE_INFORMATION11
193 FROM OKC_RULES_B RUL,
194 OKC_RULE_GROUPS_B RGP
195 WHERE RUL.RGP_ID = RGP.ID
196 AND RGP.DNZ_CHR_ID = p_contract_id
197 AND RGP.CLE_ID = p_line_id
198 AND RUL.RULE_INFORMATION_CATEGORY = 'LAASTX'
199 AND RGP.RGD_CODE = 'LAASTX';
200
201 CURSOR c_asset_upfront_tax(p_contract_id IN NUMBER)
202 IS
203 SELECT RUL.RULE_INFORMATION1
204 FROM OKC_RULES_B RUL,
205 OKC_RULE_GROUPS_B RGP
206 WHERE RUL.RGP_ID = RGP.ID
207 AND RGP.DNZ_CHR_ID = p_contract_id
208 AND RUL.RULE_INFORMATION_CATEGORY = 'LASTPR'
209 AND RGP.RGD_CODE = 'LAHDTX';
210
211 -- dcshanmu bug 6673102 start
212 CURSOR c_upfront_tax_calc(p_contract_id IN NUMBER)
213 IS
214 SELECT 'X'
215 FROM OKL_TAX_SOURCES
216 WHERE KHR_ID = p_contract_id
217 AND TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
218 AND TAX_LINE_STATUS_CODE = 'ACTIVE';
219 -- dcshanmu bug 6673102 end
220 --
221 --asawanka added for ebtax start
222 CURSOR c_get_entered_alc_trx(cp_kle_id IN NUMBER)
223 IS
224 SELECT 'X'
225 FROM OKL_TRX_ASSETS TRX,
226 OKL_TXL_ITM_INSTS TXL,
227 OKL_TRX_TYPES_B TRY
228 WHERE TRX.ID = TXL.TAS_ID
229 AND TRX.TRY_ID = TRY.ID
230 AND TRY.TRX_TYPE_CLASS = 'ASSET_RELOCATION'
231 AND TRX.TSU_CODE = 'ENTERED'
232 AND TRX.TAS_TYPE = 'ALG'
233 AND TXL.dnz_cle_id = cp_kle_id;
234
235 CURSOR c_get_chr_id (p_kle_id IN NUMBER) IS
236 SELECT kle.dnz_chr_id,
237 khr.org_id,
238 khr.currency_code
239 FROM okc_k_lines_v kle,
240 okc_k_headers_all_b khr
241 WHERE kle.id = p_kle_id
242 AND kle.dnz_chr_id = khr.id;
243
244 CURSOR check_item_csr (p_line_id IN NUMBER) IS -- p_line_id is FREE_FORM1
245 SELECT mtl.serial_number_control_code
246 FROM okc_k_lines_b line,
247 okc_line_styles_b style,
248 okc_k_items kitem,
249 mtl_system_items mtl
250 WHERE line.lse_id = style.id
251 AND style.lty_code = 'ITEM'
252 AND line.id = kitem.cle_id
253 AND kitem.jtot_object1_code = 'OKX_SYSITEM'
254 AND kitem.object1_id1 = mtl.inventory_item_id
255 AND kitem.object1_id2 = TO_CHAR(mtl.organization_id)
256 AND line.cle_id = p_line_id;
257
258 CURSOR c_get_ast_instances(p_parent_line_id IN NUMBER) IS
259 SELECT count(*)
260 FROM okc_k_lines_v okcl,
261 okc_line_styles_v lse
262 WHERE okcl.cle_id = p_parent_line_id
263 AND okcl.lse_id = lse.id
264 AND lse.lty_code = 'FREE_FORM2';
265
266 l_entered VARCHAR2(3);
267 --asawanka ebtax changes end
268 --added for bug:3569441
269 l_inst_loc_type_code VARCHAR2(30);
270 l_loc_type_code VARCHAR2(30);
271
272 l_c_ib_inst c_ib_inst%ROWTYPE;
273 l_ctr NUMBER;
274 l_obj_no NUMBER;
275
276 l_trqv_rec okl_trx_requests_pub.trqv_rec_type;
277 x_trqv_rec okl_trx_requests_pub.trqv_rec_type;
278 l_org_id NUMBER;
279 l_currency_code VARCHAR2(30);
280 l_try_id NUMBER;
281 l_serialized_yn VARCHAR2(3);
282 l_count mtl_system_items.serial_number_control_code%TYPE;
283 l_ser_count NUMBER;
284
285 l_trxv_rec trxv_rec_type;
286 lm_trxv_rec trxv_rec_type;
287 l_itiv_rec itiv_rec_type;
288 l_out_rec itiv_rec_type;
289 l_rulv_rec okl_rule_pub.rulv_rec_type;
290 l_rulv_empty_rec okl_rule_pub.rulv_rec_type;
291
292 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LOCATION';
293 l_chr_id NUMBER;
294 l_parent_line_id NUMBER;
295 l_loc_id NUMBER;
296 l_party_site_id NUMBER;
297 l_newsite_id1 NUMBER;
298 l_newsite_id2 VARCHAR2(1);
299 l_oldsite_id1 NUMBER;
300 l_oldsite_id2 VARCHAR2(1);
301 l_bill_upfront_tax VARCHAR2(450);
302 l_asset_upfront_tax VARCHAR2(450);
303 l_tax_call_type VARCHAR2(30);
304 l_alc_final_call VARCHAR2(1) := 'N';
305 -- dcshanmu bug 6673102 start
306 l_upfront_tax_calc VARCHAR2(1);
307 -- dcshanmu bug 6673102 end
308 BEGIN
309 IF (G_DEBUG_ENABLED = 'Y') THEN
310 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
311 END IF;
312 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
313 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_BLK_AST_UPD_PVT.Update_Location','Begin(+)');
314 END IF;
315
316 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
317 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Input variables in Update Location');
318 END IF;
319 --Print Input Variables
320 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
321 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_parent_line_id :'||p_loc_rec.parent_line_id);
322 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_loc_id :'||p_loc_rec.loc_id);
323 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_party_site_id :'||p_loc_rec.party_site_id);
324 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_newsite_id1 :'||p_loc_rec.newsite_id1);
325 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_newsite_id2 :'||p_loc_rec.newsite_id2);
326 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_oldsite_id1 :'||p_loc_rec.oldsite_id1);
327 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_oldsite_id2 :'||p_loc_rec.oldsite_id2);
328 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_date_from :'||p_loc_rec.date_from);
329 END IF;
330
331 x_return_status := OKL_API.G_RET_STS_SUCCESS;
332
333 --Call start_activity to create savepoint, check compatibility and initialize message list
334
335 x_return_status := OKL_API.START_ACTIVITY(
336 l_api_name
337 ,p_init_msg_list
338 ,'_PVT'
339 ,x_return_status);
340
341 --Check if activity started successfully
342
343 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
344 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
345 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
346 RAISE OKL_API.G_EXCEPTION_ERROR;
347 END IF;
348
349 --asawanka ebtax changes start
350
351 OPEN c_get_entered_alc_trx(p_loc_rec.parent_line_id);
352 FETCH c_get_entered_alc_trx INTO l_entered;
353 IF c_get_entered_alc_trx%FOUND THEN
354 OKL_API.set_message( p_app_name => 'OKL',
355 p_msg_name => 'OKL_ASTLOC_CHNG_NA_ENT');
356 RAISE OKL_API.G_EXCEPTION_ERROR;
357 END IF;
358
359 OPEN c_get_chr_id(p_loc_rec.parent_line_id);
360 FETCH c_get_chr_id INTO l_chr_id,l_org_id ,l_currency_code;
361 CLOSE c_get_chr_id;
362
363 OPEN c_bill_upfront_tax(l_chr_id,p_loc_rec.parent_line_id);
364 FETCH c_bill_upfront_tax INTO l_bill_upfront_tax;
365 CLOSE c_bill_upfront_tax;
366
367 IF l_bill_upfront_tax IS NOT NULL THEN
368 IF l_bill_upfront_tax <> 'BILLED' THEN
369 OKL_API.set_message( p_app_name => 'OKL',
370 p_msg_name => 'OKL_ASTLOC_CHNG_NA_AST');
371 RAISE OKL_API.G_EXCEPTION_ERROR;
372 END IF;
373 ELSE
374 -- dcshanmu bug 6673102 start
375 OPEN c_upfront_tax_calc(l_chr_id);
376 FETCH c_upfront_tax_calc INTO l_upfront_tax_calc;
377 IF (c_upfront_tax_calc%FOUND) THEN
378 -- dcshanmu bug 6673102 end
379 OPEN c_asset_upfront_tax(l_chr_id);
380 FETCH c_asset_upfront_tax INTO l_asset_upfront_tax;
381 CLOSE c_asset_upfront_tax;
382 IF l_asset_upfront_tax IS NULL OR l_asset_upfront_tax <> 'BILLED' THEN
383 OKL_API.set_message( p_app_name => 'OKL',
384 p_msg_name => 'OKL_ASTLOC_CHNG_NA_KHR');
385 RAISE OKL_API.G_EXCEPTION_ERROR;
386 END IF;
387 -- dcshanmu bug 6673102 start
388 END IF;
389 CLOSE c_upfront_tax_calc;
390 -- dcshanmu bug 6673102 end
391 END IF;
392
393 OPEN check_item_csr(p_loc_rec.parent_line_id);
394 FETCH check_item_csr INTO l_count;
395 CLOSE check_item_csr;
396
397 IF l_count = 1 THEN
398 l_serialized_yn := 'N';
399 ELSE
400 l_serialized_yn := 'Y';
401 END IF;
402
403 IF (l_serialized_yn = 'Y') THEN
404 OPEN c_get_ast_instances(p_loc_rec.parent_line_id);
405 FETCH c_get_ast_instances INTO l_ser_count;
406 CLOSE c_get_ast_instances;
407 END IF;
408
409 x_return_status := get_try_id(p_try_name => G_TRY_NAME,
410 x_try_id => l_try_id);
411
412 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
413 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
414 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
415 RAISE OKL_API.G_EXCEPTION_ERROR;
416 END IF;
417
418 l_trqv_rec.request_status_code := 'ENTERED';
419 l_trqv_rec.request_type_code := 'ASSET_RELOCATION';
420
421 l_trqv_rec.dnz_khr_id := l_chr_id;
422 l_trqv_rec.org_id := l_org_id;
423 l_trqv_rec.legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(l_chr_id);
424 l_trqv_rec.currency_code := l_currency_code;
425 l_trqv_rec.start_date := p_loc_rec.date_from;
426 l_trqv_rec.try_id := l_try_id;
427 okl_trx_requests_pub.insert_trx_requests(p_api_version => p_api_version,
428 p_init_msg_list => p_init_msg_list,
429 x_return_status => x_return_status,
430 x_msg_count => x_msg_count,
431 x_msg_data => x_msg_data,
432 p_trqv_rec => l_trqv_rec,
433 x_trqv_rec => x_trqv_rec);
434 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
435 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
436 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
437 RAISE OKL_API.G_EXCEPTION_ERROR;
438 END IF;
439
440 G_CTR := 1;
441 --asawanka ebtax changes end
442 FOR l_c_free_form2 in c_free_form2(p_loc_rec.parent_line_id) LOOP
443 FOR l_c_inst_item IN c_inst_item(l_c_free_form2.id) LOOP
444 l_chr_id := l_c_inst_item.dnz_chr_id;
445 FOR l_c_item IN c_items(l_c_inst_item.id) LOOP
446 OPEN c_ib_inst(l_c_item.object1_id1,l_c_item.object1_id2);
447 FETCH c_ib_inst INTO l_c_ib_inst;
448 IF c_ib_inst%FOUND THEN
449
450 ---Creating records in Okl_txl_itm_insts
451
452 l_itiv_rec.kle_id := l_c_item.cle_id;
453 l_itiv_rec.dnz_cle_id := p_loc_rec.parent_line_id;
454 l_itiv_rec.line_number := G_CTR;
455 l_itiv_rec.instance_number_ib := l_c_ib_inst.name;
456 l_itiv_rec.object_id1_new := p_loc_rec.newsite_id1;
457 l_itiv_rec.object_id2_new := p_loc_rec.newsite_id2;
458 l_itiv_rec.jtot_object_code_new := 'OKX_PARTSITE';
459
460 l_itiv_rec.object_id1_old := p_loc_rec.oldsite_id1;
461 l_itiv_rec.object_id2_old := p_loc_rec.oldsite_id2;
462 l_itiv_rec.jtot_object_code_old := 'OKX_PARTSITE';
463 l_itiv_rec.inventory_item_id := l_c_ib_inst.inventory_item_id;
464
465 /** populate the Mandatory parameters **/
466 l_itiv_rec.CREATED_BY := FND_API.G_MISS_NUM;
467 l_itiv_rec.CREATION_DATE := FND_API.G_MISS_DATE;
468 l_itiv_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
469 l_itiv_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
470 l_itiv_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
471 create_txl_itm_insts(p_api_version => p_api_version,
472 p_init_msg_list => p_init_msg_list,
473 x_return_status => x_return_status,
474 x_msg_count => x_msg_count,
475 x_msg_data => x_msg_data,
476 p_date_from => p_loc_rec.date_from,
477 p_itiv_rec => l_itiv_rec,
478 p_request_id => x_trqv_rec.id,
479 x_trxv_rec => l_trxv_rec,
480 x_itiv_rec => l_out_rec
481 );
482
483
484 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
485 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
486 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
487 RAISE OKL_API.G_EXCEPTION_ERROR;
488 END IF;
489
490 IF l_serialized_yn = 'Y' AND G_CTR = 1 THEN
491 IF G_CTR = l_ser_count THEN
492 l_alc_final_call := 'Y';
493 ELSE
494 l_alc_final_call := null;
495 END IF;
496 ELSIF l_serialized_yn = 'Y' AND (G_CTR > 1 AND G_CTR < l_ser_count) THEN
497 l_alc_final_call := 'N';
498 ELSIF l_serialized_yn = 'Y' AND G_CTR = l_ser_count THEN
499 l_alc_final_call := 'Y';
500 END IF;
501
502 --asawanka ebtax changes start
503 OKL_PROCESS_SALES_TAX_PUB.calculate_sales_tax(
504 p_api_version => p_api_version,
505 p_init_msg_list => p_init_msg_list,
506 x_return_status => x_return_status,
507 x_msg_count => x_msg_count,
508 x_msg_data => x_msg_data,
509 p_source_trx_id => l_trxv_rec.id,
510 p_source_trx_name => G_TRY_NAME,
511 p_source_table => G_TRX_TABLE,
512 p_tax_call_type => 'ESTIMATED' ,
513 p_request_id => x_trqv_rec.id,
514 p_serialized_asset => l_serialized_yn,
515 p_alc_final_call => l_alc_final_call);
516
517 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
518 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Return status from Tax API '||x_return_status);
519 END IF;
520
521 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
522 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
523 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
524 RAISE OKC_API.G_EXCEPTION_ERROR;
525 END IF;
526
527 END IF; -- If _ib_inst found
528 CLOSE c_ib_inst;
529 END LOOP;
530 END LOOP;
531 G_CTR := G_CTR + 1;
532 END LOOP;
533
534 OKL_API.END_ACTIVITY (x_msg_count,
535 x_msg_data );
536 EXCEPTION
537 WHEN OKL_API.G_EXCEPTION_ERROR THEN
538 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
539 l_api_name,
540 G_PKG_NAME,
541 'OKL_API.G_RET_STS_ERROR',
542 x_msg_count,
543 x_msg_data,
544 '_PVT');
545 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
546 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
547 l_api_name,
548 G_PKG_NAME,
549 'OKL_API.G_RET_STS_UNEXP_ERROR',
550 x_msg_count,
551 x_msg_data,
552 '_PVT');
553 WHEN OTHERS THEN
554 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
555 l_api_name,
556 G_PKG_NAME,
557 'OTHERS',
558 x_msg_count,
559 x_msg_data,
560 '_PVT');
561
562 END update_location;
563
564 PROCEDURE create_txl_itm_insts(
565 p_api_version IN NUMBER,
566 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
567 x_return_status OUT NOCOPY VARCHAR2,
568 x_msg_count OUT NOCOPY NUMBER,
569 x_msg_data OUT NOCOPY VARCHAR2,
570 p_date_from IN DATE,
571 p_itiv_rec IN itiv_rec_type,
572 p_request_id IN NUMBER,
573 x_trxv_rec OUT NOCOPY trxv_rec_type,
574 x_itiv_rec OUT NOCOPY itiv_rec_type) IS
575
576 l_trxv_rec trxv_rec_type;
577 lm_itiv_rec itiv_rec_type;
578 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TXL_ITM_INSTS';
579 BEGIN
580 x_return_status := OKL_API.G_RET_STS_SUCCESS;
581 -- Call start_activity to create savepoint, check compatibility
582 -- and initialize message list
583 x_return_status := OKL_API.START_ACTIVITY (
584 l_api_name
585 ,p_init_msg_list
586 ,'_PVT'
587 ,x_return_status);
588 -- Check if activity started successfully
589 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
590 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
591 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
592 RAISE OKL_API.G_EXCEPTION_ERROR;
593 END IF;
594 -- Create New Header record and new Line record
595 -- Before creating Header record
596 -- we should make sure atleast the required record is given
597
598 l_trxv_rec.tas_type := 'ALG';
599
600 x_return_status := get_try_id(p_try_name => G_TRY_NAME,
601 x_try_id => l_trxv_rec.try_id);
602
603
604 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
605 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
606 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
607 RAISE OKL_API.G_EXCEPTION_ERROR;
608 END IF;
609
610 l_trxv_rec.tsu_code := 'ENTERED';
611 l_trxv_rec.date_trans_occurred := p_date_from;
612 l_trxv_rec.legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_line_le_id(p_itiv_rec.kle_id); --dkagrawa added to derive le_id from kle_id
613 l_trxv_rec.req_asset_id := p_request_id;
614 -- Now creating the new header record
615 Create_asset_header(p_api_version => p_api_version,
616 p_init_msg_list => p_init_msg_list,
617 x_return_status => x_return_status,
618 x_msg_count => x_msg_count,
619 x_msg_data => x_msg_data,
620 p_trxv_rec => l_trxv_rec,
621 x_trxv_rec => x_trxv_rec);
622
623 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
624 l_trxv_rec := x_trxv_rec;
625 l_trxv_rec.tsu_code := 'ERROR';
626 Update_asset_header(p_api_version => p_api_version,
627 p_init_msg_list => p_init_msg_list,
628 x_return_status => x_return_status,
629 x_msg_count => x_msg_count,
630 x_msg_data => x_msg_data,
631 p_trxv_rec => l_trxv_rec,
632 x_trxv_rec => x_trxv_rec);
633
634 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
635 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
636 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
637 RAISE OKL_API.G_EXCEPTION_ERROR;
638 END IF;
639 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
640 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
641 l_trxv_rec := x_trxv_rec;
642 l_trxv_rec.tsu_code := 'ERROR';
643
644
645 Update_asset_header(p_api_version => p_api_version,
646 p_init_msg_list => p_init_msg_list,
647 x_return_status => x_return_status,
648 x_msg_count => x_msg_count,
649 x_msg_data => x_msg_data,
650 p_trxv_rec => l_trxv_rec,
651 x_trxv_rec => x_trxv_rec);
652 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
653 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
654 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
655 RAISE OKL_API.G_EXCEPTION_ERROR;
656 END IF;
657 RAISE OKL_API.G_EXCEPTION_ERROR;
658 END IF;
659 -- Now we are creating the new line record
660 lm_itiv_rec := p_itiv_rec;
661 lm_itiv_rec.tas_id := x_trxv_rec.id;
662 IF (lm_itiv_rec.tal_type = OKL_API.G_MISS_CHAR OR
663 lm_itiv_rec.tal_type IS NUll) THEN
664 lm_itiv_rec.tal_type := 'AGL';
665 END IF;
666
667
668 IF G_CTR > 1 THEN
669 lm_itiv_rec.mfg_serial_number_yn := 'Y';
670 ELSE
671 lm_itiv_rec.mfg_serial_number_yn := 'N';
672 END IF;
673 -- evaluate conditions, build outcomes for true conditions and
674 -- put them on outcome queue
675
676 OKL_TXL_ITM_INSTS_PUB.create_txl_itm_insts(
677 p_api_version => p_api_version,
678 p_init_msg_list => p_init_msg_list,
679 x_return_status => x_return_status,
680 x_msg_count => x_msg_count,
681 x_msg_data => x_msg_data,
682 p_iipv_rec => lm_itiv_rec,
683 x_iipv_rec => x_itiv_rec);
684
685
686
687 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
688 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
689 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
690 RAISE OKL_API.G_EXCEPTION_ERROR;
691 END IF;
692 OKL_API.END_ACTIVITY (x_msg_count,
693 x_msg_data );
694 EXCEPTION
695 WHEN OKL_API.G_EXCEPTION_ERROR THEN
696 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
697 l_api_name,
698 G_PKG_NAME,
699 'OKL_API.G_RET_STS_ERROR',
700 x_msg_count,
701 x_msg_data,
702 '_PVT');
703 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
704 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
705 l_api_name,
706 G_PKG_NAME,
707 'OKL_API.G_RET_STS_UNEXP_ERROR',
708 x_msg_count,
709 x_msg_data,
710 '_PVT');
711 WHEN OTHERS THEN
712 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
713 l_api_name,
714 G_PKG_NAME,
715 'OTHERS',
716 x_msg_count,
717 x_msg_data,
718 '_PVT');
719 END create_txl_itm_insts;
720
721 PROCEDURE Create_asset_header(
722 p_api_version IN NUMBER,
723 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
724 x_return_status OUT NOCOPY VARCHAR2,
725 x_msg_count OUT NOCOPY NUMBER,
726 x_msg_data OUT NOCOPY VARCHAR2,
727 p_trxv_rec IN trxv_rec_type,
728 x_trxv_rec OUT NOCOPY trxv_rec_type) IS
729 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TRX_ASSET_HEADER';
730 BEGIN
731 x_return_status := OKL_API.G_RET_STS_SUCCESS;
732 -- Call start_activity to create savepoint, check compatibility
733 -- and initialize message list
734 x_return_status := OKL_API.START_ACTIVITY (
735 l_api_name
736 ,p_init_msg_list
737 ,'_PVT'
738 ,x_return_status);
739 -- Check if activity started successfully
740 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
741 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
742 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
743 RAISE OKL_API.G_EXCEPTION_ERROR;
744 END IF;
745 -- evaluate conditions, build outcomes for true conditions and
746 -- put them on outcome queue
747 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def(
748 p_api_version => p_api_version,
749 p_init_msg_list => p_init_msg_list,
750 x_return_status => x_return_status,
751 x_msg_count => x_msg_count,
752 x_msg_data => x_msg_data,
753 p_thpv_rec => p_trxv_rec,
754 x_thpv_rec => x_trxv_rec);
755 OKL_API.END_ACTIVITY (x_msg_count,
756 x_msg_data );
757 EXCEPTION
758 WHEN OKL_API.G_EXCEPTION_ERROR THEN
759 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
760 l_api_name,
761 G_PKG_NAME,
762 'OKL_API.G_RET_STS_ERROR',
763 x_msg_count,
764 x_msg_data,
765 '_PVT');
766 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
767 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
768 l_api_name,
769 G_PKG_NAME,
770 'OKL_API.G_RET_STS_UNEXP_ERROR',
771 x_msg_count,
772 x_msg_data,
773 '_PVT');
774 WHEN OTHERS THEN
775 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
776 l_api_name,
777 G_PKG_NAME,
778 'OTHERS',
779 x_msg_count,
780 x_msg_data,
781 '_PVT');
782 END Create_asset_header;
783
784 PROCEDURE Update_asset_header(
785 p_api_version IN NUMBER,
786 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
787 x_return_status OUT NOCOPY VARCHAR2,
788 x_msg_count OUT NOCOPY NUMBER,
789 x_msg_data OUT NOCOPY VARCHAR2,
790 p_trxv_rec IN trxv_rec_type,
791 x_trxv_rec OUT NOCOPY trxv_rec_type) IS
792 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRX_ASSET_HEADER';
793 BEGIN
794 x_return_status := OKL_API.G_RET_STS_SUCCESS;
795 -- Call start_activity to create savepoint, check compatibility
796 -- and initialize message list
797 x_return_status := OKL_API.START_ACTIVITY (
798 l_api_name
799 ,p_init_msg_list
800 ,'_PVT'
801 ,x_return_status);
802 -- Check if activity started successfully
803 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
804 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
805 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
806 RAISE OKL_API.G_EXCEPTION_ERROR;
807 END IF;
808 -- evaluate conditions, build outcomes for true conditions and
809 -- put them on outcome queue
810 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
811 p_api_version => p_api_version,
812 p_init_msg_list => p_init_msg_list,
813 x_return_status => x_return_status,
814 x_msg_count => x_msg_count,
815 x_msg_data => x_msg_data,
816 p_thpv_rec => p_trxv_rec,
817 x_thpv_rec => x_trxv_rec);
818 OKL_API.END_ACTIVITY (x_msg_count,
819 x_msg_data );
820 EXCEPTION
821 WHEN OKL_API.G_EXCEPTION_ERROR THEN
822 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
823 l_api_name,
824 G_PKG_NAME,
825 'OKL_API.G_RET_STS_ERROR',
826 x_msg_count,
827 x_msg_data,
828 '_PVT');
829 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
830 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
831 l_api_name,
832 G_PKG_NAME,
833 'OKL_API.G_RET_STS_UNEXP_ERROR',
834 x_msg_count,
835 x_msg_data,
836 '_PVT');
837 WHEN OTHERS THEN
838 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
839 l_api_name,
840 G_PKG_NAME,
841 'OTHERS',
842 x_msg_count,
843 x_msg_data,
844 '_PVT');
845 END Update_asset_header;
846
847 FUNCTION get_try_id(p_try_name IN OKL_TRX_TYPES_V.NAME%TYPE,x_try_id OUT NOCOPY OKC_LINE_STYLES_V.ID%TYPE)
848 RETURN VARCHAR2 IS
849 x_return_status VARCHAR2(3) := OKC_API.G_RET_STS_SUCCESS;
850 CURSOR c_get_try_id(p_try_name OKL_TRX_TYPES_V.NAME%TYPE) IS
851 SELECT id
852 FROM OKL_TRX_TYPES_TL
853 WHERE upper(name) = upper(p_try_name)
854 AND language = 'US';
855 BEGIN
856 IF (p_try_name = OKC_API.G_MISS_CHAR) OR
857 (p_try_name IS NULL) THEN
858 -- store SQL error message on message stack
859 OKC_API.set_message(p_app_name => G_APP_NAME,
860 p_msg_name => G_REQUIRED_VALUE,
861 p_token1 => G_COL_NAME_TOKEN,
862 p_token1_value => 'Try Name');
863 -- halt validation as it is a required field
864 RAISE G_EXCEPTION_STOP_VALIDATION;
865 END IF;
866 OPEN c_get_try_id(p_try_name);
867 FETCH c_get_try_id INTO x_try_id;
868 IF c_get_try_id%NOTFOUND THEN
869 OKC_API.set_message(p_app_name => G_APP_NAME,
870 p_msg_name => G_NO_PARENT_RECORD,
871 p_token1 => G_COL_NAME_TOKEN,
872 p_token1_value => 'Try Name');
873 RAISE G_EXCEPTION_HALT_VALIDATION;
874 END IF;
875 CLOSE c_get_try_id;
876 RETURN x_return_status;
877 EXCEPTION
878 WHEN G_EXCEPTION_STOP_VALIDATION then
879 -- We are here since the field is required
880 -- Notify Error
881 x_return_status := OKC_API.G_RET_STS_ERROR;
882 WHEN G_EXCEPTION_HALT_VALIDATION then
883 -- We are here b'cause we have no parent record
884 -- If the cursor is open then it has to be closed
885 IF c_get_try_id%ISOPEN THEN
886 CLOSE c_get_try_id;
887 END IF;
888 -- notify caller of an error
889 x_return_status := OKC_API.G_RET_STS_ERROR;
890 WHEN OTHERS THEN
891 -- store SQL error message on message stack for caller
892 OKC_API.set_message(
893 G_APP_NAME,
894 G_UNEXPECTED_ERROR,
895 G_SQLCODE_TOKEN,
896 SQLCODE,
897 G_SQLERRM_TOKEN,
898 SQLERRM);
899 -- notify caller of an UNEXPECTED error
900 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
901 -- if the cursor is open
902 IF c_get_try_id%ISOPEN THEN
903 CLOSE c_get_try_id;
904 END IF;
905 RETURN(x_return_status);
906 END get_try_id;
907
908 /*========================================================================
909 | PUBLIC PROCEDURE Create_Tax_Schedule
910 |
911 | DESCRIPTION
912 | This procedure will query all streams for a contract, pass the stream amounts to
913 | the Global Tax Engine for calculating tax for each of the amounts and create tax schedules in
914 | OKL_TAX_LINES. This procedure takes parameters in the table structure.
915 |
916 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
917 | Enter a list of all local procedures and functions which
918 | are call this package.
919 |
920 |
921 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
922 | Enter a list of all local procedures and cuntions which
923 | this package calls.
924 |
925 | PARAMETERS
926 | p_contract_id IN Contract Identifier
927 | p_trx_date IN Schedule Request Date
928 | p_date_from IN Date From
929 | p_date_to IN Date To
930 | x_return_status OUT Return Status
931 |
932 | KNOWN ISSUES
933 |
934 | NOTES
935 | Any interesting aspect of the code in the package body which needs
936 | to be stated.
937 |
938 | MODIFICATION HISTORY
939 | Date Author Description of Changes
940 | 24-MAY-2004 RKUTTIYA Created
941 |
942 *=======================================================================*/
943
944 PROCEDURE update_location(p_api_version IN NUMBER,
945 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
946 p_loc_tbl IN okl_loc_tbl_type,
947 x_return_status OUT NOCOPY VARCHAR2,
948 x_msg_count OUT NOCOPY NUMBER,
949 x_msg_data OUT NOCOPY VARCHAR2)
950 IS
951 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
952 l_api_name CONSTANT VARCHAR2(30) := 'Update_Location';
953 l_api_version CONSTANT NUMBER := 1;
954 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
955 i NUMBER;
956 BEGIN
957 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
958 G_PKG_NAME,
959 p_init_msg_list,
960 l_api_version,
961 p_api_version,
962 '_PVT',
963 x_return_status);
964 -- check if activity started successfully
965 If (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
966 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
967 Elsif (l_return_status = OKL_API.G_RET_STS_ERROR) then
968 raise OKL_API.G_EXCEPTION_ERROR;
969 End If;
970 -- Make sure PL/SQL table has records in it before passing
971 IF (p_loc_tbl.COUNT > 0) THEN
972 i := p_loc_tbl.FIRST;
973 --Print Input Variables
974 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
975 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_BLK_AST_UPD_PVT.Update_Location',
976 'parent_line_id :'||p_loc_tbl(i).parent_line_id);
977 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_BLK_AST_UPD_PVT.Update_Location',
978 'loc_id :'||p_loc_tbl(i).loc_id);
979 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_BLK_AST_UPD_PVT.Update_Location',
980 'party_site_id :'||p_loc_tbl(i).party_site_id);
981 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_BLK_AST_UPD_PVT.Update_Location',
982 'newsite_id1 :'||p_loc_tbl(i).newsite_id1);
983 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_BLK_AST_UPD_PVT.Update_Location',
984 'newsite_id2 :'||p_loc_tbl(i).newsite_id2);
985 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_BLK_AST_UPD_PVT.Update_Location',
986 'oldsite_id1 :'||p_loc_tbl(i).oldsite_id1);
987 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_BLK_AST_UPD_PVT.Update_Location',
988 'oldsite_id2 :'||p_loc_tbl(i).oldsite_id2);
989 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_BLK_AST_UPD_PVT.Update_Location',
990 'date_from :'||p_loc_tbl(i).date_from);
991 END IF;
992 LOOP
993 update_location (
994 p_api_version => l_api_version,
995 p_init_msg_list => OKL_API.G_FALSE,
996 x_return_status => x_return_status,
997 x_msg_count => x_msg_count,
998 x_msg_data => x_msg_data,
999 p_loc_rec => p_loc_tbl(i));
1000 -- store the highest degree of error
1001 If x_return_status <> OKL_API.G_RET_STS_SUCCESS Then
1002 If l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR Then
1003 l_overall_status := x_return_status;
1004 End If;
1005 End If;
1006 EXIT WHEN (i = p_loc_tbl.LAST);
1007 i := p_loc_tbl.NEXT(i);
1008 END LOOP;
1009 -- return overall status
1010 x_return_status := l_overall_status;
1011 END IF;
1012 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
1013 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1014 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
1015 raise OKL_API.G_EXCEPTION_ERROR;
1016 End If;
1017 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
1018 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1019 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_BLK_AST_UPD_PVT.Update_Location ','End(-)');
1020 END IF;
1021 EXCEPTION
1022 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1023 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1024 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_BLK_AST_UPD_PVT.Update_Location ',
1025 'EXCEPTION :'|| 'OKL_API.G_EXCEPTION_ERROR');
1026 END IF;
1027 x_return_status := OKL_API.G_RET_STS_ERROR;
1028 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1029 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1030 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_BLK_AST_UPD_PVT.Update_Location ',
1031 'EXCEPTION :'|| 'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
1032 END IF;
1033 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1034 WHEN OTHERS THEN
1035 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1036 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_BLK_AST_UPD_PVT.Update_Location ',
1037 'EXCEPTION :'||sqlerrm);
1038 END IF;
1039 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1040 -- unexpected error
1041 OKL_API.set_message(p_app_name => g_app_name,
1042 p_msg_name => g_unexpected_error,
1043 p_token1 => g_sqlcode_token,
1044 p_token1_value => sqlcode,
1045 p_token2 => g_sqlerrm_token,
1046 p_token2_value => sqlerrm);
1047 END Update_Location;
1048
1049 --Bug# 6619311 Start
1050 -- Start of comments
1051 --
1052 -- Procedure Name : populate_account_api_data
1053 -- Description : This is a private procedure used by create_upfront_tax_accounting
1054 -- to populate accounting data tables prior to calling central OKL a/c API
1055 -- Business Rules :
1056 -- Parameters :
1057 -- Version : 1.0
1058 -- End of comments
1059
1060 PROCEDURE populate_account_data(
1061 p_api_version IN NUMBER
1062 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1063 ,p_trxh_out_rec IN Okl_Trx_Contracts_Pvt.tcnv_rec_type
1064 ,p_tclv_tbl IN okl_trx_contracts_pvt.tclv_tbl_type
1065 ,p_acc_gen_tbl IN OUT NOCOPY okl_account_dist_pvt.ACC_GEN_TBL_TYPE
1066 ,p_tmpl_identify_tbl IN OUT NOCOPY okl_account_dist_pvt.tmpl_identify_tbl_type
1067 ,p_dist_info_tbl IN OUT NOCOPY okl_account_dist_pvt.dist_info_tbl_type
1068 ,x_return_status OUT NOCOPY VARCHAR2
1069 ,x_msg_count OUT NOCOPY NUMBER
1070 ,x_msg_data OUT NOCOPY VARCHAR2)
1071 IS
1072
1073 CURSOR fnd_pro_csr
1074 IS
1075 SELECT mo_global.get_current_org_id() l_fnd_profile
1076 FROM dual;
1077
1078 fnd_pro_rec fnd_pro_csr%ROWTYPE;
1079
1080 CURSOR ra_cust_csr
1081 IS
1082 SELECT cust_trx_type_id l_cust_trx_type_id
1083 FROM ra_cust_trx_types
1084 WHERE name = 'Invoice-OKL';
1085
1086 ra_cust_rec ra_cust_csr%ROWTYPE;
1087
1088 CURSOR salesP_csr
1089 IS
1090 SELECT ct.object1_id1 id
1091 ,chr.scs_code scs_code
1092 FROM okc_contacts ct,
1093 okc_contact_sources csrc,
1094 okc_k_party_roles_b pty,
1095 okc_k_headers_b chr
1096 WHERE ct.cpl_id = pty.id
1097 AND ct.cro_code = csrc.cro_code
1098 AND ct.jtot_object1_code = csrc.jtot_object_code
1099 AND ct.dnz_chr_id = chr.id
1100 AND pty.rle_code = csrc.rle_code
1101 AND csrc.cro_code = 'SALESPERSON'
1102 AND csrc.rle_code = 'LESSOR'
1103 AND csrc.buy_or_sell = chr.buy_or_sell
1104 AND pty.dnz_chr_id = chr.id
1105 AND pty.chr_id = chr.id
1106 AND chr.id = p_trxh_out_rec.khr_id;
1107
1108 l_salesP_rec salesP_csr%ROWTYPE;
1109
1110 CURSOR custBillTo_csr
1111 IS
1112 SELECT bill_to_site_use_id cust_acct_site_id
1113 FROM okc_k_headers_b
1114 WHERE id = p_trxh_out_rec.khr_id;
1115
1116 l_custBillTo_rec custBillTo_csr%ROWTYPE;
1117
1118 l_acc_gen_primary_key_tbl okl_account_dist_pvt.acc_gen_primary_key;
1119 l_fact_synd_code FND_LOOKUPS.Lookup_code%TYPE;
1120 l_inv_acct_code OKC_RULES_B.Rule_Information1%TYPE;
1121
1122 account_data_exception EXCEPTION;
1123
1124 --Bug# 6619311
1125 CURSOR assetBillTo_csr(p_cle_id IN NUMBER)
1126 IS
1127 SELECT bill_to_site_use_id cust_acct_site_id
1128 FROM okc_k_lines_b
1129 WHERE id = p_cle_id;
1130
1131 l_assetBillTo_rec assetBillTo_csr%ROWTYPE;
1132 l_acc_gen_primary_key_tbl1 okl_account_dist_pvt.acc_gen_primary_key;
1133
1134 BEGIN
1135
1136 okl_debug_pub.logmessage('OKL: populate_account_data : START');
1137
1138 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1139
1140 l_acc_gen_primary_key_tbl(1).source_table := 'FINANCIALS_SYSTEM_PARAMETERS';
1141 OPEN fnd_pro_csr;
1142 FETCH fnd_pro_csr INTO fnd_pro_rec;
1143 IF ( fnd_pro_csr%NOTFOUND )
1144 THEN
1145 l_acc_gen_primary_key_tbl(1).primary_key_column := '';
1146 ELSE
1147 l_acc_gen_primary_key_tbl(1).primary_key_column := fnd_pro_rec.l_fnd_profile;
1148 End IF;
1149 CLOSE fnd_pro_csr;
1150
1151 l_acc_gen_primary_key_tbl(2).source_table := 'AR_SITE_USES_V';
1152 OPEN custBillTo_csr;
1153 FETCH custBillTo_csr INTO l_custBillTo_rec;
1154 CLOSE custBillTo_csr;
1155 l_acc_gen_primary_key_tbl(2).primary_key_column := l_custBillTo_rec.cust_acct_site_id;
1156
1157 l_acc_gen_primary_key_tbl(3).source_table := 'RA_CUST_TRX_TYPES';
1158 OPEN ra_cust_csr;
1159 FETCH ra_cust_csr INTO ra_cust_rec;
1160 IF ( ra_cust_csr%NOTFOUND ) THEN
1161 l_acc_gen_primary_key_tbl(3).primary_key_column := '';
1162 ELSE
1163 l_acc_gen_primary_key_tbl(3).primary_key_column := TO_CHAR(ra_cust_rec.l_cust_trx_type_id);
1164 END IF;
1165 CLOSE ra_cust_csr;
1166
1167 l_acc_gen_primary_key_tbl(4).source_table := 'JTF_RS_SALESREPS_MO_V';
1168 OPEN salesP_csr;
1169 FETCH salesP_csr INTO l_salesP_rec;
1170 CLOSE salesP_csr;
1171 l_acc_gen_primary_key_tbl(4).primary_key_column := l_salesP_rec.id;
1172
1173 IF (G_IS_DEBUG_STATEMENT_ON = TRUE)
1174 THEN
1175 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
1176 ,G_MODULE
1177 , 'OKL: populate_account_data Procedure: Calling OKL_SECURITIZATION_PVT ');
1178 END IF;
1179
1180 OKL_SECURITIZATION_PVT.Check_Khr_ia_associated(
1181 p_api_version => p_api_version,
1182 p_init_msg_list => p_init_msg_list,
1183 x_return_status => x_return_status,
1184 x_msg_count => x_msg_count,
1185 x_msg_data => x_msg_data,
1186 p_khr_id => p_trxh_out_rec.khr_id,
1187 p_scs_code => l_salesP_rec.scs_code,
1188 p_trx_date => p_trxh_out_rec.date_transaction_occurred,
1189 x_fact_synd_code => l_fact_synd_code,
1190 x_inv_acct_code => l_inv_acct_code
1191 );
1192
1193
1194 okl_debug_pub.logmessage('OKL: populate_account_data : OKL_SECURITIZATION_PVT : '||x_return_status);
1195
1196 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
1197 THEN
1198 RAISE account_data_exception;
1199 END IF;
1200
1201 FOR i in p_tclv_tbl.FIRST..p_tclv_tbl.LAST
1202 LOOP
1203
1204 --Bug# 6619311: Populate asset level bill-to site if defined
1205 l_acc_gen_primary_key_tbl1 := l_acc_gen_primary_key_tbl;
1206 IF p_tclv_tbl(i).kle_id IS NOT NULL THEN
1207 l_assetBillTo_rec := NULL;
1208 OPEN assetBillTo_csr(p_cle_id => p_tclv_tbl(i).kle_id);
1209 FETCH assetBillTo_csr INTO l_assetBillTo_rec;
1210 CLOSE assetBillTo_csr;
1211
1212 IF l_assetBillTo_rec.cust_acct_site_id IS NOT NULL THEN
1213 l_acc_gen_primary_key_tbl1(2).primary_key_column := l_assetBillTo_rec.cust_acct_site_id;
1214 END IF;
1215 END IF;
1216
1217 -- Populate account source
1218 p_acc_gen_tbl(i).acc_gen_key_tbl := l_acc_gen_primary_key_tbl1;
1219 p_acc_gen_tbl(i).source_id := p_tclv_tbl(i).id;
1220
1221 -- Populate template info
1222 p_tmpl_identify_tbl(i).product_id := p_trxh_out_rec.pdt_id;
1223 p_tmpl_identify_tbl(i).transaction_type_id := p_trxh_out_rec.try_id;
1224 p_tmpl_identify_tbl(i).stream_type_id := p_tclv_tbl(i).sty_id;
1225 p_tmpl_identify_tbl(i).advance_arrears := NULL;
1226 p_tmpl_identify_tbl(i).prior_year_yn := 'N';
1227 p_tmpl_identify_tbl(i).memo_yn := 'N';
1228 p_tmpl_identify_tbl(i).factoring_synd_flag := l_fact_synd_code;
1229 p_tmpl_identify_tbl(i).investor_code := l_inv_acct_code;
1230
1231 -- Populate distribution info
1232 p_dist_info_tbl(i).SOURCE_ID := p_tclv_tbl(i).id;
1233 p_dist_info_tbl(i).amount := p_tclv_tbl(i).amount;
1234 p_dist_info_tbl(i).ACCOUNTING_DATE := p_trxh_out_rec.date_transaction_occurred;
1235 p_dist_info_tbl(i).SOURCE_TABLE := 'OKL_TXL_CNTRCT_LNS';
1236 p_dist_info_tbl(i).GL_REVERSAL_FLAG := 'N';
1237 p_dist_info_tbl(i).POST_TO_GL := 'Y';
1238 p_dist_info_tbl(i).CONTRACT_ID := p_trxh_out_rec.khr_id;
1239 p_dist_info_tbl(i).currency_conversion_rate := p_trxh_out_rec.currency_conversion_rate;
1240 p_dist_info_tbl(i).currency_conversion_type := p_trxh_out_rec.currency_conversion_type;
1241 p_dist_info_tbl(i).currency_conversion_date := p_trxh_out_rec.currency_conversion_date;
1242 p_dist_info_tbl(i).currency_code := p_trxh_out_rec.currency_code;
1243 okl_debug_pub.logmessage('OKL: populate_account_data : p_tclv_tbl loop : l_dist_info_tbl(i).amount : '||p_dist_info_tbl(i).amount);
1244
1245 END LOOP;
1246
1247 okl_debug_pub.logmessage('OKL: populate_account_data : END');
1248
1249 EXCEPTION
1250 WHEN account_data_exception
1251 THEN
1252 x_return_status := OKL_API.G_RET_STS_ERROR;
1253
1254 END populate_account_data;
1255
1256 -- Start of comments
1257 --
1258 -- Procedure Name : create_upfront_tax_accounting
1259 -- Description : This procedure creates a/c journal entries for upfront tax lines.
1260 -- This procedure logic will be executed in its entirety, only if SLA accounting
1261 -- option AMB is enabled.
1262 -- When enabled, it creates:
1263 -- 1. TRX header in OKL_TRX_CONTRACTS for type 'Upfront Tax'
1264 -- 2. TRX lines in OKL_TXL_CNTRCT_LNS for each line in ZX_LINES,
1265 -- store values for cle-id, tax_line_id, tax_amount, etc.
1266 -- 3. Identify tax treatment for each asset line, to derive stream type
1267 -- 4. Call a/c API for upfront tax records in OKL_TXL_CNTRCT_LNS
1268 -- Business Rules :
1269 -- Parameters :
1270 -- Version : 1.0
1271 -- End of comments
1272
1273 PROCEDURE create_upfront_tax_accounting(
1274 p_api_version IN NUMBER
1275 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1276 ,p_contract_id IN okc_k_headers_all_b.id%TYPE
1277 ,p_line_id IN okc_k_lines_b.id%TYPE
1278 ,p_transaction_id IN okl_trx_contracts_all.khr_id%TYPE
1279 ,p_transaction_type IN VARCHAR2
1280 ,p_transaction_date IN DATE
1281 ,x_return_status OUT NOCOPY VARCHAR2
1282 ,x_msg_count OUT NOCOPY NUMBER
1283 ,x_msg_data OUT NOCOPY VARCHAR2)
1284 IS
1285
1286 CURSOR contract_csr (p_contract_id OKC_K_HEADERS_B.ID%TYPE)
1287 IS
1288 SELECT khr.pdt_id product_id
1289 ,khr.start_date start_date
1290 ,khr.currency_code currency_code
1291 ,khr.authoring_org_id authoring_org_id
1292 ,khr.currency_conversion_rate currency_conversion_rate
1293 ,khr.currency_conversion_type currency_conversion_type
1294 ,khr.currency_conversion_date currency_conversion_date
1295 ,khr.contract_number contract_number
1296 FROM okl_k_headers_full_v khr
1297 WHERE khr.id = p_contract_id;
1298
1299 l_contract_rec contract_csr%ROWTYPE;
1300
1301 CURSOR fnd_lookups_csr( lkp_type VARCHAR2, mng VARCHAR2 ) IS
1302 select description, lookup_code
1303 from fnd_lookup_values
1304 where language = 'US'
1305 AND lookup_type = lkp_type
1306 AND meaning = mng;
1307
1308 CURSOR Transaction_Type_csr (p_transaction_type IN okl_trx_types_v.name%TYPE ) IS
1309 SELECT id
1310 FROM okl_trx_types_tl
1311 WHERE name = p_transaction_type
1312 AND language = 'US';
1313
1314 l_Trx_Type_rec Transaction_Type_csr%ROWTYPE;
1315
1316 -- Cursor to check system level accounting option
1317 -- Upfront tax a/c is done if AMB is enabled
1318 CURSOR acct_opt_csr
1319 IS
1320 SELECT account_derivation
1321 FROM okl_sys_acct_opts;
1322
1323 l_acct_opt okl_sys_acct_opts.account_derivation%TYPE;
1324
1325 CURSOR tax_line_csr1
1326 IS
1327 SELECT 'BILLED' tax_treatment
1328 , txs.kle_id asset_id
1329 , txs.id tax_header_id
1330 , txl.tax_line_id tax_line_id
1331 , txl.tax_amt tax_amount
1332 FROM okl_tax_sources txs
1333 , zx_lines txl
1334 WHERE txs.khr_id = p_contract_id
1335 AND txs.kle_id = p_line_id
1336 AND txs.trx_id = p_transaction_id
1337 AND txs.tax_line_status_code = 'ACTIVE'
1338 AND txs.tax_call_type_code = 'UPFRONT_TAX'
1339 AND txs.trx_id = txl.trx_id
1340 AND txs.trx_line_id = txl.trx_line_id
1341 AND txl.entity_code = 'ASSETS'
1342 AND txl.event_class_code = 'ASSET_RELOCATION'
1343 AND txs.entity_code = txl.entity_code
1344 AND txs.event_class_code = txl.event_class_code
1345 AND txl.application_id = 540
1346 AND txl.trx_level_type = 'LINE'
1347 AND txs.application_id = txl.application_id
1348 AND txs.trx_level_type = txl.trx_level_type;
1349
1350 l_tclv_tbl okl_trx_contracts_pvt.tclv_tbl_type;
1351 x_tclv_tbl okl_trx_contracts_pvt.tclv_tbl_type;
1352
1353 l_tmpl_identify_rec okl_account_dist_pvt.tmpl_identify_rec_type;
1354 l_tmpl_identify_tbl okl_account_dist_pvt.tmpl_identify_tbl_type;
1355 l_template_tbl okl_account_dist_pvt.avlv_tbl_type;
1356 l_dist_info_tbl okl_account_dist_pvt.dist_info_tbl_type;
1357 l_template_out_tbl okl_account_dist_pvt.avlv_out_tbl_type;
1358 l_amount_tbl okl_account_dist_pvt.amount_out_tbl_type;
1359 l_ctxt_val_tbl okl_account_dist_pvt.CTXT_VAL_TBL_TYPE;
1360 l_acc_gen_tbl okl_account_dist_pvt.ACC_GEN_TBL_TYPE;
1361 l_ctxt_tbl okl_account_dist_pvt.CTXT_TBL_TYPE;
1362
1363 j NUMBER := 0;
1364 l_trx_id NUMBER;
1365 l_lkp_tcn_type_rec fnd_lookups_csr%ROWTYPE;
1366 l_lkp_trx_status_rec fnd_lookups_csr%ROWTYPE;
1367 SUBTYPE ac_tax_line_rec IS tax_line_csr1%ROWTYPE;
1368 TYPE ac_tax_line_tbl IS TABLE OF ac_tax_line_rec INDEX BY BINARY_INTEGER;
1369 l_accoutable_tax_lines ac_tax_line_tbl;
1370 l_fact_synd_code FND_LOOKUPS.Lookup_code%TYPE;
1371 l_inv_acct_code OKC_RULES_B.Rule_Information1%TYPE;
1372 upfront_tax_acct_exception EXCEPTION;
1373
1374 l_billed_sty_id NUMBER;
1375 l_transaction_amount NUMBER;
1376
1377 l_trxH_in_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
1378 l_trxH_out_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
1379
1380 l_legal_entity_id NUMBER;
1381 l_func_curr_code okl_k_headers_full_v.CURRENCY_CODE%TYPE;
1382 l_chr_curr_code okl_k_headers_full_v.CURRENCY_CODE%TYPE;
1383 l_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%TYPE;
1384 l_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%TYPE;
1385 l_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%TYPE;
1386
1387 BEGIN
1388
1389 IF (G_DEBUG_ENABLED = 'Y') THEN
1390 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1391 END IF;
1392
1393 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1394 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_BLK_AST_UPD_PVT.Create_Upfront_Tax_Accounting','Begin(+)');
1395 END IF;
1396
1397 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1398
1399 IF (G_IS_DEBUG_STATEMENT_ON = TRUE)
1400 THEN
1401 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT
1402 , G_MODULE
1403 , 'OKL: create_upfront_tax_accounting Procedure: deriving Accounting option ');
1404 END IF;
1405
1406 OPEN acct_opt_csr;
1407 FETCH acct_opt_csr INTO l_acct_opt;
1408
1409 IF acct_opt_csr%NOTFOUND
1410 THEN
1411 OKL_API.set_message( p_app_name => G_APP_NAME,
1412 p_msg_name => 'OKL_LA_ST_ACCT_ERROR');
1413 CLOSE acct_opt_csr;
1414 RAISE upfront_tax_acct_exception;
1415 END IF;
1416
1417 CLOSE acct_opt_csr;
1418
1419 IF (G_IS_DEBUG_STATEMENT_ON = TRUE)
1420 THEN
1421 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT
1422 , G_MODULE
1423 , 'OKL: create_upfront_tax_accounting Procedure: Validating Accounting option ');
1424 END IF;
1425
1426 IF (l_acct_opt IS NULL)
1427 THEN
1428 OKL_API.set_message( p_app_name => G_APP_NAME,
1429 p_msg_name => 'OKL_LA_ST_ACCT_ERROR');
1430 RAISE upfront_tax_acct_exception;
1431 END IF;
1432
1433 -- execute the whole logic only if AMB is enabled, otherwise get out
1434 IF (l_acct_opt <> 'AMB' )
1435 THEN
1436 NULL;
1437 ELSE
1438
1439 IF (G_IS_DEBUG_STATEMENT_ON = TRUE)
1440 THEN
1441 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT
1442 , G_MODULE
1443 ,'OKL: create_upfront_tax_accounting Procedure: before fnd_lookups_csr ');
1444 END IF;
1445
1446 l_lkp_tcn_type_rec := NULL;
1447 OPEN fnd_lookups_csr('OKL_TCN_TYPE', 'Upfront Tax');
1448 FETCH fnd_lookups_csr INTO l_lkp_tcn_type_rec;
1449 IF fnd_lookups_csr%NOTFOUND
1450 THEN
1451 Okl_Api.SET_MESSAGE( G_APP_NAME
1452 ,OKL_API.G_INVALID_VALUE
1453 ,'TRANSACTION_TYPE'
1454 ,'Upfront Tax');
1455 CLOSE fnd_lookups_csr;
1456 RAISE upfront_tax_acct_exception;
1457 END IF;
1458 CLOSE fnd_lookups_csr;
1459
1460 IF (G_IS_DEBUG_STATEMENT_ON = TRUE)
1461 THEN
1462 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT
1463 , G_MODULE
1464 ,'OKL: create_upfront_tax_accounting Procedure: before Transaction_Type_csr ');
1465 END IF;
1466
1467 l_Trx_Type_rec := NULL;
1468 OPEN Transaction_Type_csr('Upfront Tax');
1469 FETCH Transaction_Type_csr INTO l_Trx_Type_rec;
1470 IF Transaction_Type_csr%NOTFOUND THEN
1471 Okl_Api.SET_MESSAGE(G_APP_NAME,
1472 OKL_API.G_INVALID_VALUE,
1473 'TRANSACTION_TYPE',
1474 'Upfront Tax');
1475 CLOSE Transaction_Type_csr;
1476 RAISE upfront_tax_acct_exception;
1477 END IF;
1478 CLOSE Transaction_Type_csr;
1479
1480 IF (G_IS_DEBUG_STATEMENT_ON = TRUE)
1481 THEN
1482 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
1483 ,G_MODULE
1484 ,'OKL: create_upfront_tax_accounting Procedure: deriving billed stream ID ');
1485 END IF;
1486
1487 OKL_STREAMS_UTIL.get_primary_stream_type(
1488 p_khr_id => p_contract_id,
1489 p_primary_sty_purpose => 'UPFRONT_TAX_BILLED',
1490 x_return_status => x_return_status,
1491 x_primary_sty_id => l_billed_sty_id);
1492
1493 okl_debug_pub.logmessage('OKL: create_upfront_tax_accounting Procedure: UPFRONT_TAX_BILLED : '||l_billed_sty_id);
1494
1495 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1496 RAISE upfront_tax_acct_exception;
1497 End If;
1498
1499 IF (G_IS_DEBUG_STATEMENT_ON = TRUE)
1500 THEN
1501 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
1502 ,G_MODULE
1503 ,'OKL: create_upfront_tax_accounting Procedure: deriving asset tax information ');
1504 END IF;
1505
1506 l_accoutable_tax_lines.DELETE;
1507 j := 0;
1508 l_transaction_amount := 0;
1509 FOR i IN tax_line_csr1
1510 LOOP
1511 j := j+1;
1512 l_accoutable_tax_lines(j) := i;
1513 l_transaction_amount := l_transaction_amount + l_accoutable_tax_lines(j).tax_amount;
1514 END LOOP;
1515
1516 IF (G_IS_DEBUG_STATEMENT_ON = TRUE)
1517 THEN
1518 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
1519 ,G_MODULE
1520 ,'OKL: create_upfront_tax_accounting Procedure: l_transaction_amount : '||l_transaction_amount);
1521 END IF;
1522
1523 OPEN contract_csr (p_contract_id);
1524 FETCH contract_csr INTO l_contract_rec;
1525 CLOSE contract_csr;
1526
1527 l_chr_curr_code := l_contract_rec.currency_code;
1528 l_func_curr_code := OKC_CURRENCY_API.GET_OU_CURRENCY(l_contract_rec.authoring_org_id);
1529
1530 l_currency_conversion_rate := NULL;
1531 l_currency_conversion_type := NULL;
1532 l_currency_conversion_date := NULL;
1533
1534 If ( ( l_func_curr_code IS NOT NULL) AND
1535 ( l_chr_curr_code <> l_func_curr_code ) ) Then
1536
1537 l_currency_conversion_type := l_contract_rec.currency_conversion_type;
1538 l_currency_conversion_date := l_contract_rec.start_date;
1539
1540 If ( l_contract_rec.currency_conversion_type = 'User') Then
1541 l_currency_conversion_rate := l_contract_rec.currency_conversion_rate;
1542 l_currency_conversion_date := l_contract_rec.currency_conversion_date;
1543 Else
1544 l_currency_conversion_rate := okl_accounting_util.get_curr_con_rate(
1545 p_from_curr_code => l_chr_curr_code,
1546 p_to_curr_code => l_func_curr_code,
1547 p_con_date => l_contract_rec.start_date,
1548 p_con_type => l_contract_rec.currency_conversion_type);
1549 End If;
1550 End If;
1551
1552 l_trxH_in_rec.pdt_id := l_contract_rec.product_id;
1553 l_trxH_in_rec.currency_code := l_contract_rec.currency_code;
1554 l_trxH_in_rec.currency_conversion_rate := l_currency_conversion_rate;
1555 l_trxH_in_rec.currency_conversion_type := l_currency_conversion_type;
1556 l_trxH_in_rec.currency_conversion_date := l_currency_conversion_date;
1557
1558 l_trxH_in_rec.khr_id := p_contract_id;
1559 l_trxH_in_rec.source_trx_id := p_transaction_id;
1560 l_trxH_in_rec.source_trx_type := p_transaction_type;
1561 l_trxH_in_rec.date_transaction_occurred := p_transaction_date;
1562 l_trxH_in_rec.try_id := l_Trx_Type_rec.id;
1563 l_trxH_in_rec.tcn_type := l_lkp_tcn_type_rec.lookup_code;
1564 l_trxH_in_rec.amount := l_transaction_amount;
1565
1566 l_lkp_trx_status_rec := NULL;
1567 OPEN fnd_lookups_csr('OKL_TRANSACTION_STATUS', 'Processed');
1568 FETCH fnd_lookups_csr INTO l_lkp_trx_status_rec;
1569 CLOSE fnd_lookups_csr;
1570
1571 l_trxH_in_rec.tsu_code := l_lkp_trx_status_rec.lookup_code;
1572 l_trxH_in_rec.description := l_lkp_trx_status_rec.description;
1573
1574 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_contract_id) ;
1575 IF l_legal_entity_id IS NOT NULL THEN
1576 l_trxH_in_rec.legal_entity_id := l_legal_entity_id;
1577 ELSE
1578
1579 Okl_Api.set_message(p_app_name => G_APP_NAME,
1580 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
1581 p_token1 => 'CONTRACT_NUMBER',
1582 p_token1_value => l_contract_rec.contract_number);
1583 RAISE upfront_tax_acct_exception;
1584 END IF;
1585
1586 IF (G_IS_DEBUG_STATEMENT_ON = TRUE) THEN
1587 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT
1588 , G_MODULE
1589 ,'OKL: create_upfront_tax_accounting Procedure: before Okl_Trx_Contracts_Pub.create_trx_contracts ');
1590 END IF;
1591
1592 -- Create Transaction Header, Lines
1593 Okl_Trx_Contracts_Pub.create_trx_contracts(
1594 p_api_version => p_api_version
1595 ,p_init_msg_list => p_init_msg_list
1596 ,x_return_status => x_return_status
1597 ,x_msg_count => x_msg_count
1598 ,x_msg_data => x_msg_data
1599 ,p_tcnv_rec => l_trxH_in_rec
1600 ,x_tcnv_rec => l_trxH_out_rec);
1601
1602 okl_debug_pub.logmessage('OKL: create_upfront_tax_accounting Procedure: create_trx_contracts : '||x_return_status);
1603
1604 -- check transaction creation was successful
1605 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
1606 THEN
1607 RAISE upfront_tax_acct_exception;
1608 END IF;
1609
1610 FOR i IN l_accoutable_tax_lines.FIRST..l_accoutable_tax_lines.LAST
1611 LOOP
1612
1613 -- Populate TRX line array
1614 l_tclv_tbl(i).line_number := i;
1615 l_tclv_tbl(i).tcn_id := l_trxH_out_rec.id;
1616 l_tclv_tbl(i).khr_id := p_contract_id;
1617 l_tclv_tbl(i).kle_id := l_accoutable_tax_lines(i).asset_id;
1618 l_tclv_tbl(i).tcl_type := l_lkp_tcn_type_rec.lookup_code;
1619 l_tclv_tbl(i).tax_line_id := l_accoutable_tax_lines(i).tax_line_id;
1620 l_tclv_tbl(i).amount := l_accoutable_tax_lines(i).tax_amount;
1621 l_tclv_tbl(i).currency_code := l_trxh_out_rec.currency_code;
1622 l_tclv_tbl(i).sty_id := l_billed_sty_id;
1623
1624 END LOOP;
1625
1626 -- Create TRX lines with the data gathered
1627
1628 IF (G_IS_DEBUG_STATEMENT_ON = TRUE)
1629 THEN
1630 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
1631 ,G_MODULE
1632 , 'OKL: create_upfront_tax_accounting Procedure: Calling Okl_Trx_Contracts_Pub.create_trx_cntrct_lines ');
1633 END IF;
1634
1635 Okl_Trx_Contracts_Pub.create_trx_cntrct_lines(
1636 p_api_version => p_api_version,
1637 p_init_msg_list => p_init_msg_list,
1638 x_return_status => x_return_status,
1639 x_msg_count => x_msg_count,
1640 x_msg_data => x_msg_data,
1641 p_tclv_tbl => l_tclv_tbl,
1642 x_tclv_tbl => x_tclv_tbl);
1643
1644 okl_debug_pub.logmessage('OKL: create_upfront_tax_accounting Procedure: create_trx_cntrct_lines : '||x_return_status);
1645
1646 -- check transaction line creation was successful
1647 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
1648 THEN
1649 RAISE upfront_tax_acct_exception;
1650 END IF;
1651
1652 -- Populate accounting API data structures
1653 populate_account_data(
1654 p_api_version
1655 ,p_init_msg_list
1656 ,l_trxh_out_rec
1657 ,x_tclv_tbl
1658 ,l_acc_gen_tbl
1659 ,l_tmpl_identify_tbl
1660 ,l_dist_info_tbl
1661 ,x_return_status
1662 ,x_msg_count
1663 ,x_msg_data);
1664
1665 okl_debug_pub.logmessage('OKL: create_upfront_tax_accounting Procedure: populate_account_data : '||x_return_status);
1666
1667 -- check transaction line creation was successful
1668 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
1669 THEN
1670 RAISE upfront_tax_acct_exception;
1671 END IF;
1672
1673 IF (G_IS_DEBUG_STATEMENT_ON = TRUE)
1674 THEN
1675 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
1676 ,G_MODULE
1677 ,'OKL: create_upfront_tax_accounting Procedure: Calling Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST');
1678 END IF;
1679
1680 -- Call Accounting API to create distributions
1681 okl_account_dist_pvt.create_accounting_dist(
1682 p_api_version => p_api_version,
1683 p_init_msg_list => p_init_msg_list,
1684 x_return_status => x_return_status,
1685 x_msg_count => x_msg_count,
1686 x_msg_data => x_msg_data,
1687 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
1688 p_dist_info_tbl => l_dist_info_tbl,
1689 p_ctxt_val_tbl => l_ctxt_tbl,
1690 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
1691 x_template_tbl => l_template_out_tbl,
1692 x_amount_tbl => l_amount_tbl,
1693 p_trx_header_id => l_trxh_out_rec.id);
1694
1695 okl_debug_pub.logmessage('OKL: create_upfront_tax_accounting Procedure: create_accounting_dist : '|| x_return_status);
1696
1697 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS)
1698 THEN
1699 RAISE upfront_tax_acct_exception;
1700 END IF;
1701
1702 END IF; -- AMB Check
1703
1704 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1705 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_BLK_AST_UPD_PVT.Create_Upfront_Tax_Accounting','End(+)');
1706 END IF;
1707
1708 EXCEPTION
1709 WHEN upfront_tax_acct_exception
1710 THEN
1711 x_return_status := OKL_API.G_RET_STS_ERROR;
1712
1713 END create_upfront_tax_accounting;
1714 --Bug# 6619311 End
1715
1716
1717 PROCEDURE process_update_location(
1718 p_api_version IN NUMBER,
1719 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1720 p_kle_id IN NUMBER,
1721 x_return_status OUT NOCOPY VARCHAR2,
1722 x_msg_count OUT NOCOPY NUMBER,
1723 x_msg_data OUT NOCOPY VARCHAR2) IS
1724
1725 CURSOR c_free_form2(p_parent_line_id IN NUMBER) IS
1726 SELECT A.ID, K.org_id
1727 FROM OKC_K_LINES_V A,
1728 OKC_LINE_STYLES_B B,
1729 okc_k_headers_all_b K
1730 WHERE A.CLE_ID = p_parent_line_id
1731 AND A.LSE_ID = B.ID
1732 AND B.LTY_CODE = 'FREE_FORM2'
1733 AND k.id = a.dnz_chr_id;
1734
1735 CURSOR c_inst_item(p_line_id IN NUMBER) IS
1736 SELECT A.ID,
1737 A.DNZ_CHR_ID
1738 FROM OKC_K_LINES_V A,
1739 OKC_LINE_STYLES_B B
1740 WHERE A.CLE_ID = p_line_id
1741 AND A.LSE_ID = B.ID
1742 AND B.LTY_CODE = 'INST_ITEM';
1743
1744 CURSOR c_items(p_inst_itm_id IN NUMBER) is
1745 SELECT *
1746 FROM OKC_K_ITEMS_V
1747 WHERE CLE_ID = p_inst_itm_id
1748 AND JTOT_OBJECT1_CODE = 'OKX_IB_ITEM';
1749
1750
1751 CURSOR c_ib_inst(p_object1_id1 IN VARCHAR2,p_object1_id2 IN VARCHAR2) IS
1752 SELECT *
1753 FROM OKX_INSTALL_ITEMS_V A
1754 WHERE ID1 = p_object1_id1
1755 AND ID2 = p_object1_id2;
1756
1757
1758 CURSOR c_loc_typecode(p_instance_id IN NUMBER) IS
1759 SELECT LOCATION_TYPE_CODE,
1760 INSTALL_LOCATION_TYPE_CODE
1761 FROM csi_item_instances
1762 WHERE INSTANCE_ID = p_instance_id;
1763
1764 CURSOR c_get_entered_alc_trx(cp_kle_id IN NUMBER) IS
1765 SELECT TRX.ID,TRX.TSU_CODE, TXL.object_id1_new, TXL.object_id2_new,
1766 TXL.object_id1_old,object_id2_old,psu.location_id, psu.party_site_id,
1767 TRX.DATE_TRANS_OCCURRED,TRX.req_asset_id
1768 FROM OKL_TRX_ASSETS TRX,
1769 OKL_TXL_ITM_INSTS TXL,
1770 OKL_TRX_TYPES_B TRY,
1771 OKX_PARTY_SITE_USES_V psu
1772 WHERE TRX.ID = TXL.TAS_ID
1773 AND TRX.TRY_ID = TRY.ID
1774 AND TRY.TRX_TYPE_CLASS = 'ASSET_RELOCATION'
1775 AND TRX.TSU_CODE = 'ENTERED'
1776 AND TRX.TAS_TYPE = 'ALG'
1777 AND TXL.KLE_ID = cp_kle_id
1778 AND psu.ID1 = TXL.object_id1_new
1779 AND PSU.ID2 = TXL.object_id2_new;
1780
1781 CURSOR c_systemparams_csr(cp_org_id IN NUMBER) IS
1782 SELECT tax_upfront_yn
1783 FROM OKL_SYSTEM_PARAMS_ALL
1784 WHERE org_id = cp_org_id;
1785
1786 CURSOR c_get_tax_amt_csr(cp_trx_id IN NUMBER,cp_khr_id IN NUMBER,cp_kle_id IN NUMBER) IS
1787 SELECT nvl(sum(total_tax) ,0)
1788 FROM okl_tax_sources TAXS
1789 WHERE TAXS.TAX_LINE_STATUS_CODE = 'ACTIVE'
1790 AND TAXS.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
1791 AND TAXS.ENTITY_CODE = 'ASSETS'
1792 AND TAXS.APPLICATION_ID = 540
1793 AND EVENT_CLASS_CODE = 'ASSET_RELOCATION'
1794 AND TRX_ID = cp_trx_id
1795 And khr_id = cp_khr_id
1796 And kle_id = cp_kle_id
1797 And trx_level_type = 'LINE';
1798
1799 l_inst_loc_type_code VARCHAR2(30);
1800 l_loc_type_code VARCHAR2(30);
1801
1802 l_c_ib_inst c_ib_inst%ROWTYPE;
1803 l_ctr NUMBER;
1804 l_obj_no NUMBER;
1805 l_trqv_rec okl_trx_requests_pub.trqv_rec_type;
1806 x_trqv_rec okl_trx_requests_pub.trqv_rec_type;
1807
1808
1809
1810 l_trx_rec c_get_entered_alc_trx%ROWTYPE;
1811 l_trxv_rec trxv_rec_type;
1812 x_trxv_rec trxv_rec_type;
1813
1814 l_api_name CONSTANT VARCHAR2(30) := 'PROC_UPD_LOC';
1815 l_chr_id NUMBER;
1816 l_parent_line_id NUMBER;
1817 l_ou_flag VARCHAR2(2);
1818 l_tax_amt NUMBER;
1819 BEGIN
1820 IF (G_DEBUG_ENABLED = 'Y') THEN
1821 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1822 END IF;
1823 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1824 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_BLK_AST_UPD_PVT.Update_Location','Begin(+)');
1825 END IF;
1826
1827 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1828 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Input variables in Update Location');
1829 END IF;
1830
1831 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1832 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_kle_id :'|| p_kle_id);
1833 END IF;
1834
1835 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1836
1837 --Call start_activity to create savepoint, check compatibility and initialize message list
1838
1839 x_return_status := OKL_API.START_ACTIVITY(
1840 l_api_name
1841 ,p_init_msg_list
1842 ,'_PVT'
1843 ,x_return_status);
1844
1845 --Check if activity started successfully
1846
1847 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1848 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1849 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1850 RAISE OKL_API.G_EXCEPTION_ERROR;
1851 END IF;
1852
1853 FOR l_c_free_form2 in c_free_form2(p_kle_id) LOOP
1854 FOR l_c_inst_item IN c_inst_item(l_c_free_form2.id) LOOP
1855 l_chr_id := l_c_inst_item.dnz_chr_id;
1856 FOR l_c_item IN c_items(l_c_inst_item.id) LOOP
1857 OPEN c_ib_inst(l_c_item.object1_id1,l_c_item.object1_id2);
1858 FETCH c_ib_inst INTO l_c_ib_inst;
1859 IF c_ib_inst%FOUND THEN
1860
1861 OPEN c_get_entered_alc_trx(l_c_inst_item.id);
1862 FETCH c_get_entered_alc_trx INTO l_trx_rec;
1863 IF c_get_entered_alc_trx%NOTFOUND THEN
1864 OKL_API.set_message( p_app_name => 'OKL',
1865 p_msg_name => 'OKL_ASTLOC_TRX_NF');
1866 RAISE OKL_API.G_EXCEPTION_ERROR;
1867 END IF;
1868 CLOSE c_get_entered_alc_trx;
1869
1870 -- updating installed base item.
1871
1872 SELECT object_version_number
1873 INTO l_obj_no
1874 FROM csi_item_instances
1875 WHERE instance_id = l_c_ib_inst.id1;
1876
1877
1878 OPEN c_loc_typecode(l_c_ib_inst.id1);
1879 FETCH c_loc_typecode INTO l_loc_type_code,l_inst_loc_type_code;
1880 CLOSE c_loc_typecode;
1881
1882 IF (l_loc_type_code = 'HZ_LOCATIONS') THEN
1883 l_instance_rec.LOCATION_ID := l_trx_Rec.location_id;
1884 ELSIF l_loc_type_code = 'HZ_PARTY_SITES' THEN
1885 l_instance_rec.LOCATION_ID := l_trx_rec.party_site_id;
1886 END IF;
1887
1888 IF (l_inst_loc_type_code = 'HZ_LOCATIONS') THEN
1889 l_instance_rec.INSTALL_LOCATION_ID := l_trx_Rec.location_id;
1890 ELSIF l_inst_loc_type_code = 'HZ_PARTY_SITES' THEN
1891 l_instance_rec.INSTALL_LOCATION_ID := l_trx_rec.party_site_id;
1892 END IF;
1893
1894 l_instance_rec.instance_id := l_c_ib_inst.id1;
1895 l_instance_rec.instance_number := l_c_ib_inst.name;
1896 l_instance_rec.object_version_number := l_obj_no;
1897
1898 l_instance_rec.EXTERNAL_REFERENCE := FND_API.G_MISS_CHAR;
1899 l_instance_rec.INVENTORY_ITEM_ID := FND_API.G_MISS_NUM;
1900 l_instance_rec.VLD_ORGANIZATION_ID := FND_API.G_MISS_NUM;
1901 l_instance_rec.INVENTORY_REVISION := FND_API.G_MISS_CHAR;
1902 l_instance_rec.INV_MASTER_ORGANIZATION_ID := FND_API.G_MISS_NUM;
1903 l_instance_rec.MFG_SERIAL_NUMBER_FLAG := FND_API.G_MISS_CHAR;
1904 l_instance_rec.LOT_NUMBER := FND_API.G_MISS_CHAR;
1905 l_instance_rec.QUANTITY := FND_API.G_MISS_NUM;
1906 l_instance_rec.UNIT_OF_MEASURE := FND_API.G_MISS_CHAR;
1907 l_instance_rec.ACCOUNTING_CLASS_CODE := FND_API.G_MISS_CHAR;
1908 l_instance_rec.INSTANCE_CONDITION_ID := FND_API.G_MISS_NUM;
1909 l_instance_rec.INSTANCE_STATUS_ID := FND_API.G_MISS_NUM;
1910 l_instance_rec.CUSTOMER_VIEW_FLAG := FND_API.G_MISS_CHAR;
1911 l_instance_rec.MERCHANT_VIEW_FLAG := FND_API.G_MISS_CHAR;
1912 l_instance_rec.SELLABLE_FLAG := FND_API.G_MISS_CHAR;
1913 l_instance_rec.SYSTEM_ID := FND_API.G_MISS_NUM;
1914 l_instance_rec.INSTANCE_TYPE_CODE := FND_API.G_MISS_CHAR;
1915 l_instance_rec.ACTIVE_START_DATE := FND_API.G_MISS_DATE;
1916 l_instance_rec.ACTIVE_END_DATE := FND_API.G_MISS_DATE;
1917 l_instance_rec.INV_ORGANIZATION_ID := FND_API.G_MISS_NUM;
1918 l_instance_rec.INV_SUBINVENTORY_NAME := FND_API.G_MISS_CHAR;
1919 l_instance_rec.INV_LOCATOR_ID := FND_API.G_MISS_NUM;
1920 l_instance_rec.PA_PROJECT_ID := FND_API.G_MISS_NUM;
1921 l_instance_rec.PA_PROJECT_TASK_ID := FND_API.G_MISS_NUM;
1922 l_instance_rec.IN_TRANSIT_ORDER_LINE_ID := FND_API.G_MISS_NUM;
1923 l_instance_rec.WIP_JOB_ID := FND_API.G_MISS_NUM;
1924 l_instance_rec.PO_ORDER_LINE_ID := FND_API.G_MISS_NUM;
1925 l_instance_rec.LAST_OE_ORDER_LINE_ID := FND_API.G_MISS_NUM;
1926 l_instance_rec.LAST_OE_RMA_LINE_ID := FND_API.G_MISS_NUM;
1927 l_instance_rec.LAST_PO_PO_LINE_ID := FND_API.G_MISS_NUM;
1928 l_instance_rec.LAST_OE_PO_NUMBER := FND_API.G_MISS_CHAR;
1929 l_instance_rec.LAST_WIP_JOB_ID := FND_API.G_MISS_NUM;
1930 l_instance_rec.LAST_PA_PROJECT_ID := FND_API.G_MISS_NUM;
1931 l_instance_rec.LAST_PA_TASK_ID := FND_API.G_MISS_NUM;
1932 l_instance_rec.LAST_OE_AGREEMENT_ID := FND_API.G_MISS_NUM;
1933 l_instance_rec.INSTALL_DATE := FND_API.G_MISS_DATE;
1934 l_instance_rec.MANUALLY_CREATED_FLAG := FND_API.G_MISS_CHAR;
1935 l_instance_rec.RETURN_BY_DATE := FND_API.G_MISS_DATE;
1936 l_instance_rec.ACTUAL_RETURN_DATE := FND_API.G_MISS_DATE;
1937 l_instance_rec.CREATION_COMPLETE_FLAG := FND_API.G_MISS_CHAR;
1938 l_instance_rec.COMPLETENESS_FLAG := FND_API.G_MISS_CHAR;
1939 l_instance_rec.VERSION_LABEL := FND_API.G_MISS_CHAR;
1940 l_instance_rec.VERSION_LABEL_DESCRIPTION := FND_API.G_MISS_CHAR;
1941 l_instance_rec.CONTEXT := FND_API.G_MISS_CHAR;
1942 l_instance_rec.ATTRIBUTE1 := FND_API.G_MISS_CHAR;
1943 l_instance_rec.ATTRIBUTE2 := FND_API.G_MISS_CHAR;
1944 l_instance_rec.ATTRIBUTE3 := FND_API.G_MISS_CHAR;
1945 l_instance_rec.ATTRIBUTE4 := FND_API.G_MISS_CHAR;
1946 l_instance_rec.ATTRIBUTE5 := FND_API.G_MISS_CHAR;
1947 l_instance_rec.ATTRIBUTE6 := FND_API.G_MISS_CHAR;
1948 l_instance_rec.ATTRIBUTE7 := FND_API.G_MISS_CHAR;
1949 l_instance_rec.ATTRIBUTE8 := FND_API.G_MISS_CHAR;
1950 l_instance_rec.ATTRIBUTE9 := FND_API.G_MISS_CHAR;
1951 l_instance_rec.ATTRIBUTE10 := FND_API.G_MISS_CHAR;
1952 l_instance_rec.ATTRIBUTE11 := FND_API.G_MISS_CHAR;
1953 l_instance_rec.ATTRIBUTE12 := FND_API.G_MISS_CHAR;
1954 l_instance_rec.ATTRIBUTE13 := FND_API.G_MISS_CHAR;
1955 l_instance_rec.ATTRIBUTE14 := FND_API.G_MISS_CHAR;
1956 l_instance_rec.ATTRIBUTE15 := FND_API.G_MISS_CHAR;
1957 l_instance_rec.LAST_TXN_LINE_DETAIL_ID := FND_API.G_MISS_NUM;
1958
1959 l_instance_rec.INSTANCE_USAGE_CODE := FND_API.G_MISS_CHAR;
1960 l_instance_rec.CHECK_FOR_INSTANCE_EXPIRY := FND_API.G_TRUE;
1961
1962 get_trx_rec(p_api_version => p_api_version,
1963 p_init_msg_list => p_init_msg_list,
1964 x_return_status => x_return_status,
1965 x_msg_count => x_msg_count,
1966 x_msg_data => x_msg_data,
1967 p_cle_id => NULL,
1968 p_transaction_type => 'New',
1969 x_trx_rec => l_txn_rec);
1970
1971 l_txn_rec.transaction_id := FND_API.G_MISS_NUM;
1972 l_txn_rec.transaction_date := sysdate - 10;
1973
1974
1975 csi_item_instance_pub.update_item_instance(p_api_version => p_api_version,
1976 p_commit => fnd_api.g_false,
1977 p_init_msg_list => p_init_msg_list,
1978 p_validation_level => fnd_api.g_valid_level_full,
1979 p_instance_rec => l_instance_rec,
1980 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
1981 p_party_tbl => l_party_tbl,
1982 p_account_tbl => l_account_tbl,
1983 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
1984 p_org_assignments_tbl => l_org_assignments_tbl,
1985 p_asset_assignment_tbl => l_asset_assignment_tbl,
1986 p_txn_rec => l_txn_rec,
1987 x_instance_id_lst => l_instance_id_lst,
1988 x_return_status => x_return_status,
1989 x_msg_count => x_msg_count,
1990 x_msg_data => x_msg_data);
1991
1992 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1993 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Return status from updating in Install Base '||x_return_status);
1994 END IF;
1995
1996 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1997 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1998 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1999 RAISE OKC_API.G_EXCEPTION_ERROR;
2000 END IF;
2001
2002
2003
2004
2005 OKL_PROCESS_SALES_TAX_PUB.calculate_sales_tax(
2006 p_api_version => p_api_version,
2007 p_init_msg_list => p_init_msg_list,
2008 x_return_status => x_return_status,
2009 x_msg_count => x_msg_count,
2010 x_msg_data => x_msg_data,
2011 p_source_trx_id => l_trx_rec.id,
2012 p_source_trx_name => G_TRY_NAME,
2013 p_source_table => G_TRX_TABLE,
2014 p_tax_call_type => 'ACTUAL');
2015
2016 --asawanka ebtax changes end
2017 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2018 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Return status from Tax API '||x_return_status);
2019 END IF;
2020
2021 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2022 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2023 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2024 RAISE OKC_API.G_EXCEPTION_ERROR;
2025 END IF;
2026
2027 --Bug# 6619311
2028 OKL_BLK_AST_UPD_PVT.create_upfront_tax_accounting(
2029 p_api_version => p_api_version
2030 ,p_init_msg_list => p_init_msg_list
2031 ,p_contract_id => l_chr_id
2032 ,p_line_id => p_kle_id
2033 ,p_transaction_id => l_trx_rec.id
2034 ,p_transaction_type => 'TAS'
2035 ,p_transaction_date => l_trx_rec.date_trans_occurred
2036 ,x_return_status => x_return_status
2037 ,x_msg_count => x_msg_count
2038 ,x_msg_data => x_msg_data);
2039
2040 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2041 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Return status from create_upfront_tax_accounting API '||x_return_status);
2042 END IF;
2043
2044 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2045 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2046 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2047 RAISE OKC_API.G_EXCEPTION_ERROR;
2048 END IF;
2049
2050 -- 27-May-2008 SECHAWLA 6619311 l_parent_line_id was being passed to
2051 -- cursor, but there was no value being assigned to this variable
2052 -- Changed l_parent_line_id to p_kle_id
2053 --OPEN c_get_tax_amt_csr(l_trx_rec.id,l_chr_id,l_parent_line_id);
2054 OPEN c_get_tax_amt_csr(l_trx_rec.id,l_chr_id,p_kle_id);
2055 FETCH c_get_tax_amt_csr INTO l_tax_amt;
2056 CLOSE c_get_tax_amt_csr;
2057
2058 IF l_tax_amt <> 0 THEN
2059 Okl_Bill_Upfront_Tax_Pvt.Bill_Upfront_Tax(
2060 p_api_version => p_api_version,
2061 p_init_msg_list => p_init_msg_list,
2062 p_khr_id => l_chr_id,
2063 p_trx_id => l_trx_rec.id,
2064 p_invoice_date => l_trx_rec.date_trans_occurred,
2065 x_return_status => x_return_status,
2066 x_msg_count => x_msg_count,
2067 x_msg_data => x_msg_data);
2068 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2069 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Return Status after creating Tax only invoice' || x_return_status);
2070 END IF;
2071 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2072 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2073 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2074 RAISE OKC_API.G_EXCEPTION_ERROR;
2075 END IF;
2076 ELSE
2077 OKL_API.set_message( p_app_name => 'OKL',
2078 p_msg_name => 'OKL_ASTLOC_TAX_NOT_BILLED');
2079 END IF;
2080
2081
2082 l_trxv_rec.id := l_trx_rec.id;
2083 l_trxv_rec.tsu_code := 'PROCESSED';
2084
2085
2086 Update_asset_header(p_api_version => p_api_version,
2087 p_init_msg_list => p_init_msg_list,
2088 x_return_status => x_return_status,
2089 x_msg_count => x_msg_count,
2090 x_msg_data => x_msg_data,
2091 p_trxv_rec => l_trxv_rec,
2092 x_trxv_rec => x_trxv_rec);
2093
2094
2095 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2096 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2097 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2098 RAISE OKL_API.G_EXCEPTION_ERROR;
2099 END IF;
2100 END IF; -- If _ib_inst found
2101 CLOSE c_ib_inst;
2102 END LOOP;
2103 END LOOP;
2104 IF l_trx_rec.req_asset_id IS NOT NULL THEN
2105 l_trqv_rec.id := l_trx_rec.req_asset_id;
2106 l_trqv_rec.request_status_code := 'PROCESSED';
2107 SELECT object_version_number INTO l_trqv_rec.object_version_number
2108 FROM okl_trx_requests
2109 WHERE ID = l_trqv_rec.id;
2110 okl_trx_requests_pub.update_trx_requests(p_api_version => p_api_version,
2111 p_init_msg_list => p_init_msg_list,
2112 x_return_status => x_return_status,
2113 x_msg_count => x_msg_count,
2114 x_msg_data => x_msg_data,
2115 p_trqv_rec => l_trqv_rec,
2116 x_trqv_rec => x_trqv_rec);
2117
2118 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2119 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2120 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2121 RAISE OKL_API.G_EXCEPTION_ERROR;
2122 END IF;
2123
2124 END IF;
2125 G_CTR := G_CTR + 1;
2126 END LOOP;
2127
2128 OKL_API.END_ACTIVITY (x_msg_count,
2129 x_msg_data );
2130 EXCEPTION
2131 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2132 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2133 l_api_name,
2134 G_PKG_NAME,
2135 'OKL_API.G_RET_STS_ERROR',
2136 x_msg_count,
2137 x_msg_data,
2138 '_PVT');
2139 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2140 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2141 l_api_name,
2142 G_PKG_NAME,
2143 'OKL_API.G_RET_STS_UNEXP_ERROR',
2144 x_msg_count,
2145 x_msg_data,
2146 '_PVT');
2147 WHEN OTHERS THEN
2148 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2149 l_api_name,
2150 G_PKG_NAME,
2151 'OTHERS',
2152 x_msg_count,
2153 x_msg_data,
2154 '_PVT');
2155
2156 END process_update_location;
2157
2158 END OKL_BLK_AST_UPD_PVT;