[Home] [Help]
PACKAGE BODY: APPS.OKE_CONTRACT_PARTY_PUB
Source
1 PACKAGE BODY oke_contract_party_pub as
2 /* $Header: OKEPCPLB.pls 120.0.12020000.2 2013/02/22 06:48:54 ansraj noship $*/
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 ---------------------------------------------------------------------------
7 G_FND_APP CONSTANT VARCHAR2(200) := 'OKE';
8 ---------------------------------------------------------------------------
9 ------------------------------------------------------------------------------
10 -- GLOBAL CONSTANTS
11 ------------------------------------------------------------------------------
12 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
13 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
14
15 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKE_API.G_REQUIRED_VALUE;
16 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKE_CONTRACTS_UNEXPECTED_ERROR';
17 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
18 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
19 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKE_API.G_INVALID_VALUE;
20
21 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKE_API.G_COL_NAME_TOKEN;
22
23 g_api_version constant number :=1;
24 g_init_msg_list varchar2(1) := 'F';
25 g_msg_count NUMBER;
26 g_msg_data varchar2(240);
27 p_count number := 0;
28 g_api_type CONSTANT VARCHAR2(4) := '_PUB';
29 ------------------------------------------------------------------------------
30 -- GLOBAL EXCEPTIONS
31 ------------------------------------------------------------------------------
32 E_Resource_Busy EXCEPTION;
33 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
34
35
36 procedure create_k_party_role(p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 p_cplv_tbl IN cplv_tbl_type,
42 x_cplv_tbl OUT NOCOPY cplv_tbl_type,
43 p_skip_minor_vers IN VARCHAR2 DEFAULT OKE_API.G_FALSE)
44 is
45
46 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_K_PARTY_ROLE';
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
49 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
50 i NUMBER:=0;
51 BEGIN
52 -- call START_ACTIVITY to create savepoint, check compatibility
53 -- and initialize message list
54 l_return_status := OKE_API.START_ACTIVITY(
55 p_api_name => l_api_name,
56 p_pkg_name => g_pkg_name,
57 p_init_msg_list => p_init_msg_list,
58 l_api_version => l_api_version,
59 p_api_version => p_api_version,
60 p_api_type => g_api_type,
61 x_return_status => x_return_status);
62
63 -- check if activity started successfully
64 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
65 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
66 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
67 raise OKE_API.G_EXCEPTION_ERROR;
68 End If;
69
70 If (p_cplv_tbl.COUNT > 0) Then
71 i := p_cplv_tbl.FIRST;
72 LOOP
73 -- call procedure for a record
74 create_k_party_role(p_api_version=>p_api_version,
75 p_init_msg_list=>OKC_API.G_FALSE,
76 x_return_status=>l_return_status,
77 x_msg_count=>x_msg_count,
78 x_msg_data=>x_msg_data,
79 p_cplv_rec=>p_cplv_tbl(i),
80 x_cplv_rec=>x_cplv_tbl(i),
81 p_skip_minor_vers=>p_skip_minor_vers
82 );
83
84 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
85 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
86 l_overall_status := x_return_status;
87 End If;
88 End If;
89
90 EXIT WHEN (i = p_cplv_tbl.LAST);
91 i := p_cplv_tbl.NEXT(i);
92 END LOOP;
93
94 -- return overall status
95 x_return_status := l_overall_status;
96 End If;
97
98 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
99 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
100 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
101 raise OKE_API.G_EXCEPTION_ERROR;
102 End If;
103
104 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
105 x_msg_data => x_msg_data);
106 EXCEPTION
107 when OKE_API.G_EXCEPTION_ERROR then
108 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
109 p_api_name => l_api_name,
110 p_pkg_name => g_pkg_name,
111 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
112 x_msg_count => x_msg_count,
113 x_msg_data => x_msg_data,
114 p_api_type => g_api_type);
115
116 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
117 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
118 p_api_name => l_api_name,
119 p_pkg_name => g_pkg_name,
120 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
121 x_msg_count => x_msg_count,
122 x_msg_data => x_msg_data,
123 p_api_type => g_api_type);
124
125 when OTHERS then
126 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
127 p_api_name => l_api_name,
128 p_pkg_name => g_pkg_name,
129 p_exc_name => 'OTHERS',
130 x_msg_count => x_msg_count,
131 x_msg_data => x_msg_data,
132 p_api_type => g_api_type);
133
134 END CREATE_K_PARTY_ROLE;
135
136 FUNCTION ADD_WHERE_TO_SQL( p_sql VARCHAR2, p_where VARCHAR2 ) RETURN VARCHAR2
137 IS
138 l_sql_stmt VARCHAR2(2500) := p_sql;
139 i NUMBER;
140 BEGIN
141 IF p_where IS NOT NULL THEN
142 i := INSTR(l_sql_stmt, 'WHERE');
143 if (i > 0) then
144 -- add after WHERE clause
145 l_sql_stmt := SUBSTR(l_sql_stmt,1, i + 5) ||' '|| p_where || ' AND '|| SUBSTR(l_sql_stmt,i + 5);
146 Else
147 -- no where clause. Add before ORDER BY if any
148 i := INSTR(l_sql_stmt,'ORDER BY');
149 If (i > 0) Then
150 l_sql_stmt := SUBSTR(l_sql_stmt,1,i-1) || ' WHERE '|| p_where ||' '|| SUBSTR(l_sql_stmt,i);
151 Else
152 -- no where and no order by
153 l_sql_stmt := l_sql_stmt || ' WHERE '|| p_where;
154 end if;
155 End If;
156 END IF;
157 RETURN l_sql_stmt;
158 END ADD_WHERE_TO_SQL;
159
160
161
162 PROCEDURE validate_oke_attributes ( x_return_status OUT NOCOPY VARCHAR2,
163 l_JTOT_OBJECT1_CODE OUT NOCOPY VARCHAR2 ,
164 p_cplv_rec in oke_contract_party_pub.cplv_rec_type
165 )IS
166
167 CURSOR get_jtot_object_code_line is
168 SELECT DISTINCT JTOT_OBJECT1_CODE JTOT_OBJECT1_CODE
169 FROM OKC_K_PARTY_ROLES_B V
170 , okc_k_lines_b okl
171 WHERE V.DNZ_CHR_ID = p_cplv_rec.dnz_chr_id AND V.CHR_ID IS NOT NULL
172 AND okl.id= p_cplv_rec.cle_id
173 AND EXISTS (
174 SELECT NULL
175 FROM OKC_SUBCLASS_ROLES SRE
176 , OKC_LINE_STYLE_ROLES LSR
177 WHERE LSR.LSE_ID = okl.lse_id
178 AND SRE.ID = LSR.SRE_ID
179 AND SRE.SCS_CODE = 'PROJECT'
180 AND SRE.RLE_CODE = V.RLE_CODE
181 AND SYSDATE BETWEEN SRE.START_DATE
182 AND NVL(SRE.END_DATE , SYSDATE)
183 )
184 AND RLE_CODE=p_cplv_rec.RLE_CODE ;
185
186 CURSOR get_buy_sell_info IS
187 SELECT BUY_OR_SELL,scs_code FROM
188 okc_k_headers_all_b
189 WHERE id= p_cplv_rec.dnz_chr_id ;
190
191
192
193 CURSOR get_jtot_object_code_header(p_buy_sell VARCHAR2,p_scs_code IN varchar2) is
194 SELECT JTOT_OBJECT_CODE
195 FROM OKC_ROLE_SOURCES RSC
196 , OKC_SUBCLASS_ROLES SRE
197 WHERE
198 RSC.RLE_CODE = p_cplv_rec.rle_code
199 AND SRE.RLE_CODE = RSC.RLE_CODE
200 AND SRE.SCS_CODE = p_scs_code
201 AND RSC.BUY_OR_SELL = p_buy_sell
202 AND SYSDATE BETWEEN RSC.START_DATE
203 AND NVL(RSC.END_DATE , SYSDATE)
204 AND SYSDATE BETWEEN SRE.START_DATE
205 AND NVL(SRE.END_DATE , SYSDATE)
206 AND NOT (RSC.RLE_CODE IN ( 'SHIP_TO', 'BILL_TO', 'MARK_FOR')
207 AND p_buy_sell = 'S'
208 AND NOT EXISTS (select 'x' from okc_k_party_roles_b
209 where chr_id = p_cplv_rec.dnz_chr_id and rle_code = 'K_CUSTOMER'
210 and object1_id1 is not null and jtot_object1_code = 'OKE_CUSTACCT'))
211 AND NOT (RSC.RLE_CODE = 'SHIP_FROM'
212 AND p_buy_sell = 'B'
213 AND NOT EXISTS (select 'x' from okc_k_party_roles_b
214 where chr_id = p_cplv_rec.dnz_chr_id and rle_code = 'CONTRACTOR'
215 and object1_id1 is not null and jtot_object1_code = 'OKX_VENDOR')) ;
216
217
218 l_sql_stmt VARCHAR2(2500);
219 l_dummy_var VARCHAR2(1) := '?';
220
221 l_BUY_OR_SELL VARCHAR2(5);
222 l_scs_code VARCHAR2(30);
223 BEGIN
224 x_return_status := OKE_API.G_RET_STS_SUCCESS;
225
226 ---validate rle_code for line
227
228 open get_buy_sell_info;
229 FETCH get_buy_sell_info INTO l_BUY_OR_SELL ,l_scs_code;
230 CLOSE get_buy_sell_info;
231
232 IF p_cplv_rec.cle_id is NULL OR p_cplv_rec.cle_id=OKC_API.G_MISS_NUM THEN
233 --validate rle code for header
234 open get_jtot_object_code_header(l_BUY_OR_SELL,l_scs_code);
235 fetch get_jtot_object_code_header into l_JTOT_OBJECT1_CODE;
236 close get_jtot_object_code_header;
237
238
239 ELSE
240 --validate rle code for line
241 open get_jtot_object_code_line;
242 fetch get_jtot_object_code_line into l_JTOT_OBJECT1_CODE;
243 close get_jtot_object_code_line;
244
245
246
247 END IF;
248
249
250 IF ( l_JTOT_OBJECT1_CODE IS NULL) THEN
251 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
252 p_msg_name =>G_INVALID_VALUE,
253 p_token1 =>G_COL_NAME_TOKEN,
254 p_token1_value =>'RLE_CODE');
255 x_return_status := OKE_API.G_RET_STS_ERROR;
256 END IF;
257
258
259 IF l_jtot_object1_code is not null THEN
260
261 l_sql_stmt := OKC_UTIL.GET_SQL_FROM_JTFV(l_jtot_object1_code);
262
263
264 If (l_sql_stmt is null) Then
265 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
266 p_msg_name =>'No view found in JTF') ;
267 x_return_status := OKE_API.G_RET_STS_ERROR;
268 End If;
269
270 IF l_jtot_object1_code = 'OKX_VENDOR' THEN
271 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt, 'NVL(STATUS, ''I'') = ''A'' AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1) ');
272 END IF;
273
274 IF l_jtot_object1_code='OKE_CUST_KADMIN' THEN
275 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt, ' NVL(STATUS, ''I'') = ''A'' ');
276 END IF;
277
278 IF l_jtot_object1_code in ('OKX_OPERUNIT','OKX_INVENTORY') THEN
279 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt,' SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1) ');
280 END IF;
281
282
283
284 IF p_cplv_rec.rle_code in ('FUND_BY','K_CUSTOMER')
285 OR l_jtot_object1_code in ('OKX_CUSTACCT','OKE_CUSTACCT','OKX_PARTY')
286 THEN
287 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt, ' NVL(STATUS, ''I'') = ''A'' ');
288 END IF;
289
290 IF l_BUY_OR_SELL = 'S' then
291
292 if p_cplv_rec.rle_code = 'MARK_FOR' then
293
294 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt,
295 ' CUST_ACCOUNT_ID IN (SELECT OBJECT1_ID1 FROM OKC_K_PARTY_ROLES_B'
296 ||' WHERE CHR_ID = '||p_cplv_rec.dnz_chr_id||' '
297 ||' AND JTOT_OBJECT1_CODE = ''OKE_CUSTACCT'' AND RLE_CODE =''K_CUSTOMER'') AND '||
298 'STATUS = ''A'' ' );
299 END IF;
300
301 IF p_cplv_rec.rle_code = 'BILL_TO' then
302
303 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt,
304 ' Cust_account_id IN ( SELECT cust_account_id FROM hz_cust_acct_relate '||
305 ' WHERE related_cust_account_id in (SELECT OBJECT1_ID1 FROM OKC_K_PARTY_ROLES_B WHERE CHR_ID = '||p_cplv_rec.dnz_chr_id||''||
306 ' AND JTOT_OBJECT1_CODE = ''OKE_CUSTACCT'' AND RLE_CODE =''K_CUSTOMER'')'||
307 ' AND bill_to_flag = ''Y'' AND status = ''A'' UNION ' ||
308 ' SELECT cust_acct.cust_account_id FROM hz_parties party, hz_cust_accounts cust_acct '||
309 ' WHERE party.party_id = cust_acct.party_id ' ||
310 ' and cust_acct.cust_account_id in (SELECT OBJECT1_ID1 FROM OKC_K_PARTY_ROLES_B WHERE CHR_ID ='||p_cplv_rec.dnz_chr_id||''||
311 ' AND JTOT_OBJECT1_CODE = ''OKE_CUSTACCT'' AND RLE_CODE =''K_CUSTOMER'' ) ) AND ' ||
312 'STATUS = ''A'' ');
313
314 END IF;
315
316 if p_cplv_rec.rle_code = 'SHIP_TO' then
317
318 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt,
319 ' Cust_account_id IN ( SELECT cust_account_id FROM hz_cust_acct_relate '||
320 ' WHERE related_cust_account_id in (SELECT OBJECT1_ID1 FROM OKC_K_PARTY_ROLES_B WHERE CHR_ID ='||p_cplv_rec.dnz_chr_id||''||
321 ' AND JTOT_OBJECT1_CODE = ''OKE_CUSTACCT'' AND RLE_CODE =''K_CUSTOMER'')'||
322 ' AND ship_to_flag = ''Y'' AND status = ''A'' UNION ' ||
323 ' SELECT cust_acct.cust_account_id FROM hz_parties party, hz_cust_accounts cust_acct '||
324 ' WHERE party.party_id = cust_acct.party_id ' ||
325 ' and cust_acct.cust_account_id in (SELECT OBJECT1_ID1 FROM OKC_K_PARTY_ROLES_B WHERE CHR_ID ='||p_cplv_rec.dnz_chr_id||''||
326 ' AND JTOT_OBJECT1_CODE = ''OKE_CUSTACCT'' AND RLE_CODE =''K_CUSTOMER'' ) ) AND ' ||
327 'STATUS = ''A'' ' );
328
329 END IF;
330 ELSE --buy_or_sell = 'S'
331
332 if p_cplv_rec.rle_code = 'SHIP_FROM' then
333 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt, ' VENDOR_ID IN (SELECT OBJECT1_ID1 FROM OKC_K_PARTY_ROLES_B WHERE CHR_ID ='||p_cplv_rec.dnz_chr_id||' '
334 || ' AND JTOT_OBJECT1_CODE = ''OKX_VENDOR'' AND RLE_CODE =''CONTRACTOR'')'
335 || ' AND STATUS = ''A''');
336
337 end if;
338 END IF; --buy_or_Sell
339
340 IF p_cplv_rec.cle_id IS NOT NULL AND p_cplv_rec.cle_id<>OKC_API.G_MISS_NUM then
341 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt, ' ID1 in (select object1_id1 from okc_k_party_roles_v'
342 || ' where RLE_CODE= '||''''||p_cplv_rec.RLE_CODE||''''|| ' AND chr_id = '||p_cplv_rec.dnz_chr_id||')');
343
344 END IF;
345
346 l_sql_stmt:= ADD_WHERE_TO_SQL( l_sql_stmt, ' id1= '||p_cplv_rec.object1_id1||' and id2= '||''''||p_cplv_rec.object1_id2||'''');
347
348 l_sql_stmt := 'SELECT ''x'' FROM ' || l_sql_stmt ;
349
350
351
352
353 begin
354 EXECUTE IMMEDIATE l_sql_stmt INTO l_dummy_var ;
355 EXCEPTION
356 WHEN OTHERS THEN
357
358 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
359 p_msg_name =>G_INVALID_VALUE,
360 p_token1 =>G_COL_NAME_TOKEN,
361 p_token1_value =>'OBJECT1_ID1/ID2');
362
363 x_return_status := OKE_API.G_RET_STS_ERROR;
364
365 END;
366
367 END IF;
368
369 EXCEPTION
370
371 WHEN OTHERS THEN
372 -- store SQL error message on message stack
373 OKE_API.SET_MESSAGE(
374 p_app_name =>g_app_name,
375 p_msg_name =>G_UNEXPECTED_ERROR,
376 p_token1 =>G_SQLCODE_TOKEN,
377 p_token1_value =>SQLCODE,
378 p_token2 =>G_SQLERRM_TOKEN,
379 p_token2_value =>SQLERRM);
380 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
381
382 END validate_oke_attributes;
383
384 PROCEDURE INITIALIZE_RECORD(p_cplv_tbl out nocopy OKC_CONTRACT_PARTY_PUB.cplv_tbl_type) IS
385 BEGIN
386
387 p_cplv_tbl(1).ID := OKE_API.g_miss_num;
388 p_cplv_tbl(1).OBJECT_VERSION_NUMBER := OKE_API.g_miss_num;
389 p_cplv_tbl(1).SFWT_FLAG := OKE_API.g_miss_char;
390 p_cplv_tbl(1).CHR_ID := OKE_API.g_miss_num;
391 p_cplv_tbl(1).CLE_ID := OKE_API.g_miss_num;
392 p_cplv_tbl(1).RLE_CODE := OKE_API.g_miss_char;
393 p_cplv_tbl(1).DNZ_CHR_ID := OKE_API.g_miss_num;
394 p_cplv_tbl(1).OBJECT1_ID1 := OKE_API.g_miss_char;
395 p_cplv_tbl(1).OBJECT1_ID2 := OKE_API.g_miss_char;
396 p_cplv_tbl(1).JTOT_OBJECT1_CODE := OKE_API.g_miss_char;
397 p_cplv_tbl(1).COGNOMEN := OKE_API.g_miss_char;
398 p_cplv_tbl(1).CODE := OKE_API.g_miss_char;
399 p_cplv_tbl(1).FACILITY := OKE_API.g_miss_char;
400 p_cplv_tbl(1).MINORITY_GROUP_LOOKUP_CODE := OKE_API.g_miss_char;
401 p_cplv_tbl(1).SMALL_BUSINESS_FLAG := OKE_API.g_miss_char;
402 p_cplv_tbl(1).WOMEN_OWNED_FLAG := OKE_API.g_miss_char;
403 p_cplv_tbl(1).ALIAS := OKE_API.g_miss_char;
404 p_cplv_tbl(1).ATTRIBUTE_CATEGORY := OKE_API.g_miss_char;
405 p_cplv_tbl(1).ATTRIBUTE1 := OKE_API.g_miss_char;
406 p_cplv_tbl(1).ATTRIBUTE2 := OKE_API.g_miss_char;
407 p_cplv_tbl(1).ATTRIBUTE3 := OKE_API.g_miss_char;
408 p_cplv_tbl(1).ATTRIBUTE4 := OKE_API.g_miss_char;
409 p_cplv_tbl(1).ATTRIBUTE5 := OKE_API.g_miss_char;
410 p_cplv_tbl(1).ATTRIBUTE6 := OKE_API.g_miss_char;
411 p_cplv_tbl(1).ATTRIBUTE7 := OKE_API.g_miss_char;
412 p_cplv_tbl(1).ATTRIBUTE8 := OKE_API.g_miss_char;
413 p_cplv_tbl(1).ATTRIBUTE9 := OKE_API.g_miss_char;
414 p_cplv_tbl(1).ATTRIBUTE10 := OKE_API.g_miss_char;
415 p_cplv_tbl(1).ATTRIBUTE11 := OKE_API.g_miss_char;
416 p_cplv_tbl(1).ATTRIBUTE12 := OKE_API.g_miss_char;
417 p_cplv_tbl(1).ATTRIBUTE13 := OKE_API.g_miss_char;
418 p_cplv_tbl(1).ATTRIBUTE14 := OKE_API.g_miss_char;
419 p_cplv_tbl(1).ATTRIBUTE15 := OKE_API.g_miss_char;
420 p_cplv_tbl(1).CREATED_BY := OKE_API.g_miss_num;
421 p_cplv_tbl(1).CREATION_DATE := OKE_API.g_miss_date;
422 p_cplv_tbl(1).LAST_UPDATED_BY := OKE_API.g_miss_num;
423 p_cplv_tbl(1).LAST_UPDATE_DATE := OKE_API.g_miss_date;
424 p_cplv_tbl(1).LAST_UPDATE_LOGIN := OKE_API.g_miss_num;
425
426 END INITIALIZE_RECORD;
427
428
429 PROCEDURE SET_RECORD(operation in VARCHAR2,
430 p_cplv_rec in oke_contract_party_pub.cplv_rec_type ,
431 p_jtot_object1_code IN VARCHAR2 ,
432 p_cplv_tbl_in out nocopy OKC_CONTRACT_PARTY_PUB.cplv_tbl_type
433 ) IS
434 l_cplv_tbl_in OKC_CONTRACT_PARTY_PUB.cplv_tbl_type;
435 CURSOR party_roles_cur IS
436 SELECT object_version_number FROM okc_k_party_roles_B
437 WHERE id=p_cplv_rec.id;
438
439 l_object_vers_num NUMBER;
440 BEGIN
441 INITIALIZE_RECORD(l_cplv_tbl_in);
442 If (operation = 'INSERT') Then
443 l_cplv_tbl_in(1).DNZ_CHR_ID := p_cplv_rec.dnz_chr_id;
444
445 l_cplv_tbl_in(1).CHR_ID := p_cplv_rec.chr_id;
446 l_cplv_tbl_in(1).CLE_ID := p_cplv_rec.cle_id;
447
448
449 Else
450
451 l_cplv_tbl_in(1).ID := p_cplv_rec.ID ;
452
453 OPEN party_roles_cur ;
454 FETCH party_roles_cur INTO l_object_vers_num;
455 CLOSE party_roles_cur;
456 l_cplv_tbl_in(1).OBJECT_VERSION_NUMBER := l_object_vers_num;
457
458 End If;
459
460 l_cplv_tbl_in(1).RLE_CODE := p_cplv_rec.RLE_CODE;
461 l_cplv_tbl_in(1).OBJECT1_ID1 := p_cplv_rec.OBJECT1_ID1;
462 l_cplv_tbl_in(1).OBJECT1_ID2 := p_cplv_rec.OBJECT1_ID2;
463 l_cplv_tbl_in(1).JTOT_OBJECT1_CODE := p_JTOT_OBJECT1_CODE;
464 l_cplv_tbl_in(1).COGNOMEN := p_cplv_rec.COGNOMEN;
465 l_cplv_tbl_in(1).CODE := p_cplv_rec.CODE;
466 l_cplv_tbl_in(1).FACILITY :=p_cplv_rec.FACILITY;
467 l_cplv_tbl_in(1).MINORITY_GROUP_LOOKUP_CODE := p_cplv_rec.MINORITY_GROUP_LOOKUP_CODE;
468 l_cplv_tbl_in(1).SMALL_BUSINESS_FLAG := p_cplv_rec.SMALL_BUSINESS_FLAG;
469 l_cplv_tbl_in(1).WOMEN_OWNED_FLAG := p_cplv_rec.WOMEN_OWNED_FLAG;
470 l_cplv_tbl_in(1).ALIAS := p_cplv_rec.ALIAS;
471 l_cplv_tbl_in(1).ATTRIBUTE_CATEGORY := p_cplv_rec.ATTRIBUTE_CATEGORY;
472 l_cplv_tbl_in(1).ATTRIBUTE1 := p_cplv_rec.ATTRIBUTE1;
473 l_cplv_tbl_in(1).ATTRIBUTE2 := p_cplv_rec.ATTRIBUTE2;
474 l_cplv_tbl_in(1).ATTRIBUTE3 := p_cplv_rec.ATTRIBUTE3;
475 l_cplv_tbl_in(1).ATTRIBUTE4 := p_cplv_rec.ATTRIBUTE4;
476 l_cplv_tbl_in(1).ATTRIBUTE5 := p_cplv_rec.ATTRIBUTE5;
477 l_cplv_tbl_in(1).ATTRIBUTE6 := p_cplv_rec.ATTRIBUTE6;
478 l_cplv_tbl_in(1).ATTRIBUTE7 := p_cplv_rec.ATTRIBUTE7;
479 l_cplv_tbl_in(1).ATTRIBUTE8 := p_cplv_rec.ATTRIBUTE8;
480 l_cplv_tbl_in(1).ATTRIBUTE9 := p_cplv_rec.ATTRIBUTE9;
481 l_cplv_tbl_in(1).ATTRIBUTE10 := p_cplv_rec.ATTRIBUTE10;
482 l_cplv_tbl_in(1).ATTRIBUTE11 := p_cplv_rec.ATTRIBUTE11;
483 l_cplv_tbl_in(1).ATTRIBUTE12 := p_cplv_rec.ATTRIBUTE12;
484 l_cplv_tbl_in(1).ATTRIBUTE13 := p_cplv_rec.ATTRIBUTE13;
485 l_cplv_tbl_in(1).ATTRIBUTE14 := p_cplv_rec.ATTRIBUTE14;
486 l_cplv_tbl_in(1).ATTRIBUTE15 := p_cplv_rec.ATTRIBUTE15;
487 /* l_cplv_tbl_in(1).CREATED_BY := p_cplv_rec.CREATED_BY;
488 l_cplv_tbl_in(1).CREATION_DATE := p_cplv_rec.CREATION_DATE;
489 l_cplv_tbl_in(1).LAST_UPDATED_BY := p_cplv_rec.LAST_UPDATED_BY;
490 l_cplv_tbl_in(1).LAST_UPDATE_DATE := p_cplv_rec.LAST_UPDATE_DATE;
491 l_cplv_tbl_in(1).LAST_UPDATE_LOGIN := p_cplv_rec.LAST_UPDATE_LOGIN;*/
492 p_cplv_tbl_in:= l_cplv_tbl_in;
493 END SET_RECORD;
494
495
496 procedure create_k_party_role(p_api_version IN NUMBER,
497 p_init_msg_list IN VARCHAR2 ,
498 x_return_status OUT NOCOPY VARCHAR2,
499 x_msg_count OUT NOCOPY NUMBER,
500 x_msg_data OUT NOCOPY VARCHAR2,
501 p_cplv_rec IN cplv_rec_type,
502 x_cplv_rec OUT NOCOPY cplv_rec_type,
503 p_skip_minor_vers IN VARCHAR2 DEFAULT OKE_API.G_FALSE) is
504
505 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_K_PARTY_ROLE';
506 l_api_version CONSTANT NUMBER := 1.0;
507 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
508 l_cplv_tbl_in OKC_CONTRACT_PARTY_PUB.cplv_tbl_type;
509 l_cplv_tbl_out OKC_CONTRACT_PARTY_PUB.cplv_tbl_type;
510
511 l_JTOT_OBJECT1_CODE VARCHAR2(40);
512
513 BEGIN
514 -- call START_ACTIVITY to create savepoint, check compatibility
515 -- and initialize message list
516 l_return_status := OKE_API.START_ACTIVITY(
517 p_api_name => l_api_name,
518 p_pkg_name => g_pkg_name,
519 p_init_msg_list => p_init_msg_list,
520 l_api_version => l_api_version,
521 p_api_version => p_api_version,
522 p_api_type => g_api_type,
523 x_return_status => x_return_status);
524
525 -- check if activity started successfully
526 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
527 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
528 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
529 raise OKE_API.G_EXCEPTION_ERROR;
530 End If;
531
532
533
534
535
536 ---Other validations which are part of OKE and missing in the above procedure
537
538 validate_oke_attributes( x_return_status =>l_return_status ,
539 p_cplv_rec => p_cplv_rec,
540 l_JTOT_OBJECT1_CODE => l_JTOT_OBJECT1_CODE
541 );
542
543 --- If any errors happen abort API
544 /* IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
545 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
546 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
547 RAISE OKE_API.G_EXCEPTION_ERROR;
548 END IF;
549
550 */
551
552 SET_RECORD(operation => 'INSERT',
553 p_cplv_rec => p_cplv_rec,
554 p_jtot_object1_code => l_JTOT_OBJECT1_CODE ,
555 p_cplv_tbl_in => l_cplv_tbl_in
556 );
557
558
559 OKC_CONTRACT_PARTY_PUB.validate_k_party_role(
560 p_api_version => p_api_version,
561 p_init_msg_list => g_init_msg_list,
562 x_return_status => x_return_status,
563 x_msg_count => x_msg_count,
564 x_msg_data => x_msg_data,
565 p_cplv_tbl => l_cplv_tbl_in );
566
567 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR OR x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
568 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
569 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR OR x_return_status = OKE_API.G_RET_STS_ERROR) then
570 raise OKE_API.G_EXCEPTION_ERROR;
571 End If;
572
573 /*skuchima bugs 14380256 */
574 IF (FND_API.G_TRUE = p_skip_minor_vers ) THEN
575 okc_cvm_pvt.defer_minor_version_update('T');
576 end if;
577
578 OKC_CONTRACT_PARTY_PUB.CReATE_K_PARTY_ROLE (
579 p_api_version => p_api_version,
580 p_init_msg_list => g_init_msg_list,
581 x_return_status => l_return_status,
582 x_msg_count => x_msg_count,
583 x_msg_data => x_msg_data,
584 p_cplv_tbl => l_cplv_tbl_in,
585 x_cplv_tbl => l_cplv_tbl_out
586 );
587
588 okc_cvm_pvt.defer_minor_version_update('F');
589
590 x_cplv_rec:=l_cplv_tbl_out(1);
591
592 --- If any errors happen abort API
593 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
594 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
595 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
596 RAISE OKE_API.G_EXCEPTION_ERROR;
597 END IF;
598
599
600 x_return_status:=l_return_status;
601 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
602 x_msg_data => x_msg_data);
603 EXCEPTION
604 when OKE_API.G_EXCEPTION_ERROR then
605 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
606 p_api_name => l_api_name,
607 p_pkg_name => g_pkg_name,
608 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
609 x_msg_count => x_msg_count,
610 x_msg_data => x_msg_data,
611 p_api_type => g_api_type);
612
613 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
614 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
615 p_api_name => l_api_name,
616 p_pkg_name => g_pkg_name,
617 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
618 x_msg_count => x_msg_count,
619 x_msg_data => x_msg_data,
620 p_api_type => g_api_type);
621
622 when OTHERS then
623 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
624 p_api_name => l_api_name,
625 p_pkg_name => g_pkg_name,
626 p_exc_name => 'OTHERS',
627 x_msg_count => x_msg_count,
628 x_msg_data => x_msg_data,
629 p_api_type => g_api_type);
630
631 END CREATE_K_PARTY_ROLE ;
632
633 procedure update_k_party_role(p_api_version IN NUMBER,
634 p_init_msg_list IN VARCHAR2 ,
635 x_return_status OUT NOCOPY VARCHAR2,
636 x_msg_count OUT NOCOPY NUMBER,
637 x_msg_data OUT NOCOPY VARCHAR2,
638 p_cplv_tbl IN cplv_tbl_type,
639 x_cplv_tbl OUT NOCOPY cplv_tbl_type,
640 p_skip_minor_vers IN VARCHAR2 DEFAULT OKE_API.G_FALSE)
641 is
642
643 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_PARTY_ROLE';
644 l_api_version CONSTANT NUMBER := 1.0;
645 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
646 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
647 i NUMBER:=0;
648 BEGIN
649 -- call START_ACTIVITY to create savepoint, check compatibility
650 -- and initialize message list
651 l_return_status := OKE_API.START_ACTIVITY(
652 p_api_name => l_api_name,
653 p_pkg_name => g_pkg_name,
654 p_init_msg_list => p_init_msg_list,
655 l_api_version => l_api_version,
656 p_api_version => p_api_version,
657 p_api_type => g_api_type,
658 x_return_status => x_return_status);
659
660 -- check if activity started successfully
661 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
662 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
663 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
664 raise OKE_API.G_EXCEPTION_ERROR;
665 End If;
666
667 If (p_cplv_tbl.COUNT > 0) Then
668 i := p_cplv_tbl.FIRST;
669 LOOP
670 update_k_party_role(p_api_version=>p_api_version,
671 p_init_msg_list=>OKC_API.G_FALSE,
672 x_return_status=>l_return_status,
673 x_msg_count=>x_msg_count,
674 x_msg_data=>x_msg_data,
675 p_cplv_rec=>p_cplv_tbl(i),
676 x_cplv_rec=>x_cplv_tbl(i),
677 p_skip_minor_vers=>p_skip_minor_vers);
678 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
679 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
680 l_overall_status := x_return_status;
681 End If;
682 End If;
683
684 EXIT WHEN (i = p_cplv_tbl.LAST);
685 i := p_cplv_tbl.NEXT(i);
686 END LOOP;
687
688 -- return overall status
689 x_return_status := l_overall_status;
690 End If;
691
692 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
693 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
694 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
695 raise OKE_API.G_EXCEPTION_ERROR;
696 End If;
697
698 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
699 x_msg_data => x_msg_data);
700 EXCEPTION
701 when OKE_API.G_EXCEPTION_ERROR then
702 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
703 p_api_name => l_api_name,
704 p_pkg_name => g_pkg_name,
705 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
706 x_msg_count => x_msg_count,
707 x_msg_data => x_msg_data,
708 p_api_type => g_api_type);
709
710 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
711 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
712 p_api_name => l_api_name,
713 p_pkg_name => g_pkg_name,
714 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
715 x_msg_count => x_msg_count,
716 x_msg_data => x_msg_data,
717 p_api_type => g_api_type);
718
719 when OTHERS then
720 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
721 p_api_name => l_api_name,
722 p_pkg_name => g_pkg_name,
723 p_exc_name => 'OTHERS',
724 x_msg_count => x_msg_count,
725 x_msg_data => x_msg_data,
726 p_api_type => g_api_type);
727
728 END UPDATE_K_PARTY_ROLE;
729
730 procedure update_k_party_role(p_api_version IN NUMBER,
731 p_init_msg_list IN VARCHAR2 ,
732 x_return_status OUT NOCOPY VARCHAR2,
733 x_msg_count OUT NOCOPY NUMBER,
734 x_msg_data OUT NOCOPY VARCHAR2,
735 p_cplv_rec IN cplv_rec_type,
736 x_cplv_rec OUT NOCOPY cplv_rec_type,
737 p_skip_minor_vers IN VARCHAR2 DEFAULT OKE_API.G_FALSE)
738 is
739
740 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_PARTY_ROLE';
741 l_api_version CONSTANT NUMBER := 1.0;
742 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
743 l_cplv_tbl_in OKC_CONTRACT_PARTY_PUB.cplv_tbl_type;
744 l_cplv_tbl_out OKC_CONTRACT_PARTY_PUB.cplv_tbl_type;
745 l_JTOT_OBJECT1_CODE VARCHAR2(40);
746 l_cplv_rec cplv_rec_type;
747
748 ---------------------------------------------------------------------------
749 -- FUNCTION get_rec for: OKC_K_PARTY_ROLES_V
750 ---------------------------------------------------------------------------
751 FUNCTION get_rec (
752 p_cplv_rec IN cplv_rec_type,
753 x_no_data_found OUT NOCOPY BOOLEAN
754 ) RETURN cplv_rec_type IS
755 CURSOR okc_cplv_pk_csr (p_id IN NUMBER) IS
756 SELECT
757 ID,
758 OBJECT_VERSION_NUMBER,
759 SFWT_FLAG,
760 CPL_ID,CHR_ID,
761 CLE_ID,
762 RLE_CODE,
763 DNZ_CHR_ID,
764 OBJECT1_ID1,
765 OBJECT1_ID2,
766 JTOT_OBJECT1_CODE,
767 COGNOMEN,
768 CODE,
769 FACILITY,
770 MINORITY_GROUP_LOOKUP_CODE,
771 SMALL_BUSINESS_FLAG,
772 WOMEN_OWNED_FLAG,
773 ALIAS,
774 PRIMARY_YN,
775 CUST_ACCT_ID,
776 BILL_TO_SITE_USE_ID,
777 ATTRIBUTE_CATEGORY,
778 ATTRIBUTE1,
779 ATTRIBUTE2,
780 ATTRIBUTE3,
781 ATTRIBUTE4,
782 ATTRIBUTE5,
783 ATTRIBUTE6,
784 ATTRIBUTE7,
785 ATTRIBUTE8,
786 ATTRIBUTE9,
787 ATTRIBUTE10,
788 ATTRIBUTE11,
789 ATTRIBUTE12,
790 ATTRIBUTE13,
791 ATTRIBUTE14,
792 ATTRIBUTE15,
793 CREATED_BY,
794 CREATION_DATE,
795 LAST_UPDATED_BY,
796 LAST_UPDATE_DATE,
797 LAST_UPDATE_LOGIN
798 FROM Okc_K_Party_Roles_V
799 WHERE okc_k_party_roles_v.id = p_id;
800 l_okc_cplv_pk okc_cplv_pk_csr%ROWTYPE;
801 l_cplv_rec cplv_rec_type;
802 BEGIN
803
804
805 x_no_data_found := TRUE;
806 -- Get current database values
807 OPEN okc_cplv_pk_csr (p_cplv_rec.id);
808 FETCH okc_cplv_pk_csr INTO
809 l_cplv_rec.ID,
810 l_cplv_rec.OBJECT_VERSION_NUMBER,
811 l_cplv_rec.SFWT_FLAG,
812 l_cplv_rec.CPL_ID,l_cplv_rec.CHR_ID,
813 l_cplv_rec.CLE_ID,
814 l_cplv_rec.RLE_CODE,
815 l_cplv_rec.DNZ_CHR_ID,
816 l_cplv_rec.OBJECT1_ID1,
817 l_cplv_rec.OBJECT1_ID2,
818 l_cplv_rec.JTOT_OBJECT1_CODE,
819 l_cplv_rec.COGNOMEN,
820 l_cplv_rec.CODE,
821 l_cplv_rec.FACILITY,
822 l_cplv_rec.MINORITY_GROUP_LOOKUP_CODE,
823 l_cplv_rec.SMALL_BUSINESS_FLAG,
824 l_cplv_rec.WOMEN_OWNED_FLAG,
825 l_cplv_rec.ALIAS,
826 l_cplv_rec.PRIMARY_YN,
827 l_cplv_rec.CUST_ACCT_ID,
828 l_cplv_rec.BILL_TO_SITE_USE_ID,
829 l_cplv_rec.ATTRIBUTE_CATEGORY,
830 l_cplv_rec.ATTRIBUTE1,
831 l_cplv_rec.ATTRIBUTE2,
832 l_cplv_rec.ATTRIBUTE3,
833 l_cplv_rec.ATTRIBUTE4,
834 l_cplv_rec.ATTRIBUTE5,
835 l_cplv_rec.ATTRIBUTE6,
836 l_cplv_rec.ATTRIBUTE7,
837 l_cplv_rec.ATTRIBUTE8,
838 l_cplv_rec.ATTRIBUTE9,
839 l_cplv_rec.ATTRIBUTE10,
840 l_cplv_rec.ATTRIBUTE11,
841 l_cplv_rec.ATTRIBUTE12,
842 l_cplv_rec.ATTRIBUTE13,
843 l_cplv_rec.ATTRIBUTE14,
844 l_cplv_rec.ATTRIBUTE15,
845 l_cplv_rec.CREATED_BY,
846 l_cplv_rec.CREATION_DATE,
847 l_cplv_rec.LAST_UPDATED_BY,
848 l_cplv_rec.LAST_UPDATE_DATE,
849 l_cplv_rec.LAST_UPDATE_LOGIN;
850 x_no_data_found := okc_cplv_pk_csr%NOTFOUND;
851 CLOSE okc_cplv_pk_csr;
852
853
854 RETURN(l_cplv_rec);
855
856 END get_rec;
857
858 FUNCTION get_rec (
859 p_cplv_rec IN cplv_rec_type
860 ) RETURN cplv_rec_type IS
861 l_row_notfound BOOLEAN := TRUE;
862 BEGIN
863
864 RETURN(get_rec(p_cplv_rec, l_row_notfound));
865
866 END get_rec;
867
868 -- FUNCTION populate_new_record --
869 ----------------------------------
870 FUNCTION populate_new_record (
871 p_cplv_rec IN cplv_rec_type,
872 x_cplv_rec OUT NOCOPY cplv_rec_type
873 ) RETURN VARCHAR2 IS
874 l_cplv_rec cplv_rec_type;
875 l_row_notfound BOOLEAN := TRUE;
876 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
877 BEGIN
878
879
880 x_cplv_rec := p_cplv_rec;
881 -- Get current database values
882 l_cplv_rec := get_rec(p_cplv_rec, l_row_notfound);
883 IF (l_row_notfound) THEN
884
885 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
886 p_msg_name =>G_REQUIRED_VALUE,
887 p_token1 =>G_COL_NAME_TOKEN,
888 p_token1_value =>'ID');
889
890 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
891 END IF;
892 IF (x_cplv_rec.id = OKC_API.G_MISS_NUM)
893 THEN
894 x_cplv_rec.id := l_cplv_rec.id;
895 END IF;
896 IF (x_cplv_rec.object_version_number = OKC_API.G_MISS_NUM)
897 THEN
898 x_cplv_rec.object_version_number := l_cplv_rec.object_version_number;
899 END IF;
900 IF (x_cplv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
901 THEN
902 x_cplv_rec.sfwt_flag := l_cplv_rec.sfwt_flag;
903 END IF;
904 IF (x_cplv_rec.cpl_id = OKC_API.G_MISS_NUM)
905 THEN
906 x_cplv_rec.cpl_id := l_cplv_rec.cpl_id;
907 END IF;
908 IF (x_cplv_rec.chr_id = OKC_API.G_MISS_NUM)
909 THEN
910 x_cplv_rec.chr_id := l_cplv_rec.chr_id;
911 END IF;
912 IF (x_cplv_rec.cle_id = OKC_API.G_MISS_NUM)
913 THEN
914 x_cplv_rec.cle_id := l_cplv_rec.cle_id;
915 END IF;
916 IF (x_cplv_rec.rle_code = OKC_API.G_MISS_CHAR)
917 THEN
918 x_cplv_rec.rle_code := l_cplv_rec.rle_code;
919 END IF;
920 IF (x_cplv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
921 THEN
922 x_cplv_rec.dnz_chr_id := l_cplv_rec.dnz_chr_id;
923 END IF;
924 IF (x_cplv_rec.object1_id1 = OKC_API.G_MISS_CHAR)
925 THEN
926 x_cplv_rec.object1_id1 := l_cplv_rec.object1_id1;
927 END IF;
928 IF (x_cplv_rec.object1_id2 = OKC_API.G_MISS_CHAR)
929 THEN
930 x_cplv_rec.object1_id2 := l_cplv_rec.object1_id2;
931 END IF;
932 IF (x_cplv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR)
933 THEN
934 x_cplv_rec.JTOT_OBJECT1_CODE := l_cplv_rec.JTOT_OBJECT1_CODE;
935 END IF;
936 IF (x_cplv_rec.cognomen = OKC_API.G_MISS_CHAR)
937 THEN
938 x_cplv_rec.cognomen := l_cplv_rec.cognomen;
939 END IF;
940 IF (x_cplv_rec.code = OKC_API.G_MISS_CHAR)
941 THEN
942 x_cplv_rec.code := l_cplv_rec.code;
943 END IF;
944 IF (x_cplv_rec.facility = OKC_API.G_MISS_CHAR)
945 THEN
946 x_cplv_rec.facility := l_cplv_rec.facility;
947 END IF;
948 IF (x_cplv_rec.minority_group_lookup_code = OKC_API.G_MISS_CHAR)
949 THEN
950 x_cplv_rec.minority_group_lookup_code := l_cplv_rec.minority_group_lookup_code;
951 END IF;
952 IF (x_cplv_rec.small_business_flag = OKC_API.G_MISS_CHAR)
953 THEN
954 x_cplv_rec.small_business_flag := l_cplv_rec.small_business_flag;
955 END IF;
956 IF (x_cplv_rec.women_owned_flag = OKC_API.G_MISS_CHAR)
957 THEN
958 x_cplv_rec.women_owned_flag := l_cplv_rec.women_owned_flag;
959 END IF;
960 IF (x_cplv_rec.alias = OKC_API.G_MISS_CHAR)
961 THEN
962 x_cplv_rec.alias := l_cplv_rec.alias;
963 END IF;
964 IF (x_cplv_rec.primary_yn = OKC_API.G_MISS_CHAR)
965 THEN
966 x_cplv_rec.primary_yn := l_cplv_rec.primary_yn;
967 END IF;
968 IF (x_cplv_rec.cust_acct_id = OKC_API.G_MISS_NUM)
969 THEN
970 x_cplv_rec.cust_acct_id := l_cplv_rec.cust_acct_id;
971 END IF;
972 IF (x_cplv_rec.bill_to_site_use_id = OKC_API.G_MISS_NUM)
973 THEN
974 x_cplv_rec.bill_to_site_use_id := l_cplv_rec.bill_to_site_use_id;
975 END IF;
976 IF (x_cplv_rec.attribute_category = OKC_API.G_MISS_CHAR)
977 THEN
978 x_cplv_rec.attribute_category := l_cplv_rec.attribute_category;
979 END IF;
980 IF (x_cplv_rec.attribute1 = OKC_API.G_MISS_CHAR)
981 THEN
982 x_cplv_rec.attribute1 := l_cplv_rec.attribute1;
983 END IF;
984 IF (x_cplv_rec.attribute2 = OKC_API.G_MISS_CHAR)
985 THEN
986 x_cplv_rec.attribute2 := l_cplv_rec.attribute2;
987 END IF;
988 IF (x_cplv_rec.attribute3 = OKC_API.G_MISS_CHAR)
989 THEN
990 x_cplv_rec.attribute3 := l_cplv_rec.attribute3;
991 END IF;
992 IF (x_cplv_rec.attribute4 = OKC_API.G_MISS_CHAR)
993 THEN
994 x_cplv_rec.attribute4 := l_cplv_rec.attribute4;
995 END IF;
996 IF (x_cplv_rec.attribute5 = OKC_API.G_MISS_CHAR)
997 THEN
998 x_cplv_rec.attribute5 := l_cplv_rec.attribute5;
999 END IF;
1000 IF (x_cplv_rec.attribute6 = OKC_API.G_MISS_CHAR)
1001 THEN
1002 x_cplv_rec.attribute6 := l_cplv_rec.attribute6;
1003 END IF;
1004 IF (x_cplv_rec.attribute7 = OKC_API.G_MISS_CHAR)
1005 THEN
1006 x_cplv_rec.attribute7 := l_cplv_rec.attribute7;
1007 END IF;
1008 IF (x_cplv_rec.attribute8 = OKC_API.G_MISS_CHAR)
1009 THEN
1010 x_cplv_rec.attribute8 := l_cplv_rec.attribute8;
1011 END IF;
1012 IF (x_cplv_rec.attribute9 = OKC_API.G_MISS_CHAR)
1013 THEN
1014 x_cplv_rec.attribute9 := l_cplv_rec.attribute9;
1015 END IF;
1016 IF (x_cplv_rec.attribute10 = OKC_API.G_MISS_CHAR)
1017 THEN
1018 x_cplv_rec.attribute10 := l_cplv_rec.attribute10;
1019 END IF;
1020 IF (x_cplv_rec.attribute11 = OKC_API.G_MISS_CHAR)
1021 THEN
1022 x_cplv_rec.attribute11 := l_cplv_rec.attribute11;
1023 END IF;
1024 IF (x_cplv_rec.attribute12 = OKC_API.G_MISS_CHAR)
1025 THEN
1026 x_cplv_rec.attribute12 := l_cplv_rec.attribute12;
1027 END IF;
1028 IF (x_cplv_rec.attribute13 = OKC_API.G_MISS_CHAR)
1029 THEN
1030 x_cplv_rec.attribute13 := l_cplv_rec.attribute13;
1031 END IF;
1032 IF (x_cplv_rec.attribute14 = OKC_API.G_MISS_CHAR)
1033 THEN
1034 x_cplv_rec.attribute14 := l_cplv_rec.attribute14;
1035 END IF;
1036 IF (x_cplv_rec.attribute15 = OKC_API.G_MISS_CHAR)
1037 THEN
1038 x_cplv_rec.attribute15 := l_cplv_rec.attribute15;
1039 END IF;
1040 IF (x_cplv_rec.created_by = OKC_API.G_MISS_NUM)
1041 THEN
1042 x_cplv_rec.created_by := l_cplv_rec.created_by;
1043 END IF;
1044 IF (x_cplv_rec.creation_date = OKC_API.G_MISS_DATE)
1045 THEN
1046 x_cplv_rec.creation_date := l_cplv_rec.creation_date;
1047 END IF;
1048 IF (x_cplv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1049 THEN
1050 x_cplv_rec.last_updated_by := l_cplv_rec.last_updated_by;
1051 END IF;
1052 IF (x_cplv_rec.last_update_date = OKC_API.G_MISS_DATE)
1053 THEN
1054 x_cplv_rec.last_update_date := l_cplv_rec.last_update_date;
1055 END IF;
1056 IF (x_cplv_rec.last_update_login = OKC_API.G_MISS_NUM)
1057 THEN
1058 x_cplv_rec.last_update_login := l_cplv_rec.last_update_login;
1059 END IF;
1060
1061
1062
1063 RETURN(l_return_status);
1064
1065 END populate_new_record;
1066
1067
1068 BEGIN
1069 -- call START_ACTIVITY to create savepoint, check compatibility
1070 -- and initialize message list
1071 l_return_status := OKE_API.START_ACTIVITY(
1072 p_api_name => l_api_name,
1073 p_pkg_name => g_pkg_name,
1074 p_init_msg_list => p_init_msg_list,
1075 l_api_version => l_api_version,
1076 p_api_version => p_api_version,
1077 p_api_type => g_api_type,
1078 x_return_status => x_return_status);
1079
1080 -- check if activity started successfully
1081 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1082 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1083 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1084 raise OKE_API.G_EXCEPTION_ERROR;
1085 End If;
1086
1087 l_return_status := populate_new_record(p_cplv_rec, l_cplv_rec);
1088 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1089 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1090 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1091 RAISE OKE_API.G_EXCEPTION_ERROR;
1092 END IF;
1093
1094
1095
1096 ---Other validations which are part of OKE and missing in the above procedure
1097
1098 validate_oke_attributes( x_return_status =>l_return_status ,
1099 p_cplv_rec => l_cplv_rec,
1100 l_JTOT_OBJECT1_CODE => l_JTOT_OBJECT1_CODE
1101 );
1102
1103 --- If any errors happen abort API
1104 /* IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1105 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1106 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1107 RAISE OKE_API.G_EXCEPTION_ERROR;
1108 END IF;
1109 */
1110
1111 SET_RECORD(operation => 'UPDATE',
1112 p_cplv_rec => l_cplv_rec,
1113 p_jtot_object1_code => l_JTOT_OBJECT1_CODE ,
1114 p_cplv_tbl_in => l_cplv_tbl_in
1115 );
1116
1117
1118 OKC_CONTRACT_PARTY_PUB.validate_k_party_role(
1119 p_api_version => p_api_version,
1120 p_init_msg_list => g_init_msg_list,
1121 x_return_status => x_return_status,
1122 x_msg_count => x_msg_count,
1123 x_msg_data => x_msg_data,
1124 p_cplv_tbl => l_cplv_tbl_in );
1125
1126 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR OR x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1127 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1128 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR OR x_return_status = OKE_API.G_RET_STS_ERROR) then
1129 raise OKE_API.G_EXCEPTION_ERROR;
1130 End If;
1131
1132
1133 /*skuchima bugs 14380256 */
1134 IF (FND_API.G_TRUE = p_skip_minor_vers ) THEN
1135 okc_cvm_pvt.defer_minor_version_update('T');
1136 end if;
1137
1138 OKC_CONTRACT_PARTY_PUB.UPDATE_K_PARTY_ROLE (
1139 p_api_version => p_api_version,
1140 p_init_msg_list => g_init_msg_list,
1141 x_return_status => l_return_status,
1142 x_msg_count => x_msg_count,
1143 x_msg_data => x_msg_data,
1144 p_cplv_tbl => l_cplv_tbl_in,
1145 x_cplv_tbl => l_cplv_tbl_out
1146 );
1147
1148 okc_cvm_pvt.defer_minor_version_update('F');
1149
1150 x_cplv_rec:= l_cplv_tbl_out(1);
1151 --- If any errors happen abort API
1152 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1153 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1154 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1155 RAISE OKE_API.G_EXCEPTION_ERROR;
1156 END IF;
1157
1158
1159 x_return_status:=l_return_status;
1160 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1161 x_msg_data => x_msg_data);
1162 EXCEPTION
1163 when OKE_API.G_EXCEPTION_ERROR then
1164 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1165 p_api_name => l_api_name,
1166 p_pkg_name => g_pkg_name,
1167 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1168 x_msg_count => x_msg_count,
1169 x_msg_data => x_msg_data,
1170 p_api_type => g_api_type);
1171
1172 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1173 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1174 p_api_name => l_api_name,
1175 p_pkg_name => g_pkg_name,
1176 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1177 x_msg_count => x_msg_count,
1178 x_msg_data => x_msg_data,
1179 p_api_type => g_api_type);
1180
1181 when OTHERS then
1182 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1183 p_api_name => l_api_name,
1184 p_pkg_name => g_pkg_name,
1185 p_exc_name => 'OTHERS',
1186 x_msg_count => x_msg_count,
1187 x_msg_data => x_msg_data,
1188 p_api_type => g_api_type);
1189
1190 END UPDATE_K_PARTY_ROLE ;
1191
1192 procedure create_k_contact(p_api_version IN NUMBER,
1193 p_init_msg_list IN VARCHAR2 ,
1194 x_return_status OUT NOCOPY VARCHAR2,
1195 x_msg_count OUT NOCOPY NUMBER,
1196 x_msg_data OUT NOCOPY VARCHAR2,
1197 p_ctcv_tbl IN ctcv_tbl_type,
1198 x_ctcv_tbl OUT NOCOPY ctcv_tbl_type,
1199 p_skip_minor_vers IN VARCHAR2 DEFAULT OKE_API.G_FALSE)
1200 is
1201
1202 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_K_CONTACT';
1203 l_api_version CONSTANT NUMBER := 1.0;
1204 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1205 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1206 i NUMBER:=0;
1207 BEGIN
1208 -- call START_ACTIVITY to create savepoint, check compatibility
1209 -- and initialize message list
1210 l_return_status := OKE_API.START_ACTIVITY(
1211 p_api_name => l_api_name,
1212 p_pkg_name => g_pkg_name,
1213 p_init_msg_list => p_init_msg_list,
1214 l_api_version => l_api_version,
1215 p_api_version => p_api_version,
1216 p_api_type => g_api_type,
1217 x_return_status => x_return_status);
1218
1219 -- check if activity started successfully
1220 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1221 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1222 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1223 raise OKE_API.G_EXCEPTION_ERROR;
1224 End If;
1225
1226 If (p_ctcv_tbl.COUNT > 0) Then
1227 i := p_ctcv_tbl.FIRST;
1228 LOOP
1229 create_k_contact(p_api_version=>p_api_version,
1230 p_init_msg_list=>OKC_API.G_FALSE,
1231 x_return_status=>l_return_status,
1232 x_msg_count=>x_msg_count,
1233 x_msg_data=>x_msg_data,
1234 p_ctcv_rec=>p_ctcv_tbl(i),
1235 x_ctcv_rec=>x_ctcv_tbl(i),
1236 p_skip_minor_vers=>p_skip_minor_vers);
1237
1238 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1239 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1240 l_overall_status := x_return_status;
1241 End If;
1242 End If;
1243
1244 EXIT WHEN (i = p_ctcv_tbl.LAST);
1245 i := p_ctcv_tbl.NEXT(i);
1246 END LOOP;
1247
1248 -- return overall status
1249 x_return_status := l_overall_status;
1250 End If;
1251
1252 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1253 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1254 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1255 raise OKE_API.G_EXCEPTION_ERROR;
1256 End If;
1257
1258 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1259 x_msg_data => x_msg_data);
1260 EXCEPTION
1261 when OKE_API.G_EXCEPTION_ERROR then
1262 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1263 p_api_name => l_api_name,
1264 p_pkg_name => g_pkg_name,
1265 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1266 x_msg_count => x_msg_count,
1267 x_msg_data => x_msg_data,
1268 p_api_type => g_api_type);
1269
1270 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1271 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1272 p_api_name => l_api_name,
1273 p_pkg_name => g_pkg_name,
1274 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1275 x_msg_count => x_msg_count,
1276 x_msg_data => x_msg_data,
1277 p_api_type => g_api_type);
1278
1279 when OTHERS then
1280 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1281 p_api_name => l_api_name,
1282 p_pkg_name => g_pkg_name,
1283 p_exc_name => 'OTHERS',
1284 x_msg_count => x_msg_count,
1285 x_msg_data => x_msg_data,
1286 p_api_type => g_api_type);
1287
1288 END CREATE_K_CONTACT ;
1289
1290 FUNCTION COLUMN_EXISTS
1291 ( p_object_code VARCHAR2
1292 , p_column_name VARCHAR2
1293 ) RETURN BOOLEAN IS
1294 l_view_name varchar2(200);
1295 l_found NUMBER;
1296 i NUMBER;
1297
1298 Cursor l_jtfv_csr Is
1299 SELECT from_table
1300 FROM jtf_objects_vl
1301 WHERE object_code = p_object_code
1302 AND sysdate between nvl(start_date_active , sysdate-1)
1303 and nvl(end_date_active , sysdate+1);
1304
1305 Cursor l_jtf_source_csr Is
1306 SELECT 1 FROM USER_TAB_COLUMNS
1307 WHERE table_name = l_view_name
1308 AND column_name = p_column_name;
1309
1310 BEGIN
1311 open l_jtfv_csr;
1312 fetch l_jtfv_csr into l_view_name;
1313 close l_jtfv_csr;
1314
1315 -- Trim any space and character after that
1316 i := INSTR(l_view_name,' ');
1317 If (i > 0) Then
1318 l_view_name := substr(l_view_name,1,i - 1);
1319 End If;
1320
1321 open l_jtf_source_csr;
1322 fetch l_jtf_source_csr into l_found;
1323 close l_jtf_source_csr;
1324 If (l_found = 1) Then
1325 return TRUE;
1326 Else
1327 return FALSE;
1328 End If;
1329 EXCEPTION
1330 when NO_DATA_FOUND Then
1331 If (l_jtfv_csr%ISOPEN) Then
1332 close l_jtfv_csr;
1333 End If;
1334 If (l_jtf_source_csr%ISOPEN) Then
1335 close l_jtf_source_csr;
1336 End If;
1337 return FALSE;
1338
1339 when OTHERS then
1340 If (l_jtfv_csr%ISOPEN) Then
1341 close l_jtfv_csr;
1342 End If;
1343 If (l_jtf_source_csr%ISOPEN) Then
1344 close l_jtf_source_csr;
1345 End If;
1346 return FALSE;
1347 END;
1348
1349
1350 PROCEDURE validate_contact_attributes ( x_return_status OUT NOCOPY VARCHAR2,
1351 l_JTOT_OBJECT1_CODE OUT NOCOPY VARCHAR2 ,
1352 p_ctcv_rec in oke_contract_party_pub.ctcv_rec_type
1353 )IS
1354
1355 CURSOR get_party_info IS
1356 SELECT rle_code,jtot_object1_code,object1_id1,object1_id2
1357 FROM okc_k_party_roles_b
1358 WHERE id= p_ctcv_rec.cpl_id ;
1359
1360 CURSOR get_header_info IS
1361 SELECT buy_or_sell FROM
1362 okc_k_headers_all_b WHERE
1363 id= p_ctcv_rec.dnz_chr_id;
1364
1365 CURSOR get_jtot_object_code(p_rle_code IN VARCHAR2,p_BUY_OR_SELL IN varchar2) is
1366 select jtot_object_code ,CONSTRAINED_YN
1367 from okc_contact_sources_v b
1368 where b.cro_code =p_ctcv_rec.cro_code
1369 and b.rle_code = p_rle_code
1370 and b.buy_or_sell = p_BUY_OR_SELL ;
1371
1372 l_sql_stmt VARCHAR2(2500);
1373 l_dummy_var VARCHAR2(1) := '?';
1374 party_rle_code OKC_K_PARTY_ROLEs_B.RLE_CODE%TYPE;
1375 party_jtot_object1_code OKC_K_PARTY_ROLEs_B.JTOT_OBJECT1_CODE%TYPE;
1376 party_object1_id1 OKC_K_PARTY_ROLEs_B.object1_id1%TYPE;
1377 party_object1_id2 OKC_K_PARTY_ROLEs_B.object1_id2%TYPE;
1378
1379 l_party_id VARCHAR2(40);
1380
1381 l_buy_or_sell VARCHAR2(5);
1382 l_constrained_yn VARCHAR2(1) := 'N';
1383
1384
1385 BEGIN
1386 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1387
1388
1389
1390 IF ( p_ctcv_rec.cpl_id IS NULL OR p_ctcv_rec.cpl_id=OKC_API.G_MISS_NUM) THEN
1391 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1392 p_msg_name =>G_REQUIRED_VALUE,
1393 p_token1 =>G_COL_NAME_TOKEN,
1394 p_token1_value =>'cpl_id');
1395 x_return_status := OKE_API.G_RET_STS_ERROR;
1396 END IF;
1397
1398
1399
1400 open get_party_info ;
1401 fetch get_party_info into party_rle_code,party_jtot_object1_code,party_object1_id1,party_object1_id2;
1402 close get_party_info;
1403
1404
1405 open get_header_info ;
1406 fetch get_header_info into l_buy_or_sell;
1407 close get_header_info;
1408
1409
1410 open get_jtot_object_code (party_rle_code,l_buy_or_sell) ;
1411 fetch get_jtot_object_code into l_JTOT_OBJECT1_CODE ,l_constrained_yn;
1412 close get_jtot_object_code;
1413
1414
1415
1416 IF ( l_JTOT_OBJECT1_CODE IS NULL) THEN
1417 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1418 p_msg_name =>G_INVALID_VALUE,
1419 p_token1 =>G_COL_NAME_TOKEN,
1420 p_token1_value =>'CRO_CODE');
1421 x_return_status := OKE_API.G_RET_STS_ERROR;
1422 END IF;
1423
1424
1425 IF l_jtot_object1_code is not null THEN
1426
1427 l_sql_stmt := OKC_UTIL.GET_SQL_FROM_JTFV(l_jtot_object1_code);
1428
1429 If (l_sql_stmt is null) Then
1430 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1431 p_msg_name =>'No view found in JTF') ;
1432 x_return_status := OKE_API.G_RET_STS_ERROR;
1433 End If;
1434
1435 IF l_constrained_yn = 'Y' then
1436 IF ( party_jtot_object1_code IN ('OKE_BILLTO','OKE_CUST_KADMIN','OKE_MARKFOR','OKE_SHIPTO','OKE_CUSTACCT','OKE_VENDSITE','OKX_CUSTACCT'))
1437 THEN
1438 l_party_id:=OKE_AUTHORING_UTILS.retrieve_party_id(PARTY_JTOT_OBJECT1_CODE
1439 ,PARTY_OBJECT1_ID1
1440 ,PARTY_OBJECT1_ID2);
1441
1442 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt, 'PARTY_ID = '||l_party_id );
1443 ELSE
1444
1445 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt, 'PARTY_ID = '||PARTY_OBJECT1_ID1 ||' AND PARTY_ID2 = '||PARTY_OBJECT1_ID2||'');
1446 END IF;
1447
1448 END IF;
1449
1450 If (Column_Exists(l_jtot_object1_code,'END_DATE_ACTIVE')) Then
1451 -- check for END_DATE_ACTIVE
1452
1453 l_sql_stmt := ADD_WHERE_TO_SQL( l_sql_stmt, ' NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE ');
1454
1455 End If; --end if for check END_DATE_ACTIVE columnif
1456
1457 l_sql_stmt:= ADD_WHERE_TO_SQL( l_sql_stmt, ' id1= '||p_ctcv_rec.object1_id1||' and id2= '||''''||p_ctcv_rec.object1_id2||'''');
1458
1459 l_sql_stmt := 'SELECT ''x'' FROM ' || l_sql_stmt ;
1460
1461
1462
1463 begin
1464 EXECUTE IMMEDIATE l_sql_stmt INTO l_dummy_var ;
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1468 p_msg_name =>'Invalid object1_id1 and object1_id2 ') ;
1469 x_return_status := OKE_API.G_RET_STS_ERROR;
1470
1471 END;
1472
1473
1474
1475 END IF;
1476
1477
1478
1479
1480
1481 EXCEPTION
1482
1483 WHEN OTHERS THEN
1484 -- store SQL error message on message stack
1485 OKE_API.SET_MESSAGE(
1486 p_app_name =>g_app_name,
1487 p_msg_name =>G_UNEXPECTED_ERROR,
1488 p_token1 =>G_SQLCODE_TOKEN,
1489 p_token1_value =>SQLCODE,
1490 p_token2 =>G_SQLERRM_TOKEN,
1491 p_token2_value =>SQLERRM);
1492 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1493
1494 END validate_contact_attributes;
1495
1496
1497 PROCEDURE INITIALIZE_RECORD_contact(p_ctcv_tbl OUT NOCOPY okc_contract_party_pub.ctcv_tbl_type) IS
1498 BEGIN
1499
1500 p_ctcv_tbl(1).ID := OKE_API.g_miss_num;
1501 p_ctcv_tbl(1).OBJECT_VERSION_NUMBER := OKE_API.g_miss_num;
1502 p_ctcv_tbl(1).CPL_ID := OKE_API.g_miss_num;
1503 p_ctcv_tbl(1).CRO_CODE := OKE_API.g_miss_char;
1504 p_ctcv_tbl(1).DNZ_CHR_ID := OKE_API.g_miss_num;
1505 p_ctcv_tbl(1).CONTACT_SEQUENCE := OKE_API.g_miss_num;
1506 p_ctcv_tbl(1).OBJECT1_ID1 := OKE_API.g_miss_char;
1507 p_ctcv_tbl(1).OBJECT1_ID2 := OKE_API.g_miss_char;
1508 p_ctcv_tbl(1).JTOT_OBJECT1_CODE := OKE_API.g_miss_char;
1509 p_ctcv_tbl(1).ATTRIBUTE_CATEGORY := OKE_API.g_miss_char;
1510 p_ctcv_tbl(1).ATTRIBUTE1 := OKE_API.g_miss_char;
1511 p_ctcv_tbl(1).ATTRIBUTE2 := OKE_API.g_miss_char;
1512 p_ctcv_tbl(1).ATTRIBUTE3 := OKE_API.g_miss_char;
1513 p_ctcv_tbl(1).ATTRIBUTE4 := OKE_API.g_miss_char;
1514 p_ctcv_tbl(1).ATTRIBUTE5 := OKE_API.g_miss_char;
1515 p_ctcv_tbl(1).ATTRIBUTE6 := OKE_API.g_miss_char;
1516 p_ctcv_tbl(1).ATTRIBUTE7 := OKE_API.g_miss_char;
1517 p_ctcv_tbl(1).ATTRIBUTE8 := OKE_API.g_miss_char;
1518 p_ctcv_tbl(1).ATTRIBUTE9 := OKE_API.g_miss_char;
1519 p_ctcv_tbl(1).ATTRIBUTE10 := OKE_API.g_miss_char;
1520 p_ctcv_tbl(1).ATTRIBUTE11 := OKE_API.g_miss_char;
1521 p_ctcv_tbl(1).ATTRIBUTE12 := OKE_API.g_miss_char;
1522 p_ctcv_tbl(1).ATTRIBUTE13 := OKE_API.g_miss_char;
1523 p_ctcv_tbl(1).ATTRIBUTE14 := OKE_API.g_miss_char;
1524 p_ctcv_tbl(1).ATTRIBUTE15 := OKE_API.g_miss_char;
1525 p_ctcv_tbl(1).CREATED_BY := OKE_API.g_miss_num;
1526 p_ctcv_tbl(1).CREATION_DATE := OKE_API.g_miss_date;
1527 p_ctcv_tbl(1).LAST_UPDATED_BY := OKE_API.g_miss_num;
1528 p_ctcv_tbl(1).LAST_UPDATE_DATE := OKE_API.g_miss_date;
1529 p_ctcv_tbl(1).LAST_UPDATE_LOGIN := OKE_API.g_miss_num;
1530
1531 END;
1532
1533 PROCEDURE SET_RECORD_contact(
1534 operation in varchar2,
1535 p_ctcv_rec in oke_contract_party_pub.ctcv_rec_type ,
1536 p_jtot_object1_code in varchar2 ,
1537 l_ctcv_tbl_in out NOCOPY okc_contract_party_pub.ctcv_tbl_type)
1538 IS
1539
1540
1541 CURSOR get_object_vers is
1542 SELECT object_version_number FROM okc_contacts WHERE id = p_ctcv_rec.ID;
1543 l_obj_vers_num NUMBER;
1544 BEGIN
1545 INITIALIZE_RECORD_contact(l_ctcv_tbl_in);
1546
1547 If (operation = 'INSERT') Then
1548 l_ctcv_tbl_in(1).CPL_ID := p_ctcv_rec.CPL_ID;
1549 l_ctcv_tbl_in(1).DNZ_CHR_ID := p_ctcv_rec.dnz_chr_ID;
1550 Else
1551 l_ctcv_tbl_in(1).ID := p_ctcv_rec.ID ;
1552 OPEN get_object_vers;
1553 FETCH get_object_vers INTO l_obj_vers_num ;
1554 CLOSE get_object_vers;
1555 l_ctcv_tbl_in(1).OBJECT_VERSION_NUMBER := l_obj_vers_num ;
1556 End If;
1557
1558 l_ctcv_tbl_in(1).CRO_CODE :=p_ctcv_rec.CRO_CODE;
1559 l_ctcv_tbl_in(1).CONTACT_SEQUENCE := p_ctcv_rec.CONTACT_SEQUENCE;
1560 l_ctcv_tbl_in(1).JTOT_OBJECT1_CODE := p_jtot_object1_code;
1561 l_ctcv_tbl_in(1).OBJECT1_ID1 := p_ctcv_rec.OBJECT1_ID1;
1562 l_ctcv_tbl_in(1).OBJECT1_ID2 := p_ctcv_rec.OBJECT1_ID2;
1563 l_ctcv_tbl_in(1).ATTRIBUTE_CATEGORY := p_ctcv_rec.ATTRIBUTE_CATEGORY;
1564 l_ctcv_tbl_in(1).ATTRIBUTE1 := p_ctcv_rec.ATTRIBUTE1;
1565 l_ctcv_tbl_in(1).ATTRIBUTE2 := p_ctcv_rec.ATTRIBUTE2;
1566 l_ctcv_tbl_in(1).ATTRIBUTE3 := p_ctcv_rec.ATTRIBUTE3;
1567 l_ctcv_tbl_in(1).ATTRIBUTE4 := p_ctcv_rec.ATTRIBUTE4;
1568 l_ctcv_tbl_in(1).ATTRIBUTE5 :=p_ctcv_rec.ATTRIBUTE5;
1569 l_ctcv_tbl_in(1).ATTRIBUTE6 := p_ctcv_rec.ATTRIBUTE6;
1570 l_ctcv_tbl_in(1).ATTRIBUTE7 := p_ctcv_rec.ATTRIBUTE7;
1571 l_ctcv_tbl_in(1).ATTRIBUTE8 := p_ctcv_rec.ATTRIBUTE8;
1572 l_ctcv_tbl_in(1).ATTRIBUTE9 := p_ctcv_rec.ATTRIBUTE9;
1573 l_ctcv_tbl_in(1).ATTRIBUTE10 := p_ctcv_rec.ATTRIBUTE10;
1574 l_ctcv_tbl_in(1).ATTRIBUTE11 := p_ctcv_rec.ATTRIBUTE11;
1575 l_ctcv_tbl_in(1).ATTRIBUTE12 := p_ctcv_rec.ATTRIBUTE12;
1576 l_ctcv_tbl_in(1).ATTRIBUTE13 := p_ctcv_rec.ATTRIBUTE13;
1577 l_ctcv_tbl_in(1).ATTRIBUTE14 := p_ctcv_rec.ATTRIBUTE14;
1578 l_ctcv_tbl_in(1).ATTRIBUTE15 := p_ctcv_rec.ATTRIBUTE15;
1579 /*l_ctcv_tbl_in(1).CREATED_BY := name_in('CONTACT.CREATED_BY');
1580 l_ctcv_tbl_in(1).CREATION_DATE := APP_DATE.FIELD_TO_DATE('CONTACT.CREATION_DATE');
1581 l_ctcv_tbl_in(1).LAST_UPDATED_BY := name_in('CONTACT.LAST_UPDATED_BY');
1582 l_ctcv_tbl_in(1).LAST_UPDATE_DATE := APP_DATE.FIELD_TO_DATE('CONTACT.LAST_UPDATE_DATE');
1583 l_ctcv_tbl_in(1).LAST_UPDATE_LOGIN := name_in('CONTACT.LAST_UPDATE_LOGIN');*/
1584
1585 END;
1586
1587 procedure create_k_contact(p_api_version IN NUMBER,
1588 p_init_msg_list IN VARCHAR2 ,
1589 x_return_status OUT NOCOPY VARCHAR2,
1590 x_msg_count OUT NOCOPY NUMBER,
1591 x_msg_data OUT NOCOPY VARCHAR2,
1592 p_ctcv_rec IN ctcv_rec_type,
1593 x_ctcv_rec OUT NOCOPY ctcv_rec_type,
1594 p_skip_minor_vers IN VARCHAR2 DEFAULT OKE_API.G_FALSE) is
1595
1596 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_K_CONTACT';
1597 l_api_version CONSTANT NUMBER := 1.0;
1598 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1599 l_ctcv_tbl_in OKC_CONTRACT_PARTY_PUB.ctcv_tbl_type;
1600 l_ctcv_tbl_out OKC_CONTRACT_PARTY_PUB.ctcv_tbl_type;
1601
1602 l_JTOT_OBJECT1_CODE VARCHAR2(40);
1603
1604 BEGIN
1605 -- call START_ACTIVITY to create savepoint, check compatibility
1606 -- and initialize message list
1607 l_return_status := OKE_API.START_ACTIVITY(
1608 p_api_name => l_api_name,
1609 p_pkg_name => g_pkg_name,
1610 p_init_msg_list => p_init_msg_list,
1611 l_api_version => l_api_version,
1612 p_api_version => p_api_version,
1613 p_api_type => g_api_type,
1614 x_return_status => x_return_status);
1615
1616 -- check if activity started successfully
1617 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1618 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1619 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1620 raise OKE_API.G_EXCEPTION_ERROR;
1621 End If;
1622
1623 ---Other validations which are part of OKE and missing in the above procedure
1624
1625 validate_contact_attributes( x_return_status =>l_return_status ,
1626 p_ctcv_rec => p_ctcv_rec,
1627 l_JTOT_OBJECT1_CODE => l_JTOT_OBJECT1_CODE
1628 );
1629
1630 /* --- If any errors happen abort API
1631 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1632 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1633 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1634 RAISE OKE_API.G_EXCEPTION_ERROR;
1635 END IF;
1636 */
1637
1638 SET_RECORD_contact(operation => 'INSERT',
1639 p_ctcv_rec => p_ctcv_rec,
1640 p_jtot_object1_code => l_JTOT_OBJECT1_CODE ,
1641 l_ctcv_tbl_in => l_ctcv_tbl_in
1642 );
1643
1644
1645 OKC_CONTRACT_PARTY_PUB.validate_contact(
1646 p_api_version => p_api_version,
1647 p_init_msg_list => g_init_msg_list,
1648 x_return_status => x_return_status,
1649 x_msg_count => x_msg_count,
1650 x_msg_data => x_msg_data,
1651 p_ctcv_tbl => l_ctcv_tbl_in );
1652
1653 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR OR x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1654 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1655 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR OR x_return_status = OKE_API.G_RET_STS_ERROR) then
1656 raise OKE_API.G_EXCEPTION_ERROR;
1657 End If;
1658
1659 /*skuchima bugs 14380256 */
1660 IF (FND_API.G_TRUE = p_skip_minor_vers ) THEN
1661 okc_cvm_pvt.defer_minor_version_update('T');
1662 end if;
1663
1664 OKC_CONTRACT_PARTY_PUB.CReATE_contact (
1665 p_api_version => p_api_version,
1666 p_init_msg_list => g_init_msg_list,
1667 x_return_status => l_return_status,
1668 x_msg_count => x_msg_count,
1669 x_msg_data => x_msg_data,
1670 p_ctcv_tbl => l_ctcv_tbl_in ,
1671 x_ctcv_tbl => l_ctcv_tbl_out
1672 );
1673
1674 okc_cvm_pvt.defer_minor_version_update('F');
1675
1676 x_ctcv_rec:= l_ctcv_tbl_out(1);
1677 --- If any errors happen abort API
1678 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1679 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1680 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1681 RAISE OKE_API.G_EXCEPTION_ERROR;
1682 END IF;
1683
1684
1685 x_return_status:=l_return_status;
1686 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1687 x_msg_data => x_msg_data);
1688 EXCEPTION
1689 when OKE_API.G_EXCEPTION_ERROR then
1690 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1691 p_api_name => l_api_name,
1692 p_pkg_name => g_pkg_name,
1693 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1694 x_msg_count => x_msg_count,
1695 x_msg_data => x_msg_data,
1696 p_api_type => g_api_type);
1697
1698 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1699 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1700 p_api_name => l_api_name,
1701 p_pkg_name => g_pkg_name,
1702 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1703 x_msg_count => x_msg_count,
1704 x_msg_data => x_msg_data,
1705 p_api_type => g_api_type);
1706
1707 when OTHERS then
1708 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1709 p_api_name => l_api_name,
1710 p_pkg_name => g_pkg_name,
1711 p_exc_name => 'OTHERS',
1712 x_msg_count => x_msg_count,
1713 x_msg_data => x_msg_data,
1714 p_api_type => g_api_type);
1715
1716 END CREATE_K_CONTACT ;
1717
1718 procedure update_k_contact(p_api_version IN NUMBER,
1719 p_init_msg_list IN VARCHAR2 ,
1720 x_return_status OUT NOCOPY VARCHAR2,
1721 x_msg_count OUT NOCOPY NUMBER,
1722 x_msg_data OUT NOCOPY VARCHAR2,
1723 p_ctcv_tbl IN ctcv_tbl_type,
1724 x_ctcv_tbl OUT NOCOPY ctcv_tbl_type,
1725 p_skip_minor_vers IN VARCHAR2 DEFAULT OKE_API.G_FALSE)
1726 is
1727
1728 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_K_CONTACT';
1729 l_api_version CONSTANT NUMBER := 1.0;
1730 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1731 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1732 i NUMBER:=0;
1733 BEGIN
1734 -- call START_ACTIVITY to create savepoint, check compatibility
1735 -- and initialize message list
1736 l_return_status := OKE_API.START_ACTIVITY(
1737 p_api_name => l_api_name,
1738 p_pkg_name => g_pkg_name,
1739 p_init_msg_list => p_init_msg_list,
1740 l_api_version => l_api_version,
1741 p_api_version => p_api_version,
1742 p_api_type => g_api_type,
1743 x_return_status => x_return_status);
1744
1745 -- check if activity started successfully
1746 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1747 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1748 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1749 raise OKE_API.G_EXCEPTION_ERROR;
1750 End If;
1751
1752 If (p_ctcv_tbl.COUNT > 0) Then
1753 i := p_ctcv_tbl.FIRST;
1754 LOOP
1755 -- call procedure for a record
1756 update_k_contact(p_api_version=>p_api_version,
1757 p_init_msg_list=>OKC_API.G_FALSE,
1758 x_return_status=>l_return_status,
1759 x_msg_count=>x_msg_count,
1760 x_msg_data=>x_msg_data,
1761 p_ctcv_rec=>p_ctcv_tbl(i),
1762 x_ctcv_rec=>x_ctcv_tbl(i),
1763 p_skip_minor_vers=>p_skip_minor_vers);
1764
1765
1766 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1767 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1768 l_overall_status := x_return_status;
1769 End If;
1770 End If;
1771
1772 EXIT WHEN (i = p_ctcv_tbl.LAST);
1773 i := p_ctcv_tbl.NEXT(i);
1774 END LOOP;
1775
1776 -- return overall status
1777 x_return_status := l_overall_status;
1778 End If;
1779
1780 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1781 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1782 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1783 raise OKE_API.G_EXCEPTION_ERROR;
1784 End If;
1785
1786 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1787 x_msg_data => x_msg_data);
1788 EXCEPTION
1789 when OKE_API.G_EXCEPTION_ERROR then
1790 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1791 p_api_name => l_api_name,
1792 p_pkg_name => g_pkg_name,
1793 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1794 x_msg_count => x_msg_count,
1795 x_msg_data => x_msg_data,
1796 p_api_type => g_api_type);
1797
1798 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1799 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1800 p_api_name => l_api_name,
1801 p_pkg_name => g_pkg_name,
1802 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1803 x_msg_count => x_msg_count,
1804 x_msg_data => x_msg_data,
1805 p_api_type => g_api_type);
1806
1807 when OTHERS then
1808 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1809 p_api_name => l_api_name,
1810 p_pkg_name => g_pkg_name,
1811 p_exc_name => 'OTHERS',
1812 x_msg_count => x_msg_count,
1813 x_msg_data => x_msg_data,
1814 p_api_type => g_api_type);
1815
1816 END UPDATE_K_CONTACT ;
1817
1818 procedure update_k_contact(p_api_version IN NUMBER,
1819 p_init_msg_list IN VARCHAR2 ,
1820 x_return_status OUT NOCOPY VARCHAR2,
1821 x_msg_count OUT NOCOPY NUMBER,
1822 x_msg_data OUT NOCOPY VARCHAR2,
1823 p_ctcv_rec IN ctcv_rec_type,
1824 x_ctcv_rec OUT NOCOPY ctcv_rec_type,
1825 p_skip_minor_vers IN VARCHAR2 DEFAULT OKE_API.G_FALSE) is
1826
1827 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_CONTACT';
1828 l_api_version CONSTANT NUMBER := 1.0;
1829 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1830 l_ctcv_tbl_in OKC_CONTRACT_PARTY_PUB.ctcv_tbl_type;
1831 l_ctcv_tbl_out OKC_CONTRACT_PARTY_PUB.ctcv_tbl_type;
1832
1833 l_JTOT_OBJECT1_CODE VARCHAR2(40);
1834 l_ctcv_rec ctcv_rec_type;
1835
1836 ---------------------------------------------------------------------------
1837 -- FUNCTION get_rec for: OKC_CONTACTS_V
1838 ---------------------------------------------------------------------------
1839 FUNCTION get_rec (
1840 p_ctcv_rec IN ctcv_rec_type,
1841 x_no_data_found OUT NOCOPY BOOLEAN
1842 ) RETURN ctcv_rec_type IS
1843 CURSOR okc_ctcv_pk_csr (p_id IN NUMBER) IS
1844 SELECT
1845 ID,
1846 OBJECT_VERSION_NUMBER,
1847 CPL_ID,
1848 CRO_CODE,
1849 DNZ_CHR_ID,
1850 CONTACT_SEQUENCE,
1851 OBJECT1_ID1,
1852 OBJECT1_ID2,
1853 JTOT_OBJECT1_CODE,
1854 PRIMARY_YN,
1855 RESOURCE_CLASS,
1856 SALES_GROUP_ID,
1857 ATTRIBUTE_CATEGORY,
1858 ATTRIBUTE1,
1859 ATTRIBUTE2,
1860 ATTRIBUTE3,
1861 ATTRIBUTE4,
1862 ATTRIBUTE5,
1863 ATTRIBUTE6,
1864 ATTRIBUTE7,
1865 ATTRIBUTE8,
1866 ATTRIBUTE9,
1867 ATTRIBUTE10,
1868 ATTRIBUTE11,
1869 ATTRIBUTE12,
1870 ATTRIBUTE13,
1871 ATTRIBUTE14,
1872 ATTRIBUTE15,
1873 CREATED_BY,
1874 CREATION_DATE,
1875 LAST_UPDATED_BY,
1876 LAST_UPDATE_DATE,
1877 LAST_UPDATE_LOGIN,
1878 START_DATE,
1879 END_DATE
1880 FROM Okc_Contacts
1881 WHERE okc_contacts.id = p_id;
1882 l_okc_ctcv_pk okc_ctcv_pk_csr%ROWTYPE;
1883 l_ctcv_rec ctcv_rec_type;
1884 BEGIN
1885
1886
1887 x_no_data_found := TRUE;
1888 -- Get current database values
1889 OPEN okc_ctcv_pk_csr (p_ctcv_rec.id);
1890 FETCH okc_ctcv_pk_csr INTO
1891 l_ctcv_rec.ID,
1892 l_ctcv_rec.OBJECT_VERSION_NUMBER,
1893 l_ctcv_rec.CPL_ID,
1894 l_ctcv_rec.CRO_CODE,
1895 l_ctcv_rec.DNZ_CHR_ID,
1896 l_ctcv_rec.CONTACT_SEQUENCE,
1897 l_ctcv_rec.OBJECT1_ID1,
1898 l_ctcv_rec.OBJECT1_ID2,
1899 l_ctcv_rec.JTOT_OBJECT1_CODE,
1900 l_ctcv_rec.PRIMARY_YN,
1901 l_ctcv_rec.RESOURCE_CLASS,
1902 l_ctcv_rec.SALES_GROUP_ID,
1903 l_ctcv_rec.ATTRIBUTE_CATEGORY,
1904 l_ctcv_rec.ATTRIBUTE1,
1905 l_ctcv_rec.ATTRIBUTE2,
1906 l_ctcv_rec.ATTRIBUTE3,
1907 l_ctcv_rec.ATTRIBUTE4,
1908 l_ctcv_rec.ATTRIBUTE5,
1909 l_ctcv_rec.ATTRIBUTE6,
1910 l_ctcv_rec.ATTRIBUTE7,
1911 l_ctcv_rec.ATTRIBUTE8,
1912 l_ctcv_rec.ATTRIBUTE9,
1913 l_ctcv_rec.ATTRIBUTE10,
1914 l_ctcv_rec.ATTRIBUTE11,
1915 l_ctcv_rec.ATTRIBUTE12,
1916 l_ctcv_rec.ATTRIBUTE13,
1917 l_ctcv_rec.ATTRIBUTE14,
1918 l_ctcv_rec.ATTRIBUTE15,
1919 l_ctcv_rec.CREATED_BY,
1920 l_ctcv_rec.CREATION_DATE,
1921 l_ctcv_rec.LAST_UPDATED_BY,
1922 l_ctcv_rec.LAST_UPDATE_DATE,
1923 l_ctcv_rec.LAST_UPDATE_LOGIN,
1924 l_ctcv_rec.START_DATE,
1925 l_ctcv_rec.END_DATE;
1926 x_no_data_found := okc_ctcv_pk_csr%NOTFOUND;
1927 CLOSE okc_ctcv_pk_csr;
1928
1929
1930
1931 RETURN(l_ctcv_rec);
1932
1933 END get_rec;
1934
1935 FUNCTION get_rec (
1936 p_ctcv_rec IN ctcv_rec_type
1937 ) RETURN ctcv_rec_type IS
1938 l_row_notfound BOOLEAN := TRUE;
1939 BEGIN
1940
1941 RETURN(get_rec(p_ctcv_rec, l_row_notfound));
1942
1943 END get_rec;
1944
1945
1946 ----------------------------------
1947 -- FUNCTION populate_new_record --
1948 ----------------------------------
1949 FUNCTION populate_new_record (
1950 p_ctcv_rec IN ctcv_rec_type,
1951 x_ctcv_rec OUT NOCOPY ctcv_rec_type
1952 ) RETURN VARCHAR2 IS
1953 l_ctcv_rec ctcv_rec_type;
1954 l_row_notfound BOOLEAN := TRUE;
1955 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1956 BEGIN
1957
1958
1959
1960 x_ctcv_rec := p_ctcv_rec;
1961 -- Get current database values
1962 l_ctcv_rec := get_rec(p_ctcv_rec, l_row_notfound);
1963 IF (l_row_notfound) THEN
1964 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1965 p_msg_name =>G_REQUIRED_VALUE,
1966 p_token1 =>G_COL_NAME_TOKEN,
1967 p_token1_value =>'ID');
1968
1969
1970 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1971 END IF;
1972 IF (x_ctcv_rec.id = OKC_API.G_MISS_NUM)
1973 THEN
1974 x_ctcv_rec.id := l_ctcv_rec.id;
1975 END IF;
1976 IF (x_ctcv_rec.object_version_number = OKC_API.G_MISS_NUM)
1977 THEN
1978 x_ctcv_rec.object_version_number := l_ctcv_rec.object_version_number;
1979 END IF;
1980 IF (x_ctcv_rec.cpl_id = OKC_API.G_MISS_NUM)
1981 THEN
1982 x_ctcv_rec.cpl_id := l_ctcv_rec.cpl_id;
1983 END IF;
1984 IF (x_ctcv_rec.cro_code = OKC_API.G_MISS_CHAR)
1985 THEN
1986 x_ctcv_rec.cro_code := l_ctcv_rec.cro_code;
1987 END IF;
1988 IF (x_ctcv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1989 THEN
1990 x_ctcv_rec.dnz_chr_id := l_ctcv_rec.dnz_chr_id;
1991 END IF;
1992 IF (x_ctcv_rec.contact_sequence = OKC_API.G_MISS_NUM)
1993 THEN
1994 x_ctcv_rec.contact_sequence := l_ctcv_rec.contact_sequence;
1995 END IF;
1996 IF (x_ctcv_rec.object1_id1 = OKC_API.G_MISS_CHAR)
1997 THEN
1998 x_ctcv_rec.object1_id1 := l_ctcv_rec.object1_id1;
1999 END IF;
2000 IF (x_ctcv_rec.object1_id2 = OKC_API.G_MISS_CHAR)
2001 THEN
2002 x_ctcv_rec.object1_id2 := l_ctcv_rec.object1_id2;
2003 END IF;
2004 IF (x_ctcv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR)
2005 THEN
2006 x_ctcv_rec.JTOT_OBJECT1_CODE := l_ctcv_rec.JTOT_OBJECT1_CODE;
2007 END IF;
2008 IF (x_ctcv_rec.primary_yn = OKC_API.G_MISS_CHAR)
2009 THEN
2010 x_ctcv_rec.PRIMARY_YN := l_ctcv_rec.PRIMARY_YN;
2011 END IF;
2012 IF (x_ctcv_rec.RESOURCE_CLASS= OKC_API.G_MISS_CHAR)
2013 THEN
2014 x_ctcv_rec.RESOURCE_CLASS := l_ctcv_rec.RESOURCE_CLASS;
2015 END IF;
2016 --
2017 IF (x_ctcv_rec.SALES_GROUP_ID = OKC_API.G_MISS_NUM) THEN
2018 x_ctcv_rec.SALES_GROUP_ID := l_ctcv_rec.SALES_GROUP_ID;
2019 END IF;
2020 --
2021 IF (x_ctcv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2022 THEN
2023 x_ctcv_rec.attribute_category := l_ctcv_rec.attribute_category;
2024 END IF;
2025 IF (x_ctcv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2026 THEN
2027 x_ctcv_rec.attribute1 := l_ctcv_rec.attribute1;
2028 END IF;
2029 IF (x_ctcv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2030 THEN
2031 x_ctcv_rec.attribute2 := l_ctcv_rec.attribute2;
2032 END IF;
2033 IF (x_ctcv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2034 THEN
2035 x_ctcv_rec.attribute3 := l_ctcv_rec.attribute3;
2036 END IF;
2037 IF (x_ctcv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2038 THEN
2039 x_ctcv_rec.attribute4 := l_ctcv_rec.attribute4;
2040 END IF;
2041 IF (x_ctcv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2042 THEN
2043 x_ctcv_rec.attribute5 := l_ctcv_rec.attribute5;
2044 END IF;
2045 IF (x_ctcv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2046 THEN
2047 x_ctcv_rec.attribute6 := l_ctcv_rec.attribute6;
2048 END IF;
2049 IF (x_ctcv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2050 THEN
2051 x_ctcv_rec.attribute7 := l_ctcv_rec.attribute7;
2052 END IF;
2053 IF (x_ctcv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2054 THEN
2055 x_ctcv_rec.attribute8 := l_ctcv_rec.attribute8;
2056 END IF;
2057 IF (x_ctcv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2058 THEN
2059 x_ctcv_rec.attribute9 := l_ctcv_rec.attribute9;
2060 END IF;
2061 IF (x_ctcv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2062 THEN
2063 x_ctcv_rec.attribute10 := l_ctcv_rec.attribute10;
2064 END IF;
2065 IF (x_ctcv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2066 THEN
2067 x_ctcv_rec.attribute11 := l_ctcv_rec.attribute11;
2068 END IF;
2069 IF (x_ctcv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2070 THEN
2071 x_ctcv_rec.attribute12 := l_ctcv_rec.attribute12;
2072 END IF;
2073 IF (x_ctcv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2074 THEN
2075 x_ctcv_rec.attribute13 := l_ctcv_rec.attribute13;
2076 END IF;
2077 IF (x_ctcv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2078 THEN
2079 x_ctcv_rec.attribute14 := l_ctcv_rec.attribute14;
2080 END IF;
2081 IF (x_ctcv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2082 THEN
2083 x_ctcv_rec.attribute15 := l_ctcv_rec.attribute15;
2084 END IF;
2085 IF (x_ctcv_rec.created_by = OKC_API.G_MISS_NUM)
2086 THEN
2087 x_ctcv_rec.created_by := l_ctcv_rec.created_by;
2088 END IF;
2089 IF (x_ctcv_rec.creation_date = OKC_API.G_MISS_DATE)
2090 THEN
2091 x_ctcv_rec.creation_date := l_ctcv_rec.creation_date;
2092 END IF;
2093 IF (x_ctcv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2094 THEN
2095 x_ctcv_rec.last_updated_by := l_ctcv_rec.last_updated_by;
2096 END IF;
2097 IF (x_ctcv_rec.last_update_date = OKC_API.G_MISS_DATE)
2098 THEN
2099 x_ctcv_rec.last_update_date := l_ctcv_rec.last_update_date;
2100 END IF;
2101 IF (x_ctcv_rec.last_update_login = OKC_API.G_MISS_NUM)
2102 THEN
2103 x_ctcv_rec.last_update_login := l_ctcv_rec.last_update_login;
2104 END IF;
2105 IF (x_ctcv_rec.start_date = OKC_API.G_MISS_DATE)
2106 THEN
2107 x_ctcv_rec.start_date := l_ctcv_rec.start_date;
2108 END IF;
2109 IF (x_ctcv_rec.end_date = OKC_API.G_MISS_DATE)
2110 THEN
2111 x_ctcv_rec.end_date := l_ctcv_rec.end_date;
2112 END IF;
2113
2114
2115
2116 RETURN(l_return_status);
2117
2118 END populate_new_record;
2119
2120 BEGIN
2121 -- call START_ACTIVITY to create savepoint, check compatibility
2122 -- and initialize message list
2123 l_return_status := OKE_API.START_ACTIVITY(
2124 p_api_name => l_api_name,
2125 p_pkg_name => g_pkg_name,
2126 p_init_msg_list => p_init_msg_list,
2127 l_api_version => l_api_version,
2128 p_api_version => p_api_version,
2129 p_api_type => g_api_type,
2130 x_return_status => x_return_status);
2131
2132 -- check if activity started successfully
2133 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2134 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2135 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2136 raise OKE_API.G_EXCEPTION_ERROR;
2137 End If;
2138
2139 l_return_status := populate_new_record(p_ctcv_rec, l_ctcv_rec);
2140 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2141 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2142 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2143 RAISE OKC_API.G_EXCEPTION_ERROR;
2144 END IF;
2145
2146 ---Other validations which are part of OKE and missing in the above procedure
2147
2148 validate_contact_attributes( x_return_status =>l_return_status ,
2149 p_ctcv_rec => l_ctcv_rec,
2150 l_JTOT_OBJECT1_CODE => l_JTOT_OBJECT1_CODE
2151 );
2152
2153 --- If any errors happen abort API
2154 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2155 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2156 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2157 RAISE OKE_API.G_EXCEPTION_ERROR;
2158 END IF;
2159
2160
2161 SET_RECORD_contact(operation => 'UPDATE',
2162 p_ctcv_rec => l_ctcv_rec,
2163 p_jtot_object1_code => l_JTOT_OBJECT1_CODE ,
2164 l_ctcv_tbl_in => l_ctcv_tbl_in
2165 );
2166
2167
2168 OKC_CONTRACT_PARTY_PUB.validate_contact(
2169 p_api_version => p_api_version,
2170 p_init_msg_list => g_init_msg_list,
2171 x_return_status => x_return_status,
2172 x_msg_count => x_msg_count,
2173 x_msg_data => x_msg_data,
2174 p_ctcv_tbl => l_ctcv_tbl_in );
2175
2176 If (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2177 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2178 Elsif (x_return_status = OKE_API.G_RET_STS_ERROR) then
2179 raise OKE_API.G_EXCEPTION_ERROR;
2180 End If;
2181
2182 /*skuchima bugs 14380256 */
2183 IF (FND_API.G_TRUE = p_skip_minor_vers ) THEN
2184 okc_cvm_pvt.defer_minor_version_update('T');
2185 end if;
2186
2187 OKC_CONTRACT_PARTY_PUB.UPDATE_contact (
2188 p_api_version => p_api_version,
2189 p_init_msg_list => g_init_msg_list,
2190 x_return_status => l_return_status,
2191 x_msg_count => x_msg_count,
2192 x_msg_data => x_msg_data,
2193 p_ctcv_tbl => l_ctcv_tbl_in,
2194 x_ctcv_tbl => l_ctcv_tbl_out
2195 );
2196
2197 okc_cvm_pvt.defer_minor_version_update('F');
2198 x_ctcv_rec:= l_ctcv_tbl_out(1);
2199
2200 --- If any errors happen abort API
2201 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2202 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2203 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2204 RAISE OKE_API.G_EXCEPTION_ERROR;
2205 END IF;
2206
2207
2208 x_return_status:=l_return_status;
2209 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2210 x_msg_data => x_msg_data);
2211 EXCEPTION
2212 when OKE_API.G_EXCEPTION_ERROR then
2213 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2214 p_api_name => l_api_name,
2215 p_pkg_name => g_pkg_name,
2216 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2217 x_msg_count => x_msg_count,
2218 x_msg_data => x_msg_data,
2219 p_api_type => g_api_type);
2220
2221 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2222 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2223 p_api_name => l_api_name,
2224 p_pkg_name => g_pkg_name,
2225 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2226 x_msg_count => x_msg_count,
2227 x_msg_data => x_msg_data,
2228 p_api_type => g_api_type);
2229
2230 when OTHERS then
2231 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2232 p_api_name => l_api_name,
2233 p_pkg_name => g_pkg_name,
2234 p_exc_name => 'OTHERS',
2235 x_msg_count => x_msg_count,
2236 x_msg_data => x_msg_data,
2237 p_api_type => g_api_type);
2238
2239 END UPDATE_K_CONTACT ;
2240
2241 end oke_contract_party_pub;