[Home] [Help]
PACKAGE BODY: APPS.JTF_IH_MERGE_PKG
Source
1 PACKAGE BODY JTF_IH_MERGE_PKG AS
2 /* $Header: JTFIHAMB.pls 115.13 2003/02/20 15:24:04 ialeshin ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_IH_MERGE_PKG';
4 PROCEDURE MERGE(req_id IN NUMBER,
5 set_number IN NUMBER,
6 process_mode IN VARCHAR2) IS
7
8 error_message varchar2(255);
9 no_of_rows NUMBER;
10 BEGIN
11 -- insert into testmerge(a1, a2) values (50,1);
12 JTF_IH_MERGE(req_id, set_number, process_mode);
13
14 error_message := ' Ending process for Interaction Activity Customer Account Merge ';
15 arp_message.set_line(error_message);
16 END MERGE;
17
18 PROCEDURE JTF_IH_MERGE (
19 req_id NUMBER,
20 set_num NUMBER,
21 process_mode VARCHAR2) IS
22
23 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
24 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
25 INDEX BY BINARY_INTEGER;
26 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
27
28 TYPE ACTIVITY_ID_LIST_TYPE IS TABLE OF
29 JTF_IH_ACTIVITIES.ACTIVITY_ID%TYPE
30 INDEX BY BINARY_INTEGER;
31 PRIMARY_KEY_ID_LIST ACTIVITY_ID_LIST_TYPE;
32
33 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
34 JTF_IH_ACTIVITIES.CUST_ACCOUNT_ID%TYPE
35 INDEX BY BINARY_INTEGER;
36 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
37 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
38
39 l_profile_val VARCHAR2(30);
40 CURSOR merged_records IS
41 SELECT distinct CUSTOMER_MERGE_HEADER_ID
42 ,ACTIVITY_ID
43 ,CUST_ACCOUNT_ID
44 FROM JTF_IH_ACTIVITIES yt, ra_customer_merges m
45 WHERE (
46 yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
47 ) AND m.process_flag = 'N'
48 AND m.request_id = req_id
49 AND m.set_number = set_num;
50 l_last_fetch BOOLEAN := FALSE;
51 l_count NUMBER;
52 BEGIN
53 IF process_mode='LOCK' THEN
54 NULL;
55 ELSE
56 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
57 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','JTF_IH_ACTIVITIES',FALSE);
58 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
59
60 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
61
62 open merged_records;
63 LOOP
64 FETCH merged_records BULK COLLECT INTO
65 MERGE_HEADER_ID_LIST
66 , PRIMARY_KEY_ID_LIST
67 , NUM_COL1_ORIG_LIST
68 limit 1000;
69
70 IF merged_records%NOTFOUND THEN
71 l_last_fetch := TRUE;
72 END IF;
73 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
74 exit;
75 END IF;
76 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
77 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
78 END LOOP;
79
80
81 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
82 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
83 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
84 MERGE_LOG_ID,
85 TABLE_NAME,
86 MERGE_HEADER_ID,
87 PRIMARY_KEY_ID,
88 NUM_COL1_ORIG,
89 NUM_COL1_NEW,
90 ACTION_FLAG,
91 REQUEST_ID,
92 CREATED_BY,
93 CREATION_DATE,
94 LAST_UPDATE_LOGIN,
95 LAST_UPDATE_DATE,
96 LAST_UPDATED_BY
97 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
98 'JTF_IH_ACTIVITIES',
99 MERGE_HEADER_ID_LIST(I),
100 PRIMARY_KEY_ID_LIST(I),
101 NUM_COL1_ORIG_LIST(I),
102 NUM_COL1_NEW_LIST(I),
103 'U',
104 req_id,
105 hz_utility_pub.CREATED_BY,
106 hz_utility_pub.CREATION_DATE,
107 hz_utility_pub.LAST_UPDATE_LOGIN,
108 hz_utility_pub.LAST_UPDATE_DATE,
109 hz_utility_pub.LAST_UPDATED_BY
110 );
111
112 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
113 UPDATE JTF_IH_ACTIVITIES yt SET
114 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
115 , LAST_UPDATE_DATE=SYSDATE
116 , last_updated_by=arp_standard.profile.user_id
117 , last_update_login=arp_standard.profile.last_update_login
118 , REQUEST_ID=req_id
119 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
120 , PROGRAM_ID=arp_standard.profile.program_id
121 , PROGRAM_UPDATE_DATE=SYSDATE
122 WHERE ACTIVITY_ID=PRIMARY_KEY_ID_LIST(I)
123 ;
124 l_count := l_count + SQL%ROWCOUNT;
125 IF l_last_fetch THEN
126 EXIT;
127 END IF;
128 END LOOP;
129
130 arp_message.set_name('AR','AR_ROWS_UPDATED');
131 arp_message.set_token('NUM_ROWS',to_char(l_count));
132 END IF;
133 EXCEPTION
134 WHEN OTHERS THEN
135 arp_message.set_line( 'JTF_IH_MERGE');
136 RAISE;
137 END JTF_IH_MERGE;
138
139 END JTF_IH_MERGE_PKG;