[Home] [Help]
PACKAGE BODY: APPS.INVP_CMERGE_SPDM
Source
1 PACKAGE BODY INVP_CMERGE_SPDM as
2 /* $Header: invcmspb.pls 120.1 2005/07/01 13:55:46 appldev ship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
8
9 /*--------------------------- MTL_DEMAND ------------------------------------*/
10
11 procedure INV_MD (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
12
13 --Split the cursor C1 into three cursors C1, C1 and C3 for BUG # 1084777
14
15 CURSOR C1 IS
16 SELECT NULL
17 FROM MTL_DEMAND
18 WHERE bill_to_site_use_id in (select racm.duplicate_site_id
19 from ra_customer_merges racm
20 where racm.process_flag = 'N'
21 and racm.request_id = req_id
22 and racm.set_number = set_num)
23 FOR UPDATE NOWAIT;
24
25 CURSOR C2 IS
26 SELECT NULL
27 FROM MTL_DEMAND
28 WHERE ship_to_site_use_id in (select racm.duplicate_site_id
29 from ra_customer_merges racm
30 where racm.process_flag = 'N'
31 and racm.request_id = req_id
32 and racm.set_number = set_num)
33 FOR UPDATE NOWAIT;
34
35 CURSOR C3 IS
36 SELECT NULL
37 FROM MTL_DEMAND
38 WHERE customer_id in (select racm.duplicate_id
39 from ra_customer_merges racm
40 where racm.process_flag = 'N'
41 and racm.request_id = req_id
42 and racm.set_number = set_num)
43 and ship_to_site_use_id is NULL
44 and bill_to_site_use_id is NULL
45 FOR UPDATE NOWAIT;
46
47
48 BEGIN
49 arp_message.set_line( 'INVP_CMERGE_SPDM.INV_MD()+' );
50
51 /*------------+
52 | MTL_DEMAND |
53 +------------*/
54 /* try to lock the table first */
55 IF (process_mode = 'LOCK') then
56 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
57 arp_message.set_token( 'TABLE_NAME', 'MTL_DEMAND', FALSE );
58
59 OPEN C1;
60 CLOSE C1;
61
62 OPEN C2;
63 CLOSE C2;
64
65 OPEN C3;
66 CLOSE C3;
67
68 ELSE
69
70 /* customer and site level */
71
72 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
73 arp_message.set_token('TABLE_NAME', 'MTL_DEMAND',FALSE);
74 arp_message.set_line('site level update : ship to and bill to site use id');
75
76 UPDATE MTL_DEMAND yt
77 set customer_id = (select distinct racm.customer_id
78 from ra_customer_merges racm
79 where yt.customer_id = racm.duplicate_id
80 and (yt.ship_to_site_use_id = racm.duplicate_site_id
81 or yt.bill_to_site_use_id = racm.duplicate_site_id)
82 and racm.process_flag= 'N'
83 and racm.request_id = req_id
84 and racm.set_number = set_num),
85 ship_to_site_use_id = (select distinct racm.customer_site_id
86 from ra_customer_merges racm
87 where yt.customer_id = racm.duplicate_id
88 and yt.ship_to_site_use_id = racm.duplicate_site_id
89 and racm.process_flag = 'N'
90 and racm.request_id = req_id
91 and racm.set_number = set_num),
92 bill_to_site_use_id = (select distinct racm.customer_site_id
93 from ra_customer_merges racm
94 where yt.customer_id = racm.duplicate_id
95 and yt.bill_to_site_use_id = racm.duplicate_site_id
96 and racm.process_flag = 'N'
97 and racm.request_id = req_id
98 and racm.set_number = set_num),
99 last_update_date = sysdate,
100 last_updated_by = arp_standard.profile.user_id,
101 last_update_login = arp_standard.profile.last_update_login,
102 request_id = req_id,
103 program_application_id = arp_standard.profile.program_application_id,
104 program_id = arp_standard.profile.program_id,
105 program_update_date = sysdate
106 where ship_to_site_use_id in (select racm.duplicate_site_id
107 from ra_customer_merges racm
108 where racm.process_flag = 'N'
109 and racm.request_id = req_id
110 and racm.set_number = set_num)
111 and bill_to_site_use_id in (select racm.duplicate_site_id
112 from ra_customer_merges racm
113 where racm.process_flag = 'N'
114 and racm.request_id = req_id
115 and racm.set_number = set_num);
116
117 g_count := sql%rowcount;
118
119 /* Number of rows updates */
120 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
121 arp_message.set_token('NUM_ROWS', to_char(g_count));
122
123
124 /* site level update */
125 /* for bill to site use id */
126 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
127 arp_message.set_token('TABLE_NAME', 'MTL_DEMAND',FALSE);
128 arp_message.set_line('site level update : bill to site use id');
129
130 UPDATE MTL_DEMAND yt
131 set (customer_id,
132 bill_to_site_use_id) = (select distinct racm.customer_id,
133 racm.customer_site_id
134 from ra_customer_merges racm
135 where yt.customer_id = racm.duplicate_id
136 and yt.bill_to_site_use_id = racm.duplicate_site_id
137 and racm.process_flag = 'N'
138 and racm.request_id = req_id
139 and racm.set_number = set_num),
140 last_update_date = sysdate,
141 last_updated_by = arp_standard.profile.user_id,
142 last_update_login = arp_standard.profile.last_update_login,
143 request_id = req_id,
144 program_application_id =arp_standard.profile.program_application_id,
145 program_id = arp_standard.profile.program_id,
146 program_update_date = sysdate
147 where bill_to_site_use_id in (select racm.duplicate_site_id
148 from ra_customer_merges racm
149 where racm.process_flag = 'N'
150 and racm.request_id = req_id
151 and racm.set_number = set_num)
152 and (ship_to_site_use_id is NULL
153 or ship_to_site_use_id not in (select racm.duplicate_site_id
154 from ra_customer_merges racm
155 where racm.process_flag = 'N'
156 and racm.request_id = req_id
157 and racm.set_number = set_num));
158
159 g_count := sql%rowcount;
160
161 /* Number of rows updates */
162 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
163 arp_message.set_token('NUM_ROWS', to_char(g_count));
164
165
166 /* site level update */
167 /* for ship to site use id */
168
169 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
170 arp_message.set_token('TABLE_NAME', 'MTL_DEMAND',FALSE);
171 arp_message.set_line('site level update : ship to site use id');
172
173 UPDATE MTL_DEMAND yt
174 set (customer_id,
175 ship_to_site_use_id) = (select distinct racm.customer_id,
176 racm.customer_site_id
177 from ra_customer_merges racm
178 where yt.customer_id = racm.duplicate_id
179 and yt.ship_to_site_use_id = racm.duplicate_site_id
180 and racm.process_flag = 'N'
181 and racm.request_id = req_id
182 and racm.set_number = set_num),
183 last_update_date = sysdate,
184 last_updated_by = arp_standard.profile.user_id,
185 last_update_login = arp_standard.profile.last_update_login,
186 request_id = req_id,
187 program_application_id =arp_standard.profile.program_application_id,
188 program_id = arp_standard.profile.program_id,
189 program_update_date = sysdate
190 where ship_to_site_use_id in (select racm.duplicate_site_id
191 from ra_customer_merges racm
192 where racm.process_flag = 'N'
193 and racm.request_id = req_id
194 and racm.set_number = set_num)
195 and (bill_to_site_use_id is NULL
196 or bill_to_site_use_id not in (select racm.duplicate_site_id
197 from ra_customer_merges racm
198 where racm.process_flag = 'N'
199 and racm.request_id = req_id
200 and racm.set_number = set_num));
201
202 g_count := sql%rowcount;
203
204 /* Number of rows updates */
205 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
206 arp_message.set_token('NUM_ROWS', to_char(g_count));
207
208 /* customer level update */
209
210 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
211 arp_message.set_token('TABLE_NAME', 'MTL_DEMAND', FALSE);
212 arp_message.set_line('customer level : customer_id');
213
214 UPDATE MTL_DEMAND yt
215 set customer_id = (select distinct racm.customer_id
216 from ra_customer_merges racm
217 where yt.customer_id =
218 racm.duplicate_id
219 and racm.process_flag = 'N'
220 and racm.request_id = req_id
221 and racm.set_number = set_num),
222 last_update_date = sysdate,
223 last_updated_by = arp_standard.profile.user_id,
224 last_update_login = arp_standard.profile.last_update_login,
225 request_id = req_id,
226 program_application_id =arp_standard.profile.program_application_id,
227 program_id = arp_standard.profile.program_id,
228 program_update_date = sysdate
229 where customer_id in (select racm.duplicate_id
230 from ra_customer_merges racm
231 where racm.process_flag = 'N'
232 and racm.request_id = req_id
233 and racm.set_number = set_num)
234 and ship_to_site_use_id is NULL
235 and bill_to_site_use_id is NULL;
236
237 g_count := sql%rowcount;
238
239 /* Number of rows updates */
240 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
241 arp_message.set_token('NUM_ROWS', to_char(g_count));
242
243 END IF;
244
245 arp_message.set_line( 'INVP_CMERGE_SPDM.INV_MD()-' );
246
247
248 EXCEPTION
249 when others then
250 arp_message.set_error( 'INVP_CMERGE_SPDM.INV_MD');
251 raise;
252
253 END;
254
255
256 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
257
258 PROCEDURE MERGE (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
259 BEGIN
260
261 arp_message.set_line( 'INVP_CMERGE_SPDM.MERGE()+' );
262
263 INV_MD( req_id, set_num, process_mode );
264
265 arp_message.set_line( 'INVP_CMERGE_SPDM.MERGE()-' );
266
267 END MERGE;
268 end INVP_CMERGE_SPDM;