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