[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_CREDITRATINGS_PKG
Source
1 PACKAGE BODY HZ_IMP_LOAD_CREDITRATINGS_PKG AS
2 /*$Header: ARHLCRDB.pls 120.26 2006/01/17 08:33:16 vravicha noship $*/
3
4 --------------------------------------
5 -- declaration of private global varibles
6 --------------------------------------
7
8 g_debug_count NUMBER := 0;
9 --g_debug BOOLEAN := FALSE;
10
11 l_ACTION_FLAG ACTION_FLAG;
12 l_AVG_HIGH_CREDIT AVG_HIGH_CREDIT;
13 l_BANKRUPTCY_IND BANKRUPTCY_IND;
14 l_BUSINESS_DISCONTINUED BUSINESS_DISCONTINUED;
15 l_CLAIMS_IND CLAIMS_IND;
16 l_COMMENTS COMMENTS;
17 l_CREATED_BY_MODULE CREATED_BY_MODULE;
18 l_CREDIT_SCORE CREDIT_SCORE;
19 l_CREDIT_SCORE_AGE CREDIT_SCORE_AGE;
20 l_CREDIT_SCORE_CLASS CREDIT_SCORE_CLASS;
21 l_CREDIT_SCORE_COMMENTARY CREDIT_SCORE_COMMENTARY;
22 l_CREDIT_SCORE_COMMENTARY10 CREDIT_SCORE_COMMENTARY10;
23 l_CREDIT_SCORE_COMMENTARY2 CREDIT_SCORE_COMMENTARY2;
24 l_CREDIT_SCORE_COMMENTARY3 CREDIT_SCORE_COMMENTARY3;
25 l_CREDIT_SCORE_COMMENTARY4 CREDIT_SCORE_COMMENTARY4;
26 l_CREDIT_SCORE_COMMENTARY5 CREDIT_SCORE_COMMENTARY5;
27 l_CREDIT_SCORE_COMMENTARY6 CREDIT_SCORE_COMMENTARY6;
28 l_CREDIT_SCORE_COMMENTARY7 CREDIT_SCORE_COMMENTARY7;
29 l_CREDIT_SCORE_COMMENTARY8 CREDIT_SCORE_COMMENTARY8;
30 l_CREDIT_SCORE_COMMENTARY9 CREDIT_SCORE_COMMENTARY9;
31 l_CREDIT_SCORE_DATE CREDIT_SCORE_DATE;
32 l_CREDIT_SCORE_INCD_DEFAULT CREDIT_SCORE_INCD_DEFAULT;
33 l_CREDIT_SCORE_NATL_PERCENTILE CREDIT_SCORE_NATL_PERCENTILE;
34 l_CREDIT_SCORE_OVERRIDE_CODE CREDIT_SCORE_OVERRIDE_CODE;
35 l_CRIMINAL_PROCEEDING_IND CRIMINAL_PROCEEDING_IND;
36 l_CR_SCR_CLAS_EXPL CR_SCR_CLAS_EXPL;
37 l_DEBARMENTS_COUNT DEBARMENTS_COUNT;
38 l_DEBARMENTS_DATE DEBARMENTS_DATE;
39 l_DEBARMENT_IND DEBARMENT_IND;
40 l_DELQ_PMT_PCTG_FOR_ALL_FIRMS DELQ_PMT_PCTG_FOR_ALL_FIRMS;
41 l_DELQ_PMT_RNG_PRCNT DELQ_PMT_RNG_PRCNT;
42 l_DESCRIPTION DESCRIPTION;
43 l_DET_HISTORY_IND DET_HISTORY_IND;
44 l_DISASTER_IND DISASTER_IND;
45 l_FAILURE_SCORE FAILURE_SCORE;
46 l_FAILURE_SCORE_AGE FAILURE_SCORE_AGE;
47 l_FAILURE_SCORE_CLASS FAILURE_SCORE_CLASS;
48 l_FAILURE_SCORE_COMMENTARY FAILURE_SCORE_COMMENTARY;
49 l_FAILURE_SCORE_COMMENTARY10 FAILURE_SCORE_COMMENTARY10;
50 l_FAILURE_SCORE_COMMENTARY2 FAILURE_SCORE_COMMENTARY2;
51 l_FAILURE_SCORE_COMMENTARY3 FAILURE_SCORE_COMMENTARY3;
52 l_FAILURE_SCORE_COMMENTARY4 FAILURE_SCORE_COMMENTARY4;
53 l_FAILURE_SCORE_COMMENTARY5 FAILURE_SCORE_COMMENTARY5;
54 l_FAILURE_SCORE_COMMENTARY6 FAILURE_SCORE_COMMENTARY6;
55 l_FAILURE_SCORE_COMMENTARY7 FAILURE_SCORE_COMMENTARY7;
56 l_FAILURE_SCORE_COMMENTARY8 FAILURE_SCORE_COMMENTARY8;
57 l_FAILURE_SCORE_COMMENTARY9 FAILURE_SCORE_COMMENTARY9;
58 l_FAILURE_SCORE_DATE FAILURE_SCORE_DATE;
59 l_FAILURE_SCORE_INCD_DEFAULT FAILURE_SCORE_INCD_DEFAULT;
60 l_FAILURE_SCORE_NATNL_PERC FAILURE_SCORE_NATNL_PERCENTILE;
61 l_FAILURE_SCORE_OVERRIDE_CODE FAILURE_SCORE_OVERRIDE_CODE;
62 l_FINCL_EMBT_IND FINCL_EMBT_IND;
63 l_FINCL_LGL_EVENT_IND FINCL_LGL_EVENT_IND;
64 l_GLOBAL_FAILURE_SCORE GLOBAL_FAILURE_SCORE;
65 l_HIGH_CREDIT HIGH_CREDIT;
66 l_HIGH_RNG_DELQ_SCR HIGH_RNG_DELQ_SCR;
67 l_INSERT_UPDATE_FLAG INSERT_UPDATE_FLAG;
68 l_INTERFACE_STATUS INTERFACE_STATUS;
69 l_JUDGEMENT_IND JUDGEMENT_IND;
70 l_LIEN_IND LIEN_IND;
71 l_LOW_RNG_DELQ_SCR LOW_RNG_DELQ_SCR;
72 l_MAXIMUM_CREDIT_CURRENCY_CODE MAXIMUM_CREDIT_CURRENCY_CODE;
73 l_MAXIMUM_CREDIT_RECOMM MAXIMUM_CREDIT_RECOMMENDATION;
74 l_NEGV_PMT_EXPL NEGV_PMT_EXPL;
75 l_NO_TRADE_IND NO_TRADE_IND;
76 l_NUM_PRNT_BKCY_CONVS NUM_PRNT_BKCY_CONVS;
77 l_NUM_PRNT_BKCY_FILING NUM_PRNT_BKCY_FILING;
78 l_NUM_SPCL_EVENT NUM_SPCL_EVENT;
79 l_NUM_TRADE_EXPERIENCES NUM_TRADE_EXPERIENCES;
80 l_OPRG_SPEC_EVNT_IND OPRG_SPEC_EVNT_IND;
81 l_OTHER_SPEC_EVNT_IND OTHER_SPEC_EVNT_IND;
82 l_PARTY_ID PARTY_ID;
83 l_parent_party_id PARENT_PARTY_ID;
84 l_PARTY_ORIG_SYSTEM PARTY_ORIG_SYSTEM;
85 l_PARTY_ORIG_SYSTEM_REFERENCE PARTY_ORIG_SYSTEM_REFERENCE;
86 l_PAYDEX_COMMENT PAYDEX_COMMENT;
87 l_PAYDEX_FIRM_COMMENT PAYDEX_FIRM_COMMENT;
88 l_PAYDEX_FIRM_DAYS PAYDEX_FIRM_DAYS;
89 l_PAYDEX_INDUSTRY_COMMENT PAYDEX_INDUSTRY_COMMENT;
90 l_PAYDEX_INDUSTRY_DAYS PAYDEX_INDUSTRY_DAYS;
91 l_PAYDEX_NORM PAYDEX_NORM;
92 l_PAYDEX_SCORE PAYDEX_SCORE;
93 l_PAYDEX_THREE_MONTHS_AGO PAYDEX_THREE_MONTHS_AGO;
94 l_PRNT_BKCY_CHAPTER_CONV PRNT_BKCY_CHAPTER_CONV;
95 l_PRNT_BKCY_CONV_DATE PRNT_BKCY_CONV_DATE;
96 l_PRNT_BKCY_FILG_DATE PRNT_BKCY_FILG_DATE;
97 l_PRNT_BKCY_FILG_CHAPTER PRNT_BKCY_FILG_CHAPTER;
98 l_PRNT_BKCY_FILG_TYPE PRNT_BKCY_FILG_TYPE;
99 l_PRNT_HQ_BKCY_IND PRNT_HQ_BKCY_IND;
100 l_PUB_REC_EXPL PUB_REC_EXPL;
101 l_RATED_AS_OF_DATE RATED_AS_OF_DATE;
102 l_RATING RATING;
103 l_RATING_ORGANIZATION RATING_ORGANIZATION;
104 l_SECURED_FLNG_IND SECURED_FLNG_IND;
105 l_SLOW_TRADE_EXPL SLOW_TRADE_EXPL;
106 l_SPCL_EVENT_COMMENT SPCL_EVENT_COMMENT;
107 l_SPCL_EVENT_UPDATE_DATE SPCL_EVENT_UPDATE_DATE;
108 l_SPCL_EVNT_TXT SPCL_EVNT_TXT;
109 l_CREDIT_RATING_ID CREDIT_RATING_ID;
110 l_SUIT_IND SUIT_IND;
111
112 -- variables for error flags
113 l_SUIT_IND_err FLAG_ERROR;
114 l_BANKRUPTCY_IND_err FLAG_ERROR;
115 l_DEBARMENT_IND_err FLAG_ERROR;
116 l_FINCL_EMBT_IND_err FLAG_ERROR;
117 l_NO_TRADE_IND_err FLAG_ERROR;
118 l_JUDGEMENT_IND_err FLAG_ERROR;
119 l_LIEN_IND_err FLAG_ERROR;
120 l_action_flag_err FLAG_ERROR;
121 l_error_flag FLAG_ERROR;
122
123 l_CREDIT_SCR_OVERRIDE_CODE_err LOOKUP_ERROR;
124 l_FAILURE_SCR_COMMENTARY_err LOOKUP_ERROR;
125 l_FAILURE_SCR_COMMENTARY2_err LOOKUP_ERROR;
126 l_FAILURE_SCR_COMMENTARY3_err LOOKUP_ERROR;
127 l_FAILURE_SCR_COMMENTARY4_err LOOKUP_ERROR;
128 l_FAILURE_SCR_COMMENTARY5_err LOOKUP_ERROR;
129 l_FAILURE_SCR_COMMENTARY6_err LOOKUP_ERROR;
130 l_FAILURE_SCR_COMMENTARY7_err LOOKUP_ERROR;
131 l_FAILURE_SCR_COMMENTARY8_err LOOKUP_ERROR;
132 l_FAILURE_SCR_COMMENTARY9_err LOOKUP_ERROR;
133 l_FAILURE_SCR_COMMENTARY10_err LOOKUP_ERROR;
134 l_FAILURE_SCR_OVERRIDE_CD_err LOOKUP_ERROR;
135 l_CREDIT_SCR_COMMENTARY_err LOOKUP_ERROR;
136 l_CREDIT_SCR_COMMENTARY2_err LOOKUP_ERROR;
137 l_CREDIT_SCR_COMMENTARY3_err LOOKUP_ERROR;
138 l_CREDIT_SCR_COMMENTARY4_err LOOKUP_ERROR;
139 l_CREDIT_SCR_COMMENTARY5_err LOOKUP_ERROR;
140 l_CREDIT_SCR_COMMENTARY6_err LOOKUP_ERROR;
141 l_CREDIT_SCR_COMMENTARY7_err LOOKUP_ERROR;
142 l_CREDIT_SCR_COMMENTARY8_err LOOKUP_ERROR;
143 l_CREDIT_SCR_COMMENTARY9_err LOOKUP_ERROR;
144 l_CREDIT_SCR_COMMENTARY10_err LOOKUP_ERROR;
145 l_PRNT_HQ_BKCY_IND_err LOOKUP_ERROR;
146 l_MAX_CREDIT_CURR_CODE_err LOOKUP_ERROR;
147
148 l_createdby_errors LOOKUP_ERROR;
149
150
151 -- local variables
152 -- Keep track of rows that do not get inserted or updated successfully.
153 -- Those are the rows that have some validation or DML errors.
154 -- Use this when inserting into or updating other tables so that we
155 -- do not need to check all the validation arrays.
156 l_num_row_processed NUMBER_COLUMN ;
157 l_row_id ROWID;
158 l_errm varchar2(100);
159
160 --------------------------------------
161 -- forward declaration of private procedures and functions
162 --------------------------------------
163
164 /*PROCEDURE enable_debug;
165 PROCEDURE disable_debug;
166 */
167 ----------------------------------------------
168 PROCEDURE process_cr_ins (
169 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
170 ,x_return_status OUT NOCOPY VARCHAR2
171 ,x_msg_count OUT NOCOPY NUMBER
172 ,x_msg_data OUT NOCOPY VARCHAR2 );
173 ----------------------------------------------
174 procedure report_errors (
175 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
176 ,P_ACTION IN VARCHAR2
177 ,P_DML_EXCEPTION IN VARCHAR2);
178 ----------------------------------------------
179 procedure open_upd_cursor (
180 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
181 ,update_cursor IN OUT NOCOPY update_cursor_type);
182 ----------------------------------------------
183 procedure process_cr_upd (
184 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
185 ,x_return_status OUT NOCOPY VARCHAR2
186 ,x_msg_count OUT NOCOPY NUMBER
187 ,x_msg_data OUT NOCOPY VARCHAR2 );
188
189 PROCEDURE populate_error_table(
190 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
191 P_DUP_VAL_EXP IN VARCHAR2,
192 P_SQL_ERRM IN VARCHAR2 );
193
194 --------------------------------------
195 -- private procedures and functions
196 --------------------------------------
197 --------------------------------------
198 /*PROCEDURE enable_debug IS
199 BEGIN
200 g_debug_count := g_debug_count + 1;
201
202 IF g_debug_count = 1 THEN
203 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
204 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
205 THEN
206 hz_utility_v2pub.enable_debug;
207 g_debug := TRUE;
208 END IF;
209 END IF;
210 END enable_debug; -- end procedure
211 */
212 --------------------------------------
213 --------------------------------------
214 /*PROCEDURE disable_debug IS
215 BEGIN
216
217 IF g_debug THEN
218 g_debug_count := g_debug_count - 1;
219 IF g_debug_count = 0 THEN
220 hz_utility_v2pub.disable_debug;
221 g_debug := FALSE;
222 END IF;
223 END IF;
224
225 END disable_debug;
226 */
227 --------------------------------------
228 --------------------------------------
229 /**
230 * PRIVATE PROCEDURE open_upd_cursor
231 *
232 * DESCRIPTION
233 * Prepares the cursor statement
234 * for handling the update
235 *
236 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
237 *
238 * MODIFICATION HISTORY
239 *
240 * 07-15-2003 Srikanth o Created.
241 *
242 */
243 --------------------------------------
244 PROCEDURE open_upd_cursor (
245 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
246 ,update_cursor IN OUT NOCOPY update_cursor_type) IS
247 l_sql_str1 VARCHAR2(32767) :=
248 'SELECT
249 crsg.action_flag,
250 crsg.CREDIT_RATING_ID,
251 crint.ROWID,
252 crint.AVG_HIGH_CREDIT,
253 crint.BANKRUPTCY_IND,
254 crint.BUSINESS_DISCONTINUED,
255 crint.CLAIMS_IND,
256 crint.COMMENTS,
257 crint.CREATED_BY_MODULE,
258 crint.CREDIT_SCORE,
259 crint.CREDIT_SCORE_AGE,
260 crint.CREDIT_SCORE_CLASS,
261 crint.CREDIT_SCORE_COMMENTARY,
262 crint.CREDIT_SCORE_COMMENTARY10,
263 crint.CREDIT_SCORE_COMMENTARY2,
264 crint.CREDIT_SCORE_COMMENTARY3,
265 crint.CREDIT_SCORE_COMMENTARY4,
266 crint.CREDIT_SCORE_COMMENTARY5,
267 crint.CREDIT_SCORE_COMMENTARY6,
268 crint.CREDIT_SCORE_COMMENTARY7,
269 crint.CREDIT_SCORE_COMMENTARY8,
270 crint.CREDIT_SCORE_COMMENTARY9,
271 crint.CREDIT_SCORE_DATE,
272 crint.CREDIT_SCORE_INCD_DEFAULT,
273 crint.CREDIT_SCORE_NATL_PERCENTILE,
274 crint.CREDIT_SCORE_OVERRIDE_CODE,
275 crint.CRIMINAL_PROCEEDING_IND,
276 crint.CR_SCR_CLAS_EXPL,
277 crint.DEBARMENTS_COUNT,
278 crint.DEBARMENTS_DATE,
279 crint.DEBARMENT_IND,
280 crint.DELQ_PMT_PCTG_FOR_ALL_FIRMS,
281 crint.DELQ_PMT_RNG_PRCNT,
282 crint.DESCRIPTION,
283 crint.DET_HISTORY_IND,
284 crint.DISASTER_IND,
285 crint.FAILURE_SCORE,
286 crint.FAILURE_SCORE_AGE,
287 crint.FAILURE_SCORE_CLASS,
288 crint.FAILURE_SCORE_COMMENTARY,
289 crint.FAILURE_SCORE_COMMENTARY10,
290 crint.FAILURE_SCORE_COMMENTARY2,
291 crint.FAILURE_SCORE_COMMENTARY3,
292 crint.FAILURE_SCORE_COMMENTARY4,
293 crint.FAILURE_SCORE_COMMENTARY5,
294 crint.FAILURE_SCORE_COMMENTARY6,
295 crint.FAILURE_SCORE_COMMENTARY7,
296 crint.FAILURE_SCORE_COMMENTARY8,
297 crint.FAILURE_SCORE_COMMENTARY9,
298 crint.FAILURE_SCORE_DATE,
299 crint.FAILURE_SCORE_INCD_DEFAULT,
300 crint.FAILURE_SCORE_NATNL_PERCENTILE,
301 crint.FAILURE_SCORE_OVERRIDE_CODE,
302 crint.FINCL_EMBT_IND,
303 crint.FINCL_LGL_EVENT_IND,
304 crint.GLOBAL_FAILURE_SCORE,
305 crint.HIGH_CREDIT,
306 crint.HIGH_RNG_DELQ_SCR,
307 crint.INSERT_UPDATE_FLAG,
308 crint.JUDGEMENT_IND,
309 crint.LIEN_IND,
310 crint.LOW_RNG_DELQ_SCR,
311 crint.MAXIMUM_CREDIT_CURRENCY_CODE,
312 crint.MAXIMUM_CREDIT_RECOMMENDATION,
313 crint.NEGV_PMT_EXPL,
314 crint.NO_TRADE_IND,
315 crint.NUM_PRNT_BKCY_CONVS,
316 crint.NUM_PRNT_BKCY_FILING,
317 crint.NUM_SPCL_EVENT,
318 crint.NUM_TRADE_EXPERIENCES,
319 crint.OPRG_SPEC_EVNT_IND,
320 crint.OTHER_SPEC_EVNT_IND,
321 crint.PARTY_ORIG_SYSTEM,
322 crint.PARTY_ORIG_SYSTEM_REFERENCE,
323 crint.PAYDEX_COMMENT,
324 crint.PAYDEX_FIRM_COMMENT,
325 crint.PAYDEX_FIRM_DAYS,
326 crint.PAYDEX_INDUSTRY_COMMENT,
327 crint.PAYDEX_INDUSTRY_DAYS,
328 crint.PAYDEX_NORM,
329 crint.PAYDEX_SCORE,
330 crint.PAYDEX_THREE_MONTHS_AGO,
331 crint.PRNT_BKCY_CHAPTER_CONV,
332 crint.PRNT_BKCY_CONV_DATE,
333 crint.PRNT_BKCY_FILG_CHAPTER,
334 crint.PRNT_BKCY_FILG_DATE,
335 crint.PRNT_BKCY_FILG_TYPE,
336 crint.PRNT_HQ_BKCY_IND,
337 crint.PUB_REC_EXPL,
338 trunc(crint.RATED_AS_OF_DATE),
339 crint.RATING,
340 crint.RATING_ORGANIZATION,
341 crint.SECURED_FLNG_IND,
342 crint.SLOW_TRADE_EXPL,
343 crint.SPCL_EVENT_COMMENT,
344 crint.SPCL_EVENT_UPDATE_DATE,
345 crint.SPCL_EVNT_TXT,
346 crint.SUIT_IND,
347 decode(crint.SUIT_IND,''Y'',''Y'',''N'',''N'',NULL,''Z'',:G_MISS_CHAR,''Z'',NULL) SUIT_IND_ERR,
348 decode (crint.BANKRUPTCY_IND, ''Y'',''Y'',''N'',''N'',NULL,''Z'',:G_MISS_CHAR,''Z'',NULL) BANKRUPTCY_IND_ERR,
349 decode(crint.DEBARMENT_IND, ''Y'',''Y'',''N'',''N'',NULL,''Z'',:G_MISS_CHAR,''Z'',NULL) DEBARMENT_IND_ERR,
350 decode(crint.FINCL_EMBT_IND, ''Y'',''Y'',''N'',''N'',NULL,''Z'',:G_MISS_CHAR,''Z'',NULL) FINCL_EMBT_IND_ERR,
351 decode(crint.NO_TRADE_IND, ''Y'',''Y'',''N'',''N'',NULL,''Z'',:G_MISS_CHAR,''Z'',NULL) NO_TRADE_IND_ERR,
352 decode(crint.JUDGEMENT_IND, ''Y'',''Y'',''N'',''N'',NULL,''Z'',:G_MISS_CHAR,''Z'',NULL) JUDGEMENT_IND_ERR,
353 decode(crint.LIEN_IND, ''Y'',''Y'',''N'',''N'',NULL,''Z'',:G_MISS_CHAR,''Z'',NULL) LIEN_IND_ERR,
354 nvl2(nullif(crint.CREDIT_SCORE_OVERRIDE_CODE, :G_MISS_CHAR), nvl2(cr_l1.lookup_code,''Y'',NULL),''Z'') CREDIT_SCR_OVERRIDE_CD_err,
355 nvl2(nullif(crint.FAILURE_SCORE_COMMENTARY, :G_MISS_CHAR), nvl2(cr_l2.lookup_code,''Y'',NULL),''Z'') FAILURE_SCR_COMM_err,
356 nvl2(nullif(crint.FAILURE_SCORE_COMMENTARY2, :G_MISS_CHAR), nvl2(cr_l3.lookup_code,''Y'',NULL),''Z'') FAILURE_SCR_COMM2_err,
357 nvl2(nullif(crint.FAILURE_SCORE_COMMENTARY3, :G_MISS_CHAR), nvl2(cr_l4.lookup_code,''Y'',NULL),''Z'') FAILURE_SCR_COMM3_err,
358 nvl2(nullif(crint.FAILURE_SCORE_COMMENTARY4, :G_MISS_CHAR), nvl2(cr_l5.lookup_code,''Y'',NULL),''Z'') FAILURE_SCR_COMM4_err,
359 nvl2(nullif(crint.FAILURE_SCORE_COMMENTARY5, :G_MISS_CHAR), nvl2(cr_l6.lookup_code,''Y'',NULL),''Z'') FAILURE_SCR_COMM5_err,
360 nvl2(nullif(crint.FAILURE_SCORE_COMMENTARY6, :G_MISS_CHAR), nvl2(cr_l7.lookup_code,''Y'',NULL),''Z'') FAILURE_SCR_COMM6_err,
361 nvl2(nullif(crint.FAILURE_SCORE_COMMENTARY7, :G_MISS_CHAR), nvl2(cr_l8.lookup_code,''Y'',NULL),''Z'') FAILURE_SCR_COMM7_err,
362 nvl2(nullif(crint.FAILURE_SCORE_COMMENTARY8, :G_MISS_CHAR), nvl2(cr_l9.lookup_code,''Y'',NULL),''Z'') FAILURE_SCR_COMM8_err,
363 nvl2(nullif(crint.FAILURE_SCORE_COMMENTARY9, :G_MISS_CHAR), nvl2(cr_l10.lookup_code,''Y'',NULL),''Z'') FAILURE_SCR_COMM9_err,
364 nvl2(nullif(crint.FAILURE_SCORE_COMMENTARY10, :G_MISS_CHAR), nvl2(cr_l11.lookup_code,''Y'',NULL),''Z'') FAILURE_SCR_COMM10_err,
368 nvl2(nullif(crint.CREDIT_SCORE_COMMENTARY3, :G_MISS_CHAR), nvl2(cr_l15.lookup_code,''Y'',NULL),''Z'') CREDIT_SCR_COMM3_err,
365 nvl2(nullif(crint.FAILURE_SCORE_OVERRIDE_CODE, :G_MISS_CHAR), nvl2(cr_l12.lookup_code,''Y'',NULL),''Z'') FAILURE_SCR_OVERRIDE_CD_err,
366 nvl2(nullif(crint.CREDIT_SCORE_COMMENTARY, :G_MISS_CHAR), nvl2(cr_l13.lookup_code,''Y'',NULL),''Z'') CREDIT_SCR_COMM_err,
367 nvl2(nullif(crint.CREDIT_SCORE_COMMENTARY2, :G_MISS_CHAR), nvl2(cr_l14.lookup_code,''Y'',NULL),''Z'') CREDIT_SCR_COMM2_err,
369 nvl2(nullif(crint.CREDIT_SCORE_COMMENTARY4, :G_MISS_CHAR), nvl2(cr_l16.lookup_code,''Y'',NULL),''Z'') CREDIT_SCR_COMM4_err,
370 nvl2(nullif(crint.CREDIT_SCORE_COMMENTARY5, :G_MISS_CHAR), nvl2(cr_l17.lookup_code,''Y'',NULL),''Z'') CREDIT_SCR_COMM5_err,
371 nvl2(nullif(crint.CREDIT_SCORE_COMMENTARY6, :G_MISS_CHAR), nvl2(cr_l18.lookup_code,''Y'',NULL),''Z'') CREDIT_SCR_COMM6_err,
372 nvl2(nullif(crint.CREDIT_SCORE_COMMENTARY7, :G_MISS_CHAR), nvl2(cr_l19.lookup_code,''Y'',NULL),''Z'') CREDIT_SCR_COMM7_err,
373 nvl2(nullif(crint.CREDIT_SCORE_COMMENTARY8, :G_MISS_CHAR), nvl2(cr_l20.lookup_code,''Y'',NULL),''Z'') CREDIT_SCR_COMM8_err,
374 nvl2(nullif(crint.CREDIT_SCORE_COMMENTARY9, :G_MISS_CHAR), nvl2(cr_l21.lookup_code,''Y'',NULL),''Z'') CREDIT_SCR_COMM9_err,
375 nvl2(nullif(crint.CREDIT_SCORE_COMMENTARY10, :G_MISS_CHAR), nvl2(cr_l22.lookup_code,''Y'',NULL),''Z'') CREDIT_SCR_COMM10_err,
376 nvl2(nullif(crint.PRNT_HQ_BKCY_IND, :G_MISS_CHAR), nvl2(cr_l23.lookup_code,''Y'',NULL),''Z'') PRNT_HQ_BKCY_IND_err,
377 nvl2(nullif(crint.MAXIMUM_CREDIT_CURRENCY_CODE, :G_MISS_CHAR), nvl2(fc.currency_code,''Y'',NULL),''Z'') MAX_CREDIT_CURR_CODE_err,
378 decode(nvl(crint.insert_update_flag, crsg.action_flag), crsg.action_flag, ''Y'', null) action_mismatch_error,
379 crsg.error_flag
380 FROM
381 HZ_IMP_CREDITRTNGS_INT crint,
382 HZ_IMP_CREDITRTNGS_SG crsg,
383 fnd_lookup_values cr_l1,
384 fnd_lookup_values cr_l2,
385 fnd_lookup_values cr_l3,
386 fnd_lookup_values cr_l4,
387 fnd_lookup_values cr_l5,
388 fnd_lookup_values cr_l6,
389 fnd_lookup_values cr_l7,
390 fnd_lookup_values cr_l8,
391 fnd_lookup_values cr_l9,
392 fnd_lookup_values cr_l10,
393 fnd_lookup_values cr_l11,
394 fnd_lookup_values cr_l12,
395 fnd_lookup_values cr_l13,
396 fnd_lookup_values cr_l14,
397 fnd_lookup_values cr_l15,
398 fnd_lookup_values cr_l16,
399 fnd_lookup_values cr_l17,
400 fnd_lookup_values cr_l18,
401 fnd_lookup_values cr_l19,
402 fnd_lookup_values cr_l20,
403 fnd_lookup_values cr_l21,
404 fnd_lookup_values cr_l22,
405 fnd_lookup_values cr_l23,
406 fnd_currencies fc,
407 hz_orig_sys_references party_mosr
408 WHERE
409 cr_l1.lookup_code(+) = crint.CREDIT_SCORE_OVERRIDE_CODE
410 and cr_l1.lookup_type(+) = ''FAILURE_SCORE_OVERRIDE_CODE''
411 AND cr_l1.language (+) = userenv(''LANG'')
412 AND cr_l1.view_application_id (+) = 222
413 AND cr_l1.security_group_id (+) =
414 fnd_global.lookup_security_group(''FAILURE_SCORE_OVERRIDE_CODE'', 222)
415 AND cr_l2.lookup_code(+) = crint.FAILURE_SCORE_COMMENTARY
416 AND cr_l2.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
417 AND cr_l2.language (+) = userenv(''LANG'')
418 AND cr_l2.view_application_id (+) = 222
419 AND cr_l2.security_group_id (+) =
420 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
421 AND cr_l3.lookup_code(+) = crint.FAILURE_SCORE_COMMENTARY2
422 AND cr_l3.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
423 AND cr_l3.language (+) = userenv(''LANG'')
424 AND cr_l3.view_application_id (+) = 222
425 AND cr_l3.security_group_id (+) =
426 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
427 AND cr_l4.lookup_code(+) = crint.FAILURE_SCORE_COMMENTARY3
428 AND cr_l4.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
429 AND cr_l4.language (+) = userenv(''LANG'')
430 AND cr_l4.view_application_id (+) = 222
431 AND cr_l4.security_group_id (+) =
432 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
433 AND cr_l5.lookup_code(+) = crint.FAILURE_SCORE_COMMENTARY4
434 AND cr_l5.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
435 AND cr_l5.language (+) = userenv(''LANG'')
436 AND cr_l5.view_application_id (+) = 222
437 AND cr_l5.security_group_id (+) =
438 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
439 AND cr_l6.lookup_code(+) = crint.FAILURE_SCORE_COMMENTARY5
440 AND cr_l6.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
441 AND cr_l6.language (+) = userenv(''LANG'')
442 AND cr_l6.view_application_id (+) = 222
443 AND cr_l6.security_group_id (+) =
444 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
445 AND cr_l7.lookup_code(+) = crint.FAILURE_SCORE_COMMENTARY6
446 AND cr_l7.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
447 AND cr_l7.language (+) = userenv(''LANG'')
448 AND cr_l7.view_application_id (+) = 222
449 AND cr_l7.security_group_id (+) =
450 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
451 AND cr_l8.lookup_code(+) = crint.FAILURE_SCORE_COMMENTARY7
452 AND cr_l8.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
453 AND cr_l8.language (+) = userenv(''LANG'')
454 AND cr_l8.view_application_id (+) = 222
455 AND cr_l8.security_group_id (+) =
456 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
457 AND cr_l9.lookup_code(+) = crint.FAILURE_SCORE_COMMENTARY8
458 AND cr_l9.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
459 AND cr_l9.language (+) = userenv(''LANG'')
460 AND cr_l9.view_application_id (+) = 222
461 AND cr_l9.security_group_id (+) =
462 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
463 AND cr_l10.lookup_code(+) = crint.FAILURE_SCORE_COMMENTARY9
467 AND cr_l10.security_group_id (+) =
464 AND cr_l10.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
465 AND cr_l10.language (+) = userenv(''LANG'')
466 AND cr_l10.view_application_id (+) = 222
468 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
469 AND cr_l11.lookup_code(+) = crint.FAILURE_SCORE_COMMENTARY10
470 AND cr_l11.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
471 AND cr_l11.language (+) = userenv(''LANG'')
472 AND cr_l11.view_application_id (+) = 222
473 AND cr_l11.security_group_id (+) =
474 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
475 and cr_l12.lookup_code(+) = crint.FAILURE_SCORE_OVERRIDE_CODE
476 and cr_l12.lookup_type(+) = ''FAILURE_SCORE_OVERRIDE_CODE''
477 AND cr_l12.language (+) = userenv(''LANG'')
478 AND cr_l12.view_application_id (+) = 222
479 AND cr_l12.security_group_id (+) =
480 fnd_global.lookup_security_group(''FAILURE_SCORE_OVERRIDE_CODE'', 222)
481 AND cr_l13.lookup_code(+) = crint.CREDIT_SCORE_COMMENTARY
482 AND cr_l13.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
483 AND cr_l13.language (+) = userenv(''LANG'')
484 AND cr_l13.view_application_id (+) = 222
485 AND cr_l13.security_group_id (+) =
486 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
487 AND cr_l14.lookup_code(+) = crint.CREDIT_SCORE_COMMENTARY2
488 AND cr_l14.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
489 AND cr_l14.language (+) = userenv(''LANG'')
490 AND cr_l14.view_application_id (+) = 222
491 AND cr_l14.security_group_id (+) =
492 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
493 AND cr_l15.lookup_code(+) = crint.CREDIT_SCORE_COMMENTARY3
494 AND cr_l15.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
495 AND cr_l15.language (+) = userenv(''LANG'')
496 AND cr_l15.view_application_id (+) = 222
497 AND cr_l15.security_group_id (+) =
498 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
499 AND cr_l16.lookup_code(+) = crint.CREDIT_SCORE_COMMENTARY4
500 AND cr_l16.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
501 AND cr_l16.language (+) = userenv(''LANG'')
502 AND cr_l16.view_application_id (+) = 222
503 AND cr_l16.security_group_id (+) =
504 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
505 AND cr_l17.lookup_code(+) = crint.CREDIT_SCORE_COMMENTARY5
506 AND cr_l17.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
507 AND cr_l17.language (+) = userenv(''LANG'')
508 AND cr_l17.view_application_id (+) = 222
509 AND cr_l17.security_group_id (+) =
510 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
511 AND cr_l18.lookup_code(+) = crint.CREDIT_SCORE_COMMENTARY6
512 AND cr_l18.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
513 AND cr_l18.language (+) = userenv(''LANG'')
514 AND cr_l18.view_application_id (+) = 222
515 AND cr_l18.security_group_id (+) =
516 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
517 AND cr_l19.lookup_code(+) = crint.CREDIT_SCORE_COMMENTARY7
518 AND cr_l19.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
519 AND cr_l19.language (+) = userenv(''LANG'')
520 AND cr_l19.view_application_id (+) = 222
521 AND cr_l19.security_group_id (+) =
522 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
523 AND cr_l20.lookup_code(+) = crint.CREDIT_SCORE_COMMENTARY8
524 AND cr_l20.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
525 AND cr_l20.language (+) = userenv(''LANG'')
526 AND cr_l20.view_application_id (+) = 222
527 AND cr_l20.security_group_id (+) =
528 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
529 AND cr_l21.lookup_code(+) = crint.CREDIT_SCORE_COMMENTARY9
530 AND cr_l21.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
531 AND cr_l21.language (+) = userenv(''LANG'')
532 AND cr_l21.view_application_id (+) = 222
533 AND cr_l21.security_group_id (+) =
534 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
535 AND cr_l22.lookup_code(+) = crint.CREDIT_SCORE_COMMENTARY10
536 AND cr_l22.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
537 AND cr_l22.language (+) = userenv(''LANG'')
538 AND cr_l22.view_application_id (+) = 222
539 AND cr_l22.security_group_id (+) =
540 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
541 AND cr_l23.lookup_code(+) = crint.PRNT_HQ_BKCY_IND
542 AND cr_l23.lookup_type(+) = ''PRNT_HQ_IND''
543 AND cr_l23.language (+) = userenv(''LANG'')
544 AND cr_l23.view_application_id (+) = 222
545 AND cr_l23.security_group_id (+) =
546 fnd_global.lookup_security_group(''PRNT_HQ_IND'', 222)
547 AND fc.currency_code(+) = crint.MAXIMUM_CREDIT_CURRENCY_CODE
548 AND fc.currency_flag(+) = ''Y''
549 AND fc.ENABLED_flag(+) = ''Y''
550 AND party_mosr.orig_system (+) = crsg.party_orig_system
551 AND party_mosr.orig_system_reference (+) = crsg.party_orig_system_reference
552 AND party_mosr.status (+) = ''A''
553 AND party_mosr.owner_table_name (+) = ''HZ_PARTIES''
554 AND crint.rowid = crsg.int_row_id
555 AND crsg.batch_id = :P_BATCH_ID
556 AND crsg.PARTY_ORIG_SYSTEM = :P_OS
557 and crsg.batch_mode_flag = :p_mode
558 AND crsg.PARTY_ORIG_SYSTEM_REFERENCE BETWEEN :P_FROM_OSR AND :P_TO_OSR
559 AND crsg.ACTION_FLAG = ''U''';
560
561 l_where_enabled_lookup_sql varchar2(15000) :=
562 ' AND (cr_l1.ENABLED_FLAG(+) = ''Y'' AND
563 TRUNC(:l_sysdate) BETWEEN
564 TRUNC(NVL( cr_l1.START_DATE_ACTIVE,:l_sysdate ) ) AND
565 TRUNC(NVL( cr_l1.END_DATE_ACTIVE,:l_sysdate ) ) )
569 TRUNC(NVL( cr_l2.END_DATE_ACTIVE,:l_sysdate ) ) )
566 AND (cr_l2.ENABLED_FLAG(+) = ''Y'' AND
567 TRUNC(:l_sysdate) BETWEEN
568 TRUNC(NVL( cr_l2.START_DATE_ACTIVE,:l_sysdate ) ) AND
570 AND (cr_l3.ENABLED_FLAG(+) = ''Y'' AND
571 TRUNC(:l_sysdate) BETWEEN
572 TRUNC(NVL( cr_l3.START_DATE_ACTIVE,:l_sysdate ) ) AND
573 TRUNC(NVL( cr_l3.END_DATE_ACTIVE,:l_sysdate ) ) )
574 AND (cr_l4.ENABLED_FLAG(+) = ''Y'' AND
575 TRUNC(:l_sysdate) BETWEEN
576 TRUNC(NVL( cr_l4.START_DATE_ACTIVE,:l_sysdate ) ) AND
577 TRUNC(NVL( cr_l4.END_DATE_ACTIVE,:l_sysdate ) ) )
578 AND (cr_l5.ENABLED_FLAG(+) = ''Y'' AND
579 TRUNC(:l_sysdate) BETWEEN
580 TRUNC(NVL( cr_l5.START_DATE_ACTIVE,:l_sysdate ) ) AND
581 TRUNC(NVL( cr_l5.END_DATE_ACTIVE,:l_sysdate ) ) )
582 AND (cr_l6.ENABLED_FLAG(+) = ''Y'' AND
583 TRUNC(:l_sysdate) BETWEEN
584 TRUNC(NVL( cr_l6.START_DATE_ACTIVE,:l_sysdate ) ) AND
585 TRUNC(NVL( cr_l6.END_DATE_ACTIVE,:l_sysdate ) ) )
586 AND (cr_l7.ENABLED_FLAG(+) = ''Y'' AND
587 TRUNC(:l_sysdate) BETWEEN
588 TRUNC(NVL( cr_l7.START_DATE_ACTIVE,:l_sysdate ) ) AND
589 TRUNC(NVL( cr_l7.END_DATE_ACTIVE,:l_sysdate ) ) )
590 AND (cr_l8.ENABLED_FLAG(+) = ''Y'' AND
591 TRUNC(:l_sysdate) BETWEEN
592 TRUNC(NVL( cr_l8.START_DATE_ACTIVE,:l_sysdate ) ) AND
593 TRUNC(NVL( cr_l8.END_DATE_ACTIVE,:l_sysdate ) ) )
594 AND (cr_l9.ENABLED_FLAG(+) = ''Y'' AND
595 TRUNC(:l_sysdate) BETWEEN
596 TRUNC(NVL( cr_l9.START_DATE_ACTIVE,:l_sysdate ) ) AND
597 TRUNC(NVL( cr_l9.END_DATE_ACTIVE,:l_sysdate ) ) )
598 AND (cr_l10.ENABLED_FLAG(+) = ''Y'' AND
599 TRUNC(:l_sysdate) BETWEEN
600 TRUNC(NVL( cr_l10.START_DATE_ACTIVE,:l_sysdate ) ) AND
601 TRUNC(NVL( cr_l10.END_DATE_ACTIVE,:l_sysdate ) ) )
602 AND (cr_l11.ENABLED_FLAG(+) = ''Y'' AND
603 TRUNC(:l_sysdate) BETWEEN
604 TRUNC(NVL( cr_l11.START_DATE_ACTIVE,:l_sysdate ) ) AND
605 TRUNC(NVL( cr_l11.END_DATE_ACTIVE,:l_sysdate ) ) )
606 AND (cr_l12.ENABLED_FLAG(+) = ''Y'' AND
607 TRUNC(:l_sysdate) BETWEEN
608 TRUNC(NVL( cr_l12.START_DATE_ACTIVE,:l_sysdate ) ) AND
609 TRUNC(NVL( cr_l12.END_DATE_ACTIVE,:l_sysdate ) ) )
610 AND (cr_l13.ENABLED_FLAG(+) = ''Y'' AND
611 TRUNC(:l_sysdate) BETWEEN
612 TRUNC(NVL( cr_l13.START_DATE_ACTIVE,:l_sysdate ) ) AND
613 TRUNC(NVL( cr_l13.END_DATE_ACTIVE,:l_sysdate ) ) )
614 AND (cr_l14.ENABLED_FLAG(+) = ''Y'' AND
615 TRUNC(:l_sysdate) BETWEEN
616 TRUNC(NVL( cr_l14.START_DATE_ACTIVE,:l_sysdate ) ) AND
617 TRUNC(NVL( cr_l14.END_DATE_ACTIVE,:l_sysdate ) ) )
618 AND (cr_l15.ENABLED_FLAG(+) = ''Y'' AND
619 TRUNC(:l_sysdate) BETWEEN
620 TRUNC(NVL( cr_l15.START_DATE_ACTIVE,:l_sysdate ) ) AND
621 TRUNC(NVL( cr_l15.END_DATE_ACTIVE,:l_sysdate ) ) )
622 AND (cr_l16.ENABLED_FLAG(+) = ''Y'' AND
623 TRUNC(:l_sysdate) BETWEEN
624 TRUNC(NVL( cr_l16.START_DATE_ACTIVE,:l_sysdate ) ) AND
625 TRUNC(NVL( cr_l16.END_DATE_ACTIVE,:l_sysdate ) ) )
626 AND (cr_l17.ENABLED_FLAG(+) = ''Y'' AND
627 TRUNC(:l_sysdate) BETWEEN
628 TRUNC(NVL( cr_l17.START_DATE_ACTIVE,:l_sysdate ) ) AND
629 TRUNC(NVL( cr_l17.END_DATE_ACTIVE,:l_sysdate ) ) )
630 AND (cr_l18.ENABLED_FLAG(+) = ''Y'' AND
631 TRUNC(:l_sysdate) BETWEEN
632 TRUNC(NVL( cr_l18.START_DATE_ACTIVE,:l_sysdate ) ) AND
633 TRUNC(NVL( cr_l18.END_DATE_ACTIVE,:l_sysdate ) ) )
634 AND (cr_l19.ENABLED_FLAG(+) = ''Y'' AND
635 TRUNC(:l_sysdate) BETWEEN
636 TRUNC(NVL( cr_l19.START_DATE_ACTIVE,:l_sysdate ) ) AND
637 TRUNC(NVL( cr_l19.END_DATE_ACTIVE,:l_sysdate ) ) )
638 AND (cr_l20.ENABLED_FLAG(+) = ''Y'' AND
639 TRUNC(:l_sysdate) BETWEEN
640 TRUNC(NVL( cr_l20.START_DATE_ACTIVE,:l_sysdate ) ) AND
641 TRUNC(NVL( cr_l20.END_DATE_ACTIVE,:l_sysdate ) ) )
642 AND (cr_l21.ENABLED_FLAG(+) = ''Y'' AND
643 TRUNC(:l_sysdate) BETWEEN
644 TRUNC(NVL( cr_l21.START_DATE_ACTIVE,:l_sysdate ) ) AND
645 TRUNC(NVL( cr_l21.END_DATE_ACTIVE,:l_sysdate ) ) )
646 AND (cr_l22.ENABLED_FLAG(+) = ''Y'' AND
647 TRUNC(:l_sysdate) BETWEEN
648 TRUNC(NVL( cr_l22.START_DATE_ACTIVE,:l_sysdate ) ) AND
649 TRUNC(NVL( cr_l22.END_DATE_ACTIVE,:l_sysdate ) ) )
650 AND (cr_l23.ENABLED_FLAG(+) = ''Y'' AND
651 TRUNC(:l_sysdate) BETWEEN
652 TRUNC(NVL( cr_l23.START_DATE_ACTIVE,:l_sysdate ) ) AND
653 TRUNC(NVL( cr_l23.END_DATE_ACTIVE,:l_sysdate ) ) )
654 ';
655
656 l_first_run_clause varchar2(40) := ' AND crint.INTERFACE_STATUS is null';
657 l_re_run_clause varchar2(40) := ' AND crint.INTERFACE_STATUS = ''C''';
658 l_final_query varchar2(32767) := NULL;
659 l_debug_prefix VARCHAR2(30) := '';
660 BEGIN
661 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
662 hz_utility_v2pub.debug(p_message=>'CR:open_upd_cursor()+',
663 p_prefix=>l_debug_prefix,
664 p_msg_level=>fnd_log.level_procedure);
665 END IF;
666 IF P_DML_RECORD.ALLOW_DISABLED_LOOKUP = 'Y' THEN
667 IF P_DML_RECORD.RERUN = 'N' /*** First Run ***/ THEN
668 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
669 hz_utility_v2pub.debug(p_message=>'CR:first run - disabled lkup - upd',
670 p_prefix =>l_debug_prefix,
671 p_msg_level=>fnd_log.level_statement);
672 END IF;
673 l_final_query := l_sql_str1 || l_first_run_clause;
674 ELSE /* Rerun to correct errors */
675 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
676 hz_utility_v2pub.debug(p_message=>'CR:rerun - disabled lkup - upd',
677 p_prefix =>l_debug_prefix,
681 END IF;
678 p_msg_level=>fnd_log.level_statement);
679 END IF;
680 l_final_query := l_sql_str1 || l_re_run_clause;
682 OPEN update_cursor FOR l_final_query
683 USING P_DML_RECORD.GMISS_CHAR,
684 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
685 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
686 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
687 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
688 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
689 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
690 P_DML_RECORD.BATCH_ID, P_DML_RECORD.OS, P_DML_RECORD.BATCH_MODE_FLAG,
691 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
692 ELSE -- enabled lookups only
693 IF P_DML_RECORD.RERUN = 'N' /*** First Run ***/ THEN
694 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
695 hz_utility_v2pub.debug(p_message=>'CR:first run - enabled lookup upd',
696 p_prefix =>l_debug_prefix,
697 p_msg_level=>fnd_log.level_statement);
698 END IF;
699 l_final_query := l_sql_str1 || l_first_run_clause || l_where_enabled_lookup_sql;
700 ELSE /* Rerun to correct errors */
701 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
702 hz_utility_v2pub.debug(p_message=>'CR:rerun - enabled lkup - upd',
703 p_prefix =>l_debug_prefix,
704 p_msg_level=>fnd_log.level_statement);
705 END IF;
706 l_final_query := l_sql_str1 || l_re_run_clause || l_where_enabled_lookup_sql;
707 END IF;
708 OPEN update_cursor FOR l_final_query
709 USING P_DML_RECORD.GMISS_CHAR,
710 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
711 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
712 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
713 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
714 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
715 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
716 P_DML_RECORD.BATCH_ID, P_DML_RECORD.OS,P_DML_RECORD.BATCH_MODE_FLAG,
717 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
718 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
719 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
720 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
721 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
722 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
723 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
724 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
725 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
726 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
727 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
728 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
729 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
730 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
731 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
732 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
733 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
734 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
735 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
736 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
737 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
738 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
739 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE,
740 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE;
741 END IF;
742
743 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
744 hz_utility_v2pub.debug(p_message=>'CR:open_upd_cursor()-',
745 p_prefix=>l_debug_prefix,
746 p_msg_level=>fnd_log.level_procedure);
747 END IF;
748 END open_upd_cursor;
749 --------------------------------------
750 --------------------------------------
751 /**
752 * PRIVATE PROCEDURE process_cr_ins
753 *
754 * DESCRIPTION
755 * processes recs identified for
756 * insertion and does dml on
757 * hz_credit_ratings and errors tbl
758 *
759 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
760 *
761 * MODIFICATION HISTORY
762 *
763 * 07-15-2003 Srikanth o Created.
764 *
765 */
766 --------------------------------------
767 PROCEDURE process_cr_ins (
768 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
769 ,x_return_status OUT NOCOPY VARCHAR2
770 ,x_msg_count OUT NOCOPY NUMBER
771 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
772
776 when (bankruptcy_ind_err is not null
773 -- local variables
774 l_sql_str1 VARCHAR2(32767) :=
775 'BEGIN insert all
777 and suit_ind_err is not null
778 and bankruptcy_ind_err is not null
779 and debarment_ind_err is not null
780 and fincl_embt_ind_err is not null
781 and no_trade_ind_err is not null
782 and judgement_ind_err is not null
783 and lien_ind_err is not null
784 and credit_scr_override_cd_err is not null
785 and failure_scr_comm_err is not null
786 and failure_scr_comm2_err is not null
787 and failure_scr_comm3_err is not null
788 and failure_scr_comm4_err is not null
789 and failure_scr_comm5_err is not null
790 and failure_scr_comm6_err is not null
791 and failure_scr_comm7_err is not null
792 and failure_scr_comm8_err is not null
793 and failure_scr_comm9_err is not null
794 and failure_scr_comm10_err is not null
795 and failure_scr_override_cd_err is not null
796 and credit_scr_comm_err is not null
797 and credit_scr_comm2_err is not null
798 and credit_scr_comm3_err is not null
799 and credit_scr_comm4_err is not null
800 and credit_scr_comm5_err is not null
801 and credit_scr_comm6_err is not null
802 and credit_scr_comm7_err is not null
803 and credit_scr_comm8_err is not null
804 and credit_scr_comm9_err is not null
805 and credit_scr_comm10_err is not null
806 and prnt_hq_bkcy_ind_err is not null
807 and max_credit_curr_code_err is not null
808 and action_mismatch_error is not null
809 and createdby_error is not null
810 and missing_parent_err is not null) then
811 into hz_credit_ratings (
812 ACTUAL_CONTENT_SOURCE,
813 application_id,
814 content_source_type,
815 created_by,
816 creation_date,
817 last_updated_by,
818 last_update_date,
819 last_update_login,
820 program_application_id,
821 program_id,
822 program_update_date,
823 request_id,
824 avg_high_credit,
825 bankruptcy_ind,
826 business_discontinued,
827 claims_ind,
828 comments,
829 created_by_module,
830 CREDIT_RATING_ID,
831 credit_score,
832 credit_score_age,
833 credit_score_class,
834 credit_score_commentary,
835 credit_score_commentary10,
836 credit_score_commentary2,
837 credit_score_commentary3,
838 credit_score_commentary4,
839 credit_score_commentary5,
840 credit_score_commentary6,
841 credit_score_commentary7,
842 credit_score_commentary8,
843 credit_score_commentary9,
844 credit_score_date,
845 credit_score_incd_default,
846 credit_score_natl_percentile,
847 credit_score_override_code,
848 criminal_proceeding_ind,
849 cr_scr_clas_expl,
850 debarments_count,
851 debarments_date,
852 debarment_ind,
853 delq_pmt_pctg_for_all_firms,
854 delq_pmt_rng_prcnt,
855 description,
856 det_history_ind,
857 disaster_ind,
858 failure_score,
859 failure_score_age,
860 failure_score_class,
861 failure_score_commentary,
862 failure_score_commentary10,
863 failure_score_commentary2,
864 failure_score_commentary3,
865 failure_score_commentary4,
866 failure_score_commentary5,
867 failure_score_commentary6,
868 failure_score_commentary7,
869 failure_score_commentary8,
870 failure_score_commentary9,
871 failure_score_date,
872 failure_score_incd_default,
873 failure_score_natnl_percentile,
874 failure_score_override_code,
875 fincl_embt_ind,
876 fincl_lgl_event_ind,
877 global_failure_score,
878 high_credit,
879 high_rng_delq_scr,
880 judgement_ind,
881 lien_ind,
882 low_rng_delq_scr,
883 maximum_credit_currency_code,
884 maximum_credit_recommendation,
885 negv_pmt_expl,
886 no_trade_ind,
887 num_prnt_bkcy_convs,
888 num_prnt_bkcy_filing,
889 num_spcl_event,
890 num_trade_experiences,
891 object_version_number,
892 oprg_spec_evnt_ind,
893 other_spec_evnt_ind,
894 PARTY_ID,
895 paydex_comment,
896 paydex_firm_comment,
897 paydex_firm_days,
898 paydex_industry_comment,
899 paydex_industry_days,
900 paydex_norm,
901 paydex_score,
902 paydex_three_months_ago,
903 prnt_bkcy_chapter_conv,
904 prnt_bkcy_conv_date,
905 prnt_bkcy_filg_chapter,
906 prnt_bkcy_filg_date,
907 prnt_bkcy_filg_type,
908 prnt_hq_bkcy_ind,
909 pub_rec_expl,
910 RATED_AS_OF_DATE,
911 rating,
912 RATING_ORGANIZATION,
913 secured_flng_ind,
914 slow_trade_expl,
915 spcl_event_comment,
916 spcl_event_update_date,
917 spcl_evnt_txt,
918 status,
919 suit_ind)
920 values (
921 :actual_content_src,
922 :application_id,
923 ''USER_ENTERED'',
924 :user_id,
925 :l_sysdate,
926 :user_id,
927 :l_sysdate,
928 :last_update_login,
929 :program_application_id,
930 :program_id,
931 :l_sysdate,
932 :request_id,
933 avg_high_credit,
934 bankruptcy_ind,
935 business_discontinued,
936 claims_ind,
937 comments,
938 created_by_module,
939 credit_rating_id,
940 credit_score,
941 credit_score_age,
942 credit_score_class,
943 credit_score_commentary,
944 credit_score_commentary10,
945 credit_score_commentary2,
946 credit_score_commentary3,
947 credit_score_commentary4,
948 credit_score_commentary5,
949 credit_score_commentary6,
950 credit_score_commentary7,
954 credit_score_incd_default,
951 credit_score_commentary8,
952 credit_score_commentary9,
953 credit_score_date,
955 credit_score_natl_percentile,
956 credit_score_override_code,
957 criminal_proceeding_ind,
958 cr_scr_clas_expl,
959 debarments_count,
960 debarments_date,
961 debarment_ind,
962 delq_pmt_pctg_for_all_firms,
963 delq_pmt_rng_prcnt,
964 description,
965 det_history_ind,
966 disaster_ind,
967 failure_score,
968 failure_score_age,
969 failure_score_class,
970 failure_score_commentary,
971 failure_score_commentary10,
972 failure_score_commentary2,
973 failure_score_commentary3,
974 failure_score_commentary4,
975 failure_score_commentary5,
976 failure_score_commentary6,
977 failure_score_commentary7,
978 failure_score_commentary8,
979 failure_score_commentary9,
980 failure_score_date,
981 failure_score_incd_default,
982 failure_score_natnl_percentile,
983 failure_score_override_code,
984 fincl_embt_ind,
985 fincl_lgl_event_ind,
986 global_failure_score,
987 high_credit,
988 high_rng_delq_scr,
989 judgement_ind,
990 lien_ind,
991 low_rng_delq_scr,
992 maximum_credit_currency_code,
993 maximum_credit_recommendation,
994 negv_pmt_expl,
995 no_trade_ind,
996 num_prnt_bkcy_convs,
997 num_prnt_bkcy_filing,
998 num_spcl_event,
999 num_trade_experiences,
1000 1,
1001 oprg_spec_evnt_ind,
1002 other_spec_evnt_ind,
1003 party_id,
1004 paydex_comment,
1005 paydex_firm_comment,
1006 paydex_firm_days,
1007 paydex_industry_comment,
1008 paydex_industry_days,
1009 paydex_norm,
1010 paydex_score,
1011 paydex_three_months_ago,
1012 prnt_bkcy_chapter_conv,
1013 prnt_bkcy_conv_date,
1014 prnt_bkcy_filg_chapter,
1015 prnt_bkcy_filg_date,
1016 prnt_bkcy_filg_type,
1017 prnt_hq_bkcy_ind,
1018 pub_rec_expl,
1019 rated_as_of_date,
1020 rating,
1021 rating_organization,
1022 secured_flng_ind,
1023 slow_trade_expl,
1024 spcl_event_comment,
1025 spcl_event_update_date,
1026 spcl_evnt_txt,
1027 ''A'',
1028 suit_ind)
1029 else
1030 into hz_imp_tmp_errors (
1031 created_by, creation_date, last_updated_by,
1032 last_update_date, last_update_login, program_application_id,
1033 program_id, program_update_date,
1034 error_id, batch_id, request_id,
1035 int_row_id, interface_table_name, e1_flag,
1036 e2_flag, e4_flag,
1037 e5_flag, e6_flag, e7_flag,
1038 e8_flag, e9_flag, e10_flag,
1039 e11_flag, e12_flag, e13_flag,
1040 e14_flag, e15_flag, e16_flag,
1041 e17_flag, e18_flag, e19_flag,
1042 e20_flag, e21_flag, e22_flag,
1043 e23_flag, e24_flag, e25_flag,
1044 e26_flag, e27_flag, e28_flag,
1045 e29_flag, e30_flag, e31_flag,
1046 e32_flag,
1047 e33_flag,
1048 ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG)
1049 values (
1050 :user_id, :l_sysdate, :user_id,
1051 :l_sysdate, :last_update_login, :program_application_id,
1052 :program_id, :l_sysdate,
1053 hz_imp_errors_s.nextval, :p_batch_id, :request_id,
1054 row_id, ''HZ_IMP_CREDITRTNGS_INT'', bankruptcy_ind_err,
1055 suit_ind_err, debarment_ind_err,
1056 fincl_embt_ind_err, no_trade_ind_err, judgement_ind_err,
1057 lien_ind_err, credit_scr_override_cd_err, failure_scr_comm_err,
1058 failure_scr_comm2_err, failure_scr_comm3_err, failure_scr_comm4_err,
1059 failure_scr_comm5_err, failure_scr_comm6_err, failure_scr_comm7_err,
1060 failure_scr_comm8_err, failure_scr_comm9_err, failure_scr_comm10_err,
1061 failure_scr_override_cd_err, credit_scr_comm_err, credit_scr_comm2_err,
1062 credit_scr_comm3_err, credit_scr_comm4_err, credit_scr_comm5_err,
1063 credit_scr_comm6_err, credit_scr_comm7_err, credit_scr_comm8_err,
1064 credit_scr_comm9_err, credit_scr_comm10_err, prnt_hq_bkcy_ind_err,
1065 max_credit_curr_code_err,
1066 createdby_error, action_mismatch_error, missing_parent_err)
1067 select
1068 missing_parent_err,
1069 party_id,
1070 credit_rating_id,
1071 row_id,
1072 avg_high_credit,
1073 bankruptcy_ind,
1074 business_discontinued,
1075 claims_ind,
1076 comments,
1077 nvl(created_by_module,''HZ_IMPORT'') created_by_module,
1078 credit_score,
1079 credit_score_age,
1080 credit_score_class,
1081 credit_score_commentary,
1082 credit_score_commentary10,
1083 credit_score_commentary2,
1084 credit_score_commentary3,
1085 credit_score_commentary4,
1086 credit_score_commentary5,
1087 credit_score_commentary6,
1088 credit_score_commentary7,
1089 credit_score_commentary8,
1090 credit_score_commentary9,
1091 credit_score_date,
1092 credit_score_incd_default,
1093 credit_score_natl_percentile,
1094 credit_score_override_code,
1095 criminal_proceeding_ind,
1096 cr_scr_clas_expl,
1097 debarments_count,
1098 debarments_date,
1099 debarment_ind,
1100 delq_pmt_pctg_for_all_firms,
1101 delq_pmt_rng_prcnt,
1102 description,
1103 det_history_ind,
1104 disaster_ind,
1105 error_id,
1106 failure_score,
1107 failure_score_age,
1108 failure_score_class,
1109 failure_score_commentary,
1110 failure_score_commentary10,
1111 failure_score_commentary2,
1112 failure_score_commentary3,
1113 failure_score_commentary4,
1114 failure_score_commentary5,
1115 failure_score_commentary6,
1116 failure_score_commentary7,
1120 failure_score_incd_default,
1117 failure_score_commentary8,
1118 failure_score_commentary9,
1119 failure_score_date,
1121 failure_score_natnl_percentile,
1122 failure_score_override_code,
1123 fincl_embt_ind,
1124 fincl_lgl_event_ind,
1125 global_failure_score,
1126 high_credit,
1127 high_rng_delq_scr,
1128 insert_update_flag,
1129 judgement_ind,
1130 lien_ind,
1131 low_rng_delq_scr,
1132 maximum_credit_currency_code,
1133 maximum_credit_recommendation,
1134 negv_pmt_expl,
1135 no_trade_ind,
1136 num_prnt_bkcy_convs,
1137 num_prnt_bkcy_filing,
1138 num_spcl_event,
1139 num_trade_experiences,
1140 oprg_spec_evnt_ind,
1141 other_spec_evnt_ind,
1142 party_orig_system,
1143 party_orig_system_reference,
1144 paydex_comment,
1145 paydex_firm_comment,
1146 paydex_firm_days,
1147 paydex_industry_comment,
1148 paydex_industry_days,
1149 paydex_norm,
1150 paydex_score,
1151 paydex_three_months_ago,
1152 prnt_bkcy_chapter_conv,
1153 prnt_bkcy_conv_date,
1154 prnt_bkcy_filg_chapter,
1155 prnt_bkcy_filg_date,
1156 prnt_bkcy_filg_type,
1157 prnt_hq_bkcy_ind,
1158 pub_rec_expl,
1159 rated_as_of_date,
1160 rating,
1161 rating_organization,
1162 secured_flng_ind,
1163 slow_trade_expl,
1164 spcl_event_comment,
1165 spcl_event_update_date,
1166 spcl_evnt_txt,
1167 suit_ind,
1168 error_flag,
1169 decode(suit_ind, ''Y'', ''Y'', ''N'', ''N'', null, ''Z'', null) suit_ind_err,
1170 decode (bankruptcy_ind, ''Y'', ''Y'', ''N'', ''N'', null, ''Z'', null) bankruptcy_ind_err,
1171 decode(debarment_ind, ''Y'', ''Y'', ''N'', ''N'', null, ''Z'', null) debarment_ind_err,
1172 decode(fincl_embt_ind, ''Y'', ''Y'', ''N'', ''N'', null, ''Z'', null) fincl_embt_ind_err,
1173 decode(no_trade_ind, ''Y'', ''Y'', ''N'', ''N'', null, ''Z'', null) no_trade_ind_err,
1174 decode(judgement_ind, ''Y'', ''Y'', ''N'', ''N'', null, ''Z'', null) judgement_ind_err,
1175 decode(lien_ind, ''Y'', ''Y'', ''N'', ''N'', null, ''Z'', null) lien_ind_err,
1176 nvl2(credit_score_override_code, cr_l1_code, ''Z'') credit_scr_override_cd_err,
1177 nvl2(failure_score_commentary, cr_l2_code, ''Z'') failure_scr_comm_err,
1178 nvl2(failure_score_commentary2, cr_l3_code, ''Z'') failure_scr_comm2_err,
1179 nvl2(failure_score_commentary3, cr_l4_code, ''Z'') failure_scr_comm3_err,
1180 nvl2(failure_score_commentary4, cr_l5_code, ''Z'') failure_scr_comm4_err,
1181 nvl2(failure_score_commentary5, cr_l6_code, ''Z'') failure_scr_comm5_err,
1182 nvl2(failure_score_commentary6, cr_l7_code, ''Z'') failure_scr_comm6_err,
1183 nvl2(failure_score_commentary7, cr_l8_code, ''Z'') failure_scr_comm7_err,
1184 nvl2(failure_score_commentary8, cr_l9_code, ''Z'') failure_scr_comm8_err,
1185 nvl2(failure_score_commentary9, cr_l10_code, ''Z'') failure_scr_comm9_err,
1186 nvl2(failure_score_commentary10, cr_l11_code, ''Z'') failure_scr_comm10_err,
1187 nvl2(failure_score_override_code, cr_l12_code, ''Z'') failure_scr_override_cd_err,
1188 nvl2(credit_score_commentary, cr_l13_code, ''Z'') credit_scr_comm_err,
1189 nvl2(credit_score_commentary2, cr_l14_code, ''Z'') credit_scr_comm2_err,
1190 nvl2(credit_score_commentary3, cr_l15_code, ''Z'') credit_scr_comm3_err,
1191 nvl2(credit_score_commentary4, cr_l16_code, ''Z'') credit_scr_comm4_err,
1192 nvl2(credit_score_commentary5, cr_l17_code, ''Z'') credit_scr_comm5_err,
1193 nvl2(credit_score_commentary6, cr_l18_code, ''Z'') credit_scr_comm6_err,
1194 nvl2(credit_score_commentary7, cr_l19_code, ''Z'') credit_scr_comm7_err,
1195 nvl2(credit_score_commentary8, cr_l20_code, ''Z'') credit_scr_comm8_err,
1196 nvl2(credit_score_commentary9, cr_l21_code, ''Z'') credit_scr_comm9_err,
1197 nvl2(credit_score_commentary10, cr_l22_code, ''Z'') credit_scr_comm10_err,
1198 nvl2(prnt_hq_bkcy_ind, cr_l23_code, ''Z'') prnt_hq_bkcy_ind_err,
1199 nvl2(maximum_credit_currency_code, fc_code, ''Z'') max_credit_curr_code_err,
1200 nvl2(nullif(insert_update_flag, action_flag), null, ''Y'') action_mismatch_error,
1201 nvl2(created_by_module, createdby_l, ''Y'') createdby_error
1202 from (
1203 select /*+ leading(crsg) use_nl(cr_l1 cr_l2 cr_l3 cr_l4 cr_l5 cr_l6 cr_l7
1204 cr_l8 cr_l9 cr_l10 cr_l11 cr_l12 cr_l13 cr_l14 cr_l15 cr_l16 cr_l17
1205 cr_l18 cr_l19 cr_l20 cr_l21 cr_l22 cr_l23) */
1206 nvl2(hp.party_id,''Z'',NULL) missing_parent_err,
1207 crsg.action_flag,
1208 crsg.party_id,
1209 crsg.credit_rating_id,
1210 crint.rowid row_id,
1211 crint.avg_high_credit,
1212 nullif(crint.bankruptcy_ind, :p_gmiss_char) bankruptcy_ind,
1213 crint.business_discontinued,
1214 crint.claims_ind,
1215 crint.comments,
1216 nullif(crint.created_by_module, :p_gmiss_char) created_by_module,
1217 crint.credit_score,
1218 crint.credit_score_age,
1219 crint.credit_score_class,
1220 nullif(crint.credit_score_commentary, :p_gmiss_char) credit_score_commentary,
1221 nullif(crint.credit_score_commentary10, :p_gmiss_char) credit_score_commentary10,
1222 nullif(crint.credit_score_commentary2, :p_gmiss_char) credit_score_commentary2,
1223 nullif(crint.credit_score_commentary3, :p_gmiss_char) credit_score_commentary3,
1224 nullif(crint.credit_score_commentary4, :p_gmiss_char) credit_score_commentary4,
1225 nullif(crint.credit_score_commentary5, :p_gmiss_char) credit_score_commentary5,
1226 nullif(crint.credit_score_commentary6, :p_gmiss_char) credit_score_commentary6,
1227 nullif(crint.credit_score_commentary7, :p_gmiss_char) credit_score_commentary7,
1231 crint.credit_score_incd_default,
1228 nullif(crint.credit_score_commentary8, :p_gmiss_char) credit_score_commentary8,
1229 nullif(crint.credit_score_commentary9, :p_gmiss_char) credit_score_commentary9,
1230 crint.credit_score_date,
1232 crint.credit_score_natl_percentile,
1233 nullif(crint.credit_score_override_code, :p_gmiss_char) credit_score_override_code,
1234 crint.criminal_proceeding_ind,
1235 crint.cr_scr_clas_expl,
1236 crint.debarments_count,
1237 crint.debarments_date,
1238 nullif(crint.debarment_ind, :p_gmiss_char) debarment_ind,
1239 crint.delq_pmt_pctg_for_all_firms,
1240 crint.delq_pmt_rng_prcnt,
1241 crint.description,
1242 crint.det_history_ind,
1243 crint.disaster_ind,
1244 crint.error_id,
1245 crint.failure_score,
1246 crint.failure_score_age,
1247 crint.failure_score_class,
1248 nullif(crint.failure_score_commentary, :p_gmiss_char) failure_score_commentary,
1249 nullif(crint.failure_score_commentary10, :p_gmiss_char) failure_score_commentary10,
1250 nullif(crint.failure_score_commentary2, :p_gmiss_char) failure_score_commentary2,
1251 nullif(crint.failure_score_commentary3, :p_gmiss_char) failure_score_commentary3,
1252 nullif(crint.failure_score_commentary4, :p_gmiss_char) failure_score_commentary4,
1253 nullif(crint.failure_score_commentary5, :p_gmiss_char) failure_score_commentary5,
1254 nullif(crint.failure_score_commentary6, :p_gmiss_char) failure_score_commentary6,
1255 nullif(crint.failure_score_commentary7, :p_gmiss_char) failure_score_commentary7,
1256 nullif(crint.failure_score_commentary8, :p_gmiss_char) failure_score_commentary8,
1257 nullif(crint.failure_score_commentary9, :p_gmiss_char) failure_score_commentary9,
1258 crint.failure_score_date,
1259 crint.failure_score_incd_default,
1260 crint.failure_score_natnl_percentile,
1261 nullif(crint.failure_score_override_code, :p_gmiss_char) failure_score_override_code,
1262 nullif(crint.fincl_embt_ind, :p_gmiss_char) fincl_embt_ind,
1263 crint.fincl_lgl_event_ind,
1264 crint.global_failure_score,
1265 crint.high_credit,
1266 crint.high_rng_delq_scr,
1267 nullif(crint.insert_update_flag, :p_gmiss_char) insert_update_flag,
1268 nullif(crint.judgement_ind, :p_gmiss_char) judgement_ind,
1269 nullif(crint.lien_ind, :p_gmiss_char) lien_ind,
1270 crint.low_rng_delq_scr,
1271 nullif(crint.maximum_credit_currency_code, :p_gmiss_char) maximum_credit_currency_code,
1272 crint.maximum_credit_recommendation,
1273 crint.negv_pmt_expl,
1274 nullif(crint.no_trade_ind, :p_gmiss_char) no_trade_ind,
1275 crint.num_prnt_bkcy_convs,
1276 crint.num_prnt_bkcy_filing,
1277 crint.num_spcl_event,
1278 crint.num_trade_experiences,
1279 crint.oprg_spec_evnt_ind,
1280 crint.other_spec_evnt_ind,
1281 crint.party_orig_system,
1282 crint.party_orig_system_reference,
1283 crint.paydex_comment,
1284 crint.paydex_firm_comment,
1285 crint.paydex_firm_days,
1286 crint.paydex_industry_comment,
1287 crint.paydex_industry_days,
1288 crint.paydex_norm,
1289 crint.paydex_score,
1290 crint.paydex_three_months_ago,
1291 crint.prnt_bkcy_chapter_conv,
1292 crint.prnt_bkcy_conv_date,
1293 crint.prnt_bkcy_filg_chapter,
1294 crint.prnt_bkcy_filg_date,
1295 crint.prnt_bkcy_filg_type,
1296 crint.prnt_hq_bkcy_ind,
1297 crint.pub_rec_expl,
1298 trunc(crint.rated_as_of_date) rated_as_of_date,
1299 crint.rating,
1300 crint.rating_organization,
1301 crint.secured_flng_ind,
1302 crint.slow_trade_expl,
1303 crint.spcl_event_comment,
1304 crint.spcl_event_update_date,
1305 crint.spcl_evnt_txt,
1306 nullif(crint.suit_ind, :p_gmiss_char) suit_ind,
1307 crsg.error_flag,
1308 nvl2(cr_l1.lookup_code, ''Y'', null) cr_l1_code,
1309 nvl2(cr_l2.lookup_code, ''Y'', null) cr_l2_code,
1310 nvl2(cr_l3.lookup_code, ''Y'', null) cr_l3_code,
1311 nvl2(cr_l4.lookup_code, ''Y'', null) cr_l4_code,
1312 nvl2(cr_l5.lookup_code, ''Y'', null) cr_l5_code,
1313 nvl2(cr_l6.lookup_code, ''Y'', null) cr_l6_code,
1314 nvl2(cr_l7.lookup_code, ''Y'', null) cr_l7_code,
1315 nvl2(cr_l8.lookup_code, ''Y'', null) cr_l8_code,
1316 nvl2(cr_l9.lookup_code, ''Y'', null) cr_l9_code,
1317 nvl2(cr_l10.lookup_code, ''Y'', null) cr_l10_code,
1318 nvl2(cr_l11.lookup_code, ''Y'', null) cr_l11_code,
1319 nvl2(cr_l12.lookup_code, ''Y'', null) cr_l12_code,
1320 nvl2(cr_l13.lookup_code, ''Y'', null) cr_l13_code,
1321 nvl2(cr_l14.lookup_code, ''Y'', null) cr_l14_code,
1322 nvl2(cr_l15.lookup_code, ''Y'', null) cr_l15_code,
1323 nvl2(cr_l16.lookup_code, ''Y'', null) cr_l16_code,
1324 nvl2(cr_l17.lookup_code, ''Y'', null) cr_l17_code,
1325 nvl2(cr_l18.lookup_code, ''Y'', null) cr_l18_code,
1326 nvl2(cr_l19.lookup_code, ''Y'', null) cr_l19_code,
1327 nvl2(cr_l20.lookup_code, ''Y'', null) cr_l20_code,
1328 nvl2(cr_l21.lookup_code, ''Y'', null) cr_l21_code,
1329 nvl2(cr_l22.lookup_code, ''Y'', null) cr_l22_code,
1330 nvl2(cr_l23.lookup_code, ''Y'', null) cr_l23_code,
1331 nvl2(fc.CURRENCY_CODE, ''Y'', null) fc_code,
1332 nvl2(createdby_l.lookup_code, ''Y'', null) createdby_l
1333 from hz_imp_creditrtngs_int crint,
1334 hz_imp_creditrtngs_sg crsg,
1335 fnd_lookup_values cr_l1,
1336 fnd_lookup_values cr_l2,
1337 fnd_lookup_values cr_l3,
1338 fnd_lookup_values cr_l4,
1339 fnd_lookup_values cr_l5,
1340 fnd_lookup_values cr_l6,
1341 fnd_lookup_values cr_l7,
1342 fnd_lookup_values cr_l8,
1343 fnd_lookup_values cr_l9,
1344 fnd_lookup_values cr_l10,
1345 fnd_lookup_values cr_l11,
1346 fnd_lookup_values cr_l12,
1347 fnd_lookup_values cr_l13,
1348 fnd_lookup_values cr_l14,
1352 fnd_lookup_values cr_l18,
1349 fnd_lookup_values cr_l15,
1350 fnd_lookup_values cr_l16,
1351 fnd_lookup_values cr_l17,
1353 fnd_lookup_values cr_l19,
1354 fnd_lookup_values cr_l20,
1355 fnd_lookup_values cr_l21,
1356 fnd_lookup_values cr_l22,
1357 fnd_lookup_values cr_l23,
1358 fnd_currencies fc,
1359 hz_parties hp,
1360 fnd_lookup_values createdby_l
1361 where cr_l1.lookup_code(+) = crint.credit_score_override_code
1362 and cr_l1.lookup_type(+) = ''FAILURE_SCORE_OVERRIDE_CODE''
1363 and cr_l1.language (+) = userenv(''LANG'')
1364 and cr_l1.view_application_id (+) = 222
1365 and cr_l1.security_group_id (+) =
1366 fnd_global.lookup_security_group(''FAILURE_SCORE_OVERRIDE_CODE'', 222)
1367 and cr_l2.lookup_code(+) = crint.failure_score_commentary
1368 and cr_l2.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
1369 and cr_l2.language (+) = userenv(''LANG'')
1370 and cr_l2.view_application_id (+) = 222
1371 and cr_l2.security_group_id (+) =
1372 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
1373 and cr_l3.lookup_code(+) = crint.failure_score_commentary2
1374 and cr_l3.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
1375 and cr_l3.language (+) = userenv(''LANG'')
1376 and cr_l3.view_application_id (+) = 222
1377 and cr_l3.security_group_id (+) =
1378 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
1379 and cr_l4.lookup_code(+) = crint.failure_score_commentary3
1380 and cr_l4.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
1381 and cr_l4.language (+) = userenv(''LANG'')
1382 and cr_l4.view_application_id (+) = 222
1383 and cr_l4.security_group_id (+) =
1384 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
1385 and cr_l5.lookup_code(+) = crint.failure_score_commentary4
1386 and cr_l5.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
1387 and cr_l5.language (+) = userenv(''LANG'')
1388 and cr_l5.view_application_id (+) = 222
1389 and cr_l5.security_group_id (+) =
1390 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
1391 and cr_l6.lookup_code(+) = crint.failure_score_commentary5
1392 and cr_l6.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
1393 and cr_l6.language (+) = userenv(''LANG'')
1394 and cr_l6.view_application_id (+) = 222
1395 and cr_l6.security_group_id (+) =
1396 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
1397 and cr_l7.lookup_code(+) = crint.failure_score_commentary6
1398 and cr_l7.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
1399 and cr_l7.language (+) = userenv(''LANG'')
1400 and cr_l7.view_application_id (+) = 222
1401 and cr_l7.security_group_id (+) =
1402 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
1403 and cr_l8.lookup_code(+) = crint.failure_score_commentary7
1404 and cr_l8.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
1405 and cr_l8.language (+) = userenv(''LANG'')
1406 and cr_l8.view_application_id (+) = 222
1407 and cr_l8.security_group_id (+) =
1408 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
1409 and cr_l9.lookup_code(+) = crint.failure_score_commentary8
1410 and cr_l9.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
1411 and cr_l9.language (+) = userenv(''LANG'')
1412 and cr_l9.view_application_id (+) = 222
1413 and cr_l9.security_group_id (+) =
1414 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
1415 and cr_l10.lookup_code(+) = crint.failure_score_commentary9
1416 and cr_l10.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
1417 and cr_l10.language (+) = userenv(''LANG'')
1418 and cr_l10.view_application_id (+) = 222
1419 and cr_l10.security_group_id (+) =
1420 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
1421 and cr_l11.lookup_code(+) = crint.failure_score_commentary10
1422 and cr_l11.lookup_type(+) = ''FAILURE_SCORE_COMMENTARY''
1423 and cr_l11.language (+) = userenv(''LANG'')
1424 and cr_l11.view_application_id (+) = 222
1425 and cr_l11.security_group_id (+) =
1426 fnd_global.lookup_security_group(''FAILURE_SCORE_COMMENTARY'', 222)
1427 and cr_l12.lookup_code(+) = crint.failure_score_override_code
1428 and cr_l12.lookup_type(+) = ''FAILURE_SCORE_OVERRIDE_CODE''
1429 and cr_l12.language (+) = userenv(''LANG'')
1430 and cr_l12.view_application_id (+) = 222
1431 and cr_l12.security_group_id (+) =
1432 fnd_global.lookup_security_group(''FAILURE_SCORE_OVERRIDE_CODE'', 222)
1433 and cr_l13.lookup_code(+) = crint.credit_score_commentary
1434 and cr_l13.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
1435 and cr_l13.language (+) = userenv(''LANG'')
1436 and cr_l13.view_application_id (+) = 222
1437 and cr_l13.security_group_id (+) =
1438 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
1439 and cr_l14.lookup_code(+) = crint.credit_score_commentary2
1440 and cr_l14.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
1441 and cr_l14.language (+) = userenv(''LANG'')
1442 and cr_l14.view_application_id (+) = 222
1443 and cr_l14.security_group_id (+) =
1444 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
1445 and cr_l15.lookup_code(+) = crint.credit_score_commentary3
1449 and cr_l15.security_group_id (+) =
1446 and cr_l15.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
1447 and cr_l15.language (+) = userenv(''LANG'')
1448 and cr_l15.view_application_id (+) = 222
1450 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
1451 and cr_l16.lookup_code(+) = crint.credit_score_commentary4
1452 and cr_l16.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
1453 and cr_l16.language (+) = userenv(''LANG'')
1454 and cr_l16.view_application_id (+) = 222
1455 and cr_l16.security_group_id (+) =
1456 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
1457 and cr_l17.lookup_code(+) = crint.credit_score_commentary5
1458 and cr_l17.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
1459 and cr_l17.language (+) = userenv(''LANG'')
1460 and cr_l17.view_application_id (+) = 222
1461 and cr_l17.security_group_id (+) =
1462 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
1463 and cr_l18.lookup_code(+) = crint.credit_score_commentary6
1464 and cr_l18.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
1465 and cr_l18.language (+) = userenv(''LANG'')
1466 and cr_l18.view_application_id (+) = 222
1467 and cr_l18.security_group_id (+) =
1468 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
1469 and cr_l19.lookup_code(+) = crint.credit_score_commentary7
1470 and cr_l19.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
1471 and cr_l19.language (+) = userenv(''LANG'')
1472 and cr_l19.view_application_id (+) = 222
1473 and cr_l19.security_group_id (+) =
1474 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
1475 and cr_l20.lookup_code(+) = crint.credit_score_commentary8
1476 and cr_l20.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
1477 and cr_l20.language (+) = userenv(''LANG'')
1478 and cr_l20.view_application_id (+) = 222
1479 and cr_l20.security_group_id (+) =
1480 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
1481 and cr_l21.lookup_code(+) = crint.credit_score_commentary9
1482 and cr_l21.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
1483 and cr_l21.language (+) = userenv(''LANG'')
1484 and cr_l21.view_application_id (+) = 222
1485 and cr_l21.security_group_id (+) =
1486 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
1487 and cr_l22.lookup_code(+) = crint.credit_score_commentary10
1488 and cr_l22.lookup_type(+) = ''CREDIT_SCORE_COMMENTARY''
1489 and cr_l22.language (+) = userenv(''LANG'')
1490 and cr_l22.view_application_id (+) = 222
1491 and cr_l22.security_group_id (+) =
1492 fnd_global.lookup_security_group(''CREDIT_SCORE_COMMENTARY'', 222)
1493 and cr_l23.lookup_code(+) = crint.prnt_hq_bkcy_ind
1494 and cr_l23.lookup_type(+) = ''PRNT_HQ_IND''
1495 and cr_l23.language (+) = userenv(''LANG'')
1496 and cr_l23.view_application_id (+) = 222
1497 and cr_l23.security_group_id (+) =
1498 fnd_global.lookup_security_group(''PRNT_HQ_IND'', 222)
1499 and createdby_l.lookup_code (+) = crint.created_by_module
1500 and createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
1501 and createdby_l.language (+) = userenv(''LANG'')
1502 and createdby_l.view_application_id (+) = 222
1503 and createdby_l.security_group_id (+) =
1504 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
1505 and fc.currency_code(+) = crint.maximum_credit_currency_code
1506 and fc.currency_flag(+) = ''Y''
1507 and fc.enabled_flag(+) = ''Y''
1508 and hp.party_id (+) = crsg.party_id
1509 and hp.status (+) = ''A''
1510 and crint.rowid = crsg.int_row_id
1511 and crsg.batch_id = :p_batch_id
1512 and crsg.party_orig_system = :p_os
1513 and crsg.batch_mode_flag = :p_mode
1514 and crsg.party_orig_system_reference between :p_from_osr and :p_to_osr
1515 and crsg.action_flag = ''I''
1516 ';
1517 /*
1518 Fix bug 4175285: Remove duplicate selection.Since parties with same OS+OSR but different
1519 party_id can exist in a batch, when we querying, duplicate records may be created.
1520 E.g. There are 2 parties in a DNB batch:
1521 OS OSR PID STATUS
1522 ---------------------------
1523 DNB 456 1002 A
1524 DNB 456 1003 A
1525
1526 The Status will set to 'I' after stage 3. Without this where clause:
1527 'and party_mosr.party_id = nvl(crsg.party_id,party_mosr.party_id)'
1528 The above query will return duplicate records for the same credit report and raise
1529 _U1 Unique index constraint error.
1530
1531 */
1532
1533 l_where_enabled_lookup_sql varchar2(15000) :=
1534 ' AND (cr_l1.ENABLED_FLAG(+) = ''Y'' AND
1535 TRUNC(:l_sysdate) BETWEEN
1536 TRUNC(NVL( cr_l1.START_DATE_ACTIVE,:l_sysdate ) ) AND
1537 TRUNC(NVL( cr_l1.END_DATE_ACTIVE,:l_sysdate ) ) )
1538 AND (cr_l2.ENABLED_FLAG(+) = ''Y'' AND
1539 TRUNC(:l_sysdate) BETWEEN
1540 TRUNC(NVL( cr_l2.START_DATE_ACTIVE,:l_sysdate ) ) AND
1541 TRUNC(NVL( cr_l2.END_DATE_ACTIVE,:l_sysdate ) ) )
1542 AND (cr_l3.ENABLED_FLAG(+) = ''Y'' AND
1543 TRUNC(:l_sysdate) BETWEEN
1544 TRUNC(NVL( cr_l3.START_DATE_ACTIVE,:l_sysdate ) ) AND
1545 TRUNC(NVL( cr_l3.END_DATE_ACTIVE,:l_sysdate ) ) )
1546 AND (cr_l4.ENABLED_FLAG(+) = ''Y'' AND
1547 TRUNC(:l_sysdate) BETWEEN
1548 TRUNC(NVL( cr_l4.START_DATE_ACTIVE,:l_sysdate ) ) AND
1549 TRUNC(NVL( cr_l4.END_DATE_ACTIVE,:l_sysdate ) ) )
1550 AND (cr_l5.ENABLED_FLAG(+) = ''Y'' AND
1551 TRUNC(:l_sysdate) BETWEEN
1552 TRUNC(NVL( cr_l5.START_DATE_ACTIVE,:l_sysdate ) ) AND
1553 TRUNC(NVL( cr_l5.END_DATE_ACTIVE,:l_sysdate ) ) )
1557 TRUNC(NVL( cr_l6.END_DATE_ACTIVE,:l_sysdate ) ) )
1554 AND (cr_l6.ENABLED_FLAG(+) = ''Y'' AND
1555 TRUNC(:l_sysdate) BETWEEN
1556 TRUNC(NVL( cr_l6.START_DATE_ACTIVE,:l_sysdate ) ) AND
1558 AND (cr_l7.ENABLED_FLAG(+) = ''Y'' AND
1559 TRUNC(:l_sysdate) BETWEEN
1560 TRUNC(NVL( cr_l7.START_DATE_ACTIVE,:l_sysdate ) ) AND
1561 TRUNC(NVL( cr_l7.END_DATE_ACTIVE,:l_sysdate ) ) )
1562 AND (cr_l8.ENABLED_FLAG(+) = ''Y'' AND
1563 TRUNC(:l_sysdate) BETWEEN
1564 TRUNC(NVL( cr_l8.START_DATE_ACTIVE,:l_sysdate ) ) AND
1565 TRUNC(NVL( cr_l8.END_DATE_ACTIVE,:l_sysdate ) ) )
1566 AND (cr_l9.ENABLED_FLAG(+) = ''Y'' AND
1567 TRUNC(:l_sysdate) BETWEEN
1568 TRUNC(NVL( cr_l9.START_DATE_ACTIVE,:l_sysdate ) ) AND
1569 TRUNC(NVL( cr_l9.END_DATE_ACTIVE,:l_sysdate ) ) )
1570 AND (cr_l10.ENABLED_FLAG(+) = ''Y'' AND
1571 TRUNC(:l_sysdate) BETWEEN
1572 TRUNC(NVL( cr_l10.START_DATE_ACTIVE,:l_sysdate ) ) AND
1573 TRUNC(NVL( cr_l10.END_DATE_ACTIVE,:l_sysdate ) ) )
1574 AND (cr_l11.ENABLED_FLAG(+) = ''Y'' AND
1575 TRUNC(:l_sysdate) BETWEEN
1576 TRUNC(NVL( cr_l11.START_DATE_ACTIVE,:l_sysdate ) ) AND
1577 TRUNC(NVL( cr_l11.END_DATE_ACTIVE,:l_sysdate ) ) )
1578 AND (cr_l12.ENABLED_FLAG(+) = ''Y'' AND
1579 TRUNC(:l_sysdate) BETWEEN
1580 TRUNC(NVL( cr_l12.START_DATE_ACTIVE,:l_sysdate ) ) AND
1581 TRUNC(NVL( cr_l12.END_DATE_ACTIVE,:l_sysdate ) ) )
1582 AND (cr_l13.ENABLED_FLAG(+) = ''Y'' AND
1583 TRUNC(:l_sysdate) BETWEEN
1584 TRUNC(NVL( cr_l13.START_DATE_ACTIVE,:l_sysdate ) ) AND
1585 TRUNC(NVL( cr_l13.END_DATE_ACTIVE,:l_sysdate ) ) )
1586 AND (cr_l14.ENABLED_FLAG(+) = ''Y'' AND
1587 TRUNC(:l_sysdate) BETWEEN
1588 TRUNC(NVL( cr_l14.START_DATE_ACTIVE,:l_sysdate ) ) AND
1589 TRUNC(NVL( cr_l14.END_DATE_ACTIVE,:l_sysdate ) ) )
1590 AND (cr_l15.ENABLED_FLAG(+) = ''Y'' AND
1591 TRUNC(:l_sysdate) BETWEEN
1592 TRUNC(NVL( cr_l15.START_DATE_ACTIVE,:l_sysdate ) ) AND
1593 TRUNC(NVL( cr_l15.END_DATE_ACTIVE,:l_sysdate ) ) )
1594 AND (cr_l16.ENABLED_FLAG(+) = ''Y'' AND
1595 TRUNC(:l_sysdate) BETWEEN
1596 TRUNC(NVL( cr_l16.START_DATE_ACTIVE,:l_sysdate ) ) AND
1597 TRUNC(NVL( cr_l16.END_DATE_ACTIVE,:l_sysdate ) ) )
1598 AND (cr_l17.ENABLED_FLAG(+) = ''Y'' AND
1599 TRUNC(:l_sysdate) BETWEEN
1600 TRUNC(NVL( cr_l17.START_DATE_ACTIVE,:l_sysdate ) ) AND
1601 TRUNC(NVL( cr_l17.END_DATE_ACTIVE,:l_sysdate ) ) )
1602 AND (cr_l18.ENABLED_FLAG(+) = ''Y'' AND
1603 TRUNC(:l_sysdate) BETWEEN
1604 TRUNC(NVL( cr_l18.START_DATE_ACTIVE,:l_sysdate ) ) AND
1605 TRUNC(NVL( cr_l18.END_DATE_ACTIVE,:l_sysdate ) ) )
1606 AND (cr_l19.ENABLED_FLAG(+) = ''Y'' AND
1607 TRUNC(:l_sysdate) BETWEEN
1608 TRUNC(NVL( cr_l19.START_DATE_ACTIVE,:l_sysdate ) ) AND
1609 TRUNC(NVL( cr_l19.END_DATE_ACTIVE,:l_sysdate ) ) )
1610 AND (cr_l20.ENABLED_FLAG(+) = ''Y'' AND
1611 TRUNC(:l_sysdate) BETWEEN
1612 TRUNC(NVL( cr_l20.START_DATE_ACTIVE,:l_sysdate ) ) AND
1613 TRUNC(NVL( cr_l20.END_DATE_ACTIVE,:l_sysdate ) ) )
1614 AND (cr_l21.ENABLED_FLAG(+) = ''Y'' AND
1615 TRUNC(:l_sysdate) BETWEEN
1616 TRUNC(NVL( cr_l21.START_DATE_ACTIVE,:l_sysdate ) ) AND
1617 TRUNC(NVL( cr_l21.END_DATE_ACTIVE,:l_sysdate ) ) )
1618 AND (cr_l22.ENABLED_FLAG(+) = ''Y'' AND
1619 TRUNC(:l_sysdate) BETWEEN
1620 TRUNC(NVL( cr_l22.START_DATE_ACTIVE,:l_sysdate ) ) AND
1621 TRUNC(NVL( cr_l22.END_DATE_ACTIVE,:l_sysdate ) ) )
1622 AND (cr_l23.ENABLED_FLAG(+) = ''Y'' AND
1623 TRUNC(:l_sysdate) BETWEEN
1624 TRUNC(NVL( cr_l23.START_DATE_ACTIVE,:l_sysdate ) ) AND
1625 TRUNC(NVL( cr_l23.END_DATE_ACTIVE,:l_sysdate ) ) )
1626 AND ( createdby_l.ENABLED_FLAG(+) = ''Y'' AND
1627 TRUNC(:l_sysdate) BETWEEN
1628 TRUNC(NVL( createdby_l.START_DATE_ACTIVE,:l_sysdate ) ) AND
1629 TRUNC(NVL( createdby_l.END_DATE_ACTIVE,:l_sysdate ) ) ) ';
1630
1631 l_end_sql VARCHAR2(10) := '); END;';
1632 l_first_run_clause varchar2(40) := ' AND crint.INTERFACE_STATUS is null';
1633 l_re_run_clause varchar2(40) := ' AND crint.INTERFACE_STATUS = ''C''';
1634 l_final_query varchar2(32767) := NULL;
1635 l_debug_prefix VARCHAR2(30) := '';
1636 BEGIN
1637
1638 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1639 hz_utility_v2pub.debug(p_message=>'CR:process_cr_ins()+',
1640 p_prefix=>l_debug_prefix,
1641 p_msg_level=>fnd_log.level_procedure);
1642 END IF;
1643 -- savepoint process_cr_ins;
1644 FND_MSG_PUB.initialize;
1645 --Initialize API return status to success.
1646 x_return_status := FND_API.G_RET_STS_SUCCESS;
1647
1648 IF P_DML_RECORD.ALLOW_DISABLED_LOOKUP = 'Y' THEN
1649 IF P_DML_RECORD.RERUN = 'N' /*** First Run ***/ THEN
1650 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1651 hz_utility_v2pub.debug(p_message=>'CR:first run - ins',
1652 p_prefix =>l_debug_prefix,
1653 p_msg_level=>fnd_log.level_statement);
1654 END IF;
1655 l_final_query := l_sql_str1 || l_first_run_clause ||l_end_sql;
1656 ELSE /* Rerun to correct errors */
1657 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1658 hz_utility_v2pub.debug(p_message=>'CR:rerun - ins',
1659 p_prefix =>l_debug_prefix,
1660 p_msg_level=>fnd_log.level_statement);
1661 END IF;
1662 l_final_query := l_sql_str1 || l_re_run_clause ||l_end_sql;
1663 END IF;
1664 BEGIN -- anonymous block
1665 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1666 hz_utility_v2pub.debug(p_message=>'CR:disabled lookup anonymous block',
1667 p_prefix =>l_debug_prefix,
1668 p_msg_level=>fnd_log.level_statement);
1669 END IF;
1673 P_DML_RECORD.USER_ID,
1670 EXECUTE IMMEDIATE l_final_query USING
1671 P_DML_RECORD.ACTUAL_CONTENT_SRC,
1672 P_DML_RECORD.APPLICATION_ID,
1674 P_DML_RECORD.SYSDATE,
1675 P_DML_RECORD.LAST_UPDATE_LOGIN,
1676 P_DML_RECORD.PROGRAM_APPLICATION_ID,
1677 P_DML_RECORD.PROGRAM_ID,
1678 P_DML_RECORD.REQUEST_ID,
1679 P_DML_RECORD.BATCH_ID,
1680 P_DML_RECORD.GMISS_CHAR,
1681 P_DML_RECORD.OS,
1682 P_DML_RECORD.BATCH_MODE_FLAG,
1683 P_DML_RECORD.FROM_OSR,
1684 P_DML_RECORD.TO_OSR;
1685 EXCEPTION
1686 WHEN DUP_VAL_ON_INDEX THEN
1687 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1688 hz_utility_v2pub.debug(p_message=>'CR:DUP_VAL_IDX error',
1689 p_prefix=>'ERROR',
1690 p_msg_level=>fnd_log.level_error);
1691 END IF;
1692 populate_error_table(P_DML_RECORD, 'Y', sqlerrm);
1693 x_return_status := FND_API.G_RET_STS_ERROR;
1694 WHEN OTHERS THEN
1695 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1696 hz_utility_v2pub.debug(p_message=>'CR: others err in exec imm',
1697 p_prefix=>'SQL ERROR',
1698 p_msg_level=>fnd_log.level_error);
1699 hz_utility_v2pub.debug(p_message=>'CR:'||sqlerrm,
1700 p_prefix=>'SQL ERROR',
1701 p_msg_level=>fnd_log.level_error);
1702 END IF;
1703 populate_error_table(P_DML_RECORD, 'N', sqlerrm);
1704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1705 END; -- end anonymous block for MTI with diabled lookups -ends
1706
1707 ELSE -- enabled lookup case
1708
1709 IF P_DML_RECORD.RERUN = 'N' /*** First Run ***/ THEN
1710 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1711 hz_utility_v2pub.debug(p_message=>'CR:first run - ins',
1712 p_prefix =>l_debug_prefix,
1713 p_msg_level=>fnd_log.level_statement);
1714 END IF;
1715 l_final_query := l_sql_str1 || l_first_run_clause ||l_where_enabled_lookup_sql||l_end_sql;
1716 ELSE /* Rerun to correct errors */
1717 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1718 hz_utility_v2pub.debug(p_message=>'CR:rerun - ins',
1719 p_prefix =>l_debug_prefix,
1720 p_msg_level=>fnd_log.level_statement);
1721 END IF;
1722 l_final_query := l_sql_str1 || l_re_run_clause ||l_where_enabled_lookup_sql||l_end_sql;
1723 END IF;
1724
1725 BEGIN -- anonymous block
1726 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1727 hz_utility_v2pub.debug(p_message=>'CR:enable lookup anonymous block',
1728 p_prefix =>l_debug_prefix,
1729 p_msg_level=>fnd_log.level_statement);
1730 END IF;
1731 EXECUTE IMMEDIATE l_final_query USING
1732 P_DML_RECORD.ACTUAL_CONTENT_SRC,
1733 P_DML_RECORD.APPLICATION_ID,
1734 P_DML_RECORD.USER_ID,
1735 P_DML_RECORD.SYSDATE,
1736 P_DML_RECORD.LAST_UPDATE_LOGIN,
1737 P_DML_RECORD.PROGRAM_APPLICATION_ID,
1738 P_DML_RECORD.PROGRAM_ID,
1739 P_DML_RECORD.REQUEST_ID,
1740 P_DML_RECORD.BATCH_ID,
1741 P_DML_RECORD.GMISS_CHAR,
1742 P_DML_RECORD.OS,
1743 P_DML_RECORD.BATCH_MODE_FLAG,
1744 P_DML_RECORD.FROM_OSR,
1745 P_DML_RECORD.TO_OSR;
1746 EXCEPTION
1747 WHEN DUP_VAL_ON_INDEX THEN
1748
1749 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1750 hz_utility_v2pub.debug(p_message=>'CR:DUP_VAL_IDX error',
1751 p_prefix=>'ERROR',
1752 p_msg_level=>fnd_log.level_error);
1753 END IF;
1754 populate_error_table(P_DML_RECORD, 'Y', sqlerrm);
1755 x_return_status := FND_API.G_RET_STS_ERROR;
1756 WHEN OTHERS THEN
1757 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1758 hz_utility_v2pub.debug(p_message=>'CR: others err in exec imm',
1759 p_prefix=>'SQL ERROR',
1760 p_msg_level=>fnd_log.level_error);
1761 hz_utility_v2pub.debug(p_message=>'CR:'||sqlerrm,
1762 p_prefix=>'SQL ERROR',
1763 p_msg_level=>fnd_log.level_error);
1764 END IF;
1765 populate_error_table(P_DML_RECORD, 'N', sqlerrm);
1766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1767 END; -- end anonymous block for MTI with enabled lookups - ends
1768 END IF; -- disaled lookup check ends
1769 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1770 hz_utility_v2pub.debug(p_message=>'CR:bfr exec imm',
1771 p_prefix =>l_debug_prefix,
1772 p_msg_level=>fnd_log.level_statement);
1773 END IF;
1774 --------
1775 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1776 hz_utility_v2pub.debug(p_message=>'CR:process_cr_ins()-',
1777 p_prefix=>l_debug_prefix,
1778 p_msg_level=>fnd_log.level_procedure);
1779 END IF;
1780
1781 EXCEPTION
1782 WHEN OTHERS THEN
1783 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1784 hz_utility_v2pub.debug(p_message=>'CR:process_cr_ins() got others excep',
1785 p_prefix=>'SQL ERROR',
1786 p_msg_level=>fnd_log.level_error);
1787 hz_utility_v2pub.debug(p_message=>sqlerrm,
1788 p_prefix=>'SQL ERROR',
1792 END process_cr_ins;
1789 p_msg_level=>fnd_log.level_error);
1790 END IF;
1791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1793 --------------------------------------
1794 /**
1795 * PRIVATE PROCEDURE process_cr_upd
1796 *
1797 * DESCRIPTION
1798 * processes recs identified for
1799 * updation and does dml on
1800 * hz_credit_ratings and errors tbl
1801 *
1802 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1803 *
1804 * MODIFICATION HISTORY
1805 *
1806 * 07-15-2003 Srikanth o Created.
1807 *
1808 */
1809 --------------------------------------
1810 PROCEDURE process_cr_upd(
1811 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
1812 ,x_return_status OUT NOCOPY VARCHAR2
1813 ,x_msg_count OUT NOCOPY NUMBER
1814 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
1815
1816 -- local variables
1817 c_update_cursor update_cursor_type;
1818 l_dml_exception varchar2(1) := 'N';
1819 l_debug_prefix VARCHAR2(30) := '';
1820 BEGIN
1821 -- flow
1822 -- bulk fetch into collection
1823 -- do forall update on the interface table
1824 -- report_errors()
1825 --
1826
1827 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1828 hz_utility_v2pub.debug(p_message=>'CR:process_cr_upd()+',
1829 p_prefix=>l_debug_prefix,
1830 p_msg_level=>fnd_log.level_procedure);
1831 END IF;
1832
1833 -- savepoint process_cr_ins;
1834 FND_MSG_PUB.initialize;
1835
1836 --Initialize API return status to success.
1837 x_return_status := FND_API.G_RET_STS_SUCCESS;
1838 open_upd_cursor (
1839 P_DML_RECORD
1840 ,c_update_cursor);
1841
1842 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1843 hz_utility_v2pub.debug(p_message=>'CR:bfr blk collect in upd()',
1844 p_prefix =>l_debug_prefix,
1845 p_msg_level=>fnd_log.level_statement);
1846 END IF;
1847 -- bulk collect all the insert rec in this Work unit
1848 -- into set of scalar collections.
1849 FETCH c_update_cursor BULK COLLECT INTO
1850 l_action_flag,
1851 l_CREDIT_RATING_ID,
1852 l_row_id,
1853 l_AVG_HIGH_CREDIT,
1854 l_BANKRUPTCY_IND,
1855 l_BUSINESS_DISCONTINUED,
1856 l_CLAIMS_IND,
1857 l_COMMENTS,
1858 l_CREATED_BY_MODULE,
1859 l_CREDIT_SCORE,
1860 l_CREDIT_SCORE_AGE,
1861 l_CREDIT_SCORE_CLASS,
1862 l_CREDIT_SCORE_COMMENTARY,
1863 l_CREDIT_SCORE_COMMENTARY10,
1864 l_CREDIT_SCORE_COMMENTARY2,
1865 l_CREDIT_SCORE_COMMENTARY3,
1866 l_CREDIT_SCORE_COMMENTARY4,
1867 l_CREDIT_SCORE_COMMENTARY5,
1868 l_CREDIT_SCORE_COMMENTARY6,
1869 l_CREDIT_SCORE_COMMENTARY7,
1870 l_CREDIT_SCORE_COMMENTARY8,
1871 l_CREDIT_SCORE_COMMENTARY9,
1872 l_CREDIT_SCORE_DATE,
1873 l_CREDIT_SCORE_INCD_DEFAULT,
1874 l_CREDIT_SCORE_NATL_PERCENTILE,
1875 l_CREDIT_SCORE_OVERRIDE_CODE,
1876 l_CRIMINAL_PROCEEDING_IND,
1877 l_CR_SCR_CLAS_EXPL,
1878 l_DEBARMENTS_COUNT,
1879 l_DEBARMENTS_DATE,
1880 l_DEBARMENT_IND,
1881 l_DELQ_PMT_PCTG_FOR_ALL_FIRMS,
1882 l_DELQ_PMT_RNG_PRCNT,
1883 l_DESCRIPTION,
1884 l_DET_HISTORY_IND,
1885 l_DISASTER_IND,
1886 l_FAILURE_SCORE,
1887 l_FAILURE_SCORE_AGE,
1888 l_FAILURE_SCORE_CLASS,
1889 l_FAILURE_SCORE_COMMENTARY,
1890 l_FAILURE_SCORE_COMMENTARY10,
1891 l_FAILURE_SCORE_COMMENTARY2,
1892 l_FAILURE_SCORE_COMMENTARY3,
1893 l_FAILURE_SCORE_COMMENTARY4,
1894 l_FAILURE_SCORE_COMMENTARY5,
1895 l_FAILURE_SCORE_COMMENTARY6,
1896 l_FAILURE_SCORE_COMMENTARY7,
1897 l_FAILURE_SCORE_COMMENTARY8,
1898 l_FAILURE_SCORE_COMMENTARY9,
1899 l_FAILURE_SCORE_DATE,
1900 l_FAILURE_SCORE_INCD_DEFAULT,
1901 l_FAILURE_SCORE_NATNL_PERC,
1902 l_FAILURE_SCORE_OVERRIDE_CODE,
1903 l_FINCL_EMBT_IND,
1904 l_FINCL_LGL_EVENT_IND,
1905 l_GLOBAL_FAILURE_SCORE,
1906 l_HIGH_CREDIT,
1907 l_HIGH_RNG_DELQ_SCR,
1908 l_INSERT_UPDATE_FLAG,
1909 l_JUDGEMENT_IND,
1910 l_LIEN_IND,
1911 l_LOW_RNG_DELQ_SCR,
1912 l_MAXIMUM_CREDIT_CURRENCY_CODE,
1913 l_MAXIMUM_CREDIT_RECOMM,
1914 l_NEGV_PMT_EXPL,
1915 l_NO_TRADE_IND,
1916 l_NUM_PRNT_BKCY_CONVS,
1917 l_NUM_PRNT_BKCY_FILING,
1918 l_NUM_SPCL_EVENT,
1919 l_NUM_TRADE_EXPERIENCES,
1920 l_OPRG_SPEC_EVNT_IND,
1921 l_OTHER_SPEC_EVNT_IND,
1922 l_PARTY_ORIG_SYSTEM,
1923 l_PARTY_ORIG_SYSTEM_REFERENCE,
1924 l_PAYDEX_COMMENT,
1928 l_PAYDEX_INDUSTRY_DAYS,
1925 l_PAYDEX_FIRM_COMMENT,
1926 l_PAYDEX_FIRM_DAYS,
1927 l_PAYDEX_INDUSTRY_COMMENT,
1929 l_PAYDEX_NORM,
1930 l_PAYDEX_SCORE,
1931 l_PAYDEX_THREE_MONTHS_AGO,
1932 l_PRNT_BKCY_CHAPTER_CONV,
1933 l_PRNT_BKCY_CONV_DATE,
1934 l_PRNT_BKCY_FILG_CHAPTER,
1935 l_PRNT_BKCY_FILG_DATE,
1936 l_PRNT_BKCY_FILG_TYPE,
1937 l_PRNT_HQ_BKCY_IND,
1938 l_PUB_REC_EXPL,
1939 l_RATED_AS_OF_DATE,
1940 l_RATING,
1941 l_RATING_ORGANIZATION,
1942 l_SECURED_FLNG_IND,
1943 l_SLOW_TRADE_EXPL,
1944 l_SPCL_EVENT_COMMENT,
1945 l_SPCL_EVENT_UPDATE_DATE,
1946 l_SPCL_EVNT_TXT,
1947 l_SUIT_IND,
1948 -- flag errors collections
1949 l_SUIT_IND_err,
1950 l_BANKRUPTCY_IND_err,
1951 l_DEBARMENT_IND_err,
1952 l_FINCL_EMBT_IND_err,
1953 l_NO_TRADE_IND_err,
1954 l_JUDGEMENT_IND_err,
1955 l_LIEN_IND_err,
1956 -- lkup error collections
1957 l_CREDIT_SCR_OVERRIDE_CODE_err,
1958 l_FAILURE_SCR_COMMENTARY_err ,
1959 l_FAILURE_SCR_COMMENTARY2_err ,
1960 l_FAILURE_SCR_COMMENTARY3_err ,
1961 l_FAILURE_SCR_COMMENTARY4_err ,
1962 l_FAILURE_SCR_COMMENTARY5_err ,
1963 l_FAILURE_SCR_COMMENTARY6_err ,
1964 l_FAILURE_SCR_COMMENTARY7_err ,
1965 l_FAILURE_SCR_COMMENTARY8_err ,
1966 l_FAILURE_SCR_COMMENTARY9_err ,
1967 l_FAILURE_SCR_COMMENTARY10_err,
1968 l_FAILURE_SCR_OVERRIDE_CD_err ,
1969 l_CREDIT_SCR_COMMENTARY_err ,
1970 l_CREDIT_SCR_COMMENTARY2_err ,
1971 l_CREDIT_SCR_COMMENTARY3_err ,
1972 l_CREDIT_SCR_COMMENTARY4_err ,
1973 l_CREDIT_SCR_COMMENTARY5_err ,
1974 l_CREDIT_SCR_COMMENTARY6_err ,
1975 l_CREDIT_SCR_COMMENTARY7_err ,
1976 l_CREDIT_SCR_COMMENTARY8_err ,
1977 l_CREDIT_SCR_COMMENTARY9_err ,
1978 l_CREDIT_SCR_COMMENTARY10_err ,
1979 l_PRNT_HQ_BKCY_IND_err,
1980 l_MAX_CREDIT_CURR_CODE_err,
1981 l_action_flag_err,
1982 l_error_flag;
1983 CLOSE c_update_cursor;
1984 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1985 hz_utility_v2pub.debug(p_message=>'CR:# of upd recs to process:'||l_CREDIT_RATING_ID.count,
1986 p_prefix =>l_debug_prefix,
1987 p_msg_level=>fnd_log.level_statement);
1988 END IF;
1989
1990 -- updating TCA table from the above scalar collections
1991 BEGIN -- anonymous block
1992 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1993 hz_utility_v2pub.debug(p_message=>'CR:bfr forall upd',
1994 p_prefix =>l_debug_prefix,
1995 p_msg_level=>fnd_log.level_statement);
1996 END IF;
1997 forall j in 1..l_CREDIT_RATING_ID.count
1998 UPDATE HZ_CREDIT_RATINGS SET
1999 REQUEST_ID = P_DML_RECORD.REQUEST_ID,
2000 LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
2001 LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
2002 LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
2003 program_application_id = P_DML_RECORD.PROGRAM_APPLICATION_ID,
2004 program_id = P_DML_RECORD.PROGRAM_ID,
2005 AVG_HIGH_CREDIT = DECODE(l_AVG_HIGH_CREDIT(j), NULL,AVG_HIGH_CREDIT, P_DML_RECORD.GMISS_NUM, NULL, l_AVG_HIGH_CREDIT(j)),
2006 BANKRUPTCY_IND = DECODE(l_BANKRUPTCY_IND(j), NULL,BANKRUPTCY_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_BANKRUPTCY_IND(j)),
2007 BUSINESS_DISCONTINUED = DECODE(l_BUSINESS_DISCONTINUED(j), NULL,BUSINESS_DISCONTINUED, P_DML_RECORD.GMISS_CHAR, NULL, l_BUSINESS_DISCONTINUED(j)),
2008 CLAIMS_IND = DECODE(l_CLAIMS_IND(j), NULL,CLAIMS_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_CLAIMS_IND(j)),
2009 COMMENTS = DECODE(l_COMMENTS(j), NULL,COMMENTS, P_DML_RECORD.GMISS_CHAR, NULL, l_COMMENTS(j)),
2010 CREDIT_SCORE = DECODE(l_CREDIT_SCORE(j), NULL,CREDIT_SCORE, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE(j)),
2011 CREDIT_SCORE_AGE = DECODE(l_CREDIT_SCORE_AGE(j), NULL,CREDIT_SCORE_AGE, P_DML_RECORD.GMISS_NUM, NULL, l_CREDIT_SCORE_AGE(j)),
2012 CREDIT_SCORE_CLASS = DECODE(l_CREDIT_SCORE_CLASS(j), NULL,CREDIT_SCORE_CLASS, P_DML_RECORD.GMISS_NUM, NULL, l_CREDIT_SCORE_CLASS(j)),
2013 CREDIT_SCORE_COMMENTARY = DECODE(l_CREDIT_SCORE_COMMENTARY(j), NULL,CREDIT_SCORE_COMMENTARY, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_COMMENTARY(j)),
2014 CREDIT_SCORE_COMMENTARY10 = DECODE(l_CREDIT_SCORE_COMMENTARY10(j), NULL,CREDIT_SCORE_COMMENTARY10, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_COMMENTARY10(j)),
2015 CREDIT_SCORE_COMMENTARY2 = DECODE(l_CREDIT_SCORE_COMMENTARY2(j), NULL,CREDIT_SCORE_COMMENTARY2, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_COMMENTARY2(j)),
2016 CREDIT_SCORE_COMMENTARY3 = DECODE(l_CREDIT_SCORE_COMMENTARY3(j), NULL,CREDIT_SCORE_COMMENTARY3, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_COMMENTARY3(j)),
2017 CREDIT_SCORE_COMMENTARY4 = DECODE(l_CREDIT_SCORE_COMMENTARY4(j), NULL,CREDIT_SCORE_COMMENTARY4, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_COMMENTARY4(j)),
2018 CREDIT_SCORE_COMMENTARY5 = DECODE(l_CREDIT_SCORE_COMMENTARY5(j), NULL,CREDIT_SCORE_COMMENTARY5, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_COMMENTARY5(j)),
2019 CREDIT_SCORE_COMMENTARY6 = DECODE(l_CREDIT_SCORE_COMMENTARY6(j), NULL,CREDIT_SCORE_COMMENTARY6, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_COMMENTARY6(j)),
2020 CREDIT_SCORE_COMMENTARY7 = DECODE(l_CREDIT_SCORE_COMMENTARY7(j), NULL,CREDIT_SCORE_COMMENTARY7, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_COMMENTARY7(j)),
2021 CREDIT_SCORE_COMMENTARY8 = DECODE(l_CREDIT_SCORE_COMMENTARY8(j), NULL,CREDIT_SCORE_COMMENTARY8, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_COMMENTARY8(j)),
2025 CREDIT_SCORE_NATL_PERCENTILE = DECODE(l_CREDIT_SCORE_NATL_PERCENTILE(j), NULL,CREDIT_SCORE_NATL_PERCENTILE, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_NATL_PERCENTILE(j)),
2022 CREDIT_SCORE_COMMENTARY9 = DECODE(l_CREDIT_SCORE_COMMENTARY9(j), NULL,CREDIT_SCORE_COMMENTARY9, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_COMMENTARY9(j)),
2023 CREDIT_SCORE_DATE = DECODE(l_CREDIT_SCORE_DATE(j), NULL,CREDIT_SCORE_DATE, P_DML_RECORD.GMISS_DATE, NULL, l_CREDIT_SCORE_DATE(j)),
2024 CREDIT_SCORE_INCD_DEFAULT = DECODE(l_CREDIT_SCORE_INCD_DEFAULT(j), NULL,CREDIT_SCORE_INCD_DEFAULT, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_INCD_DEFAULT(j)),
2026 CREDIT_SCORE_OVERRIDE_CODE = DECODE(l_CREDIT_SCORE_OVERRIDE_CODE(j), NULL,CREDIT_SCORE_OVERRIDE_CODE, P_DML_RECORD.GMISS_CHAR, NULL, l_CREDIT_SCORE_OVERRIDE_CODE(j)),
2027 CRIMINAL_PROCEEDING_IND = DECODE(l_CRIMINAL_PROCEEDING_IND(j), NULL,CRIMINAL_PROCEEDING_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_CRIMINAL_PROCEEDING_IND(j)),
2028 CR_SCR_CLAS_EXPL = DECODE(l_CR_SCR_CLAS_EXPL(j), NULL,CR_SCR_CLAS_EXPL, P_DML_RECORD.GMISS_CHAR, NULL, l_CR_SCR_CLAS_EXPL(j)),
2029 DEBARMENTS_COUNT = DECODE(l_DEBARMENTS_COUNT(j), NULL,DEBARMENTS_COUNT, P_DML_RECORD.GMISS_CHAR, NULL, l_DEBARMENTS_COUNT(j)),
2030 DEBARMENTS_DATE = DECODE(l_DEBARMENTS_DATE(j), NULL,DEBARMENTS_DATE, P_DML_RECORD.GMISS_CHAR, NULL, l_DEBARMENTS_DATE(j)),
2031 DEBARMENT_IND = DECODE(l_DEBARMENT_IND(j), NULL,DEBARMENT_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_DEBARMENT_IND(j)),
2032 DELQ_PMT_PCTG_FOR_ALL_FIRMS = DECODE(l_DELQ_PMT_PCTG_FOR_ALL_FIRMS(j), NULL,DELQ_PMT_PCTG_FOR_ALL_FIRMS, P_DML_RECORD.GMISS_CHAR, NULL, l_DELQ_PMT_PCTG_FOR_ALL_FIRMS(j)),
2033 DELQ_PMT_RNG_PRCNT = DECODE(l_DELQ_PMT_RNG_PRCNT(j), NULL,DELQ_PMT_RNG_PRCNT, P_DML_RECORD.GMISS_CHAR, NULL, l_DELQ_PMT_RNG_PRCNT(j)),
2034 DESCRIPTION = DECODE(l_DESCRIPTION(j), NULL,DESCRIPTION, P_DML_RECORD.GMISS_CHAR, NULL, l_DESCRIPTION(j)),
2035 DET_HISTORY_IND = DECODE(l_DET_HISTORY_IND(j), NULL,DET_HISTORY_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_DET_HISTORY_IND(j)),
2036 DISASTER_IND = DECODE(l_DISASTER_IND(j), NULL,DISASTER_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_DISASTER_IND(j)),
2037 FAILURE_SCORE = DECODE(l_FAILURE_SCORE(j), NULL,FAILURE_SCORE, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE(j)),
2038 FAILURE_SCORE_AGE = DECODE(l_FAILURE_SCORE_AGE(j), NULL,FAILURE_SCORE_AGE, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_AGE(j)),
2039 FAILURE_SCORE_CLASS = DECODE(l_FAILURE_SCORE_CLASS(j), NULL,FAILURE_SCORE_CLASS, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_CLASS(j)),
2040 FAILURE_SCORE_COMMENTARY = DECODE(l_FAILURE_SCORE_COMMENTARY(j), NULL,FAILURE_SCORE_COMMENTARY, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_COMMENTARY(j)),
2041 FAILURE_SCORE_COMMENTARY10 = DECODE(l_FAILURE_SCORE_COMMENTARY10(j), NULL,FAILURE_SCORE_COMMENTARY10, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_COMMENTARY10(j)),
2042 FAILURE_SCORE_COMMENTARY2 = DECODE(l_FAILURE_SCORE_COMMENTARY2(j), NULL,FAILURE_SCORE_COMMENTARY2, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_COMMENTARY2(j)),
2043 FAILURE_SCORE_COMMENTARY3 = DECODE(l_FAILURE_SCORE_COMMENTARY3(j), NULL,FAILURE_SCORE_COMMENTARY3, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_COMMENTARY3(j)),
2044 FAILURE_SCORE_COMMENTARY4 = DECODE(l_FAILURE_SCORE_COMMENTARY4(j), NULL,FAILURE_SCORE_COMMENTARY4, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_COMMENTARY4(j)),
2045 FAILURE_SCORE_COMMENTARY5 = DECODE(l_FAILURE_SCORE_COMMENTARY5(j), NULL,FAILURE_SCORE_COMMENTARY5, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_COMMENTARY5(j)),
2046 FAILURE_SCORE_COMMENTARY6 = DECODE(l_FAILURE_SCORE_COMMENTARY6(j), NULL,FAILURE_SCORE_COMMENTARY6, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_COMMENTARY6(j)),
2047 FAILURE_SCORE_COMMENTARY7 = DECODE(l_FAILURE_SCORE_COMMENTARY7(j), NULL,FAILURE_SCORE_COMMENTARY7, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_COMMENTARY7(j)),
2048 FAILURE_SCORE_COMMENTARY8 = DECODE(l_FAILURE_SCORE_COMMENTARY8(j), NULL,FAILURE_SCORE_COMMENTARY8, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_COMMENTARY8(j)),
2049 FAILURE_SCORE_COMMENTARY9 = DECODE(l_FAILURE_SCORE_COMMENTARY9(j), NULL,FAILURE_SCORE_COMMENTARY9, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_COMMENTARY9(j)),
2050 FAILURE_SCORE_DATE = DECODE(l_FAILURE_SCORE_DATE(j), NULL,FAILURE_SCORE_DATE, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_DATE(j)),
2051 FAILURE_SCORE_INCD_DEFAULT = DECODE(l_FAILURE_SCORE_INCD_DEFAULT(j), NULL,FAILURE_SCORE_INCD_DEFAULT, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_INCD_DEFAULT(j)),
2052 FAILURE_SCORE_NATNL_PERCENTILE = DECODE(l_FAILURE_SCORE_NATNL_PERC(j), NULL,FAILURE_SCORE_NATNL_PERCENTILE, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_NATNL_PERC(j)),
2053 FAILURE_SCORE_OVERRIDE_CODE = DECODE(l_FAILURE_SCORE_OVERRIDE_CODE(j), NULL,FAILURE_SCORE_OVERRIDE_CODE, P_DML_RECORD.GMISS_CHAR, NULL, l_FAILURE_SCORE_OVERRIDE_CODE(j)),
2054 FINCL_EMBT_IND = DECODE(l_FINCL_EMBT_IND(j), NULL,FINCL_EMBT_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_FINCL_EMBT_IND(j)),
2055 FINCL_LGL_EVENT_IND = DECODE(l_FINCL_LGL_EVENT_IND(j), NULL,FINCL_LGL_EVENT_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_FINCL_LGL_EVENT_IND(j)),
2056 GLOBAL_FAILURE_SCORE = DECODE(l_GLOBAL_FAILURE_SCORE(j), NULL,GLOBAL_FAILURE_SCORE, P_DML_RECORD.GMISS_CHAR, NULL, l_GLOBAL_FAILURE_SCORE(j)),
2057 HIGH_CREDIT = DECODE(l_HIGH_CREDIT(j), NULL,HIGH_CREDIT, P_DML_RECORD.GMISS_CHAR, NULL, l_HIGH_CREDIT(j)),
2058 HIGH_RNG_DELQ_SCR = DECODE(l_HIGH_RNG_DELQ_SCR(j), NULL,HIGH_RNG_DELQ_SCR, P_DML_RECORD.GMISS_CHAR, NULL, l_HIGH_RNG_DELQ_SCR(j)),
2059 JUDGEMENT_IND = DECODE(l_JUDGEMENT_IND(j), NULL,JUDGEMENT_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_JUDGEMENT_IND(j)),
2063 MAXIMUM_CREDIT_RECOMMENDATION = DECODE(l_MAXIMUM_CREDIT_RECOMM(j), NULL,MAXIMUM_CREDIT_RECOMMENDATION, P_DML_RECORD.GMISS_CHAR, NULL, l_MAXIMUM_CREDIT_RECOMM(j)),
2060 LIEN_IND = DECODE(l_LIEN_IND(j), NULL,LIEN_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_LIEN_IND(j)),
2061 LOW_RNG_DELQ_SCR = DECODE(l_LOW_RNG_DELQ_SCR(j), NULL,LOW_RNG_DELQ_SCR, P_DML_RECORD.GMISS_CHAR, NULL, l_LOW_RNG_DELQ_SCR(j)),
2062 MAXIMUM_CREDIT_CURRENCY_CODE = DECODE(l_MAXIMUM_CREDIT_CURRENCY_CODE(j), NULL,MAXIMUM_CREDIT_CURRENCY_CODE, P_DML_RECORD.GMISS_CHAR, NULL, l_MAXIMUM_CREDIT_CURRENCY_CODE(j)),
2064 NEGV_PMT_EXPL = DECODE(l_NEGV_PMT_EXPL(j), NULL,NEGV_PMT_EXPL, P_DML_RECORD.GMISS_CHAR, NULL, l_NEGV_PMT_EXPL(j)),
2065 NO_TRADE_IND = DECODE(l_NO_TRADE_IND(j), NULL,NO_TRADE_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_NO_TRADE_IND(j)),
2066 NUM_PRNT_BKCY_CONVS = DECODE(l_NUM_PRNT_BKCY_CONVS(j), NULL,NUM_PRNT_BKCY_CONVS, P_DML_RECORD.GMISS_CHAR, NULL, l_NUM_PRNT_BKCY_CONVS(j)),
2067 NUM_PRNT_BKCY_FILING = DECODE(l_NUM_PRNT_BKCY_FILING(j), NULL,NUM_PRNT_BKCY_FILING, P_DML_RECORD.GMISS_CHAR, NULL, l_NUM_PRNT_BKCY_FILING(j)),
2068 NUM_SPCL_EVENT = DECODE(l_NUM_SPCL_EVENT(j), NULL,NUM_SPCL_EVENT, P_DML_RECORD.GMISS_CHAR, NULL, l_NUM_SPCL_EVENT(j)),
2069 NUM_TRADE_EXPERIENCES = DECODE(l_NUM_TRADE_EXPERIENCES(j), NULL,NUM_TRADE_EXPERIENCES, P_DML_RECORD.GMISS_CHAR, NULL, l_NUM_TRADE_EXPERIENCES(j)),
2070 OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,1)+1,
2071 OPRG_SPEC_EVNT_IND = DECODE(l_OPRG_SPEC_EVNT_IND(j), NULL,OPRG_SPEC_EVNT_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_OPRG_SPEC_EVNT_IND(j)),
2072 OTHER_SPEC_EVNT_IND = DECODE(l_OTHER_SPEC_EVNT_IND(j), NULL,OTHER_SPEC_EVNT_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_OTHER_SPEC_EVNT_IND(j)),
2073 PAYDEX_COMMENT = DECODE(l_PAYDEX_COMMENT(j), NULL,PAYDEX_COMMENT, P_DML_RECORD.GMISS_CHAR, NULL, l_PAYDEX_COMMENT(j)),
2074 PAYDEX_FIRM_COMMENT = DECODE(l_PAYDEX_FIRM_COMMENT(j), NULL,PAYDEX_FIRM_COMMENT, P_DML_RECORD.GMISS_CHAR, NULL, l_PAYDEX_FIRM_COMMENT(j)),
2075 PAYDEX_FIRM_DAYS = DECODE(l_PAYDEX_FIRM_DAYS(j), NULL,PAYDEX_FIRM_DAYS, P_DML_RECORD.GMISS_CHAR, NULL, l_PAYDEX_FIRM_DAYS(j)),
2076 PAYDEX_INDUSTRY_COMMENT = DECODE(l_PAYDEX_INDUSTRY_COMMENT(j), NULL,PAYDEX_INDUSTRY_COMMENT, P_DML_RECORD.GMISS_CHAR, NULL, l_PAYDEX_INDUSTRY_COMMENT(j)),
2077 PAYDEX_INDUSTRY_DAYS = DECODE(l_PAYDEX_INDUSTRY_DAYS(j), NULL,PAYDEX_INDUSTRY_DAYS, P_DML_RECORD.GMISS_CHAR, NULL, l_PAYDEX_INDUSTRY_DAYS(j)),
2078 PAYDEX_NORM = DECODE(l_PAYDEX_NORM(j), NULL,PAYDEX_NORM, P_DML_RECORD.GMISS_CHAR, NULL, l_PAYDEX_NORM(j)),
2079 PAYDEX_SCORE = DECODE(l_PAYDEX_SCORE(j), NULL,PAYDEX_SCORE, P_DML_RECORD.GMISS_CHAR, NULL, l_PAYDEX_SCORE(j)),
2080 PAYDEX_THREE_MONTHS_AGO = DECODE(l_PAYDEX_THREE_MONTHS_AGO(j), NULL,PAYDEX_THREE_MONTHS_AGO, P_DML_RECORD.GMISS_CHAR, NULL, l_PAYDEX_THREE_MONTHS_AGO(j)),
2081 PRNT_BKCY_CHAPTER_CONV = DECODE(l_PRNT_BKCY_CHAPTER_CONV(j), NULL,PRNT_BKCY_CHAPTER_CONV, P_DML_RECORD.GMISS_CHAR, NULL, l_PRNT_BKCY_CHAPTER_CONV(j)),
2082 PRNT_BKCY_CONV_DATE = DECODE(l_PRNT_BKCY_CONV_DATE(j), NULL,PRNT_BKCY_CONV_DATE, P_DML_RECORD.GMISS_CHAR, NULL, l_PRNT_BKCY_CONV_DATE(j)),
2083 PRNT_BKCY_FILG_CHAPTER = DECODE(l_PRNT_BKCY_FILG_CHAPTER(j), NULL,PRNT_BKCY_FILG_CHAPTER, P_DML_RECORD.GMISS_CHAR, NULL, l_PRNT_BKCY_FILG_CHAPTER(j)),
2084 PRNT_BKCY_FILG_DATE = DECODE(l_PRNT_BKCY_FILG_DATE(j), NULL,PRNT_BKCY_FILG_DATE, P_DML_RECORD.GMISS_CHAR, NULL, l_PRNT_BKCY_FILG_DATE(j)),
2085 PRNT_BKCY_FILG_TYPE = DECODE(l_PRNT_BKCY_FILG_TYPE(j), NULL,PRNT_BKCY_FILG_TYPE, P_DML_RECORD.GMISS_CHAR, NULL, l_PRNT_BKCY_FILG_TYPE(j)),
2086 PRNT_HQ_BKCY_IND = DECODE(l_PRNT_HQ_BKCY_IND(j), NULL,PRNT_HQ_BKCY_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_PRNT_HQ_BKCY_IND(j)),
2087 PUB_REC_EXPL = DECODE(l_PUB_REC_EXPL(j), NULL,PUB_REC_EXPL, P_DML_RECORD.GMISS_CHAR, NULL, l_PUB_REC_EXPL(j)),
2088 RATING = DECODE(l_RATING(j), NULL,RATING, P_DML_RECORD.GMISS_CHAR, NULL, l_RATING(j)),
2089 SECURED_FLNG_IND = DECODE(l_SECURED_FLNG_IND(j), NULL,SECURED_FLNG_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_SECURED_FLNG_IND(j)),
2090 SLOW_TRADE_EXPL = DECODE(l_SLOW_TRADE_EXPL(j), NULL,SLOW_TRADE_EXPL, P_DML_RECORD.GMISS_CHAR, NULL, l_SLOW_TRADE_EXPL(j)),
2091 SPCL_EVENT_COMMENT = DECODE(l_SPCL_EVENT_COMMENT(j), NULL,SPCL_EVENT_COMMENT, P_DML_RECORD.GMISS_CHAR, NULL, l_SPCL_EVENT_COMMENT(j)),
2092 SPCL_EVENT_UPDATE_DATE = DECODE(l_SPCL_EVENT_UPDATE_DATE(j), NULL,SPCL_EVENT_UPDATE_DATE, P_DML_RECORD.GMISS_CHAR, NULL, l_SPCL_EVENT_UPDATE_DATE(j)),
2093 SPCL_EVNT_TXT = DECODE(l_SPCL_EVNT_TXT(j), NULL,SPCL_EVNT_TXT, P_DML_RECORD.GMISS_CHAR, NULL, l_SPCL_EVNT_TXT(j)),
2094 SUIT_IND = DECODE(l_SUIT_IND(j), NULL,SUIT_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_SUIT_IND(j))
2095 -- CREATED_BY_MODULE = NVL(CREATED_BY_MODULE, decode(l_created_by_module(j),P_DML_RECORD.GMISS_CHAR, CREATED_BY_MODULE, null, CREATED_BY_MODULE,l_created_by_module(j)))
2096 where CREDIT_RATING_ID = l_CREDIT_RATING_ID(j) and
2097 l_BANKRUPTCY_IND_err(j) is not null and
2098 l_SUIT_IND_err(j) is not null and
2099 l_BANKRUPTCY_IND_err(j) is not null and
2100 l_DEBARMENT_IND_err(j) is not null and
2101 l_FINCL_EMBT_IND_err(j) is not null and
2102 l_NO_TRADE_IND_err(j) is not null and
2103 l_JUDGEMENT_IND_err(j) is not null and
2104 l_LIEN_IND_err(j) is not null and
2105 l_CREDIT_SCR_OVERRIDE_CODE_err(j) is not null and
2106 l_FAILURE_SCR_COMMENTARY_err(j) is not null and
2107 l_FAILURE_SCR_COMMENTARY2_err(j) is not null and
2108 l_FAILURE_SCR_COMMENTARY3_err(j) is not null and
2109 l_FAILURE_SCR_COMMENTARY4_err(j) is not null and
2110 l_FAILURE_SCR_COMMENTARY5_err(j) is not null and
2111 l_FAILURE_SCR_COMMENTARY6_err(j) is not null and
2112 l_FAILURE_SCR_COMMENTARY7_err(j) is not null and
2113 l_FAILURE_SCR_COMMENTARY8_err(j) is not null and
2117 l_CREDIT_SCR_COMMENTARY_err(j) is not null and
2114 l_FAILURE_SCR_COMMENTARY9_err(j) is not null and
2115 l_FAILURE_SCR_COMMENTARY10_err(j) is not null and
2116 l_FAILURE_SCR_OVERRIDE_CD_err(j) is not null and
2118 l_CREDIT_SCR_COMMENTARY2_err(j) is not null and
2119 l_CREDIT_SCR_COMMENTARY3_err(j) is not null and
2120 l_CREDIT_SCR_COMMENTARY4_err(j) is not null and
2121 l_CREDIT_SCR_COMMENTARY5_err(j) is not null and
2122 l_CREDIT_SCR_COMMENTARY6_err(j) is not null and
2123 l_CREDIT_SCR_COMMENTARY7_err(j) is not null and
2124 l_CREDIT_SCR_COMMENTARY8_err(j) is not null and
2125 l_CREDIT_SCR_COMMENTARY9_err(j) is not null and
2126 l_CREDIT_SCR_COMMENTARY10_err(j) is not null and
2127 l_PRNT_HQ_BKCY_IND_err(j) is not null and
2128 l_MAX_CREDIT_CURR_CODE_err(j) is not NULL AND
2129 l_action_flag_err(j) is not NULL;
2130 -- AND l_error_flag(j) is not NULL;
2131
2132 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2133 hz_utility_v2pub.debug(p_message=>'CR: Bulk Row Count aftre forall upd:',
2134 p_prefix =>l_debug_prefix,
2135 p_msg_level=>fnd_log.level_statement);
2136 FOR z IN 1..l_CREDIT_RATING_ID.COUNT LOOP
2137 hz_utility_v2pub.debug(p_message=>'CR:'||SQL%BULK_ROWCOUNT(z)||'credit rating id:'||l_CREDIT_RATING_ID(z),
2138 p_prefix =>l_debug_prefix,
2139 p_msg_level=>fnd_log.level_statement);
2140 END LOOP;
2141 END IF;
2142
2143 EXCEPTION
2144 WHEN OTHERS THEN
2145 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2146 hz_utility_v2pub.debug(p_message=>'CR:while updating got others excep',
2147 p_prefix=>'SQL ERROR',
2148 p_msg_level=>fnd_log.level_error);
2149 hz_utility_v2pub.debug(p_message=>sqlerrm,
2150 p_prefix=>'SQL ERROR',
2151 p_msg_level=>fnd_log.level_error);
2152 END IF;
2153 l_dml_exception := 'Y';
2154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2155 END; -- anonymous block end
2156 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2157 hz_utility_v2pub.debug(p_message=>'CR:credit rating id count bfr rep err:'||l_CREDIT_RATING_ID.COUNT,
2158 p_prefix =>l_debug_prefix,
2159 p_msg_level=>fnd_log.level_statement);
2160 END IF;
2161 report_errors(P_DML_RECORD, 'U', l_dml_exception);
2162 -----------
2163 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2164 hz_utility_v2pub.debug(p_message=>'CR:process_cr_upd()-',
2165 p_prefix=>l_debug_prefix,
2166 p_msg_level=>fnd_log.level_procedure);
2167 END IF;
2168 EXCEPTION
2169 WHEN OTHERS THEN
2170 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2171 hz_utility_v2pub.debug(p_message=>'CR:process_cr_upd() got others excep',
2172 p_prefix=>'ERROR',
2173 p_msg_level=>fnd_log.level_error);
2174 hz_utility_v2pub.debug(p_message=>sqlerrm,
2175 p_prefix=>'ERROR',
2176 p_msg_level=>fnd_log.level_error);
2177 END IF;
2178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2179 END process_cr_upd;
2180 --------------------------------------
2181 /**
2182 * PRIVATE PROCEDURE populate_error_table
2183 *
2184 * DESCRIPTION
2185 * processes recs identified as
2186 * errors and does dml on
2187 * errors tbl and interface table.
2188 *
2189 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2190 *
2191 * MODIFICATION HISTORY
2192 *
2193 * 08-26-2003 Srikanth o Created.
2194 *
2195 */
2196 --------------------------------------
2197
2198 PROCEDURE populate_error_table(
2199 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
2200 P_DUP_VAL_EXP IN VARCHAR2,
2201 P_SQL_ERRM IN VARCHAR2 ) IS
2202
2203 dup_val_exp_val VARCHAR2(1) := null;
2204 other_exp_val VARCHAR2(1) := 'Y';
2205 l_debug_prefix VARCHAR2(30) := '';
2206 BEGIN
2207 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2208 hz_utility_v2pub.debug(p_message=>'CR:populate_error_table()+',
2209 p_prefix=>l_debug_prefix,
2210 p_msg_level=>fnd_log.level_procedure);
2211 END IF;
2212
2213 IF(P_DUP_VAL_EXP = 'Y') then
2214 other_exp_val := null;
2215 IF(instr(P_SQL_ERRM, '_U1')<>0) THEN
2216 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2217 hz_utility_v2pub.debug(p_message=>'CR:HZ_CREDIT_RATINGS_U1 violated',
2218 p_prefix =>l_debug_prefix,
2219 p_msg_level=>fnd_log.level_statement);
2220 END IF;
2221 dup_val_exp_val := 'A';
2222 ELSE -- '_U2'
2223 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2224 hz_utility_v2pub.debug(p_message=>'CR:HZ_CREDIT_RATINGS_U2 violated',
2225 p_prefix =>l_debug_prefix,
2226 p_msg_level=>fnd_log.level_statement);
2227 END IF;
2228 dup_val_exp_val := 'B';
2229 END IF;
2230 END IF;
2231
2232 insert into hz_imp_tmp_errors
2233 (
2234 request_id,
2235 batch_id,
2236 int_row_id,
2240 created_by,
2237 interface_table_name,
2238 error_id,
2239 creation_date,
2241 last_update_date,
2242 last_updated_by,
2243 last_update_login,
2244 program_application_id,
2245 program_id,
2246 program_update_date,
2247 DUP_VAL_IDX_EXCEP_FLAG,
2248 OTHER_EXCEP_FLAG,
2249 e1_flag, e2_flag, e4_flag,
2250 e5_flag, e6_flag, e7_flag,
2251 e8_flag, e9_flag, e10_flag,
2252 e11_flag, e12_flag, e13_flag,
2253 e14_flag, e15_flag, e16_flag,
2254 e17_flag, e18_flag, e19_flag,
2255 e20_flag, e21_flag, e22_flag,
2256 e23_flag, e24_flag, e25_flag,
2257 e26_flag, e27_flag, e28_flag,
2258 e29_flag, e30_flag, e31_flag,
2259 e32_flag,
2260 e33_flag,
2261 action_mismatch_flag,missing_parent_flag
2262 )
2263 (
2264 select P_DML_RECORD.REQUEST_ID,
2265 P_DML_RECORD.BATCH_ID,
2266 cr_sg.int_row_id,
2267 'HZ_IMP_CREDITRTNGS_INT',
2268 HZ_IMP_ERRORS_S.NextVal,
2269 P_DML_RECORD.SYSDATE,
2270 P_DML_RECORD.USER_ID,
2271 P_DML_RECORD.SYSDATE,
2272 P_DML_RECORD.USER_ID,
2273 P_DML_RECORD.LAST_UPDATE_LOGIN,
2274 P_DML_RECORD.PROGRAM_APPLICATION_ID,
2275 P_DML_RECORD.PROGRAM_ID,
2276 P_DML_RECORD.SYSDATE,
2277 dup_val_exp_val,
2278 other_exp_val,
2279 'Y', 'Y', 'Y',
2280 'Y', 'Y', 'Y',
2281 'Y', 'Y', 'Y',
2282 'Y', 'Y', 'Y',
2283 'Y', 'Y', 'Y',
2284 'Y', 'Y', 'Y',
2285 'Y', 'Y', 'Y',
2286 'Y', 'Y', 'Y',
2287 'Y', 'Y', 'Y',
2288 'Y', 'Y', 'Y',
2289 'Y',
2290 'Y',
2291 'Y', 'Y'
2292 from HZ_IMP_CREDITRTNGS_SG cr_sg
2293 where cr_sg.action_flag = 'I'
2294 and cr_sg.batch_id = P_DML_RECORD.BATCH_ID
2295 and cr_sg.party_orig_system = P_DML_RECORD.OS
2296 and cr_sg.party_orig_system_reference
2297 between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
2298 );
2299 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2300 hz_utility_v2pub.debug(p_message=>'CR:populate_error_table()-',
2301 p_prefix=>l_debug_prefix,
2302 p_msg_level=>fnd_log.level_procedure);
2303 END IF;
2304
2305 END populate_error_table;
2306 --------------------------------------
2307 /**
2308 * PRIVATE PROCEDURE report_errors
2309 *
2310 * DESCRIPTION
2311 * processes recs identified as
2312 * errors and does dml on
2313 * errors tbl and interface table.
2314 * This procedure is only called from process_cr_upd()
2315 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2316 *
2317 * MODIFICATION HISTORY
2318 *
2319 * 07-28-2003 Srikanth o Created.
2320 *
2321 */
2322 --------------------------------------
2323 procedure report_errors (
2324 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
2325 ,P_ACTION IN VARCHAR2
2326 ,P_DML_EXCEPTION IN VARCHAR2 ) IS
2327
2328 -- local variables
2329 num_exp NUMBER; -- variable to store # of DML exceptions occured
2330 exp_ind NUMBER := 1; -- temp variable to store expection index.
2331
2332 -- For updating error_id in interface table in bulk
2333 l_exception_exists FLAG_ERROR;
2334 l_debug_prefix VARCHAR2(30) := '';
2335 BEGIN
2336 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2337 hz_utility_v2pub.debug(p_message=>'CR:report_errors()+',
2338 p_prefix=>l_debug_prefix,
2339 p_msg_level=>fnd_log.level_procedure);
2340 END IF;
2341 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2342 hz_utility_v2pub.debug(p_message=>'CR:no of recs to be processed:'||l_CREDIT_RATING_ID.count,
2343 p_prefix =>l_debug_prefix,
2344 p_msg_level=>fnd_log.level_statement);
2345 END IF;
2346
2347 IF l_CREDIT_RATING_ID.count = 0 THEN
2348 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2349 hz_utility_v2pub.debug(p_message=>'CR:# no rows to process - exiting',
2350 p_prefix =>l_debug_prefix,
2351 p_msg_level=>fnd_log.level_statement);
2352 END IF;
2353 RETURN ;
2354 END IF;
2355
2356 /* Note: For Credit Ratings update would not cause following errors:
2357 1. dup val exception
2358 2. missing_parent exception.
2359 other entities copying the code may need to take care of that.
2360
2361 IF g_debug THEN
2362 hz_utility_v2pub.debug('CR:report_errors:initializing collections');
2363 END IF;
2364 */
2365
2366 l_num_row_processed := NULL;
2367 l_num_row_processed := NUMBER_COLUMN(); -- initalizing
2368 l_num_row_processed.extend(l_CREDIT_RATING_ID.count);
2369
2370 num_exp := SQL%BULK_EXCEPTIONS.COUNT;
2371
2372 l_exception_exists := NULL;
2373 l_exception_exists := FLAG_ERROR();
2374 l_exception_exists.extend(l_CREDIT_RATING_ID.count);
2375
2376 -- for all the rows that must be processed
2377 -- check the BULK_ROWCOUNT exception to see
2378 -- if there are any error while doing DML.
2379 -- If so identify the row.
2380
2384 p_prefix =>l_debug_prefix,
2381 FOR k IN 1.. l_CREDIT_RATING_ID.count LOOP
2382 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2383 hz_utility_v2pub.debug(p_message=>'CR:bfr bulk row excep check',
2385 p_msg_level=>fnd_log.level_statement);
2386 hz_utility_v2pub.debug(p_message=>'CR:l_CREDIT_RATING_ID(k'||k||') is:'||l_CREDIT_RATING_ID(k),
2387 p_prefix =>l_debug_prefix,
2388 p_msg_level=>fnd_log.level_statement);
2389 END IF;
2390 -- check the bulk row exception for each row
2391 IF (SQL%BULK_ROWCOUNT(k) = 0) THEN
2392
2393 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2394 hz_utility_v2pub.debug(p_message=>'CR:DML fails at:'||k,
2395 p_prefix=>'ERROR',
2396 p_msg_level=>fnd_log.level_error);
2397 END IF;
2398
2399
2400 l_num_row_processed(k) := 0;
2401 -- Check for any exceptions during DML
2402 IF P_DML_EXCEPTION = 'Y' THEN
2403 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2404 hz_utility_v2pub.debug(p_message=>'CR:DML exception occured',
2405 p_prefix=>'ERROR',
2406 p_msg_level=>fnd_log.level_error);
2407 END IF;
2408
2409 -- determine if exception is at this index
2410 FOR i IN exp_ind..num_exp LOOP
2411
2412 IF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX = k THEN
2413 -- if the error index is same as the interface rec, process
2414 -- the exception.
2415 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2416 hz_utility_v2pub.debug(p_message=>'CR:excep code:'||SQL%BULK_EXCEPTIONS(i).ERROR_CODE,
2417 p_prefix=>'ERROR',
2418 p_msg_level=>fnd_log.level_error);
2419 END IF;
2420
2421 IF SQL%BULK_EXCEPTIONS(i).ERROR_CODE <> 1 THEN
2422 -- In case of any other exceptions, raise apps exception
2423 -- to be caught in load_creditrtaings()
2424 l_exception_exists(k) := 'Y';
2425 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2426 l_errm := SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
2427 hz_utility_v2pub.debug(p_message=>'CR:exception is:'||l_errm,
2428 p_prefix=>'ERROR',
2429 p_msg_level=>fnd_log.level_error);
2430 END IF;
2431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2432 END IF; -- error code 1 check ends
2433 ELSE
2434 -- if the error index is not the current interface row, exit
2435
2436 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2437 hz_utility_v2pub.debug(p_message=>'CR:error index <> current int row',
2438 p_prefix =>l_debug_prefix,
2439 p_msg_level=>fnd_log.level_statement);
2440 END IF;
2441 EXIT;
2442 END IF; -- end of error index check
2443 END LOOP; -- end of exceptions loop.
2444 ELSE
2445 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2446 hz_utility_v2pub.debug(p_message=>'CR:No DML exception',
2447 p_prefix =>l_debug_prefix,
2448 p_msg_level=>fnd_log.level_statement);
2449 END IF;
2450
2451 END IF; -- end of DML exception check
2452 ELSE
2453 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2454 hz_utility_v2pub.debug(p_message=>'CR:record#'||k||' processed successfully ',
2455 p_prefix =>l_debug_prefix,
2456 p_msg_level=>fnd_log.level_statement);
2457 hz_utility_v2pub.debug(p_message=>'CR:SQL%BULK_ROWCOUNT(k):'||SQL%BULK_ROWCOUNT(k),
2458 p_prefix =>l_debug_prefix,
2459 p_msg_level=>fnd_log.level_statement);
2460 END IF;
2461
2462 l_num_row_processed(k) := 1;
2463 END IF; -- end of SQL%BULK_ROWCOUNT(k) = 0 check
2464 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2465 hz_utility_v2pub.debug(p_message=>'CR:----------------------',
2466 p_prefix =>l_debug_prefix,
2467 p_msg_level=>fnd_log.level_statement);
2468 END IF;
2469
2470 END LOOP; -- end of loop for l_CREDIT_RATING_ID.count
2471
2472 BEGIN -- anonymous block to insert into hz_imp_errors
2473 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2474 hz_utility_v2pub.debug(p_message=>'CR:Bfr ForAll ins in rep errors',
2475 p_prefix =>l_debug_prefix,
2476 p_msg_level=>fnd_log.level_statement);
2477 hz_utility_v2pub.debug(p_message=>'CR:l_CREDIT_RATING_ID.count:'||l_CREDIT_RATING_ID.count,
2478 p_prefix =>l_debug_prefix,
2479 p_msg_level=>fnd_log.level_statement);
2480 END IF;
2481
2482
2483
2484 forall j in 1..l_CREDIT_RATING_ID.count
2485 insert into hz_imp_tmp_errors
2486 ( request_id, batch_id, int_row_id, interface_table_name,
2487 error_id, creation_date, created_by, last_update_date,
2488 last_updated_by, last_update_login, program_application_id, program_id,
2489 program_update_date, ACTION_MISMATCH_FLAG, OTHER_EXCEP_FLAG,
2490 e1_flag, e2_flag, e4_flag,
2491 e5_flag, e6_flag, e7_flag,
2492 e8_flag, e9_flag, e10_flag,
2493 e11_flag, e12_flag, e13_flag,
2494 e14_flag, e15_flag, e16_flag,
2495 e17_flag, e18_flag, e19_flag,
2496 e20_flag, e21_flag, e22_flag,
2500 e32_flag,
2497 e23_flag, e24_flag, e25_flag,
2498 e26_flag, e27_flag, e28_flag,
2499 e29_flag, e30_flag, e31_flag,
2501 e33_flag,
2502 MISSING_PARENT_FLAG,DUP_VAL_IDX_EXCEP_FLAG
2503 )(
2504 select
2505 P_DML_RECORD.REQUEST_ID, P_DML_RECORD.BATCH_ID, l_row_id(j), 'HZ_IMP_CREDITRTNGS_INT',
2506 HZ_IMP_ERRORS_S.NextVal, P_DML_RECORD.SYSDATE, P_DML_RECORD.USER_ID, P_DML_RECORD.SYSDATE,
2507 P_DML_RECORD.USER_ID, P_DML_RECORD.LAST_UPDATE_LOGIN, P_DML_RECORD.PROGRAM_APPLICATION_ID, P_DML_RECORD.PROGRAM_ID,
2508 P_DML_RECORD.SYSDATE, l_action_flag_err(j), l_exception_exists(j),
2509 l_bankruptcy_ind_err(j), l_suit_ind_err(j), l_debarment_ind_err(j),
2510 l_fincl_embt_ind_err(j), l_no_trade_ind_err(j), l_judgement_ind_err(j),
2511 l_lien_ind_err(j), l_credit_scr_override_code_err(j), l_failure_scr_commentary_err(j),
2512 l_failure_scr_commentary2_err(j), l_failure_scr_commentary3_err(j), l_failure_scr_commentary4_err(j),
2513 l_failure_scr_commentary5_err(j), l_failure_scr_commentary6_err(j), l_failure_scr_commentary7_err(j),
2514 l_failure_scr_commentary8_err(j), l_failure_scr_commentary9_err(j), l_failure_scr_commentary10_err(j),
2515 l_failure_scr_override_cd_err(j), l_credit_scr_commentary_err(j), l_credit_scr_commentary2_err(j),
2516 l_credit_scr_commentary3_err(j), l_credit_scr_commentary4_err(j), l_credit_scr_commentary5_err(j),
2517 l_credit_scr_commentary6_err(j), l_credit_scr_commentary7_err(j), l_credit_scr_commentary8_err(j),
2518 l_credit_scr_commentary9_err(j), l_credit_scr_commentary10_err(j),l_prnt_hq_bkcy_ind_err(j),
2519 l_max_credit_curr_code_err(j), 'Z', 'Z','Z'
2520 from dual
2521 where l_num_row_processed(j) = 0
2522 );
2523 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2524 hz_utility_v2pub.debug(p_message=>'CR:after inserting into errors tbl:',p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2525 FOR z IN 1..l_CREDIT_RATING_ID.COUNT LOOP
2526 hz_utility_v2pub.debug(p_message=>'CR:blk row count for '||z||' is:'||SQL%BULK_ROWCOUNT(z)||'credit rating id is:'||l_CREDIT_RATING_ID(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2527 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.REQUEST_ID:'||P_DML_RECORD.REQUEST_ID,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2528 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.BATCH_ID:'||P_DML_RECORD.BATCH_ID,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2529 hz_utility_v2pub.debug(p_message=>'CR:row id:'||l_row_id(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2530 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.SYSDATE:'||P_DML_RECORD.SYSDATE,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2531 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.USER_ID:'||P_DML_RECORD.USER_ID,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2532 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.LAST_UPDATE_LOGIN:'||P_DML_RECORD.LAST_UPDATE_LOGIN,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2533 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.PROGRAM_APPLICATION_ID:'||P_DML_RECORD.PROGRAM_APPLICATION_ID,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2534 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.PROGRAM_ID:'||P_DML_RECORD.PROGRAM_ID,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2535
2536 hz_utility_v2pub.debug(p_message=>'CR:l_num_row_processed:'||l_num_row_processed(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2537
2538
2539 hz_utility_v2pub.debug(p_message=>'CR:l_action_flag_err:'||l_action_flag_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2540 hz_utility_v2pub.debug(p_message=>'CR:l_exception_exists:'||l_exception_exists(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2541 hz_utility_v2pub.debug(p_message=>'CR:l_bankruptcy_ind_err:'||l_bankruptcy_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2542 hz_utility_v2pub.debug(p_message=>'CR:l_suit_ind_err:'||l_suit_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2543 hz_utility_v2pub.debug(p_message=>'CR:l_debarment_ind_err:'||l_debarment_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2544 hz_utility_v2pub.debug(p_message=>'CR:l_fincl_embt_ind_err:'||l_fincl_embt_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2545 hz_utility_v2pub.debug(p_message=>'CR:l_no_trade_ind_err:'||l_no_trade_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2546 hz_utility_v2pub.debug(p_message=>'CR:l_judgement_ind_err:'||l_judgement_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2547 hz_utility_v2pub.debug(p_message=>'CR:l_lien_ind_err:'||l_lien_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2548 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_override_code_err:'||l_credit_scr_override_code_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2549 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary_err:'||l_failure_scr_commentary_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2550 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary2_err:'||l_failure_scr_commentary2_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2551 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary3_err:'||l_failure_scr_commentary3_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2552 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary4_err:'||l_failure_scr_commentary4_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2556 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary8_err:'||l_failure_scr_commentary8_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2553 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary5_err:'||l_failure_scr_commentary5_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2554 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary6_err:'||l_failure_scr_commentary6_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2555 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary7_err:'||l_failure_scr_commentary7_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2557 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary9_err:'||l_failure_scr_commentary9_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2558 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary10_err:'||l_failure_scr_commentary10_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2559 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_override_cd_err:'||l_failure_scr_override_cd_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2560 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary_err:'||l_credit_scr_commentary_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2561 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary2_err:'||l_credit_scr_commentary2_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2562 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary3_err:'||l_credit_scr_commentary3_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2563 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary4_err:'||l_credit_scr_commentary4_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2564 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary5_err:'||l_credit_scr_commentary5_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2565 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary6_err:'||l_credit_scr_commentary6_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2566 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary7_err:'||l_credit_scr_commentary7_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2567 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary8_err:'||l_credit_scr_commentary8_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2568 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary9_err:'||l_credit_scr_commentary9_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2569 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary10_err:'||l_credit_scr_commentary10_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2570 hz_utility_v2pub.debug(p_message=>'CR:l_prnt_hq_bkcy_ind_err:'||l_prnt_hq_bkcy_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2571 hz_utility_v2pub.debug(p_message=>'CR:l_max_credit_curr_code_err:'||l_max_credit_curr_code_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2572 END LOOP;
2573 END IF;
2574 EXCEPTION
2575 WHEN OTHERS THEN
2576 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2577 FOR z IN 1..l_CREDIT_RATING_ID.COUNT LOOP
2578 hz_utility_v2pub.debug(p_message=>'CR:blk row count for '||z||' is:'||SQL%BULK_ROWCOUNT(z)||'credit rating id is:'||l_CREDIT_RATING_ID(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2579 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.REQUEST_ID:'||P_DML_RECORD.REQUEST_ID,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2580 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.BATCH_ID:'||P_DML_RECORD.BATCH_ID,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2581 hz_utility_v2pub.debug(p_message=>'CR:row id:'||l_row_id(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2582 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.SYSDATE:'||P_DML_RECORD.SYSDATE,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2583 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.USER_ID:'||P_DML_RECORD.USER_ID,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2584 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.LAST_UPDATE_LOGIN:'||P_DML_RECORD.LAST_UPDATE_LOGIN,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2585 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.PROGRAM_APPLICATION_ID:'||P_DML_RECORD.PROGRAM_APPLICATION_ID,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2586 hz_utility_v2pub.debug(p_message=>'CR:P_DML_RECORD.PROGRAM_ID:'||P_DML_RECORD.PROGRAM_ID,p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2587 hz_utility_v2pub.debug(p_message=>'CR:l_num_row_processed:'||l_num_row_processed(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2588 hz_utility_v2pub.debug(p_message=>'CR:l_action_flag_err:'||l_action_flag_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2589 hz_utility_v2pub.debug(p_message=>'CR:l_exception_exists:'||l_exception_exists(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2590 hz_utility_v2pub.debug(p_message=>'CR:l_bankruptcy_ind_err:'||l_bankruptcy_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2591 hz_utility_v2pub.debug(p_message=>'CR:l_suit_ind_err:'||l_suit_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2592 hz_utility_v2pub.debug(p_message=>'CR:l_debarment_ind_err:'||l_debarment_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2593 hz_utility_v2pub.debug(p_message=>'CR:l_fincl_embt_ind_err:'||l_fincl_embt_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2597 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_override_code_err:'||l_credit_scr_override_code_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2594 hz_utility_v2pub.debug(p_message=>'CR:l_no_trade_ind_err:'||l_no_trade_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2595 hz_utility_v2pub.debug(p_message=>'CR:l_judgement_ind_err:'||l_judgement_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2596 hz_utility_v2pub.debug(p_message=>'CR:l_lien_ind_err:'||l_lien_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2598 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary_err:'||l_failure_scr_commentary_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2599 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary2_err:'||l_failure_scr_commentary2_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2600 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary3_err:'||l_failure_scr_commentary3_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2601 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary4_err:'||l_failure_scr_commentary4_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2602 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary5_err:'||l_failure_scr_commentary5_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2603 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary6_err:'||l_failure_scr_commentary6_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2604 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary7_err:'||l_failure_scr_commentary7_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2605 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary8_err:'||l_failure_scr_commentary8_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2606 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary9_err:'||l_failure_scr_commentary9_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2607 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_commentary10_err:'||l_failure_scr_commentary10_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2608 hz_utility_v2pub.debug(p_message=>'CR:l_failure_scr_override_cd_err:'||l_failure_scr_override_cd_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2609 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary_err:'||l_credit_scr_commentary_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2610 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary2_err:'||l_credit_scr_commentary2_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2611 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary3_err:'||l_credit_scr_commentary3_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2612 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary4_err:'||l_credit_scr_commentary4_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2613 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary5_err:'||l_credit_scr_commentary5_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2614 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary6_err:'||l_credit_scr_commentary6_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2615 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary7_err:'||l_credit_scr_commentary7_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2616 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary8_err:'||l_credit_scr_commentary8_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2617 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary9_err:'||l_credit_scr_commentary9_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2618 hz_utility_v2pub.debug(p_message=>'CR:l_credit_scr_commentary10_err:'||l_credit_scr_commentary10_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2619 hz_utility_v2pub.debug(p_message=>'CR:l_prnt_hq_bkcy_ind_err:'||l_prnt_hq_bkcy_ind_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2620 hz_utility_v2pub.debug(p_message=>'CR:l_max_credit_curr_code_err:'||l_max_credit_curr_code_err(z),p_prefix =>l_debug_prefix,p_msg_level=>fnd_log.level_statement);
2621 END LOOP;
2622 END IF;
2623 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2624 hz_utility_v2pub.debug(p_message=>'CR:while inserting into errors tbl got others excep',p_prefix=>'ERROR',p_msg_level=>fnd_log.level_error);
2625 hz_utility_v2pub.debug(p_message=>sqlerrm,p_prefix=>'ERROR',p_msg_level=>fnd_log.level_error);
2626 END IF;
2627 END; -- anonymous block end
2628 --------------
2629 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2630 hz_utility_v2pub.debug(p_message=>'CR:report_errors()-',
2631 p_prefix=>l_debug_prefix,
2632 p_msg_level=>fnd_log.level_procedure);
2633 END IF;
2634
2635 EXCEPTION
2636 WHEN OTHERS THEN
2637 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2638 hz_utility_v2pub.debug(p_message=>'CR:in report_errors() expection block',
2639 p_prefix=>'ERROR',
2640 p_msg_level=>fnd_log.level_error);
2641 hz_utility_v2pub.debug(p_message=>sqlerrm,
2642 p_prefix=>'ERROR',
2643 p_msg_level=>fnd_log.level_error);
2644 END IF;
2645 END report_errors;
2646 --------------------------------------
2647 /**
2648 * PRIVATE PROCEDURE load_creditratings
2649 *
2650 * DESCRIPTION
2651 * processes recs from credit ratings interface table.
2652 *
2653 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2654 *
2655 * MODIFICATION HISTORY
2656 *
2657 * 07-28-2003 Srikanth o Created.
2658 *
2659 */
2660 --------------------------------------
2661
2662 PROCEDURE load_creditratings (
2663 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
2664 ,x_return_status OUT NOCOPY VARCHAR2
2665 ,x_msg_count OUT NOCOPY NUMBER
2666 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
2667
2668 -- Enter the procedure variables here. As shown below
2669 -- variable_name datatype NOT NULL DEFAULT default_value;
2670 l_debug_prefix VARCHAR2(30) := '';
2671 BEGIN
2672 -- flow
2673 /*
2674 1. set the save point, initialize mesgs, return status
2675 2. cache the who column values - by calling cache_who()
2676 3. Process CRs for insertion - by calling process_cr_ins()
2677 4. Process CRs for updation - by calling process_cr_ins()
2678 */
2679
2680 -- Check if API is called in debug mode. If yes, enable debug.
2681 --enable_debug;
2682 -- Debug info.
2683 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2684 hz_utility_v2pub.debug(p_message=>'CR:load_creditratings()+',
2685 p_prefix=>l_debug_prefix,
2686 p_msg_level=>fnd_log.level_procedure);
2687 END IF;
2688 -- Set the save point
2689 savepoint load_creditratings;
2690 -- Initialize teh message stack
2691 FND_MSG_PUB.initialize;
2692 -- Initialize API return status to success.
2693 x_return_status := FND_API.G_RET_STS_SUCCESS;
2694
2695 process_cr_ins (
2696 P_DML_RECORD
2697 ,x_return_status
2698 ,x_msg_count
2699 ,x_msg_data );
2700
2701
2702 process_cr_upd (
2703 P_DML_RECORD
2707
2704 ,x_return_status
2705 ,x_msg_count
2706 ,x_msg_data );
2708
2709 -- cache the who columns
2710 -- cache_who;
2711 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2712 hz_utility_v2pub.debug(p_message=>'CR:load_creditratings()-',
2713 p_prefix=>l_debug_prefix,
2714 p_msg_level=>fnd_log.level_procedure);
2715 END IF;
2716 -- if enabled, disable debug
2717 --disable_debug;
2718 EXCEPTION
2719
2720 WHEN FND_API.G_EXC_ERROR THEN
2721 ROLLBACK TO load_creditratings;
2722 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2723 hz_utility_v2pub.debug(p_message=>'CR:In load_creditratings() G_EXC_ERROR excp blk-',
2724 p_prefix=>'ERROR',
2725 p_msg_level=>fnd_log.level_error);
2726 hz_utility_v2pub.debug(p_message=>sqlerrm,
2727 p_prefix=>'ERROR',
2728 p_msg_level=>fnd_log.level_error);
2729 END IF;
2730 -- FND_FILE.put_line(fnd_file.log,'Expected error occurs while loading credit ratings');
2731 -- FND_FILE.put_line(fnd_file.log, l_errm);
2732 x_return_status := FND_API.G_RET_STS_ERROR;
2733 FND_MSG_PUB.Count_And_Get(
2734 p_encoded => FND_API.G_FALSE,
2735 p_count => x_msg_count,
2736 p_data => x_msg_data);
2737
2738 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2739 ROLLBACK TO load_creditratings;
2740 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2741 hz_utility_v2pub.debug(p_message=>'CR:In load_creditratings() G_EXC_UNEXPECTED_ERROR excp blk-',
2742 p_prefix=>'UNEXPECTED ERROR',
2743 p_msg_level=>fnd_log.level_error);
2744 hz_utility_v2pub.debug(p_message=>sqlerrm,
2745 p_prefix=>'UNEXPECTED ERROR',
2746 p_msg_level=>fnd_log.level_error);
2747 END IF;
2748 -- FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading credit ratings');
2749 -- FND_FILE.put_line(fnd_file.log, l_errm);
2750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2751 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2752 FND_MESSAGE.SET_TOKEN('ERROR' ,l_errm);
2753 FND_MSG_PUB.ADD;
2754 FND_MSG_PUB.Count_And_Get(
2755 p_encoded => FND_API.G_FALSE,
2756 p_count => x_msg_count,
2757 p_data => x_msg_data);
2758
2759 WHEN OTHERS THEN
2760 ROLLBACK TO load_creditratings;
2761 -- FND_FILE.put_line(fnd_file.log,'Unexpected (other) error occurs while loading parties');
2762 -- FND_FILE.put_line(fnd_file.log, l_errm);
2763 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2764 hz_utility_v2pub.debug(p_message=>'CR:In load_creditratings() OTHERS excp blk-',
2765 p_prefix=>'SQL ERROR',
2766 p_msg_level=>fnd_log.level_error);
2767 hz_utility_v2pub.debug(p_message=>sqlerrm,
2768 p_prefix=>'SQL ERROR',
2769 p_msg_level=>fnd_log.level_error);
2770 END IF;
2771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2772 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2773 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2774 FND_MSG_PUB.ADD;
2775 FND_MSG_PUB.Count_And_Get(
2776 p_encoded => FND_API.G_FALSE,
2777 p_count => x_msg_count,
2778 p_data => x_msg_data);
2779
2780 END load_creditratings;
2781 --------------------------------------
2782 END HZ_IMP_LOAD_CREDITRATINGS_PKG;