[Home] [Help]
PACKAGE BODY: APPS.ARP_CMERGE_ARDUN
Source
1 PACKAGE BODY ARP_CMERGE_ARDUN as
2 /* $Header: ARPLDUNB.pls 120.4 2005/10/30 04:24:36 appldev ship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7
8 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
9
10
11 PROCEDURE ar_corres (
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 correspondence_id_LIST_TYPE IS TABLE OF
22 AR_CORRESPONDENCES.correspondence_id%TYPE
23 INDEX BY BINARY_INTEGER;
24 PRIMARY_KEY_ID_LIST correspondence_id_LIST_TYPE;
25
26 TYPE customer_id_LIST_TYPE IS TABLE OF
27 AR_CORRESPONDENCES.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 site_use_id_LIST_TYPE IS TABLE OF
33 AR_CORRESPONDENCES.site_use_id%TYPE
34 INDEX BY BINARY_INTEGER;
35 NUM_COL2_ORIG_LIST site_use_id_LIST_TYPE;
36 NUM_COL2_NEW_LIST site_use_id_LIST_TYPE;
37
38 l_profile_val VARCHAR2(30);
39 CURSOR merged_records IS
40 SELECT distinct CUSTOMER_MERGE_HEADER_ID
41 ,correspondence_id
42 ,yt.customer_id
43 ,site_use_id
44 FROM AR_CORRESPONDENCES yt, ra_customer_merges m
45 WHERE ( yt.customer_id = m.DUPLICATE_ID AND
46 yt.site_use_id = m.DUPLICATE_SITE_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','AR_CORRESPONDENCES',FALSE);
58 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
59 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
60
61 open merged_records;
62 LOOP
63 FETCH merged_records BULK COLLECT INTO
64 MERGE_HEADER_ID_LIST
65 , PRIMARY_KEY_ID_LIST
66 , NUM_COL1_ORIG_LIST
67 , NUM_COL2_ORIG_LIST
68 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
69 IF merged_records%NOTFOUND THEN
70 l_last_fetch := TRUE;
71 END IF;
72 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
73 exit;
74 END IF;
75 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
76 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
77 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
78 END LOOP;
79 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
80 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
81 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
82 MERGE_LOG_ID,
83 TABLE_NAME,
84 MERGE_HEADER_ID,
85 PRIMARY_KEY_ID,
86 NUM_COL1_ORIG,
87 NUM_COL1_NEW,
88 NUM_COL2_ORIG,
89 NUM_COL2_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 'AR_CORRESPONDENCES',
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 NUM_COL2_ORIG_LIST(I),
104 NUM_COL2_NEW_LIST(I),
105 'U',
106 req_id,
107 hz_utility_pub.CREATED_BY,
108 hz_utility_pub.CREATION_DATE,
109 hz_utility_pub.LAST_UPDATE_LOGIN,
110 hz_utility_pub.LAST_UPDATE_DATE,
111 hz_utility_pub.LAST_UPDATED_BY
112 );
113
114 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
115 UPDATE AR_CORRESPONDENCES yt SET
116 customer_id=NUM_COL1_NEW_LIST(I)
117 ,site_use_id=NUM_COL2_NEW_LIST(I)
118 , LAST_UPDATE_DATE=SYSDATE
119 , last_updated_by=arp_standard.profile.user_id
120 , last_update_login=arp_standard.profile.last_update_login
121 , REQUEST_ID=req_id
122 WHERE correspondence_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( 'ar_corres');
136 RAISE;
137 END ar_corres;
138
139
140 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
141 PROCEDURE merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
142 BEGIN
143
144 arp_message.set_line( 'ARP_CMERGE_ARDUN.MERGE()+' );
145
146 AR_CORRES(req_id, set_num, process_mode );
147
148 arp_message.set_line( 'ARP_CMERGE_ARDUN.MERGE()-' );
149
150 END merge;
151
152 end ARP_CMERGE_ARDUN;