[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_CODE_ASSIGNMENTS
Source
1 PACKAGE BODY HZ_IMP_LOAD_CODE_ASSIGNMENTS AS
2 /*$Header: ARHLCDAB.pls 120.36 2008/11/26 05:58:44 vsegu ship $*/
3
4
5 l_row_id ROWID;
6 l_batch_id NUMBER;
7 l_code_assignment_id CODE_ASSIGNMENT_ID;
8 l_party_orig_system PARTY_ORIG_SYSTEM;
9 l_party_orig_system_reference PARTY_ORIG_SYSTEM_REFERENCE;
10 l_owner_table_id OWNER_TABLE_ID;
11 l_class_category CLASS_CATEGORY;
12 l_class_code CLASS_CODE;
13 l_start_date_active START_DATE_ACTIVE;
14 l_end_date_active END_DATE_ACTIVE;
15 l_rank RANK;
16 l_created_by_module CREATED_BY_MODULE;
17
18 l_error_flag FLAG_COLUMN;
19 l_action_mismatch_errors FLAG_ERROR;
20 l_missing_parent_error FLAG_ERROR;
21 l_class_category_null_errors FLAG_ERROR;
22 l_class_cate_foreignkey_errors CLASS_CATEGORY;
23 l_class_code_null_errors FLAG_ERROR;
24 l_class_code_lookup_errors LOOKUP_ERROR;
25 l_start_end_date_errors FLAG_ERROR;
26 l_identical_classcode_errors FLAG_ERROR;
27 l_multi_assign_errors FLAG_ERROR;
28 l_leaf_node_errors FLAG_ERROR;
29 l_start_date_errors FLAG_ERROR;
30 l_dss_security_errors FLAG_COLUMN;
31 l_insert_update_flag INSERT_UPDATE_FLAG;
32 l_exception_exists FLAG_ERROR;
33
34
35 l_error_party_id OWNER_TABLE_ID;
36 l_error_class_category CLASS_CATEGORY;
37 l_error_party_type PARTY_TYPE;
38 l_update_party_id OWNER_TABLE_ID;
39 l_update_code_assignment_id CODE_ASSIGNMENT_ID;
40 l_update_class_category CLASS_CATEGORY;
41 l_update_class_code CLASS_CODE;
42 l_update_party_type PARTY_TYPE;
43 l_update_max_party_id NUMBER;
44 l_update_min_party_id NUMBER;
45
46 l_createdby_errors LOOKUP_ERROR;
47
48 /* Keep track of rows that do not get inserted or updated successfully.
49 Those are the rows that have some validation or DML errors.
50 Use this when inserting into or updating other tables so that we
51 do not need to check all the validation arrays. */
52
53 l_num_row_processed NUMBER_COLUMN;
54
55 l_no_end_date DATE;
56 l_update_int_class_category CLASS_CATEGORY;
57
58
59 /**********************************************
60 * private procedure validate_DSS_security
61 *
62 * DESCRIPTION
63 * Check if the DSS security is
64 * granted to the user.
65 *
66 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
67 *
68 * ARGUMENTS
69 * IN:
70 *
71 *
72 * NOTES
73 *
74 * MODIFICATION HISTORY
75 *
76 * 07-10-03 Kate Shan o Created
77
78 ************************************************/
79
80 PROCEDURE validate_DSS_security IS
81 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
82 dss_msg_count NUMBER := 0;
83 dss_msg_data VARCHAR2(2000):= null;
84 BEGIN
85
86 /* Check if the DSS security is granted to the user.
87 Only check for update. */
88 FOR i IN 1..l_code_assignment_id.count LOOP
89 l_dss_security_errors(i) :=
90 hz_dss_util_pub.test_instance(
91 p_operation_code => 'UPDATE',
92 p_db_object_name => 'HZ_CODE_ASSIGNMENTS',
93 p_instance_pk1_value => l_code_assignment_id(i),
94 p_user_name => fnd_global.user_name,
95 x_return_status => dss_return_status,
96 x_msg_count => dss_msg_count,
97 x_msg_data => dss_msg_data);
98
99 END LOOP;
100
101 END validate_DSS_security;
102
103
104 /**********************************************
105 * private procedure report_errors
106 *
107 * DESCRIPTION
108 * Report error.
109 *
110 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
111 *
112 * ARGUMENTS
113 * IN:
114 * P_ACTION IN VARCHAR2,
115 * P_DML_EXCEPTION IN VARCHAR2,
116 *
117 * NOTES Used by update procedure.
118 * Error is caught individually, it's reported individually
119 *
120 * MODIFICATION HISTORY
121 *
122 * 07-10-03 Kate Shan o Created
123 *
124 **********************************************/
125
126 PROCEDURE report_errors(
127 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
128 P_ACTION IN VARCHAR2,
129 P_DML_EXCEPTION IN VARCHAR2
130 ) IS
131 l_error_id HZ_IMP_CLASSIFICS_INT.ERROR_ID%TYPE;
132 m NUMBER := 1;
133 n NUMBER := 1;
134 num_exp NUMBER;
135 exp_ind NUMBER := 1;
136
137 BEGIN
138
139 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'report_errors (+)');
140 /**********************************/
141 /* Validation and Error reporting */
142 /**********************************/
143
144 IF l_code_assignment_id.count = 0 THEN
145 return;
146 END IF;
147
148 /**********************************/
149 /* Validation and Error reporting */
150 /**********************************/
151 l_num_row_processed := null;
152 l_num_row_processed := NUMBER_COLUMN();
153 l_num_row_processed.extend(l_code_assignment_id.count);
154 l_exception_exists := null;
155 l_exception_exists := FLAG_ERROR();
156 l_exception_exists.extend(l_code_assignment_id.count);
157 num_exp := SQL%BULK_EXCEPTIONS.COUNT;
158
159 -- FND_FILE.PUT_LINE(FND_FILE.LOG, ' ' || P_ACTION || ' Action, ' || ' total ' || num_exp || ' exceptions');
160
161 FOR k IN 1..l_code_assignment_id.count LOOP
162
163 /* If DML fails due to validation errors or exceptions */
164 IF SQL%BULK_ROWCOUNT(k) = 0 THEN
165 --FND_FILE.PUT_LINE(FND_FILE.LOG, ' DML fails at record ' || ' -> l_code_assignment_id = ' || l_code_assignment_id (k) ||' !');
166
167 l_num_row_processed(k) := 0;
168
169 /* Check for any exceptions during DML */
170 IF P_DML_EXCEPTION = 'Y' THEN
171 /* determine if exception at this index */
172 FOR i IN exp_ind..num_exp LOOP
173 IF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX = k THEN
174 l_exception_exists(k) := 'Y';
175 ELSIF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX > k THEN
176 EXIT;
177 END IF;
178 END LOOP;
179 END IF; /* P_DML_EXCEPTION = 'Y' */
180
181 ELSE
182 l_num_row_processed(k) := 1;
183 END IF; /* SQL%BULK_ROWCOUNT(k) = 0 */
184 END LOOP;
185
186 /* insert into tmp error tables */
187 forall j in 1..l_code_assignment_id.count
188 insert into hz_imp_tmp_errors
189 (
190 request_id,
191 batch_id,
192 int_row_id,
193 interface_table_name,
194 error_id,
195 creation_date,
196 created_by,
197 last_update_date,
198 last_updated_by,
199 last_update_login,
200 program_application_id,
201 program_id,
202 program_update_date,
203 ACTION_MISMATCH_FLAG,
204 MISSING_PARENT_FLAG,
205 e2_flag, e3_flag,
206 e4_flag,e5_flag,e6_flag,
207 e7_flag,
208 e8_flag, e9_flag, e10_flag,
209 OTHER_EXCEP_FLAG
210 )
211
212 (
213 select P_DML_RECORD.REQUEST_ID,
214 P_DML_RECORD.BATCH_ID,
215 l_row_id(j),
216 'HZ_IMP_CLASSIFICS_INT',
217 HZ_IMP_ERRORS_S.NextVal,
218 P_DML_RECORD.SYSDATE,
219 P_DML_RECORD.USER_ID,
220 P_DML_RECORD.SYSDATE,
221 P_DML_RECORD.USER_ID,
222 P_DML_RECORD.LAST_UPDATE_LOGIN,
223 P_DML_RECORD.PROGRAM_APPLICATION_ID,
224 P_DML_RECORD.PROGRAM_ID,
225 P_DML_RECORD.SYSDATE,
226 l_action_mismatch_errors(j),
227 'Y',
228 'Y', 'Y',
229 l_start_end_date_errors(j),
230 l_identical_classcode_errors(j),
231 l_multi_assign_errors(j),
232 'Y',
233 l_start_date_errors(j),
234 decode(l_dss_security_errors(j), FND_API.G_TRUE,'Y',null),
235 'Y',
236 l_exception_exists(j)
237 from dual
238 where l_num_row_processed(j) = 0
239 );
240
241 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'report_errors (-)');
242
243 END report_errors;
244
245
246
247 /********************************************************************************
248 *
249 * PROCEDURE populate_error_table
250 *
251 * DESCRIPTION
252 *
253 *
254 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
255 *
256 * ARGUMENTS
257 * IN:
258 * P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
259 * P_DUP_VAL_EXP IN VARCHAR2
260 * P_SQL_ERRM IN VARCHAR2
261 *
262 * OUT
263 * NOTES record errors in temp error when exception happens during insert or update.
264 *
265 * MODIFICATION HISTORY
266 *
267 * 08-27-03 Kate Shan o Created
268 *
269 ********************************************************************************/
270
271 PROCEDURE populate_error_table(
272 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
273 P_DUP_VAL_EXP IN VARCHAR2,
274 P_SQL_ERRM IN VARCHAR2 ) IS
275
276 dup_val_exp_val VARCHAR2(1) := null;
277 other_exp_val VARCHAR2(1) := 'Y';
278 BEGIN
279
280 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'populate_error_table');
281
282 -- in constraint voilation happen, DUP_VAL_IDX_EXCEP_FLAG column
283 -- in temp error table will be set
284 -- 'A' indicate HZ_CODE_ASSIGNMENTS_U1 constraint violation
285 -- 'B' indicate HZ_CODE_ASSIGNMENTS_U2 constraint violation
286
287 if (P_DUP_VAL_EXP = 'Y') then
288 other_exp_val := null;
289 if(instr(P_SQL_ERRM, 'HZ_CODE_ASSIGNMENTS_U1')<>0) then
290 dup_val_exp_val := 'A';
291 elsif(instr(P_SQL_ERRM, 'HZ_CODE_ASSIGNMENTS_U2')<>0) then
292 dup_val_exp_val := 'B';
293 end if;
294 end if;
295
296 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'dup_val_exp_val:' || dup_val_exp_val);
297
298 insert into hz_imp_tmp_errors
299 (
300 request_id,
301 batch_id,
302 int_row_id,
303 interface_table_name,
304 error_id,
305 creation_date,
306 created_by,
307 last_update_date,
308 last_updated_by,
309 last_update_login,
310 program_application_id,
311 program_id,
312 program_update_date,
313 e1_flag,
314 e2_flag,
315 e3_flag,
316 e4_flag,
317 e5_flag,
318 e6_flag,
319 e7_flag,
320 e8_flag,
321 e9_flag,
322 e10_flag,
323 DUP_VAL_IDX_EXCEP_FLAG,
324 OTHER_EXCEP_FLAG,
325 ACTION_MISMATCH_FLAG,MISSING_PARENT_FLAG
326 )
327 (
328 select P_DML_RECORD.REQUEST_ID,
329 P_DML_RECORD.BATCH_ID,
330 clsf_sg.int_row_id,
331 'HZ_IMP_CLASSIFICS_INT',
332 HZ_IMP_ERRORS_S.NextVal,
333 P_DML_RECORD.SYSDATE,
334 P_DML_RECORD.USER_ID,
335 P_DML_RECORD.SYSDATE,
336 P_DML_RECORD.USER_ID,
337 P_DML_RECORD.LAST_UPDATE_LOGIN,
338 P_DML_RECORD.PROGRAM_APPLICATION_ID,
339 P_DML_RECORD.PROGRAM_ID,
340 P_DML_RECORD.SYSDATE,
341 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
342 dup_val_exp_val,
343 other_exp_val,
344 'Y','Y'
345 from hz_imp_classifics_sg clsf_sg, hz_imp_classifics_int clsf_int
346 where clsf_sg.action_flag = 'I'
347 and clsf_int.rowid = clsf_sg.int_row_id
348 and clsf_int.batch_id = P_DML_RECORD.BATCH_ID
349 and clsf_int.party_orig_system = P_DML_RECORD.OS
350 and clsf_int.party_orig_system_reference
351 between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
352 );
353
354 END populate_error_table;
355
356 /********************************************************************************
357 *
358 * process_insert_codeassigns
359 *
360 ********************************************************************************/
361
362 PROCEDURE process_insert_code_assignment (
363 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
364 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
365 ,X_MSG_COUNT OUT NOCOPY NUMBER
366 ,X_MSG_DATA OUT NOCOPY VARCHAR2
367 ) IS
368
369 c_handle_insert RefCurType;
370
371 l_insert_sql varchar2(20000) :=
372 '
373 insert all
374 when (--error_flag is null
375 action_mismatch_error is not null
376 and classcat_foreignkey_error is not null
377 and class_code_lookup_error is not null
378 and start_end_date_error is not null
379 and identical_classcode_error is not null
380 and multi_assign_error is not null
381 and leaf_node_error is not null
382 and createdby_error is not null
383 and missing_parent_error is not null) then
384 into hz_code_assignments (
385 application_id,
386 actual_content_source, -- Bug 4079902
387 content_source_type,
388 created_by,
389 creation_date,
390 last_updated_by,
391 last_update_date,
392 last_update_login,
393 program_application_id,
394 program_id,
395 program_update_date,
396 request_id,
397 code_assignment_id,
398 owner_table_name,
399 owner_table_id,
400 class_category,
401 class_code,
402 primary_flag,
403 rank,
404 start_date_active,
405 end_date_active,
406 status,
407 object_version_number,
408 created_by_module)
409 values (
410 :application_id,
411 :actual_content_src,
412 ''USER_ENTERED'', -- Bug 4079902
413 :user_id,
414 :l_sysdate,
415 :user_id,
416 :l_sysdate,
417 :last_update_login,
418 :program_application_id,
419 :program_id,
420 :l_sysdate,
421 :request_id,
422 code_assignment_id,
423 ''HZ_PARTIES'',
424 party_id,
425 class_category,
426 class_code,
427 nvl(primary_flag, ''N''),
428 rank,
429 start_date_active,
430 end_date_active,
431 ''A'',
432 1, -- OBJECT_VERSION_NUMBER,
433 created_by_module)
434 else
435 into hz_imp_tmp_errors (
436 created_by,
437 creation_date,
438 last_updated_by,
439 last_update_date,
440 last_update_login,
441 program_application_id,
442 program_id,
443 program_update_date,
444 error_id,
445 batch_id,
446 request_id,
447 int_row_id,
448 interface_table_name,
449 ACTION_MISMATCH_FLAG,
450 MISSING_PARENT_FLAG,
451 --e1_flag,
452 e2_flag,
453 e3_flag,
454 e4_flag,
455 e5_flag,
456 e6_flag,
457 e7_flag,
458 e8_flag,
459 e9_flag,
460 e10_flag)
461 values (
462 :user_id,
463 :l_sysdate,
464 :user_id,
465 :l_sysdate,
466 :last_update_login,
467 :program_application_id,
468 :program_id,
469 :l_sysdate,
470 hz_imp_errors_s.nextval,
471 :p_batch_id,
472 :request_id,
473 row_id,
474 ''HZ_IMP_CLASSIFICS_INT'',
475 action_mismatch_error,
476 missing_parent_error,
477 --error_flag,
478 classcat_foreignkey_error,
479 class_code_lookup_error,
480 start_end_date_error,
481 identical_classcode_error,
482 multi_assign_error,
483 leaf_node_error,
484 ''Y'', ''Y'',
485 createdby_error)
486 with tc as (select 0 a from dual union all select 1 a from dual)
487 select /*+ ordered push_subq index(cas) use_nl(cai) use_nl(class_code_lookups) */ cai.rowid row_id,
488 cas.code_assignment_id,
489 hp.party_id party_id,
490 cai.class_category,
491 class_code_lookups.lookup_code class_code,
492 decode(cai.start_date_active, :p_gmiss_date, sysdate, null, sysdate, cai.start_date_active) start_date_active,
493 nullif(cai.end_date_active, :p_gmiss_date) end_date_active,
494 nullif(cai.rank, :p_gmiss_num) rank,
495 cas.primary_flag primary_flag,
496 nvl(nullif(cai.created_by_module, :p_gmiss_char), ''HZ_IMPORT'') created_by_module,
497 cas.error_flag,
498 nvl2(nullif(nullif(cai.insert_update_flag, :p_gmiss_char), cas.action_flag), null, ''Y'') action_mismatch_error,
499 nvl2(hp.party_id,''Y'',null) missing_parent_error,
500 decode(cai.class_category, :p_gmiss_char, null, nvl2(class_cat.class_category, ''Y'', null)) classcat_foreignkey_error,
501 nvl2(nullif(cai.class_code, :p_gmiss_char), nvl2(class_code_lookups.lookup_code, ''Y'', null), null) class_code_lookup_error,
502 decode(cai.start_date_active, :p_gmiss_date, ''Y'',
503 nvl2(cai.end_date_active, decode(sign(cai.end_date_active - nvl(cai.start_date_active, sysdate)), -1, null, ''Y''), ''Y'')) start_end_date_error,
504 decode(tc1.a, 0, ''Y'') identical_classcode_error,
505 decode(tc2.a, 0, ''Y'') multi_assign_error,
506 decode(tc3.a, 0, ''Y'') leaf_node_error,
507 decode(cai.created_by_module, :p_gmiss_char, ''Y'', null, ''Y'', nvl2(createdby_l.lookup_code,''Y'',null)) createdby_error
508 from hz_imp_classifics_sg cas,
509 hz_imp_classifics_int cai,
510 fnd_lookup_values class_code_lookups,
511 hz_class_categories class_cat,
512 tc tc1, tc tc2, tc tc3,
513 hz_parties hp,
514 fnd_lookup_values createdby_l
515
516 where hp.party_id (+) = cas.party_id
517 AND hp.status (+) = ''A''
518 AND cas.batch_id = :p_batch_id
519 and cas.batch_mode_flag = :p_batch_mode_flag
520 and cas.party_orig_system = :p_wu_os
521 and cas.party_orig_system_reference between :p_from_osr and :p_to_osr
522 and cai.rowid = cas.int_row_id
523 and cas.action_flag = ''I''
524 and cai.class_category = class_cat.class_category (+)
525 -- and decode(cai.class_category, ''NACE'', replace(cai.class_code, ''.'', ''''), cai.class_code) = decode(cai.class_category, ''NACE'', replace(class_code_lookups.lookup_code (+), ''.'', ''''), class_code_lookups.lookup_code (+))
526 and cai.class_code = class_code_lookups.lookup_code (+)
527 and cai.class_category = class_code_lookups.lookup_type (+)
528 and class_code_lookups.language (+) = userenv(''LANG'')
529 and createdby_l.lookup_code (+) = cai.created_by_module
530 and createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
531 and createdby_l.language (+) = userenv(''LANG'')
532 and createdby_l.view_application_id (+) = 222
533 and createdby_l.security_group_id (+) =
534 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
535 and tc1.a = (select count(*) -- check date overlap, 0 indicates no error
536 from hz_code_assignments c
537 where c.class_category = cai.class_category
538 and c.class_code = cai.class_code
539 and c.owner_table_name = ''HZ_PARTIES''
540 and c.owner_table_id = cas.party_id
541 and nvl(cai.end_date_active, :l_no_end_date) >=
542 c.start_date_active
543 and nvl(c.end_date_active, :l_no_end_date) >=
544 decode(cai.start_date_active, null, sysdate, :p_gmiss_date, sysdate, cai.start_date_active)
545 --and c.content_source_type = :l_content_source_type (bug 4079902)
546 and c.actual_content_source = :l_content_source_type
547 and c.status = ''A''
548 and rownum = 1)
549 and tc2.a = (select count(*) -- check multi class code, 0 indicates no error
550 from hz_code_assignments c_assign,
551 hz_class_categories c_cat
552 where c_cat.class_category = c_assign.class_category
553 and c_cat.class_category = cai.class_category
554 and c_assign.owner_table_id = cas.party_id
555 and c_assign.owner_table_name = ''HZ_PARTIES''
556 and c_cat.allow_multi_assign_flag = ''N''
557 and nvl(cai.end_date_active, :l_no_end_date) >=
558 c_assign.start_date_active
559 and nvl(c_assign.end_date_active, :l_no_end_date) >=
560 decode(cai.start_date_active, null, sysdate, :p_gmiss_date, sysdate, cai.start_date_active)
561 --and c_assign.content_source_type = :l_content_source_type (bug 4079902)
562 and c_assign.actual_content_source = :l_content_source_type
563 and status = ''A''
564 and rownum = 1)
565 and tc3.a = (select count(*) -- check leaf node, 0 indicates no error
566 from hz_class_categories c_cate,
567 hz_class_code_relations c_rel
568 where c_cate.class_category = cai.class_category
569 and c_cate.allow_leaf_node_only_flag = ''Y''
570 and c_rel.class_category = cai.class_category
571 and c_rel.class_code = cai.class_code
572 and c_rel.sub_class_code is not null
573 and sysdate between c_rel.start_date_active
574 and nvl(c_rel.end_date_active, :l_no_end_date)
575 and rownum = 1)
576 ';
577
578
579 l_where_first_run_sql varchar2(35) := ' AND cai.interface_status is null';
580 l_where_rerun_sql varchar2(35) := ' AND cai.interface_status = ''C''';
581
582 l_where_enabled_lookup_sql varchar2(1000) :=
583 ' AND ( class_code_lookups.ENABLED_FLAG(+) = ''Y'' AND
584 TRUNC(:l_sysdate) BETWEEN
585 TRUNC(NVL( class_code_lookups.START_DATE_ACTIVE,:l_sysdate ) ) AND
586 TRUNC(NVL( class_code_lookups.END_DATE_ACTIVE,:l_sysdate ) ) )
587 AND ( createdby_l.ENABLED_FLAG(+) = ''Y'' AND
588 TRUNC(:l_sysdate) BETWEEN
589 TRUNC(NVL( createdby_l.START_DATE_ACTIVE,:l_sysdate ) ) AND
590 TRUNC(NVL( createdby_l.END_DATE_ACTIVE,:l_sysdate ) ) )';
591
592 l_entity_attr_id number := null;
593 l_dml_exception varchar2(1) := 'N';
594
595 primary_flag_err_cursor RefCurType;
596 de_norm_cursor RefCurType;
597 pid_cursor RefCurType;
598
599 BEGIN
600
601 FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_insert_code_assignments (+)');
602
603 savepoint process_insert_codeassigns_pvt;
604
605 FND_MSG_PUB.initialize;
606
607 --Initialize API return status to success.
608 x_return_status := FND_API.G_RET_STS_SUCCESS;
609
610 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_BATCH_ID'||P_BATCH_ID);
611 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_wu_os'||p_wu_os);
612 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_from_osr'||p_from_osr);
613 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_to_osr'||p_to_osr);
614
615 IF P_DML_RECORD.ALLOW_DISABLED_LOOKUP = 'Y' THEN
616 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_allow_disabled_lookup = Y');
617
618 IF P_DML_RECORD.RERUN = 'N' THEN
619 -- First Run
620 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'First run');
621 EXECUTE IMMEDIATE l_insert_sql || l_where_first_run_sql
622 USING
623 p_dml_record.application_id,
624 p_dml_record.actual_content_src,
625 p_dml_record.user_id,
626 p_dml_record.sysdate,
627 p_dml_record.user_id,
628 p_dml_record.sysdate,
629 p_dml_record.last_update_login,
630 p_dml_record.program_application_id,
631 p_dml_record.program_id,
632 p_dml_record.sysdate,
633 p_dml_record.request_id,
634 p_dml_record.user_id,
635 p_dml_record.sysdate,
636 p_dml_record.user_id,
637 p_dml_record.sysdate,
638 p_dml_record.last_update_login,
639 p_dml_record.program_application_id,
640 p_dml_record.program_id,
641 p_dml_record.sysdate,
642 p_dml_record.batch_id,
643 p_dml_record.request_id,
644 p_dml_record.gmiss_date,
645 p_dml_record.gmiss_date,
646 p_dml_record.gmiss_num,
647 p_dml_record.gmiss_char,
648 p_dml_record.gmiss_char,
649 p_dml_record.gmiss_char,
650 p_dml_record.gmiss_char,
651 p_dml_record.gmiss_date,
652 p_dml_record.gmiss_char,
653 p_dml_record.batch_id,
654 p_dml_record.batch_mode_flag,
655 p_dml_record.os,
656 p_dml_record.from_osr,
657 p_dml_record.to_osr,
658 l_no_end_date,
659 l_no_end_date,
660 p_dml_record.gmiss_date,
661 p_dml_record.actual_content_src,
662 l_no_end_date,
663 l_no_end_date,
664 p_dml_record.gmiss_date,
665 p_dml_record.actual_content_src,
666 l_no_end_date
667 ;
668
669 ELSE
670 -- Rerun
671 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Re-run');
672 EXECUTE IMMEDIATE l_insert_sql || l_where_rerun_sql
673 USING
674 p_dml_record.application_id,
675 p_dml_record.actual_content_src,
676 p_dml_record.user_id,
677 p_dml_record.sysdate,
678 p_dml_record.user_id,
679 p_dml_record.sysdate,
680 p_dml_record.last_update_login,
681 p_dml_record.program_application_id,
682 p_dml_record.program_id,
683 p_dml_record.sysdate,
684 p_dml_record.request_id,
685 p_dml_record.user_id,
686 p_dml_record.sysdate,
687 p_dml_record.user_id,
688 p_dml_record.sysdate,
689 p_dml_record.last_update_login,
690 p_dml_record.program_application_id,
691 p_dml_record.program_id,
692 p_dml_record.sysdate,
693 p_dml_record.batch_id,
694 p_dml_record.request_id,
695 p_dml_record.gmiss_date,
696 p_dml_record.gmiss_date,
697 p_dml_record.gmiss_num,
698 p_dml_record.gmiss_char,
699 p_dml_record.gmiss_char,
700 p_dml_record.gmiss_char,
701 p_dml_record.gmiss_char,
702 p_dml_record.gmiss_date,
703 p_dml_record.gmiss_char,
704 p_dml_record.batch_id,
705 p_dml_record.batch_mode_flag,
706 p_dml_record.os,
707 p_dml_record.from_osr,
708 p_dml_record.to_osr,
709 l_no_end_date,
710 l_no_end_date,
711 p_dml_record.gmiss_date,
712 p_dml_record.actual_content_src,
713 l_no_end_date,
714 l_no_end_date,
715 p_dml_record.gmiss_date,
716 p_dml_record.actual_content_src,
717 l_no_end_date;
718
719
720 END IF;
721
722 ELSE -- l_allow_disabled_lookup
723 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_allow_disabled_lookup = N');
724
725 IF P_DML_RECORD.RERUN = 'N' THEN
726
727 -- First Run
728 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'First run');
729 EXECUTE IMMEDIATE l_insert_sql|| l_where_first_run_sql || l_where_enabled_lookup_sql
730 USING
731 p_dml_record.application_id,
732 p_dml_record.actual_content_src,
733 p_dml_record.user_id,
734 p_dml_record.sysdate,
735 p_dml_record.user_id,
736 p_dml_record.sysdate,
737 p_dml_record.last_update_login,
738 p_dml_record.program_application_id,
739 p_dml_record.program_id,
740 p_dml_record.sysdate,
741 p_dml_record.request_id,
742 p_dml_record.user_id,
743 p_dml_record.sysdate,
744 p_dml_record.user_id,
745 p_dml_record.sysdate,
746 p_dml_record.last_update_login,
747 p_dml_record.program_application_id,
748 p_dml_record.program_id,
749 p_dml_record.sysdate,
750 p_dml_record.batch_id,
751 p_dml_record.request_id,
752 p_dml_record.gmiss_date,
753 p_dml_record.gmiss_date,
754 p_dml_record.gmiss_num,
755 p_dml_record.gmiss_char,
756 p_dml_record.gmiss_char,
757 p_dml_record.gmiss_char,
758 p_dml_record.gmiss_char,
759 p_dml_record.gmiss_date,
760 p_dml_record.gmiss_char,
761 p_dml_record.batch_id,
762 p_dml_record.batch_mode_flag,
763 p_dml_record.os,
764 p_dml_record.from_osr,
765 p_dml_record.to_osr,
766 l_no_end_date,
767 l_no_end_date,
768 p_dml_record.gmiss_date,
769 p_dml_record.actual_content_src,
770 l_no_end_date,
771 l_no_end_date,
772 p_dml_record.gmiss_date,
773 p_dml_record.actual_content_src,
774 l_no_end_date,
775 p_dml_record.sysdate,
776 p_dml_record.sysdate,
777 p_dml_record.sysdate,
778 p_dml_record.sysdate,
779 p_dml_record.sysdate,
780 p_dml_record.sysdate;
781
782 ELSE
783 -- Rerun
784 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Re-run');
785 EXECUTE IMMEDIATE l_insert_sql || l_where_rerun_sql || l_where_enabled_lookup_sql
786 USING
787 p_dml_record.application_id,
788 p_dml_record.actual_content_src,
789 p_dml_record.user_id,
790 p_dml_record.sysdate,
791 p_dml_record.user_id,
792 p_dml_record.sysdate,
793 p_dml_record.last_update_login,
794 p_dml_record.program_application_id,
795 p_dml_record.program_id,
796 p_dml_record.sysdate,
797 p_dml_record.request_id,
798 p_dml_record.user_id,
799 p_dml_record.sysdate,
800 p_dml_record.user_id,
801 p_dml_record.sysdate,
802 p_dml_record.last_update_login,
803 p_dml_record.program_application_id,
804 p_dml_record.program_id,
805 p_dml_record.sysdate,
806 p_dml_record.batch_id,
807 p_dml_record.request_id,
808 p_dml_record.gmiss_date,
809 p_dml_record.gmiss_date,
810 p_dml_record.gmiss_num,
811 p_dml_record.gmiss_char,
812 p_dml_record.gmiss_char,
813 p_dml_record.gmiss_char,
814 p_dml_record.gmiss_char,
815 p_dml_record.gmiss_date,
816 p_dml_record.gmiss_char,
817 p_dml_record.batch_id,
818 p_dml_record.batch_mode_flag,
819 p_dml_record.os,
820 p_dml_record.from_osr,
821 p_dml_record.to_osr,
822 l_no_end_date,
823 l_no_end_date,
824 p_dml_record.gmiss_date,
825 p_dml_record.actual_content_src,
826 l_no_end_date,
827 l_no_end_date,
828 p_dml_record.gmiss_date,
829 p_dml_record.actual_content_src,
830 l_no_end_date,
831 p_dml_record.sysdate,
832 p_dml_record.sysdate,
833 p_dml_record.sysdate,
834 p_dml_record.sysdate,
835 p_dml_record.sysdate,
836 p_dml_record.sysdate;
837
838 END IF;
839
840 END IF;
841
842
843 /* Failed primary code assignment */
844 /* for all failed primary code assignment, nullify the corresponding denormalization column */
845
846 FND_FILE.PUT_LINE(FND_FILE.LOG, 'de-norm failed primary record with primary_flag = Y');
847
848 OPEN primary_flag_err_cursor FOR
849 'select party_id, class_category
850 from (
851 select cls_sg.party_id, cls_sg.class_category,
852 rank() over (partition by
853 cls_sg.party_id, cls_sg.class_category order by
854 cls_sg.code_assignment_id) r
855 from HZ_IMP_TMP_ERRORS err_table,
856 hz_imp_classifics_sg cls_sg,
857 hz_parties hp
858 where err_table.request_id = :request_id
859 and err_table.interface_table_name = ''HZ_IMP_CLASSIFICS_INT''
860 and cls_sg.batch_id = :batch_id
861 and cls_sg.batch_mode_flag = :batch_mode_flag
862 and cls_sg.party_orig_system = :orig_system
863 and cls_sg.party_orig_system_reference between :from_osr and :to_osr
864 and cls_sg.primary_flag = ''Y''
865 and cls_sg.action_flag = ''I''
866 and cls_sg.class_category in ( ''CUSTOMER_CATEGORY'', ''SIC'', ''NACE'')
867 and cls_sg.int_row_id = err_table.int_row_id
868 and hp.party_id=cls_sg.party_id
869 )
870 where r=1'
871 using P_DML_RECORD.REQUEST_ID,P_DML_RECORD.BATCH_ID,
872 P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS,
873 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
874
875 fetch primary_flag_err_cursor BULK COLLECT INTO
876 l_error_party_id, l_error_class_category;
877
878 close primary_flag_err_cursor;
879
880 -- nullify denorm column in hz_parties
881 /* Bug 409189: when checking class_category in hz_imp_classifics_sg,
882 check for 'SIC' instead of the individual SIC code type as this is how
883 matching populates the column */
884 forall i in 1..l_error_party_id.count
885 update hz_parties hz_pty
886 set category_code = decode(l_error_class_category(i), 'CUSTOMER_CATEGORY', null, category_code),
887 sic_code_type = decode(l_error_class_category(i), 'SIC', null, sic_code_type),
888 sic_code = decode(l_error_class_category(i), 'SIC', null, sic_code)
889 where hz_pty.party_id = l_error_party_id(i);
890
891 -- nullify denorm column in hz_organization_profiles
892 /* Bug 409189: when checking hz_organization_profiles.actual_content_source,
893 there are three cases to consider:
894 1. ACS is third party. Update org profile where ACS = '<third party>'
895 2. ACS is USER_ENTERED and party has third party profile.
896 Update org profile where ACS = 'USER_ENTERED'
897 3. ACS is USER_ENTERED and party only has USER_ENTERED profile.
898 Update org profile where ACS = 'SST'
899 */
900
901 /* Take care of cases 1 and 2 */
902 forall i in 1..l_error_party_id.count
903 update hz_organization_profiles org
904 set
905 local_activity_code = decode(l_error_class_category(i), 'NACE', null, local_activity_code),
906 sic_code_type = decode(l_error_class_category(i), 'SIC',null, sic_code_type),
907 sic_code = decode(l_error_class_category(i), 'SIC', null, sic_code)
908 where org.party_id = l_error_party_id(i)
909 and effective_end_date is null
910 and actual_content_source = P_DML_RECORD.actual_content_src;
911
912 /* Take care of case 3.
913 Even though this will update SST record for case 2 as well, since
914 we will rerun mix-n-match to derive SST, it'll be ok to update here */
915 forall i in 1..l_error_party_id.count
916 update hz_organization_profiles org
917 set
918 local_activity_code = decode(l_error_class_category(i), 'NACE', null, local_activity_code),
919 sic_code_type = decode(l_error_class_category(i), 'SIC',null, sic_code_type),
920 sic_code = decode(l_error_class_category(i), 'SIC', null, sic_code)
921 where org.party_id = l_error_party_id(i)
922 and effective_end_date is null
923 and actual_content_source = decode(P_DML_RECORD.actual_content_src, 'USER_ENTERED', 'SST',
924 '-INVALID_ACS-');
925
926
927
928 /* de-norm the primary address to parties */
929 /* Note: for error case, the party with the id will just be not found */
930 /* in update. Not necessary to filter out here. */
931
932 FND_FILE.PUT_LINE(FND_FILE.LOG, 'de-norm record with primary_flag = Y');
933
934 OPEN de_norm_cursor FOR
935 ' select cls_sg.party_id,
936 cls_sg.code_assignment_id,
937 cls_sg.class_category,
938 cls_sg.class_code,
939 cls_int.class_category
940 from hz_imp_classifics_sg cls_sg,
941 hz_imp_parties_sg p_sg,
942 hz_imp_classifics_int cls_int,
943 hz_code_assignments hca
944 where cls_sg.batch_id = :batch_id
945 and cls_sg.batch_mode_flag = :batch_mode_flag
946 and cls_sg.party_orig_system = :orig_system
947 and cls_sg.party_orig_system_reference
948 between :from_osr and :to_osr
949 and cls_sg.primary_flag = ''Y''
950 and cls_sg.action_flag = ''I''
951 and p_sg.action_flag(+) = ''U''
952 and p_sg.batch_id(+) = cls_sg.batch_id
953 and p_sg.batch_mode_flag(+) = :batch_mode_flag
954 and p_sg.party_orig_system(+) = :orig_system
955 and p_sg.party_orig_system_reference(+) = cls_sg.party_orig_system_reference
956 and cls_sg.class_category in ( ''CUSTOMER_CATEGORY'', ''SIC'', ''NACE'')
957 and cls_int.rowid = cls_sg.int_row_id
958 and hca.code_assignment_id=cls_sg.code_assignment_id
959 '
960 using P_DML_RECORD.BATCH_ID,
961 P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS,
962 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
963 P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS;
964
965 fetch de_norm_cursor BULK COLLECT INTO
966 l_update_party_id, l_update_code_assignment_id, l_update_class_category,
967 l_update_class_code, l_update_int_class_category;
968
969 close de_norm_cursor;
970
971 /* Get the max and min party_id for all records that need to redo classification
972 denormalization. These are used for calling HZ_MIXNM_CONC_DYNAMIC_PKG to
973 derive SST. */
974 OPEN pid_cursor FOR
975 'select max(party_id), min(party_id)
976 from hz_imp_classifics_sg
977 where batch_id = :batch_id
978 and batch_mode_flag = :batch_mode_flag
979 and party_orig_system = :orig_system
980 and party_orig_system_reference between :from_osr and :to_osr
981 and primary_flag = ''Y''
982 and action_flag = ''I''
983 and class_category in (''CUSTOMER_CATEGORY'',''SIC'',''NACE'')'
984 using P_DML_RECORD.BATCH_ID, P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS,
985 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
986
987 fetch pid_cursor INTO
988 l_update_max_party_id, l_update_min_party_id;
989
990
991 -- de-norm to hz_parties
992 forall i in 1..l_update_party_id.count
993 update hz_parties hz_pty
994 set category_code = decode(l_update_class_category(i), 'CUSTOMER_CATEGORY', l_update_class_code(i), category_code),
995 sic_code_type = decode(l_update_class_category(i), 'SIC', l_update_int_class_category(i), sic_code_type),
996 sic_code = decode(l_update_class_category(i), 'SIC', l_update_class_code(i), sic_code)
997 where hz_pty.party_id = l_update_party_id(i);
998
999 -- de-norm to hz_organization_profiles
1000 /* Bug 409189: when checking hz_organization_profiles.actual_content_source,
1001 there are three cases to consider:
1002 1. ACS is third party. Update org profile where ACS = '<third party>'
1003 2. ACS is USER_ENTERED and party has third party profile.
1004 Update org profile where ACS = 'USER_ENTERED'
1005 3. ACS is USER_ENTERED and party only has USER_ENTERED profile.
1006 Update org profile where ACS = 'SST'
1007 */
1008
1009 /* Take care of cases 1 and 2 */
1010 forall i in 1..l_update_party_id.count
1011 update hz_organization_profiles org
1012 set local_activity_code = decode(l_update_class_category(i), 'NACE', l_update_class_code(i), local_activity_code),
1013 sic_code_type = decode(l_update_class_category(i), 'SIC', l_update_int_class_category(i), sic_code_type),
1014 sic_code = decode(l_update_class_category(i), 'SIC', l_update_class_code(i), sic_code)
1015 where org.party_id = l_update_party_id(i)
1016 and effective_end_date is null
1017 and actual_content_source = P_DML_RECORD.actual_content_src;
1018
1019 /* Take care of cases 3 */
1020 forall i in 1..l_update_party_id.count
1021 update hz_organization_profiles org
1022 set local_activity_code = decode(l_update_class_category(i), 'NACE', l_update_class_code(i), local_activity_code),
1023 sic_code_type = decode(l_update_class_category(i), 'SIC', l_update_int_class_category(i), sic_code_type),
1024 sic_code = decode(l_update_class_category(i), 'SIC', l_update_class_code(i), sic_code)
1025 where org.party_id = l_update_party_id(i)
1026 and effective_end_date is null
1027 and actual_content_source = decode(P_DML_RECORD.actual_content_src, 'USER_ENTERED', 'SST',
1028 P_DML_RECORD.actual_content_src);
1029
1030 /* Run mix-n-match after updating org profiles if
1031 mix-n-match is enabled */
1032 IF l_update_party_id.count > 0 AND
1033 HZ_MIXNM_UTILITY.isMixNMatchEnabled('HZ_ORGANIZATION_PROFILES',l_entity_attr_id) = 'Y' THEN
1034 HZ_MIXNM_CONC_DYNAMIC_PKG.ImportUpdateOrgSST(P_DML_RECORD.actual_content_src,P_DML_RECORD.FROM_OSR,P_DML_RECORD.TO_OSR,P_DML_RECORD.BATCH_ID,P_DML_RECORD.request_id,P_DML_RECORD.program_id,P_DML_RECORD.program_application_id);
1035 END IF;
1036
1037 FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_insert_code_assignment (-)');
1038
1039 EXCEPTION
1040
1041 WHEN DUP_VAL_ON_INDEX THEN
1042 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert code assignment dup val exception: ' || SQLERRM);
1043 ROLLBACK to process_insert_codeassigns_pvt;
1044
1045 populate_error_table(P_DML_RECORD, 'Y', SQLERRM);
1046 x_return_status := FND_API.G_RET_STS_ERROR;
1047
1048 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1049 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1050 FND_MSG_PUB.ADD;
1051
1052 WHEN OTHERS THEN
1053 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert code assignment other exception: ' || SQLERRM);
1054 ROLLBACK to process_insert_codeassigns_pvt;
1055
1056 populate_error_table(P_DML_RECORD, 'N', SQLERRM);
1057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1058
1059 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1060 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1061 FND_MSG_PUB.ADD;
1062
1063
1064 END process_insert_code_assignment;
1065
1066
1067
1068 /********************************************************************************
1069 *
1070 * process_update_code_assignment
1071 *
1072 ********************************************************************************/
1073
1074 PROCEDURE process_update_code_assignment (
1075 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
1076 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
1077 ,X_MSG_COUNT OUT NOCOPY NUMBER
1078 ,X_MSG_DATA OUT NOCOPY VARCHAR2
1079 ) IS
1080
1081 c_handle_update RefCurType;
1082
1083 l_update_sql varchar2(20000) :=
1084 'SELECT
1085 cai.rowid,
1086 nvl(cai.party_orig_system, cas.party_orig_system) party_orig_system,
1087 nvl(cai.party_orig_system_reference, cas.party_orig_system_reference) party_orig_system_reference,
1088
1089 -- code assignment columns
1090 cas.code_assignment_id,
1091 nvl(cai.start_date_active, sysdate) start_date_active,
1092 decode(cai.end_date_active, :G_MISS_DATE, null) end_date_active,
1093 decode(cai.rank, :G_MISS_NUM, null, cai.rank) rank,
1094
1095 -- errors
1096 cas.error_flag,
1097 ''T'' dss_security_errors,
1098 decode(nvl(cai.insert_update_flag, cas.action_flag), cas.action_flag, ''Y'', null) action_mismatch_error,
1099 ''Y'' missing_parent_error,
1100 decode(cai.end_date_active, null, ''Y'',
1101 decode(sign(cai.end_date_active - nvl(cai.start_date_active, sysdate)), -1, null, ''Y'')) start_end_date_error,
1102 decode(cai.START_DATE_ACTIVE, :G_MISS_DATE, null, ''Y'') start_date_error,
1103
1104 decode(tc1.a, 0, ''Y'') identical_classcode_error,
1105 decode(tc2.a, 0, ''Y'') multi_assign_error
1106 FROM HZ_IMP_CLASSIFICS_INT cai,
1107 HZ_IMP_CLASSIFICS_SG cas,
1108 (select 0 a from dual union all select 1 a from dual) tc1,
1109 (select 0 a from dual union all select 1 a from dual) tc2
1110 WHERE
1111 cas.batch_id = :P_BATCH_ID
1112 AND cas.batch_mode_flag = :P_BATCH_MODE_FLAG
1113 AND cas.party_orig_system = :P_WU_OS
1114 AND cas.party_orig_system_reference between :P_FROM_OSR and :P_TO_OSR
1115 AND cai.rowid = cas.int_row_id
1116
1117 and tc1.a = (select count(*) -- check date overlap, 0 indicates no error
1118 from hz_code_assignments c
1119 where c.code_assignment_id <> cas.code_assignment_id
1120 and c.class_category = cas.class_category
1121 and c.class_code = cas.class_code
1122 and c.owner_table_name = ''HZ_PARTIES''
1123 and c.owner_table_id = cas.party_id
1124 and nvl(cai.end_date_active, :l_no_end_date) >=
1125 c.start_date_active
1126 and nvl(c.end_date_active, :l_no_end_date) >=
1127 cai.start_date_active
1128 and c.actual_content_source = :l_content_source_type --(bug 4079902)
1129 --and c.content_source_type = :l_content_source_type
1130 and c.status = ''A''
1131 and rownum = 1)
1132 and tc2.a = (select count(*) -- check multi class code, 0 indicates no error
1133 from hz_code_assignments c_assign,
1134 hz_class_categories c_cat
1135 where c_cat.class_category = c_assign.class_category
1136 and c_cat.class_category = cas.class_category
1137 and c_assign.owner_table_id = cas.party_id
1138 and c_assign.owner_table_name = ''HZ_PARTIES''
1139 and c_cat.allow_multi_assign_flag = ''N''
1140 and nvl(cai.end_date_active, :l_no_end_date) >=
1141 c_assign.start_date_active
1142 and nvl(c_assign.end_date_active, :l_no_end_date) >=
1143 cai.start_date_active
1144 --and c_assign.content_source_type = :l_content_source_type (bug 4079902)
1145 and c_assign.actual_content_source = :l_content_source_type
1146 and status = ''A''
1147 and rownum = 1)
1148 AND cas.ACTION_FLAG = ''U''
1149 ';
1150
1151
1152 l_where_first_run_sql varchar2(35) := ' AND cai.interface_status is null';
1153 l_where_rerun_sql varchar2(35) := ' AND cai.interface_status = ''C''';
1154
1155 l_dml_exception varchar2(1) := 'N';
1156
1157 BEGIN
1158
1159 FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_update_code_assignment (+)');
1160
1161 savepoint process_update_codeassigns_pvt;
1162
1163 FND_MSG_PUB.initialize;
1164
1165 --Initialize API return status to success.
1166 x_return_status := FND_API.G_RET_STS_SUCCESS;
1167
1168 IF P_DML_RECORD.RERUN = 'N' THEN
1169 -- First Run
1170 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'First run');
1171 OPEN c_handle_update FOR l_update_sql || l_where_first_run_sql
1172 USING P_DML_RECORD.GMISS_DATE, P_DML_RECORD.GMISS_NUM, P_DML_RECORD.GMISS_DATE,
1173 P_DML_RECORD.batch_id, P_DML_RECORD.batch_mode_flag,
1174 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
1175 l_no_end_date, l_no_end_date, P_DML_RECORD.ACTUAL_CONTENT_SRC,
1176 l_no_end_date, l_no_end_date, P_DML_RECORD.ACTUAL_CONTENT_SRC;
1177
1178 ELSE
1179 -- Rerun
1180 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Re-run');
1181 OPEN c_handle_update FOR l_update_sql || l_where_rerun_sql
1182 USING P_DML_RECORD.GMISS_DATE, P_DML_RECORD.GMISS_NUM, P_DML_RECORD.GMISS_DATE,
1183 P_DML_RECORD.batch_id, P_DML_RECORD.batch_mode_flag,
1184 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
1185 l_no_end_date, l_no_end_date, P_DML_RECORD.ACTUAL_CONTENT_SRC,
1186 l_no_end_date, l_no_end_date, P_DML_RECORD.ACTUAL_CONTENT_SRC;
1187
1188 END IF;
1189
1190 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Fetch update cursor');
1191 FETCH c_handle_update BULK COLLECT INTO
1192 l_row_id,
1193 l_party_orig_system,
1194 l_party_orig_system_reference,
1195
1196 -- code assignment columns
1197 l_code_assignment_id,
1198 l_start_date_active,
1199 l_end_date_active,
1200 l_rank,
1201
1202 -- errors
1203 l_error_flag,
1204 l_dss_security_errors,
1205 l_action_mismatch_errors,
1206 l_missing_parent_error,
1207 l_start_end_date_errors,
1208 l_start_date_errors,
1209 l_identical_classcode_errors,
1210 l_multi_assign_errors;
1211
1212 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'count = ' || l_code_assignment_id.count);
1213
1214 /*** Do DSS security validation based on profile ***/
1215 IF NVL(FND_PROFILE.value('HZ_IMP_DSS_SECURITY'), 'N') = 'Y' THEN
1216 validate_DSS_security;
1217 END IF;
1218
1219 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Update code assignment');
1220
1221 BEGIN
1222
1223 ForAll j in 1..l_code_assignment_id.count SAVE EXCEPTIONS
1224 update hz_code_assignments set
1225 /* comment out for bug fix 3546566
1226 START_DATE_ACTIVE =
1227 nvl(l_start_date_active(j), start_date_active),
1228 END_DATE_ACTIVE =
1229 DECODE(l_end_date_active(j),
1230 NULL, end_date_active,
1231 P_DML_RECORD.GMISS_DATE, NULL,
1232 l_end_date_active(j)),
1233 */
1234 RANK = l_rank(j),
1235 LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
1236 LAST_UPDATE_DATE = trunc(P_DML_RECORD.SYSDATE),
1237 LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
1238 OBJECT_VERSION_NUMBER =
1239 DECODE(OBJECT_VERSION_NUMBER,
1240 NULL, 1,
1241 OBJECT_VERSION_NUMBER+1),
1242 REQUEST_ID = P_DML_RECORD.REQUEST_ID,
1243 PROGRAM_APPLICATION_ID = P_DML_RECORD.PROGRAM_APPLICATION_ID,
1244 PROGRAM_ID = P_DML_RECORD.PROGRAM_ID,
1245 PROGRAM_UPDATE_DATE = P_DML_RECORD.SYSDATE
1246 where
1247 code_assignment_id = l_code_assignment_id(j)
1248 and l_dss_security_errors(j) = 'T'
1249 and l_action_mismatch_errors(j) is not null
1250 and l_start_end_date_errors(j) is not null
1251 and l_start_date_errors(j) is not null
1252 and l_identical_classcode_errors(j) is not null
1253 and l_multi_assign_errors(j) is not null;
1254
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Other exceptions');
1258 l_dml_exception := 'Y';
1259
1260 FOR k IN 1..l_code_assignment_id.count LOOP
1261 FND_FILE.PUT_LINE(FND_FILE.LOG, 'dml_errors BULK_ROWCOUNT = ' || SQL%BULK_ROWCOUNT(k));
1262 END LOOP;
1263
1264 END;
1265
1266 report_errors(P_DML_RECORD, 'U', l_dml_exception);
1267
1268 CLOSE c_handle_update;
1269
1270 FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_update_code_assignment (-)');
1271
1272 EXCEPTION
1273
1274 WHEN OTHERS THEN
1275 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Update code assignment other exception: ' || SQLERRM);
1276
1277 ROLLBACK to process_update_codeassigns_pvt;
1278
1279 populate_error_table(P_DML_RECORD, 'N', SQLERRM);
1280
1281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1282
1283 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1284 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1285 FND_MSG_PUB.ADD;
1286
1287 END process_update_code_assignment;
1288
1289
1290 /********************************************************************************
1291 *
1292 * load_code_assignments
1293 *
1294 ********************************************************************************/
1295
1296 PROCEDURE load_code_assignments (
1297 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
1298 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
1299 ,X_MSG_COUNT OUT NOCOPY NUMBER
1300 ,X_MSG_DATA OUT NOCOPY VARCHAR2
1301 ) IS
1302
1303 l_return_status VARCHAR2(30);
1304 l_msg_data VARCHAR2(2000);
1305 l_msg_count NUMBER;
1306
1307 BEGIN
1308
1309 savepoint load_code_assignments_pvt;
1310 FND_MSG_PUB.initialize;
1311 FND_FILE.PUT_LINE(FND_FILE.LOG, 'load_code_assignments (+)');
1312
1313 --Initialize API return status to success.
1314 x_return_status := FND_API.G_RET_STS_SUCCESS;
1315
1316 l_no_end_date := TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS');
1317
1318
1319 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.os = ' || p_dml_record.os) ;
1320 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.from_osr = ' || p_dml_record.from_osr ) ;
1321 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.to_osr = ' || p_dml_record.to_osr) ;
1322 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.actual_content_src = ' || p_dml_record.actual_content_src) ;
1323 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.rerun = ' || p_dml_record.rerun) ;
1324 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.error_limit = ' || p_dml_record.error_limit) ;
1325 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.batch_mode_flag = ' || p_dml_record.batch_mode_flag) ;
1326 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.user_id = ' || p_dml_record.user_id ) ;
1327 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.last_update_login = ' || p_dml_record.last_update_login) ;
1328 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.program_id = ' || p_dml_record.program_id) ;
1329 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.program_application_id = ' || p_dml_record.program_application_id) ;
1330 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.request_id = ' || p_dml_record.request_id) ;
1331 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.application_id = ' || p_dml_record.application_id) ;
1332 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.gmiss_char = ' || p_dml_record.gmiss_char) ;
1333 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.gmiss_num = ' || to_char(p_dml_record.gmiss_num)) ;
1334 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.gmiss_date = ' || to_char(p_dml_record.gmiss_date, 'MM-DD-YYYY')) ;
1335 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.flex_validation = ' || p_dml_record.flex_validation) ;
1336 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.allow_disabled_lookup = ' || p_dml_record.allow_disabled_lookup) ;
1337 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.profile_version = ' || p_dml_record.profile_version) ;
1338 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_no_end_date = ' || l_no_end_date) ;
1339
1340
1341 process_insert_code_assignment(
1342 P_DML_RECORD => P_DML_RECORD
1343 ,x_return_status => x_return_status
1344 ,x_msg_count => x_msg_count
1345 ,x_msg_data => x_msg_data
1346 );
1347
1348 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1349 return;
1350 END IF;
1351
1352 process_update_code_assignment(
1353 P_DML_RECORD => P_DML_RECORD
1354 ,x_return_status => x_return_status
1355 ,x_msg_count => x_msg_count
1356 ,x_msg_data => x_msg_data
1357 );
1358
1359 FND_FILE.PUT_LINE(FND_FILE.LOG, 'load_code_assignments (-)');
1360
1361 END load_code_assignments;
1362 END HZ_IMP_LOAD_CODE_ASSIGNMENTS;