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