[Home] [Help]
PACKAGE BODY: APPS.OEP_CMERGE_OEHLD
Source
1 PACKAGE BODY OEP_CMERGE_OEHLD AS
2 /* $Header: oehldpb.pls 115.1 99/07/16 08:26:03 porting shi $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7
8 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
9
10 PROCEDURE oe_hs (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
11
12 CURSOR c1 is
13 select hold_source_id
14 from so_hold_sources
15 where hold_entity_id in (select m.duplicate_site_id
16 from ra_customer_merges m
17 where m.process_flag = 'N'
18 and m.request_id = req_id
19 and m.set_number = set_num)
20 and hold_entity_code = 'STS'
21 for update nowait;
22
23 CURSOR c2 is
24 select hold_source_id
25 from so_hold_sources
26 where hold_entity_id in (select m.duplicate_site_id
27 from ra_customer_merges m
28 where m.process_flag = 'N'
29 and m.request_id = req_id
30 and m.set_number = set_num)
31 and hold_entity_code = 'ITS'
32 for update nowait;
33
34 CURSOR c3 is
35 select hold_source_id
36 from so_hold_sources
37 where hold_entity_id in (select m.duplicate_site_id
38 from ra_customer_merges m
39 where m.process_flag = 'N'
40 and m.request_id = req_id
41 and m.set_number = set_num)
42 and hold_entity_code = 'S'
43 for update nowait;
44
45 CURSOR c4 is
46 select hold_source_id
47 from so_hold_sources
48 where hold_entity_id in (select m.duplicate_id
49 from ra_customer_merges m
50 where m.process_flag = 'N'
51 and m.request_id = req_id
52 and m.set_number = set_num)
53 and hold_entity_code = 'C'
54 for update nowait;
55
56 BEGIN
57
58 arp_message.set_line( 'OEP_CMERGE_OEHLD.OE_HS()+' );
59
60 /*-----------------------------+
61 | SO_HOLD_SOURCES |
62 +-----------------------------*/
63 /* both customer and site level */
64
65 IF( process_mode = 'LOCK' ) THEN
66
67 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
68 arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
69
70 open c1;
71 close c1;
72
73 open c2;
74 close c2;
75
76 open c3;
77 close c3;
78
79 open c4;
80 close c4;
81
82 ELSE
83
84
85 /* site level update */
86 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
87 arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
88
89 UPDATE SO_HOLD_SOURCES a
90 set (hold_entity_id) = (select distinct m.customer_site_id
91 from ra_customer_merges m
92 where a.hold_entity_id =
93 m.duplicate_site_id
94 and m.request_id = req_id
95 and m.process_flag = 'N'
96 and m.set_number = set_num),
97 last_update_date = sysdate,
98 last_updated_by = arp_standard.profile.user_id,
99 last_update_login = arp_standard.profile.last_update_login
100 where hold_entity_id in (select m.duplicate_site_id
101 from ra_customer_merges m
102 where m.process_flag = 'N'
103 and m.request_id = req_id
104 and m.set_number = set_num)
105 and hold_entity_code = 'STS';
106
107 g_count := sql%rowcount;
108
109 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
110 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
111
112
113 /* site level update */
114 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
115 arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
116
117 UPDATE SO_HOLD_SOURCES a
118 set (hold_entity_id) = (select distinct m.customer_site_id
119 from ra_customer_merges m
120 where a.hold_entity_id =
121 m.duplicate_site_id
122 and m.request_id = req_id
123 and m.process_flag = 'N'
124 and m.set_number = set_num),
125 last_update_date = sysdate,
126 last_updated_by = arp_standard.profile.user_id,
127 last_update_login = arp_standard.profile.last_update_login
128 where hold_entity_id in (select m.duplicate_site_id
129 from ra_customer_merges m
130 where m.process_flag = 'N'
131 and m.request_id = req_id
132 and m.set_number = set_num)
133 and hold_entity_code = 'ITS';
134
135 g_count := sql%rowcount;
136
137 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
138 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
139
140
141 /* site level update */
142 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
143 arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
144
145 UPDATE SO_HOLD_SOURCES a
146 set (hold_entity_id) = (select distinct m.customer_site_id
147 from ra_customer_merges m
148 where a.hold_entity_id =
149 m.duplicate_site_id
150 and m.request_id = req_id
151 and m.process_flag = 'N'
152 and m.set_number = set_num),
153 last_update_date = sysdate,
154 last_updated_by = arp_standard.profile.user_id,
155 last_update_login = arp_standard.profile.last_update_login
156 where hold_entity_id in (select m.duplicate_site_id
157 from ra_customer_merges m
158 where m.process_flag = 'N'
159 and m.request_id = req_id
160 and m.set_number = set_num)
161 and hold_entity_code = 'S';
162
163 g_count := sql%rowcount;
164
165 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
166 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
167
168
169 /* customer level update */
170 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
171 arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
172
173 UPDATE SO_HOLD_SOURCES a
174 set hold_entity_id = (select distinct m.customer_id
175 from ra_customer_merges m
176 where a.hold_entity_id =
177 m.duplicate_id
178 and m.process_flag = 'N'
179 and m.request_id = req_id
180 and m.set_number = set_num),
181 last_update_date = sysdate,
182 last_updated_by = arp_standard.profile.user_id,
183 last_update_login = arp_standard.profile.last_update_login
184 where hold_entity_id in (select m.duplicate_id
185 from ra_customer_merges m
186 where m.process_flag = 'N'
187 and m.request_id = req_id
188 and m.set_number = set_num)
189 and hold_entity_code = 'C';
190
191 g_count := sql%rowcount;
192
193 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
194 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
195
196 END IF;
197
198 arp_message.set_line( 'OEP_CMERGE_OEHLD.OE_HS()-' );
199
200
201 EXCEPTION
202 when others then
203 arp_message.set_error( 'OEP_CMERGE_OEHLD.OE_HS');
204 raise;
205
206 END;
207
208
209
210 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
211
212
213 PROCEDURE MERGE (REQ_ID NUMBER, SET_NUM NUMBER, PROCESS_MODE VARCHAR2) IS
214 BEGIN
215
216 arp_message.set_line( 'OEP_CMERGE_OEHLD.MERGE()+' );
217
218 oe_hs( req_id, set_num, process_mode );
219
220 arp_message.set_line( 'OEP_CMERGE_OEHLD.MERGE()-' );
221
222 EXCEPTION
223 when others then
224 raise;
225
226 END MERGE;
227 END OEP_CMERGE_OEHLD;