1 PACKAGE BODY OKL_VENDORMERGE_GRP AS
2 /* $Header: OKLRVMAB.pls 120.5 2007/09/10 15:34:35 pagarg noship $ */
3
4 L_MODULE FND_LOG_MESSAGES.MODULE%TYPE;
5 L_DEBUG_ENABLED VARCHAR2(10);
6 IS_DEBUG_PROCEDURE_ON BOOLEAN;
7 IS_DEBUG_STATEMENT_ON BOOLEAN;
8 ------------------------------------------------------------------------------
9 -- PROCEDURE MERGE_VENDOR
10 ------------------------------------------------------------------------------
11 -- Start of comments
12 --
13 -- Procedure Name : MERGE_VENDOR
14 -- Description : This procedure updates OKL data when two vendors
15 -- are merged.
16 -- Business Rules : This procedure updates OKL data when two vendors
17 -- are merged.
18 --
19 -- Parameters : p_vendor_id - Represents Merge To Vendor.
20 -- p_dup_vendor_id - Represents Merge From Vendor.
21 -- p_vendor_site_id - Represents Merge To Vendor Site.
22 -- p_dup_vendor_site_id Represents Merge From Vendor Site
23 -- p_party_id - Represents Merge To Party.
24 -- P_dup_party_id - Represents Merge From Party
25 -- p_party_site_id - Represents Merge To Party Site
26 -- p_dup_party_site_id -Represents Merge From Party Site
27 --
28 -- Version : 1.0
29 -- History : 26-Dec-2006 Bug# 4541415 PAGARG created
30 --
31 -- End of comments
32 ------------------------------------------------------------------------------
33 PROCEDURE MERGE_VENDOR
34 (p_api_version IN NUMBER
35 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
36 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
37 ,p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
38 ,p_return_status OUT NOCOPY VARCHAR2
39 ,p_msg_count OUT NOCOPY NUMBER
40 ,p_msg_data OUT NOCOPY VARCHAR2
41 ,p_vendor_id IN NUMBER
42 ,p_dup_vendor_id IN NUMBER
43 ,p_vendor_site_id IN NUMBER
44 ,p_dup_vendor_site_id IN NUMBER
45 ,p_party_id IN NUMBER
46 ,P_dup_party_id IN NUMBER
47 ,p_party_site_id IN NUMBER
48 ,p_dup_party_site_id IN NUMBER
49 )
50 IS
51 -- Variables Declarations
52 l_api_version CONSTANT NUMBER DEFAULT G_INIT_VERSION;
53 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'MERGE_VENDOR';
54 l_return_status VARCHAR2(1);
55 l_counter NUMBER;
56 l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
57 l_last_updated_by NUMBER;
58 l_last_update_date DATE;
59 l_last_update_login NUMBER;
60 l_cplv_rec OKC_CPL_PVT.cplv_rec_type;
61 l_kplv_rec OKL_KPL_PVT.kplv_rec_type;
62
63 --This cursor fetches all those contracts, which have both source and destination
64 --Vendors as the parties to the contract
65 CURSOR chk_vendor_chr_csr (p_src_vendor_id NUMBER, p_des_vendor_id NUMBER)
66 IS
67 SELECT CHR.CONTRACT_NUMBER
68 , CHR.ID
69 FROM OKC_K_HEADERS_ALL_B CHR
70 , OKC_K_PARTY_ROLES_B CPRS
71 , OKC_K_PARTY_ROLES_B CPRD
72 WHERE CPRS.CHR_ID = CHR.ID
73 AND CPRS.DNZ_CHR_ID = CPRD.DNZ_CHR_ID
74 AND CPRS.OBJECT1_ID1 <> CPRD.OBJECT1_ID1
75 AND CPRS.OBJECT1_ID1 = p_src_vendor_id
76 AND CPRD.OBJECT1_ID1 = p_des_vendor_id
77 AND CPRS.JTOT_OBJECT1_CODE = 'OKX_VENDOR'
78 AND CPRD.JTOT_OBJECT1_CODE = 'OKX_VENDOR'
79 AND CHR.SCS_CODE = 'LEASE'
80 AND CPRS.CLE_ID IS NULL
81 AND CPRD.CLE_ID IS NULL
82 AND CPRS.CHR_ID = CPRD.CHR_ID
83 GROUP BY CHR.CONTRACT_NUMBER, CHR.ID
84 ORDER BY CHR.CONTRACT_NUMBER;
85 chk_vendor_chr_rec chk_vendor_chr_csr%ROWTYPE;
86
87 --This cursor fetches all those contracts along with the contract line id,
88 --which have both source and destination Vendors as the parties to the contract line
89 CURSOR chk_vendor_chr_ln_csr (p_src_vendor_id NUMBER, p_des_vendor_id NUMBER)
90 IS
91 SELECT CHR.CONTRACT_NUMBER
92 , CHR.ID
93 , CPRD.CLE_ID
94 FROM OKC_K_HEADERS_ALL_B CHR
95 , OKC_K_PARTY_ROLES_B CPRS
96 , OKC_K_PARTY_ROLES_B CPRD
97 WHERE CPRS.DNZ_CHR_ID = CHR.ID
98 AND CPRS.DNZ_CHR_ID = CPRD.DNZ_CHR_ID
99 AND CPRS.OBJECT1_ID1 <> CPRD.OBJECT1_ID1
100 AND CPRS.OBJECT1_ID1 = p_src_vendor_id
101 AND CPRD.OBJECT1_ID1 = p_des_vendor_id
102 AND CPRS.JTOT_OBJECT1_CODE = 'OKX_VENDOR'
103 AND CPRD.JTOT_OBJECT1_CODE = 'OKX_VENDOR'
104 AND CHR.SCS_CODE = 'LEASE'
105 AND CPRS.CHR_ID IS NULL
106 AND CPRD.CHR_ID IS NULL
107 AND CPRS.CLE_ID = CPRD.CLE_ID
108 GROUP BY CHR.CONTRACT_NUMBER, CHR.ID, CPRD.CLE_ID
109 ORDER BY CHR.CONTRACT_NUMBER;
110 chk_vendor_chr_ln_rec chk_vendor_chr_ln_csr%ROWTYPE;
111
112 --This cursor returns the Party Role Id for both source and destination vendor
113 --for a given Contract. It returns the Id for Party Role defined at Contract
114 --Header level
115 CURSOR party_role_dtls_csr (p_src_vendor_id NUMBER, p_des_vendor_id NUMBER, p_chr_id NUMBER)
116 IS
117 SELECT CPRS.ID CPRS_CPL_ID
118 , CPRD.ID CPRD_CPL_ID
119 FROM OKC_K_PARTY_ROLES_B CPRS
120 , OKC_K_PARTY_ROLES_B CPRD
121 WHERE CPRS.DNZ_CHR_ID = p_chr_id
122 AND CPRS.DNZ_CHR_ID = CPRD.DNZ_CHR_ID
123 AND CPRS.OBJECT1_ID1 <> CPRD.OBJECT1_ID1
124 AND CPRS.OBJECT1_ID1 = p_src_vendor_id
125 AND CPRD.OBJECT1_ID1 = p_des_vendor_id
126 AND CPRS.JTOT_OBJECT1_CODE = 'OKX_VENDOR'
127 AND CPRD.JTOT_OBJECT1_CODE = 'OKX_VENDOR'
128 AND CPRS.CLE_ID IS NULL
129 AND CPRD.CLE_ID IS NULL
130 AND CPRS.CHR_ID = CPRD.CHR_ID;
131 party_role_dtls_rec party_role_dtls_csr%ROWTYPE;
132
133 --This cursor returns the Party Role Id for both source and destination vendor
134 --for a given Contract Line. It returns the Id for Party Role defined at Contract
135 --Line level
136 CURSOR party_role_dtls_ln_csr (p_src_vendor_id NUMBER, p_des_vendor_id NUMBER, p_chr_id NUMBER, p_cle_id NUMBER)
137 IS
138 SELECT CPRS.ID CPRS_CPL_ID
139 , CPRD.ID CPRD_CPL_ID
140 FROM OKC_K_PARTY_ROLES_B CPRS
141 , OKC_K_PARTY_ROLES_B CPRD
142 WHERE CPRS.DNZ_CHR_ID = p_chr_id
143 AND CPRS.DNZ_CHR_ID = CPRD.DNZ_CHR_ID
144 AND CPRS.OBJECT1_ID1 <> CPRD.OBJECT1_ID1
145 AND CPRS.OBJECT1_ID1 = p_src_vendor_id
146 AND CPRD.OBJECT1_ID1 = p_des_vendor_id
147 AND CPRS.JTOT_OBJECT1_CODE = 'OKX_VENDOR'
148 AND CPRD.JTOT_OBJECT1_CODE = 'OKX_VENDOR'
149 AND CPRS.CHR_ID IS NULL
150 AND CPRD.CHR_ID IS NULL
151 AND CPRS.CLE_ID = p_cle_id
152 AND CPRS.CLE_ID = CPRD.CLE_ID;
153 party_role_dtls_ln_rec party_role_dtls_ln_csr%ROWTYPE;
154
155 --This cursor verifies whether there is passthrough setup being done at either
156 --Contract or Line level for the source/merged vendor
157 CURSOR chk_passthrough_csr (p_chr_id NUMBER, p_cle_id NUMBER, p_src_cpl_id NUMBER)
158 IS
159 SELECT 1
160 FROM OKL_PARTY_PAYMENT_HDR PPH
161 , OKL_PARTY_PAYMENT_DTLS PPD
162 WHERE DNZ_CHR_ID = p_chr_id
163 AND NVL(CLE_ID, -1) = NVL(p_cle_id, NVL(CLE_ID, -1))
164 AND PPH.ID = PPD.PAYMENT_HDR_ID
165 AND PPD.CPL_ID = p_src_cpl_id;
166 chk_passthrough_rec chk_passthrough_csr%ROWTYPE;
167
168 --This cursor is used to fetch the Line Name for the given Line Id.
169 --It is specific to Passthrough Fee line. Line Name is needed to be passed
170 --as token value in the error message
171 CURSOR fee_name_csr(p_cle_id NUMBER)
172 IS
173 SELECT STY.NAME FEE_NAME
174 FROM OKC_K_ITEMS ITM
175 , OKL_STRM_TYPE_TL STY
176 , OKC_K_LINES_B CLE
177 WHERE ITM.OBJECT1_ID1 = STY.ID
178 AND ITM.JTOT_OBJECT1_CODE = 'OKL_STRMTYP'
179 AND STY.LANGUAGE = USERENV('LANG')
180 AND ITM.CLE_ID = CLE.ID
181 AND CLE.LSE_ID = 52
182 AND CLE.ID = p_cle_id
183 UNION
184 SELECT NAME
185 FROM okc_k_lines_v
186 WHERE id = p_cle_id
187 AND lse_id = 48;
188 l_fee_name OKL_STRM_TYPE_TL.NAME%TYPE;
189
190 BEGIN
191 l_return_status := OKL_API.G_RET_STS_SUCCESS;
192 l_debug_enabled := okl_debug_pub.check_log_enabled;
193
194 l_last_updated_by := FND_GLOBAL.USER_ID;
195 l_last_update_date := TRUNC(SYSDATE);
196 l_last_update_login := FND_GLOBAL.LOGIN_ID;
197
198 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
199 ,fnd_log.level_procedure);
200
201 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
202 okl_debug_pub.log_debug(fnd_log.level_procedure
203 ,l_module
204 ,'start debug okl_vendormerge_grp.merge_vendor');
205 END IF; -- check for logging on STATEMENT level
206 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
207 ,fnd_log.level_statement);
208 -- call START_ACTIVITY to create savepoint, check compatibility
209 -- and initialize message list
210 l_return_status := okl_api.start_activity(p_api_name => l_api_name
211 ,p_pkg_name => G_PKG_NAME
212 ,p_init_msg_list => p_init_msg_list
213 ,p_api_version => p_api_version
214 ,l_api_version => l_api_version
215 ,p_api_type => G_API_TYPE
216 ,x_return_status => l_return_status);
217 -- check if activity started successfully
218
219 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
220 RAISE okl_api.g_exception_unexpected_error;
221 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
222 RAISE okl_api.g_exception_error;
223 END IF;
224
225 --Update OKL_TAX_ATTR_DEFINITIONS for references of Vendor Site
226 UPDATE OKL_TAX_ATTR_DEFINITIONS
227 SET VENDOR_SITE_ID = p_vendor_site_id,
228 LAST_UPDATED_BY = l_last_updated_by,
229 LAST_UPDATE_DATE = l_last_update_date,
230 LAST_UPDATE_LOGIN = l_last_update_login
231 WHERE VENDOR_SITE_ID = p_dup_vendor_site_id;
232
233 --Sales and Front End Objects
234 --Update OKL_SERVICES_B for references of Vendor
235 UPDATE OKL_SERVICES_B
236 SET SUPPLIER_ID = p_vendor_id,
237 LAST_UPDATED_BY = l_last_updated_by,
238 LAST_UPDATE_DATE = l_last_update_date,
239 LAST_UPDATE_LOGIN = l_last_update_login
240 WHERE SUPPLIER_ID = p_dup_vendor_id;
241
242 --Update OKL_ASSET_COMPONENTS_B for references of Vendor
243 UPDATE OKL_ASSET_COMPONENTS_B
244 SET SUPPLIER_ID = p_vendor_id,
245 LAST_UPDATED_BY = l_last_updated_by,
246 LAST_UPDATE_DATE = l_last_update_date,
247 LAST_UPDATE_LOGIN = l_last_update_login
248 WHERE SUPPLIER_ID = p_dup_vendor_id;
249
250 --Update OKL_COST_ADJUSTMENTS_B for references of Vendor
251 UPDATE OKL_COST_ADJUSTMENTS_B
252 SET SUPPLIER_ID = p_vendor_id,
253 LAST_UPDATED_BY = l_last_updated_by,
254 LAST_UPDATE_DATE = l_last_update_date,
255 LAST_UPDATE_LOGIN = l_last_update_login
256 WHERE SUPPLIER_ID = p_dup_vendor_id;
257
258 --Update OKL_FEES_B for references of Vendor
259 UPDATE OKL_FEES_B
260 SET SUPPLIER_ID = p_vendor_id,
261 LAST_UPDATED_BY = l_last_updated_by,
262 LAST_UPDATE_DATE = l_last_update_date,
263 LAST_UPDATE_LOGIN = l_last_update_login
264 WHERE SUPPLIER_ID = p_dup_vendor_id;
265
266 --Update OKL_LEASE_OPPS_ALL_B for references of Vendor
267 UPDATE OKL_LEASE_OPPS_ALL_B
268 SET SUPPLIER_ID = p_vendor_id,
269 LAST_UPDATED_BY = l_last_updated_by,
270 LAST_UPDATE_DATE = l_last_update_date,
271 LAST_UPDATE_LOGIN = l_last_update_login
272 WHERE SUPPLIER_ID = p_dup_vendor_id;
273
274 --Update OKL_LEASE_OPPS_ALL_B for references of Vendor
275 UPDATE OKL_LEASE_OPPS_ALL_B
276 SET ORIGINATING_VENDOR_ID = p_vendor_id,
277 LAST_UPDATED_BY = l_last_updated_by,
278 LAST_UPDATE_DATE = l_last_update_date,
279 LAST_UPDATE_LOGIN = l_last_update_login
280 WHERE ORIGINATING_VENDOR_ID = p_dup_vendor_id;
281
282 --Update OKL_LEASE_APPS_ALL_B for references of Vendor
283 UPDATE OKL_LEASE_APPS_ALL_B
284 SET ORIGINATING_VENDOR_ID = p_vendor_id,
285 LAST_UPDATED_BY = l_last_updated_by,
286 LAST_UPDATE_DATE = l_last_update_date,
287 LAST_UPDATE_LOGIN = l_last_update_login
288 WHERE ORIGINATING_VENDOR_ID = p_dup_vendor_id;
289
290 --Disbursements
291 --Update OKL_CURE_FUND_SUMS_ALL for references of Vendor
292 UPDATE OKL_CURE_FUND_SUMS_ALL
293 SET VENDOR_ID = p_vendor_id,
294 LAST_UPDATED_BY = l_last_updated_by,
295 LAST_UPDATE_DATE = l_last_update_date,
296 LAST_UPDATE_LOGIN = l_last_update_login
297 WHERE VENDOR_ID = p_dup_vendor_id;
298
299 --Update OKL_CURE_FUND_TRANS_ALL for references of Vendor
300 UPDATE OKL_CURE_FUND_TRANS_ALL
301 SET VENDOR_ID = p_vendor_id,
302 LAST_UPDATED_BY = l_last_updated_by,
303 LAST_UPDATE_DATE = l_last_update_date,
304 LAST_UPDATE_LOGIN = l_last_update_login
305 WHERE VENDOR_ID = p_dup_vendor_id;
306
307 --Update OKL_CURE_REFUND_HEADERS_B for references of Vendor Site
308 UPDATE OKL_CURE_REFUND_HEADERS_B
309 SET VENDOR_SITE_ID = p_vendor_site_id,
310 LAST_UPDATED_BY = l_last_updated_by,
311 LAST_UPDATE_DATE = l_last_update_date,
312 LAST_UPDATE_LOGIN = l_last_update_login
313 WHERE VENDOR_SITE_ID = p_dup_vendor_site_id;
314
315 --Update OKL_CURE_REFUNDS_ALL for references of Vendor Site
316 UPDATE OKL_CURE_REFUNDS_ALL
317 SET VENDOR_SITE_ID = p_vendor_site_id,
318 LAST_UPDATED_BY = l_last_updated_by,
319 LAST_UPDATE_DATE = l_last_update_date,
320 LAST_UPDATE_LOGIN = l_last_update_login
321 WHERE VENDOR_SITE_ID = p_dup_vendor_site_id;
322
323 --Update OKL_CURE_REFUND_STAGE for references of Vendor
324 UPDATE OKL_CURE_REFUND_STAGE
325 SET VENDOR_ID = p_vendor_id,
326 LAST_UPDATED_BY = l_last_updated_by,
327 LAST_UPDATE_DATE = l_last_update_date,
328 LAST_UPDATE_LOGIN = l_last_update_login
329 WHERE VENDOR_ID = p_dup_vendor_id;
330
331 --Update OKL_CURE_REPORTS_ALL for references of Vendor
332 UPDATE OKL_CURE_REPORTS_ALL
333 SET VENDOR_ID = p_vendor_id,
334 VENDOR_SITE_ID = p_vendor_site_id,
335 LAST_UPDATED_BY = l_last_updated_by,
336 LAST_UPDATE_DATE = l_last_update_date,
337 LAST_UPDATE_LOGIN = l_last_update_login
338 WHERE VENDOR_ID = p_dup_vendor_id
339 AND VENDOR_SITE_ID = p_dup_vendor_site_id;
340
341 -- OKL_TRX_AP_INVS_ALL_B
342 UPDATE OKL_TRX_AP_INVS_ALL_B
343 SET VENDOR_ID = p_vendor_id,
344 LAST_UPDATED_BY = l_last_updated_by,
345 LAST_UPDATE_DATE = l_last_update_date,
346 LAST_UPDATE_LOGIN = l_last_update_login
347 WHERE VENDOR_ID = p_dup_vendor_id;
348
349 UPDATE OKL_TRX_AP_INVS_ALL_B
350 SET IPVS_ID = p_vendor_site_id,
351 LAST_UPDATED_BY = l_last_updated_by,
352 LAST_UPDATE_DATE = l_last_update_date,
353 LAST_UPDATE_LOGIN = l_last_update_login
354 WHERE IPVS_ID = p_dup_vendor_site_id;
355
356 --Insurance Objects
357 --Update OKL_INS_POLICIES_ALL_B for references of Vendor
358 UPDATE OKL_INS_POLICIES_ALL_B
359 SET ISU_ID = p_vendor_id,
360 LAST_UPDATED_BY = l_last_updated_by,
361 LAST_UPDATE_DATE = l_last_update_date,
362 LAST_UPDATE_LOGIN = l_last_update_login
363 WHERE ISU_ID = p_dup_vendor_id;
364
365 --Update OKL_INS_PRODUCTS_B for references of Vendor
366 UPDATE OKL_INS_PRODUCTS_B
367 SET ISU_ID = p_vendor_id,
368 LAST_UPDATED_BY = l_last_updated_by,
369 LAST_UPDATE_DATE = l_last_update_date,
370 LAST_UPDATE_LOGIN = l_last_update_login
371 WHERE ISU_ID = p_dup_vendor_id;
372
373 --Update OKL_INSURER_RANKINGS for references of Vendor
374 UPDATE OKL_INSURER_RANKINGS
375 SET ISU_ID = p_vendor_id,
376 LAST_UPDATED_BY = l_last_updated_by,
377 LAST_UPDATE_DATE = l_last_update_date,
378 LAST_UPDATE_LOGIN = l_last_update_login
379 WHERE ISU_ID = p_dup_vendor_id;
380
381 --Update OKC_RULES_B for reference of Vendor in object1_id1, which will
382 --be identified based on value for JTOT_OBJECT1_CODE
383 UPDATE OKC_RULES_B RUL
384 SET OBJECT1_ID1 = TO_CHAR(p_vendor_id),
385 LAST_UPDATED_BY = l_last_updated_by,
386 LAST_UPDATE_DATE = l_last_update_date,
387 LAST_UPDATE_LOGIN = l_last_update_login
388 WHERE OBJECT1_ID1 = TO_CHAR(p_dup_vendor_id)
389 AND JTOT_OBJECT1_CODE = 'OKX_VENDOR'
390 AND EXISTS (SELECT ID
391 FROM OKL_K_HEADERS KHR
392 WHERE KHR.ID = RUL.DNZ_CHR_ID);
393
394 --Update OKC_RULES_B for reference of Vendor Site in object2_id1, which will
395 --be identified based on value for JTOT_OBJECT2_CODE
396 UPDATE OKC_RULES_B RUL
397 SET OBJECT2_ID1 = TO_CHAR(p_vendor_site_id),
398 LAST_UPDATED_BY = l_last_updated_by,
399 LAST_UPDATE_DATE = l_last_update_date,
400 LAST_UPDATE_LOGIN = l_last_update_login
401 WHERE OBJECT2_ID1 = TO_CHAR(p_dup_vendor_site_id)
402 AND JTOT_OBJECT2_CODE IN ('OKX_PAYTO', 'OKX_VENDSITE')
403 AND EXISTS (SELECT ID
404 FROM OKL_K_HEADERS KHR
405 WHERE KHR.ID = RUL.DNZ_CHR_ID);
406
407 --Update OKC_RULES_B for reference of Vendor Site in object3_id1, which will
408 --be identified based on value for JTOT_OBJECT3_CODE
409 UPDATE OKC_RULES_B RUL
410 SET OBJECT3_ID1 = TO_CHAR(p_vendor_site_id),
411 LAST_UPDATED_BY = l_last_updated_by,
412 LAST_UPDATE_DATE = l_last_update_date,
413 LAST_UPDATE_LOGIN = l_last_update_login
414 WHERE OBJECT3_ID1 = TO_CHAR(p_dup_vendor_site_id)
415 AND JTOT_OBJECT3_CODE IN ('OKX_PAYTO', 'OKX_VENDSITE')
416 AND EXISTS (SELECT ID
417 FROM OKL_K_HEADERS KHR
418 WHERE KHR.ID = RUL.DNZ_CHR_ID);
419
420 --Update OKL_SUBSIDIES_ALL_B for reference of Vendor
421 UPDATE OKL_SUBSIDIES_ALL_B
422 SET VENDOR_ID = p_vendor_id,
423 LAST_UPDATED_BY = l_last_updated_by,
424 LAST_UPDATE_DATE = l_last_update_date,
425 LAST_UPDATE_LOGIN = l_last_update_login
426 WHERE VENDOR_ID = p_dup_vendor_id;
427
428 --Update OKL_TXL_ASSETS_B for references of Vendor in SUPPLIER_ID
429 UPDATE OKL_TXL_ASSETS_B
430 SET SUPPLIER_ID = p_vendor_id,
431 LAST_UPDATED_BY = l_last_updated_by,
432 LAST_UPDATE_DATE = l_last_update_date,
433 LAST_UPDATE_LOGIN = l_last_update_login
434 WHERE SUPPLIER_ID = p_dup_vendor_id;
435
436 --Update OKL_TXL_ASSETS_B for references of Vendor in RESIDUAL_SHR_PARTY_ID
437 UPDATE OKL_TXL_ASSETS_B
438 SET RESIDUAL_SHR_PARTY_ID = p_vendor_id,
439 LAST_UPDATED_BY = l_last_updated_by,
440 LAST_UPDATE_DATE = l_last_update_date,
441 LAST_UPDATE_LOGIN = l_last_update_login
442 WHERE RESIDUAL_SHR_PARTY_ID = p_dup_vendor_id;
443
444 --Update OKL_TRX_SUBSIDY_POOLS for references of Vendor
445 UPDATE OKL_TRX_SUBSIDY_POOLS
446 SET VENDOR_ID = p_vendor_id,
447 LAST_UPDATED_BY = l_last_updated_by,
448 LAST_UPDATE_DATE = l_last_update_date,
449 LAST_UPDATE_LOGIN = l_last_update_login
450 WHERE VENDOR_ID = p_dup_vendor_id;
451
452 --Update OKL_EXT_PAY_INVS_ALL_B for references of Vendor and Vendor Site
453 --Both the updates can be done in single update as both Vendor and Vendor
454 --Site will be there together in any record
455 UPDATE OKL_EXT_PAY_INVS_ALL_B
456 SET VENDOR_ID = p_vendor_id,
457 VENDOR_SITE_ID = p_vendor_site_id,
458 LAST_UPDATED_BY = l_last_updated_by,
459 LAST_UPDATE_DATE = l_last_update_date,
460 LAST_UPDATE_LOGIN = l_last_update_login
461 WHERE VENDOR_ID = p_dup_vendor_id
462 AND VENDOR_SITE_ID = p_dup_vendor_site_id;
463
464 --Update OKL_QUOTE_SUBPOOL_USAGE for references of Vendor
465 UPDATE OKL_QUOTE_SUBPOOL_USAGE
466 SET VENDOR_ID = p_vendor_id,
467 LAST_UPDATED_BY = l_last_updated_by,
468 LAST_UPDATE_DATE = l_last_update_date,
469 LAST_UPDATE_LOGIN = l_last_update_login
470 WHERE VENDOR_ID = p_dup_vendor_id;
471
472 --Update OKL_EXT_BILLING_INTF_ALL for references of Vendor
473 UPDATE OKL_EXT_BILLING_INTF_ALL
474 SET TAX_VENDOR_ID = p_vendor_id,
475 LAST_UPDATED_BY = l_last_updated_by,
476 LAST_UPDATE_DATE = l_last_update_date,
477 LAST_UPDATE_LOGIN = l_last_update_login
478 WHERE TAX_VENDOR_ID = p_dup_vendor_id;
479
480 --Update OKL_EXT_BILLING_INTF_ALL for references of Vendor Site
481 --Need a separate update as both Vendor and Vendor Site may not be available
482 --together in all the records
483 UPDATE OKL_EXT_BILLING_INTF_ALL
484 SET TAX_VENDOR_SITE_ID = p_vendor_site_id,
485 LAST_UPDATED_BY = l_last_updated_by,
486 LAST_UPDATE_DATE = l_last_update_date,
487 LAST_UPDATE_LOGIN = l_last_update_login
488 WHERE TAX_VENDOR_SITE_ID = p_dup_vendor_site_id;
489
490 --Update OKL_CNSLD_AP_INVS_ALL for references of Vendor
491 UPDATE OKL_CNSLD_AP_INVS_ALL
492 SET VENDOR_ID = p_vendor_id,
493 LAST_UPDATED_BY = l_last_updated_by,
494 LAST_UPDATE_DATE = l_last_update_date,
495 LAST_UPDATE_LOGIN = l_last_update_login
496 WHERE VENDOR_ID = p_dup_vendor_id;
497
498 --Check if both Source and the Destination Vendor exists on the same contract
499 --at line level for the same line.
500 OPEN chk_vendor_chr_ln_csr(p_vendor_id, p_dup_vendor_id);
501 LOOP
502 FETCH chk_vendor_chr_ln_csr INTO chk_vendor_chr_ln_rec;
503 EXIT WHEN chk_vendor_chr_ln_csr%NOTFOUND;
504
505 --If there is such a contract which has both the vendors on the same line
506 --then obtain the Party Role id for these vendors for a given line.
507 OPEN party_role_dtls_ln_csr(p_dup_vendor_id, p_vendor_id, chk_vendor_chr_ln_rec.id, chk_vendor_chr_ln_rec.cle_id);
508 FETCH party_role_dtls_ln_csr INTO party_role_dtls_ln_rec;
509 CLOSE party_role_dtls_ln_csr;
510
511 --Check for Passthrough setup at Contract Lines
512 OPEN chk_passthrough_csr(chk_vendor_chr_ln_rec.id, chk_vendor_chr_ln_rec.cle_id, party_role_dtls_ln_rec.CPRS_CPL_ID);
513 FETCH chk_passthrough_csr INTO chk_passthrough_rec;
514 IF chk_passthrough_csr%FOUND
515 THEN
516 --If both the vendors are setup for Passthrough line then set the message
517 --and raise the error.
518 --Obtain the Passthrough Line name to be passed as token in error message
519 OPEN fee_name_csr(chk_vendor_chr_ln_rec.cle_id);
520 FETCH fee_name_csr INTO l_fee_name;
521 CLOSE fee_name_csr;
522 --Set the Error
523 OKL_API.SET_MESSAGE(
524 p_app_name => G_APP_NAME,
525 p_msg_name => 'OKL_VM_PASSTHROUGH_LINE_ERROR',
526 p_token1 => 'LINE',
527 p_token1_value => l_fee_name,
528 p_token2 => 'CONTRACT',
529 p_token2_value => chk_vendor_chr_ln_rec.contract_number);
530
531 l_return_status := OKL_API.G_RET_STS_ERROR;
532 RAISE OKL_API.G_EXCEPTION_ERROR;
533 ELSE
534 --If there is no passthrough setup which uses both the vendors at line level
535 --then remove the party role using source vendor for the given line.
536 l_kplv_rec.id := party_role_dtls_ln_rec.CPRS_CPL_ID;
537 l_cplv_rec.id := party_role_dtls_ln_rec.CPRS_CPL_ID;
538
539 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
540 THEN
541 OKL_DEBUG_PUB.LOG_DEBUG(
542 FND_LOG.LEVEL_PROCEDURE
543 ,L_MODULE
544 ,'begin debug call OKL_KPL_PVT.DELETE_ROW');
545 END IF;
546
547 --Remove the Party Role at the line level for the vendor getting merged
548 --Call the following API to remove the record in OKL
549 OKL_KPL_PVT.DELETE_ROW
550 (p_api_version => l_api_version,
551 p_init_msg_list => 'F',
552 x_return_status => l_return_status,
553 x_msg_count => p_msg_count,
554 x_msg_data => p_msg_data,
555 p_kplv_rec => l_kplv_rec);
556
557 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
558 THEN
559 OKL_DEBUG_PUB.LOG_DEBUG(
560 FND_LOG.LEVEL_PROCEDURE
561 ,L_MODULE
562 ,'end debug call OKL_KPL_PVT.DELETE_ROW');
563 END IF;
564
565 -- write to log
566 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_STATEMENT_ON) THEN
567 OKL_DEBUG_PUB.LOG_DEBUG(
568 FND_LOG.LEVEL_STATEMENT
569 ,L_MODULE || ' Result of OKL_KPL_PVT.DELETE_ROW'
570 ,'l_return_status ' || l_return_status);
571 END IF; -- end of statement level debug
572
573 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
574 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
575 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
576 RAISE OKL_API.G_EXCEPTION_ERROR;
577 END IF;
578
579 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
580 THEN
581 OKL_DEBUG_PUB.LOG_DEBUG(
582 FND_LOG.LEVEL_PROCEDURE
583 ,L_MODULE
584 ,'begin debug call OKC_CONTRACT_PARTY_PVT.DELETE_K_PARTY_ROLE');
585 END IF;
586
587 --Remove the Party Role at the line level for the vendor getting merged
588 --Call the following API to remove the record in OKC
589 OKC_CONTRACT_PARTY_PVT.DELETE_K_PARTY_ROLE
590 (p_api_version => l_api_version,
591 p_init_msg_list => 'F',
592 x_return_status => l_return_status,
593 x_msg_count => p_msg_count,
594 x_msg_data => p_msg_data,
595 p_cplv_rec => l_cplv_rec);
596
597 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
598 THEN
599 OKL_DEBUG_PUB.LOG_DEBUG(
600 FND_LOG.LEVEL_PROCEDURE
601 ,L_MODULE
602 ,'end debug call OKC_CONTRACT_PARTY_PVT.DELETE_K_PARTY_ROLE');
603 END IF;
604
605 -- write to log
606 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_STATEMENT_ON) THEN
607 OKL_DEBUG_PUB.LOG_DEBUG(
608 FND_LOG.LEVEL_STATEMENT
609 ,L_MODULE || ' Result of OKC_CONTRACT_PARTY_PVT.DELETE_K_PARTY_ROLE'
610 ,'l_return_status ' || l_return_status);
611 END IF; -- end of statement level debug
612
613 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
614 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
615 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
616 RAISE OKL_API.G_EXCEPTION_ERROR;
617 END IF;
618 END IF;
619 CLOSE chk_passthrough_csr;
620 END LOOP;
621 CLOSE chk_vendor_chr_ln_csr;
622
623 --Check if both Source and the Destination Vendor exists on the same contract
624 --at Contract Level
625 OPEN chk_vendor_chr_csr(p_dup_vendor_id, p_vendor_id);
626 LOOP
627 FETCH chk_vendor_chr_csr INTO chk_vendor_chr_rec;
628 EXIT WHEN chk_vendor_chr_csr%NOTFOUND;
629
630 --If there is such a contract which has both the vendors at Contract level
631 --then obtain the Party Role id for these vendors.
632 OPEN party_role_dtls_csr(p_dup_vendor_id, p_vendor_id, chk_vendor_chr_rec.id);
633 FETCH party_role_dtls_csr INTO party_role_dtls_rec;
634 CLOSE party_role_dtls_csr;
635
636 --Handle the Termination Quote Parties if both the vendors are parties
637 --to same termination quote
638 --Update the CPL_ID and PARTY_OBJECT1_ID1 of the merged vendor to point to
639 --destination vendor
640 UPDATE OKL_QUOTE_PARTIES QPT
641 SET PARTY_OBJECT1_ID1 = TO_CHAR(p_vendor_id),
642 LAST_UPDATED_BY = l_last_updated_by,
643 LAST_UPDATE_DATE = l_last_update_date,
644 LAST_UPDATE_LOGIN = l_last_update_login,
645 CPL_ID = party_role_dtls_rec.CPRD_CPL_ID
646 WHERE PARTY_OBJECT1_ID1 = TO_CHAR(p_dup_vendor_id)
647 AND PARTY_JTOT_OBJECT1_CODE = 'OKX_VENDOR'
648 AND CPL_ID = party_role_dtls_rec.CPRS_CPL_ID
649 AND EXISTS (SELECT 1
650 FROM OKL_TRX_QUOTES_ALL_B QTE
651 WHERE QTE.ID = QPT.QTE_ID
652 AND QTE.KHR_ID = chk_vendor_chr_rec.id
653 );
654
655 --Contract level evergreen passthrough terms and cond setup error
656 --Check for Passthrough setup at Contract Header
657 OPEN chk_passthrough_csr(chk_vendor_chr_rec.id, NULL, party_role_dtls_rec.CPRS_CPL_ID);
658 FETCH chk_passthrough_csr INTO chk_passthrough_rec;
659 IF chk_passthrough_csr%FOUND
660 THEN
661 --If both the vendors are used for Passthrough setup then set the message
662 --and raise the error.
663 --Set the Error
664 OKL_API.SET_MESSAGE(
665 p_app_name => G_APP_NAME,
666 p_msg_name => 'OKL_VM_PASSTHROUGH_HDR_ERROR',
667 p_token1 => 'CONTRACT',
668 p_token1_value => chk_vendor_chr_rec.contract_number);
669
670 l_return_status := OKL_API.G_RET_STS_ERROR;
671 RAISE OKL_API.G_EXCEPTION_ERROR;
672 ELSE
673 --If there is no passthrough setup which uses both the vendors at
674 --contract level then remove the party role for source vendor
675 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
676 THEN
677 OKL_DEBUG_PUB.LOG_DEBUG(
678 FND_LOG.LEVEL_PROCEDURE
679 ,L_MODULE
680 ,'begin debug call OKL_JTOT_CONTACT_EXTRACT_PUB.DELETE_PARTY');
681 END IF;
682
683 --Remove the Party Role from the contract for the vendor getting merged
684 OKL_JTOT_CONTACT_EXTRACT_PUB.DELETE_PARTY(
685 l_api_version
686 ,'F'
687 ,l_return_status
688 ,p_msg_count
689 ,p_msg_data
690 ,chk_vendor_chr_rec.id
691 ,party_role_dtls_rec.CPRS_CPL_ID);
692
693 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
694 THEN
695 OKL_DEBUG_PUB.LOG_DEBUG(
696 FND_LOG.LEVEL_PROCEDURE
697 ,L_MODULE
698 ,'end debug call OKL_JTOT_CONTACT_EXTRACT_PUB.DELETE_PARTY');
699 END IF;
700
701 -- write to log
702 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_STATEMENT_ON) THEN
703 OKL_DEBUG_PUB.LOG_DEBUG(
704 FND_LOG.LEVEL_STATEMENT
705 ,L_MODULE || ' Result of OKL_JTOT_CONTACT_EXTRACT_PUB.DELETE_PARTY'
706 ,'l_return_status ' || l_return_status);
707 END IF; -- end of statement level debug
708
709 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
710 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
711 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
712 RAISE OKL_API.G_EXCEPTION_ERROR;
713 END IF;
714 END IF;
715 CLOSE chk_passthrough_csr;
716 END LOOP;
717 CLOSE chk_vendor_chr_csr;
718
719 --Termination Quotes Objects
720 --Update OKL_QUOTE_PARTIES for references of Vendor, which will be identified
721 --based on value for PARTY_JTOT_OBJECT1_CODE
722 --This is needed for the cases in which there is only source vendor added in
723 --the contract.
724 UPDATE OKL_QUOTE_PARTIES
725 SET PARTY_OBJECT1_ID1 = TO_CHAR(p_vendor_id),
726 LAST_UPDATED_BY = l_last_updated_by,
727 LAST_UPDATE_DATE = l_last_update_date,
728 LAST_UPDATE_LOGIN = l_last_update_login
729 WHERE PARTY_OBJECT1_ID1 = TO_CHAR(p_dup_vendor_id)
730 AND PARTY_JTOT_OBJECT1_CODE = 'OKX_VENDOR';
731
732 --Update OKL_PARTY_PAYMENT_DTLS for references of Vendor and Vendor Site
733 --Passthrough setup update is fine in the case where there is only source
734 --vendor used on the setup.
735 UPDATE OKL_PARTY_PAYMENT_DTLS
736 SET VENDOR_ID = p_vendor_id,
737 PAY_SITE_ID = p_vendor_site_id,
738 LAST_UPDATED_BY = l_last_updated_by,
739 LAST_UPDATE_DATE = l_last_update_date,
740 LAST_UPDATE_LOGIN = l_last_update_login
741 WHERE VENDOR_ID = p_dup_vendor_id
742 AND PAY_SITE_ID = p_dup_vendor_site_id;
743
744 --Update OKC_K_PARTY_ROLES_B for references of Vendor
745 --This will take care of the cases where only source vendor and not the destination
746 --vendor is added to the contract. Both the vendor added to contract case is
747 --handled separately above.
748 UPDATE OKC_K_PARTY_ROLES_B CPR
749 SET OBJECT1_ID1 = TO_CHAR(p_vendor_id),
750 LAST_UPDATED_BY = l_last_updated_by,
751 LAST_UPDATE_DATE = l_last_update_date,
752 LAST_UPDATE_LOGIN = l_last_update_login
753 WHERE OBJECT1_ID1 = TO_CHAR(p_dup_vendor_id)
754 AND JTOT_OBJECT1_CODE = 'OKX_VENDOR'
755 AND EXISTS (SELECT ID
756 FROM OKL_K_HEADERS KHR
757 WHERE KHR.ID = CPR.DNZ_CHR_ID);
758
759 OKL_API.END_ACTIVITY(
760 x_msg_count => p_msg_count
761 ,x_msg_data => p_msg_data);
762
763 -- NULL is intentionally treated as false by this statement.
764 IF p_commit = fnd_api.G_TRUE
765 THEN
766 COMMIT;
767 END IF;
768
769 p_return_status := l_return_status;
770 okl_api.end_activity(x_msg_count => p_msg_count
771 ,x_msg_data => p_msg_data);
772
773 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on)
774 THEN
775 okl_debug_pub.log_debug(fnd_log.level_procedure
776 ,l_module
777 ,'end debug okl_vendormerge_grp.merge_vendor');
778 END IF;
779 EXCEPTION
780 WHEN OKL_API.G_EXCEPTION_ERROR
781 THEN
782 IF chk_vendor_chr_csr%ISOPEN
783 THEN
784 CLOSE chk_vendor_chr_csr;
785 END IF;
786 IF chk_vendor_chr_ln_csr%ISOPEN
787 THEN
788 CLOSE chk_vendor_chr_ln_csr;
789 END IF;
790 IF party_role_dtls_csr%ISOPEN
791 THEN
792 CLOSE party_role_dtls_csr;
793 END IF;
794 IF party_role_dtls_ln_csr%ISOPEN
795 THEN
796 CLOSE party_role_dtls_ln_csr;
797 END IF;
798 IF chk_passthrough_csr%ISOPEN
799 THEN
800 CLOSE chk_passthrough_csr;
801 END IF;
802 IF fee_name_csr%ISOPEN
803 THEN
804 CLOSE fee_name_csr;
805 END IF;
806 p_return_status := OKL_API.HANDLE_EXCEPTIONS(
807 p_api_name => l_api_name,
808 p_pkg_name => G_PKG_NAME,
809 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
810 x_msg_count => p_msg_count,
811 x_msg_data => p_msg_data,
812 p_api_type => G_API_TYPE);
813 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR
814 THEN
815 IF chk_vendor_chr_csr%ISOPEN
816 THEN
817 CLOSE chk_vendor_chr_csr;
818 END IF;
819 IF chk_vendor_chr_ln_csr%ISOPEN
820 THEN
821 CLOSE chk_vendor_chr_ln_csr;
822 END IF;
823 IF party_role_dtls_csr%ISOPEN
824 THEN
825 CLOSE party_role_dtls_csr;
826 END IF;
827 IF party_role_dtls_ln_csr%ISOPEN
828 THEN
829 CLOSE party_role_dtls_ln_csr;
830 END IF;
831 IF chk_passthrough_csr%ISOPEN
832 THEN
833 CLOSE chk_passthrough_csr;
834 END IF;
835 IF fee_name_csr%ISOPEN
836 THEN
837 CLOSE fee_name_csr;
838 END IF;
839 p_return_status := OKL_API.HANDLE_EXCEPTIONS(
840 p_api_name => l_api_name,
841 p_pkg_name => G_PKG_NAME,
842 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
843 x_msg_count => p_msg_count,
844 x_msg_data => p_msg_data,
845 p_api_type => G_API_TYPE);
846 WHEN OTHERS
847 THEN
848 IF chk_vendor_chr_csr%ISOPEN
849 THEN
850 CLOSE chk_vendor_chr_csr;
851 END IF;
852 IF chk_vendor_chr_ln_csr%ISOPEN
853 THEN
854 CLOSE chk_vendor_chr_ln_csr;
855 END IF;
856 IF party_role_dtls_csr%ISOPEN
857 THEN
858 CLOSE party_role_dtls_csr;
859 END IF;
860 IF party_role_dtls_ln_csr%ISOPEN
861 THEN
862 CLOSE party_role_dtls_ln_csr;
863 END IF;
864 IF chk_passthrough_csr%ISOPEN
865 THEN
866 CLOSE chk_passthrough_csr;
867 END IF;
868 IF fee_name_csr%ISOPEN
869 THEN
870 CLOSE fee_name_csr;
871 END IF;
872 p_return_status := OKL_API.HANDLE_EXCEPTIONS(
873 p_api_name => l_api_name,
874 p_pkg_name => G_PKG_NAME,
875 p_exc_name => 'OTHERS',
876 x_msg_count => p_msg_count,
877 x_msg_data => p_msg_data,
878 p_api_type => G_API_TYPE);
879 END MERGE_VENDOR;
880 END OKL_VENDORMERGE_GRP;
[Home] [Help]