[Home] [Help]
PACKAGE BODY: APPS.INV_CMERGE_ITEMS
Source
1 PACKAGE BODY INV_CMERGE_ITEMS as
2 /* $Header: invcmib.pls 120.1 2006/02/22 03:44:24 swshukla noship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
8
9 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
10
11 PROCEDURE MERGE (
12 req_id NUMBER,
13 set_num NUMBER,
14 process_mode VARCHAR2) IS
15
16 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
17 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
18 INDEX BY BINARY_INTEGER;
19 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
20
21 TYPE CUSTOMER_ITEM_ID_LIST_TYPE IS TABLE OF
22 MTL_CUSTOMER_ITEMS.CUSTOMER_ITEM_ID%TYPE
23 INDEX BY BINARY_INTEGER;
24 PRIMARY_KEY_ID_LIST CUSTOMER_ITEM_ID_LIST_TYPE;
25
26 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
27 MTL_CUSTOMER_ITEMS.CUSTOMER_ID%TYPE
28 INDEX BY BINARY_INTEGER;
29 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
30 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
31
32 TYPE ADDRESS_ID_LIST_TYPE IS TABLE OF
33 MTL_CUSTOMER_ITEMS.ADDRESS_ID%TYPE
34 INDEX BY BINARY_INTEGER;
35 NUM_COL2_ORIG_LIST ADDRESS_ID_LIST_TYPE;
36 NUM_COL2_NEW_LIST ADDRESS_ID_LIST_TYPE;
37
38 -- Bug 4135064.
39 TYPE CUSTOMER_ITEM_NUMBER_LIST_TYPE IS TABLE OF
40 MTL_CUSTOMER_ITEMS.CUSTOMER_ITEM_NUMBER%TYPE
41 INDEX BY BINARY_INTEGER;
42 CUST_ITEM_NUM_LIST CUSTOMER_ITEM_NUMBER_LIST_TYPE;
43
44 TYPE CUST_CATEGORY_CODE_LIST_TYPE IS TABLE OF
45 MTL_CUSTOMER_ITEMS.CUSTOMER_CATEGORY_CODE%TYPE
46 INDEX BY BINARY_INTEGER;
47 CUST_CATEGORY_CODE_LIST CUST_CATEGORY_CODE_LIST_TYPE;
48
49 TYPE ITEM_DEF_LEVEL_LIST_TYPE IS TABLE OF
50 MTL_CUSTOMER_ITEMS.ITEM_DEFINITION_LEVEL%TYPE
51 INDEX BY BINARY_INTEGER;
52 ITEM_DEF_LEVEL_LIST ITEM_DEF_LEVEL_LIST_TYPE;
53 -- Bug 4135064
54
55 l_profile_val VARCHAR2(30);
56 -- Bug 4135064. Added additional columns in the select statement.
57 CURSOR merged_records IS
58 SELECT distinct CUSTOMER_MERGE_HEADER_ID
59 ,CUSTOMER_ITEM_ID
60 --Selecting the customer_id,address_id according to functionality
61 ,decode(yt.CUSTOMER_ID,m.DUPLICATE_ID,m.CUSTOMER_ID,yt.CUSTOMER_ID)
62 ,decode(yt.ADDRESS_ID,m.DUPLICATE_ADDRESS_ID,
63 decode(yt.item_definition_level,3,m.CUSTOMER_ADDRESS_ID,yt.ADDRESS_ID),yt.ADDRESS_ID)
64 , customer_item_number
65 , customer_category_code
66 , item_definition_level
67 FROM MTL_CUSTOMER_ITEMS yt, ra_customer_merges m
68 WHERE (
69 yt.CUSTOMER_ID = m.DUPLICATE_ID
70 OR yt.ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
71 ) AND m.process_flag = 'N'
72 AND m.request_id = req_id
73 AND m.set_number = set_num;
74 l_last_fetch BOOLEAN := FALSE;
75 l_count NUMBER;
76 BEGIN
77
78 arp_message.set_line( 'INV_CMERGE_ITEMS.MERGE()+' );
79 /*-----------------------+
80 | MTL_CUSTOMER_ITEMS |
81 +-----------------------*/
82 /* try to lock the table first */
83
84 IF process_mode='LOCK' THEN
85 NULL;
86 ELSE
87 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
88 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','MTL_CUSTOMER_ITEMS',FALSE);
89 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
90 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
91
92 open merged_records;
93 LOOP
94 FETCH merged_records BULK COLLECT INTO
95 MERGE_HEADER_ID_LIST
96 , PRIMARY_KEY_ID_LIST
97 , NUM_COL1_ORIG_LIST
98 , NUM_COL2_ORIG_LIST
99 , CUST_ITEM_NUM_LIST
100 , CUST_CATEGORY_CODE_LIST
101 , ITEM_DEF_LEVEL_LIST
102 ;
103 IF merged_records%NOTFOUND THEN
104 l_last_fetch := TRUE;
105 END IF;
106 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
107 exit;
108 END IF;
109 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
110 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
111 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
112
113 END LOOP;
114 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
115 /* inserting in log table */
116 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
117 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
118 MERGE_LOG_ID,
119 TABLE_NAME,
120 MERGE_HEADER_ID,
121 PRIMARY_KEY_ID,
122 NUM_COL1_ORIG,
123 NUM_COL1_NEW,
124 NUM_COL2_ORIG,
125 NUM_COL2_NEW,
126 ACTION_FLAG,
127 REQUEST_ID,
128 CREATED_BY,
129 CREATION_DATE,
130 LAST_UPDATE_LOGIN,
131 LAST_UPDATE_DATE,
132 LAST_UPDATED_BY
133 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
134 'MTL_CUSTOMER_ITEMS',
135 MERGE_HEADER_ID_LIST(I),
136 PRIMARY_KEY_ID_LIST(I),
137 NUM_COL1_ORIG_LIST(I),
138 NUM_COL1_NEW_LIST(I),
139 NUM_COL2_ORIG_LIST(I),
140 NUM_COL2_NEW_LIST(I),
141 'U',
142 req_id,
143 hz_utility_pub.CREATED_BY,
144 hz_utility_pub.CREATION_DATE,
145 hz_utility_pub.LAST_UPDATE_LOGIN,
146 hz_utility_pub.LAST_UPDATE_DATE,
147 hz_utility_pub.LAST_UPDATED_BY
148 );
149
150 END IF;
151
152 -- Bug 4135064. Delete the records which on update will result in unique
153 -- constraint violation error in mtl_customer_items_U1.
154 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
155 DELETE FROM MTL_CUSTOMER_ITEMS mci
156 WHERE CUSTOMER_ITEM_ID=PRIMARY_KEY_ID_LIST(I)
157 AND EXISTS (SELECT 1 FROM MTL_CUSTOMER_ITEMS yt
158 WHERE yt.CUSTOMER_ID = NUM_COL1_NEW_LIST(I)
159 AND NVL(yt.ADDRESS_ID, -999) = NVL(NUM_COL2_NEW_LIST(I), -999)
160 AND yt.CUSTOMER_ITEM_NUMBER = CUST_ITEM_NUM_LIST(I)
161 AND NVL(yt.CUSTOMER_CATEGORY_CODE, '@@@') = NVL(CUST_CATEGORY_CODE_LIST(I), '@@@')
162 AND yt.ITEM_DEFINITION_LEVEL = ITEM_DEF_LEVEL_LIST(I)
163 AND yt.rowid <> mci.rowid --Bug: 5054179 Added this clause based on rowids
164 );
165
166 /* customer level update */
167 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
168 UPDATE MTL_CUSTOMER_ITEMS yt SET
169 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
170 ,ADDRESS_ID=NUM_COL2_NEW_LIST(I)
171 , LAST_UPDATE_DATE=SYSDATE
172 , last_updated_by=arp_standard.profile.user_id
173 , last_update_login=arp_standard.profile.last_update_login
174 , REQUEST_ID=req_id
175 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
176 , PROGRAM_ID=arp_standard.profile.program_id
177 , PROGRAM_UPDATE_DATE=SYSDATE
178 WHERE CUSTOMER_ITEM_ID=PRIMARY_KEY_ID_LIST(I)
179 ;
180 l_count := l_count + SQL%ROWCOUNT;
181 IF l_last_fetch THEN
182 EXIT;
183 END IF;
184 END LOOP;
185 /* Number of rows updates */
186 arp_message.set_line( 'INV_CMERGE_ITEMS.MERGE()-' );
187 arp_message.set_name('AR','AR_ROWS_UPDATED');
188 arp_message.set_token('NUM_ROWS',to_char(l_count));
189 END IF;
190 EXCEPTION
191 WHEN OTHERS THEN
192 arp_message.set_line( 'INV_CMERGE_ITEMS.MERGE');
193 arp_message.set_line( substrb(SQLERRM,1,200) );
194 RAISE;
195 END MERGE;
196
197 end INV_CMERGE_ITEMS;