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