1 PACKAGE BODY OE_AUDIT_HISTORY_PVT AS
2 /* $Header: OEXPPCHB.pls 120.20.12010000.2 2008/08/11 06:25:45 amallik ship $ */
3
4 FUNCTION id_to_value ( p_attribute_id IN NUMBER,
5 attribute_value IN varchar2,
6 p_context_value IN VARCHAR2 DEFAULT NULL,
7 p_org_id IN NUMBER DEFAULT NULL
8 ) RETURN VARCHAR2 IS
9 l_attribute_code varchar2(80);
10 l_attribute_display_value varchar2(2000);
11 l_display_name varchar2(500);
12 --
13 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
14 --
15 BEGIN
16
17 BEGIN
18 SELECT ATTRIBUTE_DISPLAY_NAME,
19 ATTRIBUTE_CODE
20 INTO l_display_name,
21 l_attribute_code
22 FROM OE_PC_ATTRIBUTES_V
23 WHERE ATTRIBUTE_ID = p_attribute_id
24 AND APPLICATION_ID=660;
25 EXCEPTION WHEN OTHERS THEN
26 l_display_name:=NULL;
27 END;
28
29 if l_attribute_code = 'SALESREP_ID' then
30 BEGIN
31 SELECT NAME
32 INTO l_attribute_display_value
33 FROM RA_SALESREPS
34 WHERE salesrep_id = attribute_value and org_id = p_org_id;
35
36 EXCEPTION WHEN OTHERS THEN
37 l_attribute_display_value := NULL;
38 END;
39 elsif l_attribute_code = 'SALES_CREDIT_TYPE_ID' then
40 l_attribute_display_value :=
41 OE_ID_TO_VALUE.Sales_credit_type(attribute_value);
42 elsif l_attribute_code = 'ACCOUNTING_RULE_ID' then
43 l_attribute_display_value :=
44 OE_ID_TO_VALUE.accounting_rule(attribute_value);
45 elsif l_attribute_code = 'AGREEMENT_ID' then
46 l_attribute_display_value :=
47 OE_ID_TO_VALUE.agreement(attribute_value);
48 elsif l_attribute_code = 'CONVERSION_TYPE_CODE' then
49 l_attribute_display_value :=
50 OE_ID_TO_VALUE.conversion_type(attribute_value);
51 elsif l_attribute_code = 'DELIVER_TO_CONTACT_ID' then
52 l_attribute_display_value :=
53 OE_ID_TO_VALUE.Deliver_to_contact(attribute_value);
54 elsif l_attribute_code = 'DELIVER_TO_ORG_ID' then
55 IF attribute_value is not null then
56 BEGIN
57 SELECT LOCATION
58 INTO l_attribute_display_value
59 FROM HZ_CUST_SITE_USES_ALL
60 WHERE site_use_id = attribute_value
61 AND SITE_USE_CODE = 'DELIVER_TO'
62 AND ROWNUM = 1;
63 END;
64 END IF;
65 elsif l_attribute_code = 'FOB_POINT_CODE' then
66 l_attribute_display_value :=
67 OE_ID_TO_VALUE.fob_point(attribute_value);
68 elsif l_attribute_code = 'FREIGHT_TERMS_CODE' then
69 l_attribute_display_value :=
70 OE_ID_TO_VALUE.freight_terms(attribute_value);
71 elsif l_attribute_code = 'INVOICE_TO_CONTACT_ID' then
72 l_attribute_display_value :=
73 OE_ID_TO_VALUE.Invoice_to_contact(attribute_value);
74 elsif l_attribute_code = 'INVOICE_TO_ORG_ID' then
75 IF attribute_value is not null then
76 BEGIN
77 SELECT LOCATION
78 INTO l_attribute_display_value
79 FROM HZ_CUST_SITE_USES_ALL
80 WHERE site_use_id = attribute_value
81 AND SITE_USE_CODE = 'BILL_TO'
82 AND ROWNUM = 1;
83 END;
84 END IF;
85 elsif l_attribute_code = 'INVOICING_RULE_ID' then
86 l_attribute_display_value :=
87 OE_ID_TO_VALUE.Invoicing_rule(attribute_value);
88 elsif l_attribute_code = 'ORDER_TYPE_ID' then
89 l_attribute_display_value :=
90 OE_ID_TO_VALUE.Order_type(attribute_value);
91 elsif l_attribute_code = 'PAYMENT_TERM_ID' then
92 l_attribute_display_value :=
93 OE_ID_TO_VALUE.Payment_Term(attribute_value);
94 elsif l_attribute_code = 'PRICE_LIST_ID' then
95 l_attribute_display_value :=
96 OE_ID_TO_VALUE.Price_List(attribute_value);
97 elsif l_attribute_code = 'SHIPMENT_PRIORITY_CODE' then
98 l_attribute_display_value :=
99 OE_ID_TO_VALUE.Shipment_Priority(attribute_value);
100 elsif l_attribute_code = 'SHIP_FROM_ORG_ID' then
101 IF attribute_value is not null then
102 BEGIN
103 SELECT name
104 INTO l_attribute_display_value
105 FROM HR_ORGANIZATION_UNITS
106 WHERE organization_id = attribute_value
107 AND ROWNUM = 1;
108 END;
109 END IF;
110 elsif l_attribute_code = 'SHIP_TO_CONTACT_ID' then
111 l_attribute_display_value :=
112 OE_ID_TO_VALUE.Ship_to_contact(attribute_value);
113 elsif l_attribute_code = 'SHIP_TO_ORG_ID' then
114 IF attribute_value is not null then
115 BEGIN
116 SELECT LOCATION
117 INTO l_attribute_display_value
118 FROM HZ_CUST_SITE_USES_ALL
119 WHERE site_use_id = attribute_value
120 AND SITE_USE_CODE = 'SHIP_TO'
121 AND ROWNUM = 1;
122 END;
123 END IF;
124 elsif l_attribute_code = 'SOLD_TO_CONTACT_ID' then
125 l_attribute_display_value :=
126 OE_ID_TO_VALUE.Sold_to_contact(attribute_value);
127 elsif l_attribute_code = 'SOLD_TO_ORG_ID' then
128 IF attribute_value is not null then
129 BEGIN
130 SELECT SUBSTR(HZP.PARTY_NAME,1,50) name
131 INTO l_attribute_display_value
132 FROM HZ_PARTIES HZP, HZ_CUST_ACCOUNTS HZC
133 WHERE HZC.cust_account_id = attribute_value
134 AND HZP.PARTY_ID = HZC.PARTY_ID;
135 END;
136 END IF;
137 elsif l_attribute_code = 'TAX_EXEMPT_FLAG' then
138 l_attribute_display_value :=
139 OE_ID_TO_VALUE.tax_exempt(attribute_value);
140 elsif l_attribute_code = 'TAX_EXEMPT_REASON_CODE' then
141 l_attribute_display_value :=
142 OE_ID_TO_VALUE.tax_exempt_reason(attribute_value);
143 elsif l_attribute_code = 'RETURN_REASON_CODE' then
144 l_attribute_display_value :=
145 OE_ID_TO_VALUE.Return_Reason(attribute_value);
146 elsif l_attribute_code = 'SHIPPING_METHOD_CODE' then
147 l_attribute_display_value :=
148 OE_ID_TO_VALUE.Ship_method(attribute_value);
149 elsif l_attribute_code = 'SOURCE_TYPE_CODE' then
150 l_attribute_display_value :=
151 OE_ID_TO_VALUE.source_type(attribute_value);
152 elsif l_attribute_code = 'CALCULATE_PRICE_FLAG' then
153 IF attribute_value in ('Y','N','P') THEN
154 BEGIN
155 SELECT meaning
156 INTO l_attribute_display_value
157 FROM OE_LOOKUPS
158 WHERE LOOKUP_TYPE = 'CALCULATE_PRICE_FLAG'
159 AND LOOKUP_CODE = attribute_value;
160 END;
161 ELSE
162 l_attribute_display_value := NULL;
163 END IF;
164 elsif l_attribute_code = 'OVER_SHIP_REASON_CODE' then
165 l_attribute_display_value :=
166 OE_ID_TO_VALUE.over_ship_reason(attribute_value);
167 elsif l_attribute_code = 'INVENTORY_ITEM_ID' then
168 l_attribute_display_value :=
169 OE_ID_TO_VALUE.Inventory_Item(attribute_value);
170 elsif l_attribute_code = 'DISCOUNT_ID' then
171 if attribute_value is not null then
172 BEGIN
173 SELECT name
174 INTO l_attribute_display_value
175 FROM OE_DISCOUNTS_V
176 WHERE discount_id = attribute_value;
177 END;
178 end if;
179 elsif l_attribute_code in ( 'OVER_SHIP_RESOLVED_FLAG',
180 'SHIP_MODEL_COMPLETE_FLAG',
181 'AUTHORIZED_TO_SHIP_FLAG',
182 'FULFILLED_FLAG',
183 'AUTOMATIC_FLAG',
184 'ACCRUAL_FLAG' ,
185 'INVOICE_COMPLETE_FLAG',
186 'PRINT_ON_INVOICE_FLAG',
187 'UPDATED_FLAG',
188 'INCLUDE_ON_RETURNS_FLAG',
189 'ESTIMATED_FLAG',
190 'APPLIED_FLAG') THEN
191 IF attribute_value in ('Y','N') THEN
192 BEGIN
193 SELECT MEANING
194 INTO l_attribute_display_value
195 FROM OE_LOOKUPS
196 WHERE LOOKUP_TYPE = 'YES_NO'
197 AND LOOKUP_CODE = attribute_value;
198 END;
199 else
200 l_attribute_display_value := NULL;
201 end if;
202 elsif l_attribute_code = 'USER_ID' then
203 select user_name
204 into l_attribute_display_value
205 from fnd_user
206 where user_id = attribute_value;
207 elsif l_attribute_code = 'RESPONSIBILITY_ID' then
208 if attribute_value is not null then
209 select responsibility_name
210 into l_attribute_display_value
211 from fnd_responsibility_tl fr
212 where fr.responsibility_id = attribute_value
213 and fr.application_id = FND_GLOBAL.RESP_APPL_ID
214 and fr.language = userenv('LANG');
215 else
216 l_attribute_display_value := '';
217 end if;
218 elsif l_attribute_code = 'REASON_CODE' then
219 if (attribute_value is not null) then
220 select meaning
221 into l_attribute_display_value
222 from oe_lookups oel
223 where oel.lookup_type = 'CANCEL_CODE'
224 and oel.lookup_code = attribute_value;
225 else
226 l_attribute_display_value := null;
227 end if;
228
229 --bug#5631508
230 ELSIF l_attribute_code = 'SHIP_SET_ID' or l_attribute_code = 'ARRIVAL_SET_ID' THEN
231 IF attribute_value IS NOT NULL THEN
232 begin
233 SELECT set_name
234 INTO l_attribute_display_value
235 FROM OE_SETS_HISTORY
236 WHERE set_id = attribute_value
237 and rownum<2;
238 Exception
239 when no_data_found then
240 SELECT set_name
241 INTO l_attribute_display_value
242 FROM OE_SETS
243 WHERE set_id = attribute_value;
244 End;
245 END IF;
246 else
247 IF (p_attribute_id between 1078 and 1092 ) OR (p_attribute_id between 4218 and 4222) THEN
248 l_attribute_display_value := OE_AUDIT_HISTORY_PVT.Get_Translated_Value(
249 'OE_HEADER_ATTRIBUTES',
250 UPPER(l_display_name),
251 attribute_value, p_context_value );
252 ELSIF (p_attribute_id between 1018 and 1032) OR (p_attribute_id between 4223 and 4227) THEN
253 l_attribute_display_value := OE_AUDIT_HISTORY_PVT.Get_Translated_Value(
254 'OE_LINE_ATTRIBUTES',
255 UPPER(l_display_name),
256 attribute_value,
257 p_context_value);
258 ELSE
259 l_attribute_display_value := attribute_value; --none of the above
260 END IF;
261 end if;
262 RETURN l_attribute_display_value;
263 EXCEPTION WHEN OTHERS THEN
264 l_attribute_display_value := attribute_value;
265 RETURN attribute_value;
266 END ID_TO_VALUE;
267
268 PROCEDURE set_attribute_history (
269 retcode OUT NOCOPY /* file.sql.39 change */ varchar2,
270 errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
271 p_org_id IN NUMBER,
272 start_date IN VARCHAR2,
273 end_date IN VARCHAR2,
274 order_number_from IN NUMBER,
275 order_number_to IN NUMBER,
276 audit_duration IN NUMBER)
277 IS
278
279 /* Get all entity ids specific history views */
280 -- Not including entities greater than 1000 (blankets, payments)
281 CURSOR c_entities IS
282 SELECT ENTITY_ID
283 ,decode(ENTITY_ID, 1, 'oe_order_header_history',
284
285 2, 'oe_order_lines_history',
286 5, 'oe_sales_credit_history',
287 6, 'oe_price_adjs_history',
288 7, 'oe_sales_credit_history',
289 8, 'oe_price_adjs_history') entity_name
290 ,ENTITY_DISPLAY_NAME
291 FROM OE_PC_ENTITIES_V
292 WHERE APPLICATION_ID = 660
293 AND ENTITY_ID < 1000
294 ORDER BY ENTITY_ID;
295
296 /* select all constrainable attributes*/
297 CURSOR C_ALL_ATTRIBUTES (ent_id number) IS
298
299 SELECT OEV.ATTRIBUTE_ID
300 ,OEV.ATTRIBUTE_CODE
301 ,OEV.COLUMN_NAME
302 ,OEV.ATTRIBUTE_DISPLAY_NAME
303 FROM OE_PC_ATTRIBUTES_V OEV
304 WHERE OEV.ENTITY_ID = ent_id
305 AND OEV.APPLICATION_ID = 660
306 AND OEV.CONSTRAINTS_ENABLED_FLAG = 'Y';
307
308 /* Select currently constrained attributes only */
309 CURSOR C_CONST_ATTRIBUTES (ent_id number) IS
310 SELECT OEV.ATTRIBUTE_ID
311 ,OEV.ATTRIBUTE_CODE
312 ,OEV.COLUMN_NAME
313 ,OEV.ATTRIBUTE_DISPLAY_NAME
314 FROM OE_PC_ATTRIBUTES_V OEV
315 WHERE OEV.ENTITY_ID = ent_id
316 AND OEV.APPLICATION_ID = 660
317 AND OEV.CONSTRAINTS_ENABLED_FLAG = 'Y'
318 AND EXISTS ( SELECT 'CONSTRAINT EXISTS FOR THIS COLUMN'
319 FROM OE_PC_CONSTRAINTS OEP
320 WHERE OEP.COLUMN_NAME = OEV.COLUMN_NAME
321 AND OEP.ON_OPERATION_ACTION IN (1,2)
322 AND OEP.ENTITY_ID = OEV.ENTITY_ID)
323 ORDER BY OEV.ATTRIBUTE_ID;
324
325
326 /* Check Whether the entity is constrained for update */
327 CURSOR IS_ENTITY_CONSTRAINED (v_ent_id NUMBER) IS
328 SELECT 'Y' -- There is an entity level constraint'
329 FROM OE_PC_CONSTRAINTS
330 WHERE ENTITY_ID = v_ent_id
331 AND COLUMN_NAME IS NULL
332 AND ON_OPERATION_ACTION IN (1,2)
333 AND CONSTRAINED_OPERATION <> 'X';
334
335 TYPE OE_ATTR_CONSTRAINED IS RECORD
336 (ENTITY_ID NUMBER,
337 ATTRIBUTE_ID NUMBER,
338 ATTRIBUTE_CODE VARCHAR2(50),
339 COLUMN_NAME VARCHAR2(150),
340 ATTRIBUTE_DISPLAY_NAME VARCHAR2(250));
341
342 TYPE OE_ATTR_CONSTR_TABLE IS TABLE OF OE_ATTR_CONSTRAINED INDEX BY BINARY_INTEGER;
343 TYPE OE_PC_ATTRIBUTE_HISTORY_REC IS RECORD
344 (ENTITY_ID NUMBER,
345 ATTRIBUTE_ID NUMBER,
346 ENTITY_NUMBER NUMBER,
347 ORDER_NUMBER NUMBER,
348 RESPONSIBILITY_ID NUMBER,
349 USER_ID NUMBER,
350 ORG_ID NUMBER,
351 ORDER_TYPE_ID NUMBER,
352 SOLD_TO_ORG_ID NUMBER,
353 HIST_CREATION_DATE DATE,
354 REASON_CODE VARCHAR2(80),
355 OLD_ATTRIBUTE_VALUE VARCHAR2(2000),
356 NEW_ATTRIBUTE_VALUE VARCHAR2(2000),
357 CHANGE_COMMENTS VARCHAR2(2000),
358 OLD_CONTEXT_VALUE VARCHAR2(30),
359 NEW_CONTEXT_VALUE VARCHAR2(30));
360
361 TYPE oe_pc_attribute_history_tbl IS TABLE OF oe_pc_attribute_history_rec
362
363 INDEX BY BINARY_INTEGER;
364
365 C_ATTR_TBL OE_ATTR_CONSTR_TABLE;
366 oe_pc_attr_tbl OE_PC_ATTRIBUTE_HISTORY_TBL;
367 min_hist_creation_date DATE; --start date for fetch
368 max_hist_creation_date DATE; --end date for fetch
369 l_attribute_value VARCHAR2(2000); /* Attrb value, desc for current , old and last*/
370 l_old_attribute_value VARCHAR2(2000);
371 l_attribute_value_last VARCHAR2(2000);
372 l_hist_creation_date DATE;
373 l_old_hist_creation_date DATE;
374 l_hist_creation_date_last DATE;
375 l_context_value VARCHAR2(30);
376 l_old_context_value VARCHAR2(30);
377 l_context_value_last VARCHAR2(30);
378 l_order_number NUMBER;
379 l_entity_number NUMBER;
380 l_org_id NUMBER;
381 l_user_id NUMBER;
382 l_old_user_id NUMBER;
383 l_user_id_last NUMBER;
384 l_responsibility_id NUMBER;
385 l_responsibility_id_last NUMBER;
386 l_old_responsibility_id NUMBER;
387
388 l_reason_code VARCHAR2(80);
389 l_reason_code_last VARCHAR2(80);
390 l_old_reason_code VARCHAR2(80);
391 l_order_type_id NUMBER;
392 l_sold_to_org_id NUMBER;
393 l_change_comments VARCHAR2(2000);
394 l_old_db_rec_upd_flag BOOLEAN;
395 l_order_number_last NUMBER;
396 i_counter NUMBER; -- to track end of fetch
397 l_rec_counter NUMBER; --to track not null records
398 l_ent_stmt VARCHAR2(500); --entity specific where
399 l_ent_stmt_subquery VARCHAR2(500); -- 4394119, entity specific where used for subqueries
400 l_sql_stmt VARCHAR2(4000); --main loop
401 l_sql_stmt_last VARCHAR2(4000); --last condition hist/transaction table
402 l_sql_stmt_txn VARCHAR2(4000); -- for txn table fetch
403 l_header_id_stmt VARCHAR2(500);
404 l_count NUMBER;
405 l_count_last NUMBER;
406 l_count_hist NUMBER;
407 l_header_id NUMBER;
408 l_header_id_from NUMBER;
409 l_header_id_to NUMBER;
410 l_cnt_stmt VARCHAR2(5000);
411 l_id_stmt VARCHAR2(5000);
412 l_order_by VARCHAR2(100);
413 type refcur is ref cursor; -- for any ref cur
414 ref_id REFCUR; --for header id, line id etc.
415 ref_attr REFCUR; -- for main loop
416 ref_attr_last REFCUR; -- last rec
417 ref_attr_txn REFCUR; -- last rec as txn rec
418 v_entity_constrained char(1) := 'N';
419 v_attr_index NUMBER:=0;
420 j NUMBER:=0;
421 l_input_org_id NUMBER := p_org_id;
422 l_org_id_clause varchar2(80) := '';
423 l_org_id_stmt varchar2(500) := '';
424 l_card_number_equal VARCHAR2(1) := 'N';
425 l_new_value VARCHAR2(2000); /* Attrb value, desc for current , old and last*/
426 l_credit_card_number VARCHAR2(80);
427 l_credit_card_code VARCHAR2(80);
428 l_credit_card_holder_name VARCHAR2(80);
429 l_credit_card_expiration_date DATE;
430 l_last_instrument_id NUMBER;
431 l_instr_flag VARCHAR2(1);
432
433 --
434 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
435 --
436
437 BEGIN
438
439 IF l_debug_level > 0 THEN
440 oe_debug_pub.add('Enter audit history consolidate program',1);
441 END IF;
442
443 --Added for MOAC
444 If l_input_org_id IS NOT NULL THEN
445 MO_GLOBAL.set_policy_context('S', l_input_org_id);
446 END IF;
447
448
449 /* Get date range for which data needs to be collected for this entity */
450
451 SELECT nvl(fnd_date.canonical_to_date(start_date),to_date('01/01/1950','MM/DD/RRRR')),
452 nvl(fnd_date.canonical_to_date(end_date), sysdate)
453 INTO min_hist_creation_date, max_hist_creation_date
454 FROM dual;
455 retcode := 0;
456
457 --Commented this out for bug 3630281.
458 /*if order_number_from is not null then
459 BEGIN
460 SELECT HEADER_ID
461 INTO l_header_id_from
462 FROM OE_ORDER_HEADERS_ALL
463 WHERE ORDER_NUMBER = order_number_from;
464 END;
465 end if;
466 if order_number_from is not null then
467 BEGIN
468 SELECT HEADER_ID
469 INTO l_header_id_to
470 FROM OE_ORDER_HEADERS_ALL
471 WHERE ORDER_NUMBER = order_number_to;
472 END;
473 end if;*/
474
475 -- added for MOAC
476 IF l_input_org_id IS NOT NULL THEN
477 l_org_id_clause := ' and org_id = :o';
478 END IF;
479
480 -- changed for MOAC
481 if order_number_from is not null and order_number_to is null
482 then
483 if l_input_org_id is not null then
484 l_header_id_stmt:= ' (SELECT header_id from oe_order_headers where order_number >=:m and org_id = :o)';
485 else
486 l_header_id_stmt:= ' (SELECT header_id from oe_order_headers where order_nu
487 mber >=:m)';
488 end if;
489 elsif order_number_from is null and order_number_to is not null
490 then
491 if l_input_org_id is not null then
492 l_header_id_stmt:= ' (SELECT Header_id from oe_order_headers where order_number <=:n and org_id = :o)';
493 else
494 l_header_id_stmt:= ' (SELECT Header_id from oe_order_headers where order_number <=:n)';
495 end if;
496 elsif order_number_from is not null and order_number_to is not null
497 then
498 if l_input_org_id is not null then
499 l_header_id_stmt:= ' (SELECT Header_id from oe_order_headers where order_number between :m and :n and org_id = :o)';
500 else
501 l_header_id_stmt:= ' (SELECT Header_id from oe_order_headers where order_number between :m and :n)';
502 end if;
503 end if;
504
505
506 if start_date is null and end_date is null then
507 null;
508 elsif start_date is not null and end_date is null then
509 min_hist_creation_date := fnd_date.canonical_to_date(start_date);
510 max_hist_creation_date := sysdate;
511 elsif end_date is not null and start_date is null then
512 min_hist_creation_date := sysdate;
513 max_hist_creation_date := round(fnd_date.canonical_to_date(end_date)+1);
514 elsif end_date is not null and start_date is not null then
515 min_hist_creation_date := fnd_date.canonical_to_date(start_date);
516 max_hist_creation_date := round(fnd_date.canonical_to_date(end_date)+1);
517 end if;
518
519 if audit_duration is not null then
520 min_hist_creation_date := sysdate - audit_duration;
521 max_hist_creation_date := sysdate;
522 end if;
523
524 IF l_debug_level > 0 THEN
525 oe_debug_pub.add('History start date => '||to_char(min_hist_creation_date,'dd-mon-yyyy hh24:mi:ss'),1);
526 oe_debug_pub.add('History end date => '||to_char(max_hist_creation_date,'dd-mon-yyyy hh24:mi:ss'),1);
527 END IF;
528
529 FOR c_ent_rec in c_entities
530 LOOP
531
532 IF l_debug_level > 0 THEN
533 oe_debug_pub.add('Consolidating history records for entity '||to_char(c_ent_rec.entity_id),1);
534 END IF;
535
536 /* Check whether history records exist for this entity in the date range */
537 l_cnt_stmt := 'select count(*) from ' || c_ent_rec.entity_name || ' where trunc(hist_creation_date) between :x and :z and ';
538 l_cnt_stmt := l_cnt_stmt || 'nvl(audit_flag, ''Y'') = ''Y'' and ';
539
540 --Commenting out for bug 3630281
541 /* if l_header_id_from is not null and l_header_id_to is not null then
542 l_cnt_stmt := l_cnt_stmt || ' header_id between :m and :n and ';
543 elsif l_header_id_from is not null and l_header_id_to is null then
544 l_cnt_stmt := l_cnt_stmt || ' header_id >= :m and ';
545 elsif l_header_id_from is null and l_header_id_to is not null then
546
547 l_cnt_stmt := l_cnt_stmt || ' header_id <= :m and ';
548 end if;*/
549
550 IF l_input_org_id IS NOT NULL THEN
551 l_org_id_stmt := ' header_id IN (SELECT header_id from oe_order_headers where org_id = :o)';
552 END IF;
553
554 if l_header_id_stmt is not null
555 then
556 l_cnt_stmt := l_cnt_stmt || ' header_id in ' || l_header_id_stmt || ' and' ;
557 elsif l_org_id_stmt is not null then -- added for MOAC
558 -- Org restriction must be explicitly included
559 l_cnt_stmt := l_cnt_stmt || l_org_id_stmt || ' and';
560 end if;
561
562 l_cnt_stmt := l_cnt_stmt || ' 1 = 1 ';
563
564 if order_number_from is not null and order_number_to is not null then
565 If l_input_org_id IS NOT NULL THEN
566 -- Single Org processing
567 OPEN ref_attr FOR l_cnt_stmt USING min_hist_creation_date,max_hist_creation_date,order_number_from,order_number_to, l_input_org_id;
568 ELSE
569 --Mulitple Org processing
570 OPEN ref_attr FOR l_cnt_stmt USING min_hist_creation_date,max_hist_creation_date,order_number_from,order_number_to;
571 End If;
572 elsif order_number_from is not null and order_number_to is null then
573 If l_input_org_id IS NOT NULL THEN
574 -- Single Org processing
575 OPEN ref_attr FOR l_cnt_stmt USING min_hist_creation_date,max_hist_creation_date,order_number_from, l_input_org_id;
576 ELSE
577 --Mulitple Org processing
578 OPEN ref_attr FOR l_cnt_stmt USING min_hist_creation_date,max_hist_creation_date,order_number_from;
579 End If;
580 elsif order_number_from is null and order_number_to is not null then
581 If l_input_org_id IS NOT NULL THEN
582 -- Single Org processing
583 OPEN ref_attr FOR l_cnt_stmt USING min_hist_creation_date,max_hist_creation_date,order_number_to, l_input_org_id;
584 ELSE
585 --Mulitple Org processing
586 OPEN ref_attr FOR l_cnt_stmt USING min_hist_creation_date,max_hist_creation_date,order_number_to;
587 End If;
588 else
589 If l_input_org_id IS NOT NULL THEN
590 -- Single Org processing
591 OPEN ref_attr FOR l_cnt_stmt USING min_hist_creation_date,max_hist_creation_date, l_input_org_id;
592 ELSE
593 --Mulitple Org processing
594 OPEN ref_attr FOR l_cnt_stmt USING min_hist_creation_date,max_hist_creation_date;
595 End If;
596 end if;
597 FETCH ref_attr INTO l_count;
598 CLOSE ref_attr;
599
600 if l_count=0 then
601 IF l_debug_level > 0 THEN
602 oe_debug_pub.add('NO matching history records in this date range for entity '||to_char(c_ent_rec.entity_id),1);
603 END IF;
604 goto End_Of_Entity;
605 end if;
606
607 /* Moved the below code from inside the Order Loop to here for FP bug4282167 */
608 C_ATTR_TBL.delete;
609 OPEN IS_ENTITY_CONSTRAINED(c_ent_rec.entity_id);
610 FETCH IS_ENTITY_CONSTRAINED INTO v_entity_constrained;
611 CLOSE IS_ENTITY_CONSTRAINED;
612
613 v_attr_index:=1;
614 IF v_entity_constrained = 'Y' THEN
615 IF l_debug_level > 0 THEN
616 OE_DEBUG_PUB.add('Consolidating for all attributes',1);
617 END IF;
618 FOR c_attr_rec IN c_all_attributes(c_ent_rec.entity_id) LOOP
619 c_attr_tbl(v_attr_index).entity_id := c_ent_rec.entity_id;
620 c_attr_tbl(v_attr_index).attribute_id := c_attr_rec.attribute_id;
621 c_attr_tbl(v_attr_index).attribute_code := c_attr_rec.attribute_code;
622 c_attr_tbl(v_attr_index).column_name := c_attr_rec.column_name;
623 c_attr_tbl(v_attr_index).attribute_display_name := c_attr_rec.attribute_display_name;
624 v_attr_index := v_attr_index+1;
625 END LOOP;
626 ELSE
627 IF l_debug_level > 0 THEN
628 OE_DEBUG_PUB.add('Consolidating for currently constrained attributes only',1);
629 END IF;
630
631 FOR c_attr_rec IN c_const_attributes(c_ent_rec.entity_id) LOOP
632 c_attr_tbl(v_attr_index).entity_id := c_ent_rec.entity_id;
633 c_attr_tbl(v_attr_index).attribute_id := c_attr_rec.attribute_id;
634 c_attr_tbl(v_attr_index).attribute_code := c_attr_rec.attribute_code;
635 c_attr_tbl(v_attr_index).column_name := c_attr_rec.column_name;
636 c_attr_tbl(v_attr_index).attribute_display_name := c_attr_rec.attribute_display_name;
637 v_attr_index := v_attr_index+1;
638 END LOOP;
639 END IF;
640 /* End of changes done for FP bug4282167 */
641
642 if (c_ent_rec.entity_id = 1) then
643 l_ent_stmt := ' and hist.header_id = :a ';
644 elsif (c_ent_rec.entity_id = 2) then
645 l_ent_stmt := ' and hist.line_id = :a ';
646 elsif (c_ent_rec.entity_id = 5) then
647 l_ent_stmt := ' and hist.sales_credit_id = :a and hist.line_id is null ';
648 elsif (c_ent_rec.entity_id = 6) then
649 l_ent_stmt := ' and hist.price_adjustment_id = :a and hist.line_id is null ';
650 elsif (c_ent_rec.entity_id = 7) then
651 l_ent_stmt := ' and hist.sales_credit_id = :a and hist.line_id is not null ';
652 elsif (c_ent_rec.entity_id = 8) then
653 l_ent_stmt := ' and hist.price_adjustment_id = :a and hist.line_id is not null ';
654 end if;
655
656 l_ent_stmt := l_ent_stmt || ' and nvl(audit_flag, ''Y'') = ''Y'' ';
657
658 -- Following code added for 4394119. This version of the entity statement does not
659 -- use the hist table alias. As a result, when the inner query uses this statement
660 -- the resultant SQL statement does not have a correlated subquery, which would cause
661 -- FTSs in the outer query
662 if (c_ent_rec.entity_id = 1) then
663 l_ent_stmt_subquery := ' and header_id = :a ';
664 elsif (c_ent_rec.entity_id = 2) then
665 l_ent_stmt_subquery := ' and line_id = :a ';
666 elsif (c_ent_rec.entity_id = 5) then
667 l_ent_stmt_subquery := ' and sales_credit_id = :a and line_id is null ';
668 elsif (c_ent_rec.entity_id = 6) then
669 l_ent_stmt_subquery := ' and price_adjustment_id = :a and line_id is null ';
670 elsif (c_ent_rec.entity_id = 7) then
671 l_ent_stmt_subquery := ' and sales_credit_id = :a and line_id is not null ';
672 elsif (c_ent_rec.entity_id = 8) then
673 l_ent_stmt_subquery := ' and price_adjustment_id = :a and line_id is not null ';
674 end if;
675
676 l_ent_stmt_subquery := l_ent_stmt_subquery || ' and nvl(audit_flag, ''Y'') = ''Y'' ';
677
678 IF l_debug_level > 0 THEN
679 oe_debug_pub.add('For entity id ' || c_ent_rec.entity_id || ' Constructed subquery entity statement: ' || l_ent_stmt_subquery);
680 END IF;
681 -- end bug 4394119
682
683 if (c_ent_rec.entity_id = 1) then
684 l_id_stmt := ' select distinct header_id, header_id entity_number '||
685 ' from oe_order_header_history where nvl(audit_flag, ''Y'') = ''Y'' and hist_creation_date between :m and :n and ';
686
687 l_order_by := ' 1 = 1 ';
688 elsif (c_ent_rec.entity_id = 2) then
689 l_id_stmt := ' select distinct header_id, line_id entity_number '
690 ||' from oe_order_lines_history where nvl(audit_flag, ''Y'') = ''Y'' and hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'') and hist_creation_date between :m and :n and ';
691
692 l_order_by := ' 1 = 1 ';
693 elsif (c_ent_rec.entity_id = 5) then
694 l_id_stmt := ' select distinct header_id, sales_credit_id entity_number ' ||
695 ' from oe_sales_credit_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is null and hist_creation_date between :m and :n and ';
696 l_order_by := ' 1 = 1 ';
697 elsif (c_ent_rec.entity_id = 6) then
698 l_id_stmt := ' select distinct header_id, price_adjustment_id entity_number '||
699 ' from oe_price_adjs_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is null and hist_creation_date between :m and :n and ';
700 l_order_by := ' 1 = 1 ';
701 elsif (c_ent_rec.entity_id = 7) then
702 l_id_stmt := ' select distinct header_id, sales_credit_id entity_number ' ||
703 ' from oe_sales_credit_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is not null and hist_creation_date between :m and :n and ';
704 l_order_by := ' 1 = 1 ';
705 elsif (c_ent_rec.entity_id = 8) then
706 l_id_stmt := ' select distinct header_id, price_adjustment_id entity_number '||
707 ' from oe_price_adjs_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is not null and hist_creation_date between :m and :n and ';
708 l_order_by := ' 1 = 1 ';
709 end if;
710 --Commenting out for bug 3630281
711 /* if l_header_id_from is not null and l_header_id_to is not null then
712 l_id_stmt := l_id_stmt || ' header_id between :o and :p and ';
713
714 elsif l_header_id_from is not null and l_header_id_to is null then
715 l_id_stmt := l_id_stmt || ' header_id >= :o and ';
716 elsif l_header_id_from is null and l_header_id_to is not null then
717 l_id_stmt := l_id_stmt || ' header_id <= :o and ';
718 end if;*/
719
720
721 if l_header_id_stmt is not null
722 then
723 l_id_stmt := l_id_stmt || ' header_id in ' || l_header_id_stmt || ' and' ;
724 elsif l_org_id_stmt is not null
725 then
726 -- Org restriction must be explicitly included - MOAC
727
728 l_id_stmt := l_id_stmt || l_org_id_stmt || ' and';
729 end if;
730
731 l_id_stmt := l_id_stmt || l_order_by;
732
733 if order_number_from is not null and order_number_to is not null then
734 If l_input_org_id IS NOT NULL THEN
735 -- Single Org processing
736 OPEN ref_id FOR l_id_stmt using min_hist_creation_date,max_hist_creation_date,order_number_from,order_number_to, l_input_org_id;
737
738 ELSE
739 -- Multi Org processing
740
741 OPEN ref_id FOR l_id_stmt using min_hist_creation_date,max_hist_creation_date,order_number_from,order_number_to;
742 End If;
743 elsif order_number_from is not null and order_number_to is null then
744 If l_input_org_id IS NOT NULL THEN
745 -- Single Org processing
746 OPEN ref_id FOR l_id_stmt using min_hist_creation_date,max_hist_creation_date,order_number_from, l_input_org_id;
747 ELSE
748 -- Multi Org processing
749 OPEN ref_id FOR l_id_stmt using min_hist_creation_date,max_hist_creation_date,order_number_from;
750 End If;
751 elsif order_number_from is null and order_number_to is not null then
752 If l_input_org_id IS NOT NULL THEN
753 -- Single Org processing
754 OPEN ref_id FOR l_id_stmt using min_hist_creation_date,max_hist_creation_date,order_number_to, l_input_org_id;
755 ELSE
756 --Multi Org processing
757 OPEN ref_id FOR l_id_stmt using min_hist_creation_date,max_hist_creation_date,order_number_to;
758 End If;
759 else
760 IF l_input_org_id IS NOT NULL THEN
761 -- Single Org processing
762 OPEN REF_ID FOR l_id_stmt using min_hist_creation_date,max_hist_creation_date, l_input_org_id;
763 ELSE
764 --Multi Org processing
765 OPEN REF_ID FOR l_id_stmt using min_hist_creation_date,max_hist_creation_date;
766 End If;
767 end if;
768
769 LOOP /* Order number cursor */
770 FETCH ref_id INTO l_header_id, l_entity_number;
771 EXIT WHEN ref_id%NOTFOUND;
772
773 BEGIN
774 SELECT ORDER_NUMBER,SOLD_TO_ORG_ID,ORDER_TYPE_ID,ORG_ID
775 INTO l_order_number,l_sold_to_org_id,l_order_type_id,l_org_id
776 FROM OE_ORDER_HEADERS
777 WHERE HEADER_ID = l_header_id;
778
779 EXCEPTION WHEN OTHERS THEN
780 IF l_debug_level > 0 THEN
781 oe_debug_pub.add('Unable to locate header record for header ID : '||l_header_id||' ERROR: '||SQLERRM , 1 ) ;
782 END IF;
783 END;
784
785 FOR J in 1..c_attr_tbl.count LOOP
786
787 IF (c_ent_rec.entity_id) = 2 THEN
788
789 l_sql_stmt := 'select nvl(oer.reason_code, '
790 || ' hist.reason_code) , hist_creation_date, '
791 || ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
792 || ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
793 || ' from '
794 || c_ent_rec.entity_name || ' hist '
795 || ' , oe_reasons oer '
796 -- || ' where hist_type_code = '||''''||'UPDATE'||''''||' and '
797 || ' where hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'') and '
798 || ' trunc(hist_creation_date) between :x and :z '
799 || ' and hist.header_id = :y '
800 || ' and hist.reason_id = oer.reason_id(+) '
801 || l_ent_stmt;
802 ELSIF (c_ent_rec.entity_id in (6,8)) THEN
803 l_sql_stmt := 'select nvl(oer.reason_code, '
804 || ' hist.change_reason_code), hist_creation_date, '
805 || ' hist_created_by , responsibility_id, nvl(oer.comments,change_reason_text) hist_comments, '
806 || ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
807 || ' from '
808 || c_ent_rec.entity_name || ' hist '
809 || ' , oe_reasons oer '
810 || ' where '
811 || ' trunc(hist_creation_date) between :x and :z '
812 || ' and hist.header_id = :y '
813 || ' and hist.reason_id = oer.reason_id(+) '
814 || l_ent_stmt;
815 ELSIF (c_ent_rec.entity_id in (1,5,7)) THEN
816
817 -- R12 CC encryption
818 -- for security reason, we only store the instrument id for the
819 -- credit card, but only credit card number is updateable,
820 -- and hence the following logic for the column name.
821 IF c_attr_tbl(j).column_name IN ('CREDIT_CARD_NUMBER',
822 'CREDIT_CARD_CODE',
823 'CREDIT_CARD_HOLDER_NAME',
824 'CREDIT_CARD_EXPIRATION_DATE')
825 THEN
826
827
828 IF c_attr_tbl(j).column_name = 'CREDIT_CARD_NUMBER' THEN
829 l_sql_stmt := 'select nvl(oer.reason_code, '
830 || ' hist.reason_code), hist_creation_date, '
831 || ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
832 || ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
833 || ' from '
834 || c_ent_rec.entity_name || ' hist '
835 || ' , oe_reasons oer '
836 || ' where '
837 || ' trunc(hist_creation_date) between :x and :z '
838 || ' and hist.header_id = :y '
839 || ' and hist.reason_id = oer.reason_id(+) '
840 || ' and (hist.credit_card_number IS NOT NULL OR (hist.credit_card_number IS NULL AND hist.credit_card_holder_name IS NULL AND hist.credit_card_code IS NULL AND hist.credit_card_expiration_date IS NULL))'
841 || l_ent_stmt;
842 ELSIF c_attr_tbl(j).column_name = 'CREDIT_CARD_CODE' THEN
843 l_sql_stmt := 'select nvl(oer.reason_code, '
844 || ' hist.reason_code), hist_creation_date, '
845 || ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
846 || ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
847 || ' from '
848 || c_ent_rec.entity_name || ' hist '
849 || ' , oe_reasons oer '
850 || ' where '
851 || ' trunc(hist_creation_date) between :x and :z '
852 || ' and hist.header_id = :y '
853 || ' and hist.reason_id = oer.reason_id(+) '
854 || ' and (hist.credit_card_code IS NOT NULL OR (hist.credit_card_number IS NULL AND hist.credit_card_holder_name IS NULL AND hist.credit_card_code IS NULL AND hist.credit_card_expiration_date IS NULL))'
855 || l_ent_stmt;
856 ELSIF c_attr_tbl(j).column_name = 'CREDIT_CARD_HOLDER_NAME' THEN
857 l_sql_stmt := 'select nvl(oer.reason_code, '
858 || ' hist.reason_code), hist_creation_date, '
859 || ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
860 || ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
861 || ' from '
862 || c_ent_rec.entity_name || ' hist '
863 || ' , oe_reasons oer '
864 || ' where '
865 || ' trunc(hist_creation_date) between :x and :z '
866 || ' and hist.header_id = :y '
867 || ' and hist.reason_id = oer.reason_id(+) '
868 || ' and (hist.credit_card_holder_name IS NOT NULL OR (hist.credit_card_number IS NULL AND hist.credit_card_holder_name IS NULL AND hist.credit_card_code IS NULL AND hist.credit_card_expiration_date IS NULL))'
869 || l_ent_stmt;
870 ELSIF c_attr_tbl(j).column_name = 'CREDIT_CARD_EXPIRATION_DATE' THEN
871 l_sql_stmt := 'select nvl(oer.reason_code, '
872 || ' hist.reason_code), hist_creation_date, '
873 || ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
874 || ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
875 || ' from '
876 || c_ent_rec.entity_name || ' hist '
877 || ' , oe_reasons oer '
878 || ' where '
879 || ' trunc(hist_creation_date) between :x and :z '
880 || ' and hist.header_id = :y '
881 || ' and hist.reason_id = oer.reason_id(+) '
882 || ' and (hist.credit_card_expiration_date IS NOT NULL OR (hist.credit_card_number IS NULL AND hist.credit_card_holder_name IS NULL AND hist.credit_card_code IS NULL AND hist.credit_card_expiration_date IS NULL))'
883 || l_ent_stmt;
884
885 END IF;
886
887 c_attr_tbl(j).column_name := 'INSTRUMENT_ID';
888
889 ELSE
890 l_sql_stmt := 'select nvl(oer.reason_code, '
891 || ' hist.reason_code), hist_creation_date, '
892 || ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
893 || ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
894 || ' from '
895 || c_ent_rec.entity_name || ' hist '
896 || ' , oe_reasons oer '
897 || ' where '
898 || ' trunc(hist_creation_date) between :x and :z '
899 || ' and hist.header_id = :y '
900 || ' and hist.reason_id = oer.reason_id(+) '
901 || l_ent_stmt;
902 END IF;
903 ELSE
904 IF l_debug_level > 0 THEN
905 oe_debug_pub.add('Entity id not recognized '||c_ent_rec.entity_id,1);
906 END IF;
907 END IF;
908
909 BEGIN
910 OPEN ref_attr FOR l_sql_stmt
911 USING min_hist_creation_date, max_hist_creation_date, l_header_id, l_entity_number;
912 i_counter := 1;
913 l_rec_counter := 1;
914 /* This flag will be true when the last record in oe_audit_attr_history
915 will be updated with the new attribute value column */
916 l_old_db_rec_upd_flag := false;
917 l_old_hist_creation_date := null;
918 l_old_reason_code := null;
919 l_old_user_id := null;
920 l_old_responsibility_id := null;
921 l_old_attribute_value := null;
922 l_old_context_value := null;
923 LOOP
924 l_attribute_value := NULL;
925 l_attribute_value_last := NULL;
926 l_context_value := NULL;
927 l_context_value_last := NULL;
928
929 /* Following condition for any iteration after the first */
930 IF (l_old_db_rec_upd_flag = TRUE) THEN
931
932 l_old_hist_creation_date := l_hist_creation_date;
933 l_old_reason_code := l_reason_code;
934 l_old_attribute_value := l_attribute_value;
935 l_old_user_id := l_user_id;
936 l_old_responsibility_id := l_responsibility_id;
937 l_old_context_value := l_context_value;
938 END IF;
939
940 FETCH ref_attr INTO
941 l_reason_code
942 ,l_hist_creation_date
943 ,l_user_id
944 ,l_responsibility_id
945
946 ,l_change_comments
947 ,l_attribute_value
948 ,l_context_value;
949 EXIT WHEN ref_attr%NOTFOUND;
950
951 -- Check if history records exist for this attribute
952 SELECT count(*)
953 INTO l_count_hist
954 FROM oe_audit_attr_history
955 WHERE hist_creation_date = l_hist_creation_date
956 AND entity_number = l_entity_number
957 AND attribute_id = c_attr_tbl(j).attribute_id
958 AND entity_id = c_ent_rec.entity_id
959 and rownum = 1; -- Added for bug 7319059
960
961 IF l_count_hist > 0 THEN
962 goto next_attribute;
963 END IF;
964
965 BEGIN
966 select credit_card_code, credit_card_number
967 ,credit_card_holder_name, credit_card_expiration_date
968 into l_credit_card_code, l_credit_card_number
969 ,l_credit_card_holder_name, l_credit_card_expiration_date
970 from oe_order_header_history
971 where hist_creation_date = l_hist_creation_date
972 and header_id = l_header_id;
973 EXCEPTION WHEN NO_DATA_FOUND THEN
974 null;
975 END;
976
977 IF NOT (l_credit_card_code IS NULL
978 AND l_credit_card_number IS NULL
979 AND l_credit_card_holder_name IS NULL
980 AND l_credit_card_expiration_date IS NULL) THEN
981
982
983 IF c_attr_tbl(j).attribute_id IN (46,49) AND
984 l_credit_card_code IS NULL and l_credit_card_number IS NULL THEN
985 goto next_attribute;
986
987 ELSIF c_attr_tbl(j).attribute_id = 47 AND
988 l_credit_card_expiration_date IS NULL THEN
989 goto next_attribute;
990 ELSIF c_attr_tbl(j).attribute_id = 48 AND
991 l_credit_card_holder_name IS NULL THEN
992 goto next_attribute;
993 END IF;
994 END IF;
995
996 IF ((l_attribute_value <> l_old_attribute_value)
997 AND (l_old_db_rec_upd_flag = true))
998 -- R12 cc encryption
999 OR (c_attr_tbl(j).attribute_id IN (46,47,48,49)
1000 AND (l_old_db_rec_upd_flag = true))
1001 then
1002 IF c_attr_tbl(j).attribute_id IN (46,47,48,49)
1003 AND l_old_db_rec_upd_flag = true
1004 THEN
1005 -- if the credit card attribute is the same, skip to next record.
1006
1007 Compare_Credit_Card(p_attribute_id => c_attr_tbl(j).attribute_id,
1008 p_header_id =>l_header_id,
1009 p_old_hist_creation_date =>l_old_hist_creation_date,
1010 p_new_hist_creation_date => l_hist_creation_date,
1011 x_old_attribute_value => l_old_attribute_value,
1012 x_new_attribute_value => l_attribute_value,
1013 x_card_number_equal => l_card_number_equal);
1014
1015 IF (l_old_attribute_value = l_attribute_value
1016 AND c_attr_tbl(j).attribute_id IN (46,47,48))
1017 OR
1018 (l_card_number_equal = 'Y'
1019 AND c_attr_tbl(j).attribute_id = 49)
1020 THEN
1021 goto next_attribute;
1022 END IF;
1023
1024 END IF;
1025 -- end R12 cc encryption
1026 oe_pc_attr_tbl(l_rec_counter).entity_id := c_ent_rec.entity_id;
1027 oe_pc_attr_tbl(l_rec_counter).attribute_id := c_attr_tbl(j).attribute_id;
1028 oe_pc_attr_tbl(l_rec_counter).old_attribute_value := l_old_attribute_value;
1029 oe_pc_attr_tbl(l_rec_counter).hist_creation_date := l_old_hist_creation_date;
1030 oe_pc_attr_tbl(l_rec_counter).order_number := l_order_number;
1031 oe_pc_attr_tbl(l_rec_counter).entity_number:= l_entity_number;
1032 oe_pc_attr_tbl(l_rec_counter).user_id := l_old_user_id;
1033 oe_pc_attr_tbl(l_rec_counter).reason_code := l_old_reason_code;
1034 oe_pc_attr_tbl(l_rec_counter).new_attribute_value:= l_attribute_value;
1035 oe_pc_attr_tbl(l_rec_counter).responsibility_id := l_old_responsibility_id;
1036 oe_pc_attr_tbl(l_rec_counter).new_context_value:= l_context_value;
1037 oe_pc_attr_tbl(l_rec_counter).old_context_value:= l_old_context_value;
1038 l_rec_counter := l_rec_counter +1;
1039 END IF;
1040
1041
1042 i_counter := i_counter + 1;
1043 l_old_db_rec_upd_flag := FALSE;
1044 l_sql_stmt_last := 'select count(*) from '
1045 || c_ent_rec.entity_name || ' hist '
1046 -- || ' where hist_type_code = '||''''||'UPDATE'||''''||' and '
1047 || ' where hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'') and '
1048 || ' hist_creation_date > :x '
1049 || ' and header_id = :y '
1050 || l_ent_stmt;
1051 OPEN ref_attr_last FOR l_sql_stmt_last using l_hist_creation_date ,l_header_id ,l_entity_number;
1052 FETCH ref_attr_last INTO l_count_last;
1053 CLOSE ref_attr_last;
1054
1055 IF (l_count_last = 0) THEN
1056
1057 -- R12 CC encryption
1058 -- for security reason, we only store the instrument id for the
1059 -- credit card, but only credit card number is updateable,
1060 -- and hence the following logic for the column name.
1061 IF c_attr_tbl(j).column_name = 'INSTRUMENT_ID' THEN
1062 IF c_attr_tbl(j).attribute_id = 46 THEN
1063
1064 c_attr_tbl(j).column_name := 'CARD_ISSUER_CODE';
1065 ELSIF c_attr_tbl(j).attribute_id = 47 THEN
1066 c_attr_tbl(j).column_name := 'CARD_EXPIRYDATE';
1067 ELSIF c_attr_tbl(j).attribute_id = 48 THEN
1068 c_attr_tbl(j).column_name := 'CARD_HOLDER_NAME';
1069 ELSIF c_attr_tbl(j).attribute_id = 49 THEN
1070 c_attr_tbl(j).column_name := 'CARD_NUMBER';
1071
1072 END IF;
1073 END IF;
1074
1075 IF l_debug_level > 0 THEN
1076 oe_debug_pub.add('column name is: '||c_attr_tbl(j).column_name, 5);
1077 END IF;
1078
1079 if (c_ent_rec.entity_id = 1) then
1080 if c_attr_tbl(j).attribute_id in (46, 47, 48,49) then
1081
1082 -- get the instrument id for cc number
1083 l_sql_stmt_txn := 'select itev.instrument_id'||' ,ooh.Context '||
1084 ' from oe_order_headers ooh, oe_payments op, iby_trxn_extensions_v itev '||
1085 ' where '||
1086 ' ooh.header_id = :y'||' and ooh.header_id = op.header_id and op.line_id is null and nvl(op.payment_collection_event,''PREPAY'') = ''INVOICE'' and op.trxn_extension_id = itev.trxn_extension_id';
1087 else
1088 l_sql_stmt_txn := 'select '||c_attr_tbl(j).column_name||' ,Context '||
1089 ' from oe_order_headers '||
1090 ' where '||
1091 ' header_id = :y';
1092 end if;
1093
1094 OPEN ref_attr_txn for l_sql_stmt_txn using l_entity_number;
1095 FETCH ref_attr_txn into l_attribute_value_last,l_context_value_last;
1096
1097 CLOSE ref_attr_txn;
1098
1099
1100 ELSIF (c_ent_rec.entity_id = 2) THEN
1101 l_sql_stmt_txn := 'select '||c_attr_tbl(j).column_name||' ,Context '||
1102 ' from oe_order_lines '||
1103 ' where '||
1104 ' line_id = :y';
1105
1106 OPEN ref_attr_txn FOR l_sql_stmt_txn USING l_entity_number;
1107 FETCH ref_attr_txn INTO l_attribute_value_last,l_context_value_last;
1108 CLOSE ref_attr_txn;
1109
1110 elsif (c_ent_rec.entity_id = 5) then
1111 l_sql_stmt_txn := 'select '||c_attr_tbl(j).column_name||' ,Context '||
1112 ' from oe_sales_credits '||
1113 ' where '||
1114 ' sales_credit_id = :y';
1115
1116 OPEN ref_attr_txn for l_sql_stmt_txn using l_entity_number;
1117 FETCH ref_attr_txn into l_attribute_value_last,l_context_value_last;
1118 CLOSE ref_attr_txn;
1119 elsif (c_ent_rec.entity_id = 6) then
1120 l_sql_stmt_txn := 'select '
1121 ||c_attr_tbl(j).column_name || ',Context '
1122 ||' from oe_price_adjustments '
1123 ||' where '
1124 ||' price_adjustment_id = :y';
1125
1126 OPEN ref_attr_txn for l_sql_stmt_txn using l_entity_number;
1127 FETCH ref_attr_txn into l_attribute_value_last,l_context_value_last;
1128 CLOSE ref_attr_txn;
1129 elsif (c_ent_rec.entity_id = 7) then
1130 l_sql_stmt_txn := 'select '||c_attr_tbl(j).column_name||',Context '||
1131 ' from oe_sales_credits '||
1132 ' where '||
1133 ' sales_credit_id = :y';
1134
1135 OPEN ref_attr_txn for l_sql_stmt_txn using l_entity_number;
1136 FETCH ref_attr_txn into l_attribute_value_last,l_context_value_last;
1137 CLOSE ref_attr_txn;
1138 elsif (c_ent_rec.entity_id = 8) then
1139 l_sql_stmt_txn := 'select '||c_attr_tbl(j).column_name||',Context '||
1140 ' from oe_price_adjustments'||
1141 ' where '||
1142 ' price_adjustment_id = :y';
1143
1144 OPEN ref_attr_txn for l_sql_stmt_txn using l_entity_number;
1145
1146 FETCH ref_attr_txn into l_attribute_value_last,l_context_value_last;
1147 CLOSE ref_attr_txn;
1148 end if;
1149 /* End Get the last record i.e. from the transaction table */
1150 l_order_number_last := l_order_number;
1151 ELSE
1152
1153 -- R12 CC encryption
1154 -- for security reason, we only store the instrument id for the
1155 -- credit card, but only credit card number is updateable,
1156 -- and hence the following logic for the column name.
1157
1158 if (l_count_last = 1) then
1159 IF (c_ent_rec.entity_id in (6,8)) THEN
1160 l_sql_stmt_last :=
1161 'select hist_creation_date, nvl(oer.reason_code, change_reason_code), '
1162 || ' hist_created_by, responsibility_id, '
1163 || ' hist.' || c_attr_tbl(j).column_name || ', hist.context '
1164 || ' from '
1165 || c_ent_rec.entity_name || ' hist, oe_reasons oer '
1166 || ' where hist_creation_date > :x '
1167 || ' and hist_type_code = '||''''||'UPDATE'||''''
1168 || ' and hist.header_id = :y '
1169 || ' and hist.reason_id = oer.reason_id(+) '
1170 || l_ent_stmt;
1171 ELSE
1172 l_sql_stmt_last :=
1173 'select hist_creation_date, nvl(oer.reason_code, hist.reason_code), '
1174 || ' hist_created_by, responsibility_id, '
1175 || ' hist.' || c_attr_tbl(j).column_name || ', hist.context '
1176 || ' from '
1177 || c_ent_rec.entity_name || ' hist, oe_reasons oer '
1178 || ' where hist_creation_date > :x '
1179 -- || ' and hist_type_code = '||''''||'UPDATE'||''''
1180 || ' and hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'') '
1181 || ' and hist.header_id = :y '
1182 || ' and hist.reason_id = oer.reason_id(+) '
1183 || l_ent_stmt;
1184
1185 END IF;
1186 ELSIF (l_count_last > 1) then
1187 IF l_debug_level > 0 THEN
1188 oe_debug_pub.add('Executing subquery: ' || l_ent_stmt_subquery);
1189 oe_debug_pub.add('Entity id: ' || c_ent_rec.entity_id || ' date: ' || l_hist_creation_date || ' Header Id: ' || l_header_id || ' entity number ' || l_entity_number);
1190 END IF;
1191 IF (c_ent_rec.entity_id in (6,8)) THEN
1192 l_sql_stmt_last :=
1193 'select hist_creation_date, nvl(oer.reason_code, hist.change_reason_code), '
1194 || ' hist_created_by, responsibility_id, '
1195 || ' hist.' || c_attr_tbl(j).column_name || ' ,hist.context '
1196 || ' from '
1197 || c_ent_rec.entity_name || ' hist, oe_reasons oer '
1198 || ' where hist.reason_id = oer.reason_id(+) '
1199 || ' and nvl(audit_flag, ''Y'') = ''Y'' '
1200 || ' and hist.rowid = '
1201 || ' (select min(rowid) from '
1202 || c_ent_rec.entity_name
1203 || ' where hist_creation_date > :x '
1204 || ' and hist_type_code = '||''''||'UPDATE'|| ''''
1205 || ' and header_id = :y '
1206 || l_ent_stmt_subquery
1207 || ') ';
1208 ELSE
1209 l_sql_stmt_last :=
1210 'select hist_creation_date, nvl(oer.reason_code, hist.reason_code), '
1211 || ' hist_created_by, responsibility_id, '
1212 || ' hist.' || c_attr_tbl(j).column_name || ' ,hist.context '
1213 || ' from '
1214 || c_ent_rec.entity_name || ' hist, oe_reasons oer '
1215 || ' where hist.reason_id = oer.reason_id(+) '
1216 || ' and nvl(audit_flag, ''Y'') = ''Y'' '
1217 || ' and hist.rowid = '
1218 || ' (select min(rowid) from '
1219 || c_ent_rec.entity_name
1220 || ' where hist_creation_date > :x '
1221 -- || ' and hist_type_code = '||''''||'UPDATE' ||''''
1222 || ' and hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'') '
1223 || ' and header_id = :y '
1224 || l_ent_stmt_subquery
1225 || ') ';
1226 END IF;
1227 END IF;
1228 OPEN ref_attr_last for l_sql_stmt_last
1229
1230 USING l_hist_creation_date, l_header_id, l_entity_number;
1231
1232 FETCH ref_attr_last
1233 INTO l_hist_creation_date_last
1234 , l_reason_code_last
1235 , l_user_id_last
1236 , l_responsibility_id_last
1237 , l_attribute_value_last
1238 , l_context_value_last;
1239 CLOSE ref_attr_last;
1240
1241
1242 END IF;
1243
1244 -- IF c_attr_tbl(j).column_name = 'INSTRUMENT_ID'
1245 IF c_attr_tbl(j).column_name IN ('CARD_ISSUER_CODE',
1246 'CARD_EXPIRYDATE',
1247 'CARD_HOLDER_NAME',
1248 'CARD_NUMBER')
1249 AND c_ent_rec.entity_id = 1 THEN
1250
1251 BEGIN
1252 select instrument_id
1253 into l_last_instrument_id
1254 from oe_order_header_history
1255 where header_id = l_header_id
1256 and hist_creation_date =
1257 (select max(hist_creation_date)
1258 from oe_order_header_history
1259 where header_id = l_header_id);
1260 EXCEPTION WHEN NO_DATA_FOUND THEN
1261 null;
1262 END;
1263
1264 END IF;
1265
1266
1267 IF (nvl(l_attribute_value_last,'Value was null') <> nvl(l_attribute_value,'Value was null'))
1268 -- R12 CC encryption
1269 OR c_attr_tbl(j).attribute_id IN (46,47,48,49)
1270 THEN
1271 IF c_attr_tbl(j).attribute_id IN (46,47,48,49) AND l_count_last <> 0
1272 -- IF c_attr_tbl(j).attribute_id IN (46,47,48,49)
1273 THEN
1274 -- if the credit card attribute is the same, skip to next record.
1275
1276 -- IF Card_Equal(c_attr_tbl(j).attribute_id, l_header_id, l_hist_creation_date_last, l_hist_creation_date) THEN
1277
1278 Compare_Credit_Card(p_attribute_id => c_attr_tbl(j).attribute_id,
1279 p_header_id =>l_header_id,
1280 p_old_hist_creation_date =>l_hist_creation_date,
1281 p_new_hist_creation_date => l_hist_creation_date_last,
1282 x_old_attribute_value => l_attribute_value,
1283 x_new_attribute_value => l_attribute_value_last,
1284 x_card_number_equal => l_card_number_equal);
1285
1286
1287 IF (l_attribute_value_last = l_attribute_value
1288 AND c_attr_tbl(j).attribute_id IN (46,47,48))
1289 OR
1290 (l_card_number_equal = 'Y'
1291 AND c_attr_tbl(j).attribute_id = 49)
1292 THEN
1293 goto next_attribute;
1294 END IF;
1295
1296 IF l_debug_level > 0 THEN
1297 oe_debug_pub.add('old date is '||to_char(l_hist_creation_date ,'HH24:MI:SS DD-MON-YYYY'),1);
1298 oe_debug_pub.add('new date is '||to_char(l_hist_creation_date_last ,'HH24:MI:SS DD-MON-YYYY'),1);
1299 oe_debug_pub.add('old value is '||l_attribute_value ,1);
1300 oe_debug_pub.add('new value is '||l_attribute_value_last,1);
1301 END IF;
1302
1303 ELSIF c_attr_tbl(j).attribute_id IN (46,47,48, 49) AND l_count_last = 0 THEN
1304 -- compare the card number between the last record and the one
1305 -- in the transaction table.
1306
1307 IF c_attr_tbl(j).attribute_id = 49
1308
1309 -- l_attribute_value_last is the instrument id on the current order
1310 -- l_last_instrument_id is the last record in oe_order_header_history.
1311 AND OE_GLOBALS.Is_Same_Credit_Card(p_cc_num_old => null
1312 ,p_cc_num_new => null
1313 ,p_instrument_id_old => l_attribute_value_last
1314 -- ,p_instrument_id_new => l_attribute_value) THEN
1315 ,p_instrument_id_new => l_last_instrument_id) THEN
1316 goto next_attribute;
1317
1318 ELSE
1319
1320 IF l_credit_card_number IS NULL AND
1321 l_credit_card_code IS NULL
1322 AND c_attr_tbl(j).attribute_id in (47,48)
1323 AND l_count_last <>0 THEN
1324 l_instr_flag :='N';
1325 ELSE
1326 l_instr_flag := 'Y';
1327 END IF;
1328
1329 l_attribute_value_last := Get_Card_Attribute_Value
1330 (p_instr_flag => l_instr_flag,
1331 p_attribute_id => c_attr_tbl(j).attribute_id,
1332 p_instrument_id => to_number(l_attribute_value_last));
1333
1334 IF l_credit_card_number IS NULL AND
1335 l_credit_card_code IS NULL
1336 AND c_attr_tbl(j).attribute_id in (47,48)
1337 AND l_count_last = 0 THEN
1338 -- this is to get the last record in oe_order_header_history,
1339 -- if it stores card history id then set flag to 'N' when
1340 -- calling Get_Card_Attribute_Value to get l_attribute_value.
1341
1342 l_instr_flag :='N';
1343 END IF;
1344
1345 l_attribute_value := Get_Card_Attribute_Value
1346 (p_instr_flag => l_instr_flag,
1347 p_attribute_id => c_attr_tbl(j).attribute_id,
1348 p_instrument_id => l_last_instrument_id);
1349
1350 END IF;
1351
1352 END IF;
1353
1354 -- end R12 cc encryption
1355
1356 IF l_debug_level > 0 THEN
1357 oe_debug_pub.add('Found history records for order => '||l_order_number,1);
1358 END IF;
1359
1360 oe_pc_attr_tbl(l_rec_counter).old_attribute_value := l_attribute_value;
1361 oe_pc_attr_tbl(l_rec_counter).new_attribute_value := l_attribute_value_last;
1362 oe_pc_attr_tbl(l_rec_counter).entity_id := c_ent_rec.entity_id;
1363 oe_pc_attr_tbl(l_rec_counter).attribute_id := c_attr_tbl(j).attribute_id;
1364 oe_pc_attr_tbl(l_rec_counter).hist_creation_date := l_hist_creation_date;
1365 oe_pc_attr_tbl(l_rec_counter).order_number := l_order_number;
1366 oe_pc_attr_tbl(l_rec_counter).entity_number:= l_entity_number;
1367 oe_pc_attr_tbl(l_rec_counter).user_id := l_user_id;
1368 oe_pc_attr_tbl(l_rec_counter).reason_code := l_reason_code;
1369 oe_pc_attr_tbl(l_rec_counter).responsibility_id := l_responsibility_id;
1370 oe_pc_attr_tbl(l_rec_counter).order_type_id := l_order_type_id;
1371 oe_pc_attr_tbl(l_rec_counter).org_id := l_org_id;
1372 oe_pc_attr_tbl(l_rec_counter).sold_to_org_id := l_sold_to_org_id;
1373 oe_pc_attr_tbl(l_rec_counter).change_comments := l_change_comments;
1374 oe_pc_attr_tbl(l_rec_counter).new_context_value := l_context_value_last;
1375 oe_pc_attr_tbl(l_rec_counter).old_context_value := l_context_value;
1376 l_rec_counter := l_rec_counter+1;
1377
1378 END IF;
1379
1380 <<next_attribute>>
1381 null;
1382 END LOOP;
1383 CLOSE REF_ATTR;
1384
1385 FOR I in 1..oe_pc_attr_tbl.count LOOP
1386 IF (oe_pc_attr_tbl(I).old_attribute_value is not null OR
1387 oe_pc_attr_tbl(I).new_attribute_value is not null) THEN
1388 IF l_debug_level > 0 THEN
1389 oe_debug_pub.add('Inserting history records for order number => '||oe_pc_attr_tbl(i).order_number,1);
1390
1391 END IF;
1392 INSERT INTO OE_AUDIT_ATTR_HISTORY
1393 (entity_id,
1394 attribute_id,
1395 reason_code,
1396 hist_creation_date,
1397 order_number,
1398 user_id,
1399 responsibility_id,
1400 old_attribute_value,
1401 new_attribute_value,
1402 entity_number,
1403
1404 order_type_id,
1405 org_id,
1406 sold_to_org_id,
1407 change_comments,
1408 old_context_value,
1409 new_context_value )
1410 values
1411 (oe_pc_attr_tbl(I).entity_id,
1412 oe_pc_attr_tbl(I).attribute_id,
1413 oe_pc_attr_tbl(I).reason_code,
1414 oe_pc_attr_tbl(I).hist_creation_date,
1415 oe_pc_attr_tbl(I).order_number,
1416 oe_pc_attr_tbl(I).user_id,
1417
1418 oe_pc_attr_tbl(I).responsibility_id,
1419 oe_pc_attr_tbl(I).old_attribute_value,
1420 oe_pc_attr_tbl(I).new_attribute_value,
1421 oe_pc_attr_tbl(I).entity_number,
1422 oe_pc_attr_tbl(I).order_type_id,
1423 oe_pc_attr_tbl(I).org_id,
1424 oe_pc_attr_tbl(I).sold_to_org_id,
1425 oe_pc_attr_tbl(I).change_comments,
1426 oe_pc_attr_tbl(I).old_context_value, --Bug4324371
1427 oe_pc_attr_tbl(I).new_context_Value );
1428 END IF;
1429 END LOOP;
1430 oe_pc_attr_tbl.delete;
1431
1432 COMMIT;
1433 EXCEPTION WHEN OTHERS THEN
1434 IF l_debug_level > 0 THEN
1435 oe_debug_pub.add('SQL stmt => '||l_sql_stmt,1);
1436 oe_debug_pub.add('SQL stmt last => '||l_sql_stmt_last,1);
1437 oe_debug_pub.add('SQL stmt txn => '||l_sql_stmt_txn,1);
1438 oe_debug_pub.add('Error => '||sqlerrm,1);
1439 END IF;
1440 END;
1441 END LOOP; /* Attr cursor */
1442 END LOOP;
1443 <<End_of_Entity>>
1444 null;
1445
1446 END LOOP; /* entity cursor */
1447 IF l_debug_level > 0 THEN
1448 oe_debug_pub.add('Exit audit history consolidator program..',1);
1449 END IF;
1450 EXCEPTION WHEN OTHERS THEN
1451 IF l_debug_level > 0 THEN
1452 oe_debug_pub.add('Exiting with error '||sqlerrm,1);
1453 END IF;
1454 retcode := 2;
1455 errbuf := sqlerrm;
1456 END set_attribute_history;
1457
1458
1459 FUNCTION get_num_date_from_canonical(p_datatype IN VARCHAR2
1460 ,p_value IN VARCHAR2
1461 )RETURN VARCHAR2 IS
1462 l_varchar_out varchar2(2000);
1463 INVALID_DATA_TYPE EXCEPTION;
1464 BEGIN
1465 IF p_datatype = 'N' THEN
1466 l_varchar_out := to_char(fnd_number.canonical_to_number(p_value));
1467 ELSIF p_datatype = 'X' THEN
1468 l_varchar_out := fnd_date.canonical_to_date(p_value);
1469 ELSIF p_datatype = 'Y' THEN
1470 l_varchar_out := fnd_date.canonical_to_date(p_value);
1471 ELSIF p_datatype = 'C' THEN
1472 l_varchar_out := p_value;
1473 ELSE
1474 l_varchar_out := p_value;
1475 END IF;
1476 RETURN l_varchar_out;
1477
1478 EXCEPTION When Others Then
1479 l_varchar_out := p_value;
1480 END GET_NUM_DATE_FROM_CANONICAL;
1481
1482 PROCEDURE get_valueset_id_r(p_flexfield_name IN VARCHAR2,
1483 p_context IN VARCHAR2 ,
1484 p_seg IN VARCHAR2 ,
1485 x_vsid OUT NOCOPY NUMBER,
1486 x_format_type OUT NOCOPY VARCHAR2,
1487 x_validation_type OUT NOCOPY VARCHAR2)
1488 IS
1489 L_Valueset_R FND_VSET.VALUESET_R;
1490 X_VALUESETID NUMBER;
1491 L_valueset_dr FND_VSET.VALUESET_DR;
1492 v_dflex_r fnd_dflex.dflex_r;
1493 v_segments_dr fnd_dflex.segments_dr;
1494 v_context_r fnd_dflex.context_r;
1495 BEGIN
1496 v_dflex_r.application_id := 660;
1497 v_dflex_r.flexfield_name := p_flexfield_name;
1498 v_context_r.flexfield := v_dflex_r;
1499 v_context_r.context_code := p_context;
1500 -- Get the enabled segments for the context selected.
1501 fnd_dflex.get_segments(v_context_r,v_segments_dr,TRUE);
1502
1503 FOR i IN 1..v_segments_dr.nsegments LOOP
1504 IF (v_segments_dr.application_column_name(i) = p_seg) THEN
1505 X_VALUESETID := v_SEGMENTS_dr.VALUE_SET(i);
1506 exit;
1507 END IF;
1508 END LOOP;
1509
1510 IF X_VALUESETID IS NOT NULL THEN
1511 FND_VSET.GET_VALUESET(X_VALUESETID,l_valueset_r,l_valueset_dr);
1512 X_VSID :=X_VALUESETID;
1513 X_FORMAT_TYPE :=l_valueset_dr.FORMAT_TYPE;
1514 X_VALIDATION_TYPE :=l_valueset_r.VALIDATION_TYPE;
1515 ELSE
1516 X_VSID :=NULL;
1517 X_FORMAT_TYPE :='C';
1518 X_VALIDATION_TYPE :=NULL;
1519 END IF;
1520 end GET_VALUESET_ID_R;
1521
1522 FUNCTION Get_Attribute_Value(p_FlexField_Name IN VARCHAR2
1523 ,p_Context_Name IN VARCHAR2
1524 ,p_segment_name IN VARCHAR2
1525 ,p_attr_value IN VARCHAR2
1526 ,p_comparison_operator_code IN VARCHAR2 := NULL
1527 ) RETURN VARCHAR2 IS
1528 Vset FND_VSET.valueset_r;
1529 Fmt FND_VSET.valueset_dr;
1530 Found BOOLEAN;
1531 Row NUMBER;
1532 Value FND_VSET.value_dr;
1533 x_Format_Type Varchar2(1);
1534 x_Validation_Type Varchar2(1);
1535 x_Vsid NUMBER;
1536 x_attr_value_code VARCHAR2(240);
1537 l_attr_value VARCHAR2(2000);
1538 Value_Valid_In_Valueset BOOLEAN := FALSE;
1539 l_id VARCHAR2(240);
1540 l_value VARCHAR2(240);
1541
1542 BEGIN
1543
1544 OE_AUDIT_HISTORY_PVT.get_valueset_id_r(p_FlexField_Name
1545 ,p_Context_Name
1546 ,p_Segment_Name
1547 ,x_Vsid
1548 ,x_Format_Type
1549 ,x_Validation_Type);
1550
1551 l_attr_value := get_num_date_from_canonical(x_format_type,p_attr_value);
1552
1553 -- if comparison operator is other than then no need to get the
1554 -- meaning as the value itself will be stored in qualifier_attr_value
1555
1556 -- change made by spgopal. added parameter called p_comparison_operator_code
1557 -- to generalise the code for all forms and packages
1558
1559 IF p_comparison_operator_code <> 'BETWEEN' THEN
1560 IF x_Validation_Type In('F' ,'I') AND x_Vsid IS NOT NULL THEN
1561 IF x_Validation_Type = 'I' THEN
1562 FND_VSET.get_valueset(x_Vsid,Vset,Fmt);
1563 FND_VSET.get_value_init(Vset,TRUE);
1564 FND_VSET.get_value(Vset,Row,Found,Value);
1565 IF Fmt.Has_Id Then --id is defined.Hence compare for id
1566 While(Found) Loop
1567 If l_attr_value = Value.id Then
1568 x_attr_value_code := Value.value;
1569 Value_Valid_In_Valueset := TRUE;
1570 EXIT;
1571 End If;
1572 FND_VSET.get_value(Vset,Row,Found,Value);
1573 End Loop;
1574 Else -- id not defined.Hence compare for value
1575 While(Found) Loop
1576 If l_attr_value = Value.value Then
1577 x_attr_value_code := l_attr_value;
1578 Value_Valid_In_Valueset := TRUE;
1579 EXIT;
1580 End If;
1581 FND_VSET.get_value(Vset,Row,Found,Value);
1582 End Loop;
1583 End If; ---end of Fmt.Has_Id
1584 FND_VSET.get_value_end(Vset);
1585 ELSIF X_Validation_type = 'F' THEN
1586 FND_VSET.get_valueset(x_Vsid,Vset,Fmt);
1587 IF (OE_AUDIT_HISTORY_PVT.value_exists_in_table(Vset.table_info,l_attr_value,l_id,l_value)) THEN
1588 IF Fmt.Has_Id Then --id is defined.Hence compare for id
1589 If l_attr_value = l_id Then
1590 x_attr_value_code := l_value;
1591 Value_Valid_In_Valueset := TRUE;
1592 End If;
1593 Else -- id not defined.Hence compare for value
1594 If l_attr_value = l_value Then
1595 x_attr_value_code := l_attr_value;
1596 Value_Valid_In_Valueset := TRUE;
1597 End If;
1598 End if; -- End of Fmt.Has_ID
1599 ELSE
1600 Value_Valid_In_Valueset := FALSE;
1601 END IF;
1602 END IF; --X_Validation_Type
1603 ELSE -- if validation type is not F or I or valueset id is null (not defined)
1604 x_attr_value_code := l_attr_value;
1605 END IF;
1606 ELSE -- if comparison operator is 'between'
1607 x_attr_value_code := l_attr_value;
1608 END IF;
1609 RETURN x_attr_value_code;
1610
1611 END Get_Attribute_Value;
1612
1613 FUNCTION Get_Display_Name(p_attribute_ID NUMBER,
1614 p_context_value VARCHAR2 DEFAULT NULL,
1615 p_old_context_value VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
1616 IS
1617 p_display_name VARCHAR2(500);
1618 p_column_name VARCHAR2(50);
1619 BEGIN
1620 BEGIN
1621 SELECT ATTRIBUTE_DISPLAY_NAME,COLUMN_NAME
1622 INTO p_display_name,p_column_name
1623 FROM OE_PC_ATTRIBUTES_V
1624 WHERE ATTRIBUTE_ID = p_attribute_id;
1625 EXCEPTION WHEN OTHERS THEN
1626 p_display_name:=NULL;
1627 END;
1628
1629 IF (p_attribute_id between 1078 and 1092 ) OR (p_attribute_id between 4218 and 4222) THEN
1630 p_display_name := OE_AUDIT_HISTORY_PVT.Get_column_label('OE_HEADER_ATTRIBUTES',UPPER(p_column_name), p_context_value,p_old_context_value);
1631 ELSIF (p_attribute_id between 1018 and 1032) OR (p_attribute_id between 4223 and 4227) THEN
1632 p_display_name := OE_AUDIT_HISTORY_PVT.Get_column_label('OE_LINE_ATTRIBUTES',UPPER(p_column_name),p_context_value,p_old_context_value);
1633 END IF;
1634
1635 RETURN p_display_name;
1636
1637 END Get_Display_Name;
1638
1639 FUNCTION Get_Column_Label(p_flexfield_name IN varchar2,
1640 p_appl_column_name IN varchar2,
1641 p_context_value IN Varchar2 DEFAULT NULL,
1642 p_old_context_value IN Varchar2 DEFAULT NULL) RETURN VARCHAR2
1643 IS
1644 p_column_label VARCHAR2(500);
1645 BEGIN
1646 SELECT NVL(FORM_LEFT_PROMPT,FORM_ABOVE_PROMPT)
1647 INTO p_column_label
1648 FROM FND_DESCR_FLEX_COL_USAGE_VL
1649 WHERE APPLICATION_ID=660
1650 AND APPLICATION_COLUMN_NAME=p_appl_column_name
1651 AND DESCRIPTIVE_FLEXFIELD_NAME=p_flexfield_name
1652 AND DESCRIPTIVE_FLEX_CONTEXT_CODE IN ('Global Data Elements',p_context_value);
1653 RETURN p_column_label;
1654 EXCEPTION WHEN NO_DATA_FOUND THEN
1655 BEGIN
1656 SELECT NVL(FORM_LEFT_PROMPT,FORM_ABOVE_PROMPT)
1657 INTO p_column_label
1658 FROM FND_DESCR_FLEX_COL_USAGE_VL
1659 WHERE APPLICATION_ID=660
1660 AND APPLICATION_COLUMN_NAME=p_appl_column_name
1661 AND DESCRIPTIVE_FLEXFIELD_NAME=p_flexfield_name
1662 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_old_context_value;
1663
1664 RETURN p_column_label;
1665 EXCEPTION WHEN OTHERS THEN
1666 OE_DEBUG_PUB.add('Unable to get column label : '||sqlerrm,1);
1667 RETURN NULL;
1668 END;
1669 WHEN OTHERS THEN
1670 OE_DEBUG_PUB.add('Unable to get column label : '||sqlerrm,1);
1671 RETURN NULL;
1672 END Get_Column_Label;
1673
1674 FUNCTION Get_translated_value(p_flexfield_name IN varchar2,
1675 p_appl_column_name IN varchar2,
1676 p_column_value IN varchar2,
1677 p_context_value IN Varchar2 DEFAULT NULL)
1678 RETURN VARCHAR2
1679 IS
1680 p_column_translated_value VARCHAR2(500);
1681 v_context varchar2(30) := NULL;
1682 BEGIN
1683
1684 SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
1685 INTO v_context
1686 FROM FND_DESCR_FLEX_COL_USAGE_VL
1687 WHERE APPLICATION_ID=660
1688 AND APPLICATION_COLUMN_NAME=p_appl_column_name
1689 AND DESCRIPTIVE_FLEXFIELD_NAME=p_flexfield_name
1690 AND DESCRIPTIVE_FLEX_CONTEXT_CODE IN ('Global Data Elements',p_context_value);
1691
1692 p_column_translated_value := OE_AUDIT_HISTORY_PVT.Get_Attribute_value(p_flexfield_name
1693 ,v_context
1694 ,p_appl_column_name
1695 ,p_column_value
1696 ,'=');
1697 IF p_column_translated_value IS NULL THEN
1698 RETURN p_column_value;
1699 ELSE
1700 RETURN p_column_translated_value;
1701 END IF;
1702
1703 EXCEPTION WHEN OTHERS THEN
1704 OE_DEBUG_PUB.add('Error when getting translated value : '||sqlerrm,1);
1705 RETURN p_column_value;
1706 END Get_Translated_Value;
1707
1708 -- ==========================================================================
1709 -- Function value_exists_in_table
1710 -- funtion type Private
1711 -- Returns BOOLEAN
1712 -- out parameters : None
1713 -- DESCRIPTION
1714 -- Searches for value if it exist by building dynamic query stmt when when valueset validation type is F
1715 -- the list populated by get_valueset call.
1716 -- ===========================================================================
1717
1718
1719 FUNCTION value_exists_in_table(p_table_r fnd_vset.table_r,
1720 p_value VARCHAR2,
1721 x_id OUT NOCOPY VARCHAR2,
1722 x_value OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
1723 v_selectstmt VARCHAR2(2000) ; --dhgupta changed length from 500 to 2000 for bug # 1888160
1724 v_cursor_id INTEGER;
1725 v_value VARCHAR2(150);
1726 v_id VARCHAR2(150);
1727 v_retval INTEGER;
1728 v_where_clause fnd_flex_validation_tables.additional_where_clause%type;
1729 v_cols VARCHAR2(1000);
1730 l_order_by VARCHAR2(1000);
1731 l_pos1 number;
1732 l_where_length number;
1733
1734 BEGIN
1735 v_cursor_id := DBMS_SQL.OPEN_CURSOR;
1736
1737 IF instr(UPPER(p_table_r.where_clause), 'ORDER BY') > 0 THEN
1738 l_order_by := substr(p_table_r.where_clause, instr(UPPER(p_table_r.where_clause), 'ORDER BY'));
1739 v_where_clause := replace(p_table_r.where_clause, l_order_by ,'');
1740 ELSE
1741 v_where_clause := p_table_r.where_clause;
1742 END IF;
1743
1744 IF instr(upper(v_where_clause),'WHERE ') > 0 then
1745 v_where_clause:= rtrim(ltrim(v_where_clause));
1746 l_pos1 := instr(upper(v_where_clause),'WHERE');
1747 l_where_length := LENGTHB('WHERE');
1748 v_where_clause:= substr(v_where_clause,l_pos1+l_where_length);
1749
1750 IF (p_table_r.id_column_name IS NOT NULL) THEN
1751 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' AND '||v_where_clause; -- 2492020
1752 ELSE
1753 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' AND '||v_where_clause;--2492020
1754 END IF;
1755 ELSE
1756 IF (p_table_r.id_column_name IS NOT NULL) THEN
1757 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' '||v_where_clause;
1758 ELSE
1759 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' '||v_where_clause;
1760 END IF;
1761 END IF;
1762 IF l_order_by IS NOT NULL THEN
1763 v_where_clause := v_where_clause||' '||l_order_by;
1764 END IF;
1765 v_cols := p_table_r.value_column_name;
1766 IF (p_table_r.id_column_name IS NOT NULL) THEN
1767 IF (p_table_r.id_column_type IN ('D', 'N')) THEN
1768 v_cols := v_cols || ' , To_char(' || p_table_r.id_column_name || ')';
1769 ELSE
1770 v_cols := v_cols || ' , ' || p_table_r.id_column_name;
1771 END IF;
1772 ELSE
1773 v_cols := v_cols || ', NULL ';
1774 END IF;
1775 v_selectstmt := 'SELECT '||v_cols||' FROM '||p_table_r.table_name||' '||v_where_clause;
1776 oe_debug_pub.add('select stmt'||v_selectstmt);
1777 -- parse the query
1778 DBMS_SQL.PARSE(v_cursor_id,v_selectstmt,DBMS_SQL.V7);
1779 -- Bind the input variables
1780 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,1,v_value,150);
1781 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,2,v_id,150);
1782 v_retval := DBMS_SQL.EXECUTE(v_cursor_id);
1783 LOOP
1784 -- Fetch rows in to buffer and check the exit condition from the loop
1785 IF( DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0) THEN
1786 EXIT;
1787 END IF;
1788 -- Retrieve the rows from buffer into PLSQL variables
1789 DBMS_SQL.COLUMN_VALUE(v_cursor_id,1,v_value);
1790 DBMS_SQL.COLUMN_VALUE(v_cursor_id,2,v_id);
1791
1792 IF v_id IS NULL AND (p_value = v_value) THEN
1793 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1794 x_id := v_id;
1795 x_value := v_value;
1796 RETURN TRUE;
1797 ELSIF (p_value = v_id) THEN
1798 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1799 x_id := v_id;
1800 x_value := v_value;
1801 RETURN TRUE;
1802 ELSE
1803 Null;
1804 oe_debug_pub.add('value does notmatch, continue search'||p_value||','||v_id);
1805 END IF;
1806 END LOOP;
1807 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1808 RETURN FALSE;
1809 EXCEPTION
1810 WHEN OTHERS THEN
1811 oe_debug_pub.add('value_exists_in_table exception');
1812 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1813 RETURN FALSE;
1814 END value_exists_in_table;
1815
1816 PROCEDURE Compare_Credit_Card
1817 ( p_attribute_id IN NUMBER
1818 , p_header_id IN NUMBER
1819 , p_old_hist_creation_date IN DATE
1820 , p_new_hist_creation_date IN DATE
1821 , x_old_attribute_value OUT NOCOPY VARCHAR2
1822 , x_new_attribute_value OUT NOCOPY VARCHAR2
1823 , x_card_number_equal OUT NOCOPY VARCHAR2
1824 )
1825 IS
1826
1827 l_old_instrument_id NUMBER;
1828 l_new_instrument_id NUMBER;
1829 l_column_name VARCHAR2(30);
1830 l_sql_stmt VARCHAR2(1000);
1831 l_old_sql_stmt VARCHAR2(1000);
1832 l_new_sql_stmt VARCHAR2(1000);
1833 l_old_exp_date DATE;
1834 l_new_exp_date DATE;
1835 l_old_holder_name VARCHAR2(80);
1836 l_new_holder_name VARCHAR2(80);
1837 l_old_cc_number VARCHAR2(80);
1838 l_new_cc_number VARCHAR2(80);
1839 l_old_cc_code VARCHAR2(80);
1840 l_new_cc_code VARCHAR2(80);
1841
1842 type refcur is ref cursor;
1843 ref_attr REFCUR;
1844 --
1845 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1846 --
1847
1848 BEGIN
1849
1850 IF l_debug_level > 0 THEN
1851 oe_debug_pub.add('Entering Comparing_Credit_Card for attribute_id: '||p_attribute_id, 1);
1852 END IF;
1853
1854 IF l_debug_level > 0 THEN
1855 oe_debug_pub.add('p_old_hist_creation_date is: '||p_old_hist_creation_date, 3);
1856 oe_debug_pub.add('p_new_hist_creation_date is: '||p_new_hist_creation_date, 3);
1857 END IF;
1858
1859
1860 BEGIN
1861 SELECT instrument_id, credit_card_expiration_date,credit_card_number,credit_card_code
1862 INTO l_old_instrument_id,l_old_exp_date,l_old_cc_number, l_old_cc_code
1863 FROM oe_order_header_history
1864 WHERE header_id = p_header_id
1865 AND hist_creation_date = p_old_hist_creation_date;
1866 EXCEPTION WHEN NO_DATA_FOUND THEN
1867 null;
1868 END;
1869
1870
1871 BEGIN
1872 SELECT instrument_id,credit_card_expiration_date, credit_card_number,credit_card_code
1873 INTO l_new_instrument_id,l_new_exp_date,l_new_cc_number, l_new_cc_code
1874 FROM oe_order_header_history
1875 WHERE header_id = p_header_id
1876 AND hist_creation_date = p_new_hist_creation_date;
1877 EXCEPTION WHEN NO_DATA_FOUND THEN
1878 null;
1879 END;
1880
1881
1882 IF p_attribute_id in (46, 49) AND l_old_cc_number IS NULL AND l_old_cc_code IS NULL THEN
1883 BEGIN
1884 SELECT instrid
1885 INTO l_old_instrument_id
1886 FROM iby_creditcard_h
1887 WHERE card_history_change_id = l_old_instrument_id;
1888 EXCEPTION WHEN NO_DATA_FOUND THEN
1889 l_old_instrument_id := null;
1890 END;
1891
1892 END IF;
1893
1894 IF p_attribute_id in (46, 49) AND l_new_cc_number IS NULL AND l_new_cc_code IS NULL THEN
1895
1896 BEGIN
1897 SELECT instrid
1898 INTO l_new_instrument_id
1899 FROM iby_creditcard_h
1900 WHERE card_history_change_id = l_new_instrument_id;
1901 EXCEPTION WHEN NO_DATA_FOUND THEN
1902 l_new_instrument_id := null;
1903 END;
1904
1905 END IF;
1906
1907
1908
1909
1910 -- IF p_attribute_id = 49 AND l_old_exp_date is null and l_new_exp_date is null THEN
1911 -- IF p_attribute_id = 49 AND l_old_cc_number is not null and l_new_cc_number is not null THEN
1912 IF p_attribute_id = 49 THEN
1913 IF l_old_instrument_id IS NOT NULL AND l_new_instrument_id IS NOT NULL THEN
1914 IF OE_GLOBALS.Is_Same_Credit_Card(p_cc_num_old => null
1915 ,p_cc_num_new => null
1916 ,p_instrument_id_old => l_old_instrument_id
1917 ,p_instrument_id_new => l_new_instrument_id)
1918 THEN
1919 x_card_number_equal := 'Y';
1920 ELSE
1921 x_card_number_equal := 'N';
1922 END IF;
1923
1924 ELSIF l_old_instrument_id IS NULL AND l_new_instrument_id IS NULL THEN
1925 x_card_number_equal := 'Y';
1926 ELSE
1927 x_card_number_equal := 'N';
1928 END IF;
1929
1930 END IF;
1931
1932 IF l_debug_level > 0 THEN
1933 oe_debug_pub.add('x_card_number_equal is: '||x_card_number_equal, 3);
1934 END IF;
1935
1936 IF p_attribute_id = 46 THEN
1937 l_column_name := 'CARD_ISSUER_CODE';
1938 ELSIF p_attribute_id = 47 THEN
1939 l_column_name := 'EXPIRYDATE';
1940 ELSIF p_attribute_id = 48 THEN
1941 l_column_name := 'CHNAME';
1942 ELSIF p_attribute_id = 49 THEN
1943 l_column_name := 'MASKED_CC_NUMBER';
1944 END IF;
1945
1946 IF l_debug_level > 0 THEN
1947 oe_debug_pub.add('l_old_cc_code is: '||l_old_cc_code, 3);
1948 oe_debug_pub.add('l_new_cc_code is: '||l_new_cc_code, 3);
1949 oe_debug_pub.add('l_old_cc_number is: '||l_old_cc_number, 3);
1950 oe_debug_pub.add('l_new_cc_number is: '||l_new_cc_number, 3);
1951 END IF;
1952
1953 --get old and new attribute values for credit card number and credit card code.
1954 -- instrument_id stores the instrument_id
1955 IF p_attribute_id = 46 OR p_attribute_id = 49
1956
1957 THEN
1958 l_sql_stmt := 'select '
1959 || l_column_name
1960 || ' from iby_creditcard'
1961 || ' where instrid = :x ';
1962
1963 OPEN ref_attr FOR l_sql_stmt using l_old_instrument_id ;
1964 FETCH ref_attr INTO x_old_attribute_value;
1965 CLOSE ref_attr;
1966
1967 OPEN ref_attr FOR l_sql_stmt using l_new_instrument_id ;
1968 FETCH ref_attr INTO x_new_attribute_value;
1969 CLOSE ref_attr;
1970
1971 -- instrument_id stores the card_history_change_id
1972 ELSIF p_attribute_id in (47, 48) THEN
1973 IF (l_old_cc_number is null and l_old_cc_number is null) THEN
1974 -- AND (l_old_cc_code is null and l_new_cc_code is null)
1975 -- THEN
1976 l_old_sql_stmt := 'select '
1977 || l_column_name
1978 || ' from iby_creditcard_h'
1979 || ' where card_history_change_id = :x ';
1980 ELSE
1981 l_old_sql_stmt := 'select '
1982 || l_column_name
1983 || ' from iby_creditcard'
1984 || ' where instrid = :x ';
1985
1986 END IF;
1987
1988 IF (l_new_cc_code is null and l_new_cc_code is null) THEN
1989 l_new_sql_stmt := 'select '
1990 || l_column_name
1991 || ' from iby_creditcard_h'
1992 || ' where card_history_change_id = :x ';
1993 ELSE
1994 l_new_sql_stmt := 'select '
1995 || l_column_name
1996 || ' from iby_creditcard'
1997 || ' where instrid = :x ';
1998
1999 END IF;
2000
2001
2002 OPEN ref_attr FOR l_old_sql_stmt using l_old_instrument_id ;
2003 FETCH ref_attr INTO x_old_attribute_value;
2004 CLOSE ref_attr;
2005
2006 OPEN ref_attr FOR l_new_sql_stmt using l_new_instrument_id ;
2007 FETCH ref_attr INTO x_new_attribute_value;
2008 CLOSE ref_attr;
2009
2010 END IF;
2011
2012 IF l_debug_level > 0 THEN
2013 oe_debug_pub.add('x_old_attribute_value is: '||x_old_attribute_value, 3);
2014 oe_debug_pub.add('x_new_attribute_value is: '||x_new_attribute_value, 3);
2015 END IF;
2016
2017 IF l_debug_level > 0 THEN
2018 oe_debug_pub.add('Exiting Comparing_Credit_Card. ', 1);
2019 END IF;
2020
2021 END Compare_Credit_Card;
2022
2023 FUNCTION Get_Card_Attribute_Value
2024 ( p_instr_flag IN VARCHAR2
2025 , p_attribute_id IN NUMBER
2026 , p_instrument_id IN NUMBER)
2027 RETURN VARCHAR2 IS
2028
2029 l_card_attribute_value VARCHAR2(80);
2030 l_column_name VARCHAR2(30);
2031 l_sql_stmt VARCHAR2(1000);
2032 type refcur is ref cursor;
2033 ref_attr REFCUR;
2034
2035 BEGIN
2036
2037 IF p_attribute_id = 46 THEN
2038 l_column_name := 'CARD_ISSUER_CODE';
2039 ELSIF p_attribute_id = 47 THEN
2040 l_column_name := 'EXPIRYDATE';
2041 ELSIF p_attribute_id = 48 THEN
2042 l_column_name := 'CHNAME';
2043 ELSIF p_attribute_id = 49 THEN
2044 l_column_name := 'MASKED_CC_NUMBER';
2045 END IF;
2046
2047 IF p_instr_flag = 'N' THEN
2048 l_sql_stmt := 'select '
2049 || l_column_name
2050 || ' from iby_creditcard_h'
2051 || ' where card_history_change_id = :x ';
2052
2053 ELSE
2054 l_sql_stmt := 'select '
2055 || l_column_name
2056 || ' from iby_creditcard'
2057 || ' where instrid = :x ';
2058 END IF;
2059
2060 OPEN ref_attr FOR l_sql_stmt using p_instrument_id ;
2061 FETCH ref_attr INTO l_card_attribute_value;
2062 CLOSE ref_attr;
2063
2064 RETURN l_card_attribute_value;
2065
2066 EXCEPTION WHEN OTHERS THEN
2067 RETURN null;
2068
2069 END Get_Card_Attribute_Value;
2070
2071 --Added for bug5631508
2072 PROCEDURE RECORD_SET_HISTORY(
2073 p_header_id IN number ,
2074 p_line_id IN number,
2075 p_set_id IN number,
2076 x_return_status OUT NOCOPY varchar2 ) is
2077
2078 l_set_name varchar2(30);
2079 l_set_type varchar2(30);
2080 begin
2081 select set_name,set_type into l_set_name,l_set_type
2082 from oe_sets
2083 where set_id=p_set_id;
2084
2085 insert into OE_SETS_HISTORY
2086 (set_id,
2087 set_name,
2088 set_type,
2089 line_id,
2090 header_id,
2091 last_update_date,
2092 last_updated_by,
2093 creation_date,
2094 created_by)
2095 values(
2096 p_set_id,
2097 l_set_name,
2098 l_set_type,
2099 p_line_id,
2100 p_header_id,
2101 sysdate,
2102 FND_GLOBAL.USER_ID,
2103 sysdate,
2104 FND_GLOBAL.USER_ID );
2105
2106 x_return_status := FND_API.G_RET_STS_SUCCESS;
2107
2108 EXCEPTION
2109 WHEN OTHERS THEN
2110 x_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
2111 oe_debug_pub.add('Error in inserting data in sets history');
2112 end;
2113
2114 --Added for bug5631508
2115 PROCEDURE DELETE_SET_HISTORY(
2116 p_line_id IN number,
2117 x_return_status OUT NOCOPY varchar2 ) is
2118 begin
2119 delete from OE_SETS_HISTORY
2120 where line_id=p_line_id;
2121
2122 x_return_status := FND_API.G_RET_STS_SUCCESS;
2123 exception
2124 WHEN OTHERS THEN
2125 oe_debug_pub.add('Error in Deleting data from sets history');
2126 x_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
2127 end;
2128
2129
2130 END OE_AUDIT_HISTORY_PVT;
[Home] [Help]