1 PACKAGE BODY GML_UPDATE_CUST_DATA_PVT AS
2 /* $Header: GMLCUSYB.pls 120.2 2006/11/20 20:03:23 plowe noship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMLCUSYB.pls |
10 | |
11 | DESCRIPTION |
12 | This package body contains code to update cust_id in OPM |
13 | Tables so that after customer synchronization is eliminated the |
14 | CUST_ID properly matches those in Customer tables on APPS side. |
15 | |
16 | |
17 | HISTORY |
18 | 26-JAN-2005 PKANETKA Created |
19 | 14-NOV-2006 PLOWE Bug 5651374 |
20 +=========================================================================+
21 API Name : GML_UPDATE_CUST_DATA_PVT
22 Type : Private
23 -
24 Pre-reqs : N/A
25 Parameters: Per function
26
27 Current Vers : 1.0
28 */
29
30 /* ======================================================================= */
31 /* Function to get equivalent Bill To ID customer id on Apps side for a */
32 /* cust_id in op_cust_mst_opm passed to it. */
33 /* ======================================================================= */
34
35 FUNCTION GET_BILLCUST_ID
36 (
37 p_opm_cust_id IN NUMBER
38 )
39 RETURN NUMBER IS
40
41 l_billcust_id NUMBER;
42 BEGIN
43
44 SELECT a.cust_id INTO l_billcust_id
45 FROM op_cust_mst_v a, op_cust_mst_opm b
46 WHERE a.cust_no = b.cust_no
47 AND a.of_cust_id = b.of_cust_id --Bug 5651374 Performance issue.
48 AND a.co_code = b.co_code
49 AND b.cust_id = p_opm_cust_id
50 AND b.bill_ind = 1
51 AND rownum < 2;
52
53 RETURN l_billcust_id ;
54
55 EXCEPTION WHEN OTHERS THEN
56 RETURN NULL;
57
58 END GET_BILLCUST_ID;
59
60
61 /* ======================================================================= */
62 /* Function to get equivalent ship To ID customer id on Apps side for a */
63 /* cust_id in op_cust_mst_opm passed to it. */
64 /* ======================================================================= */
65
66
67 FUNCTION GET_SHIPCUST_ID
68 (
69 p_opm_cust_id IN NUMBER
70 )
71 RETURN NUMBER IS
72
73 l_shipcust_id NUMBER;
74
75 BEGIN
76
77 SELECT a.cust_id INTO l_shipcust_id
78 FROM op_cust_mst_v a, op_cust_mst_opm b
79 WHERE a.cust_no = b.cust_no
80 AND a.of_cust_id = b.of_cust_id --Bug 5651374 Performance issue.
81 AND a.co_code = b.co_code
82 AND b.cust_id = p_opm_cust_id
83 AND b.ship_ind = 1
84 AND rownum < 2;
85
86 RETURN l_shipcust_id;
87
88 EXCEPTION WHEN OTHERS THEN
89 RETURN NULL;
90
91 END GET_SHIPCUST_ID;
92
93 /* ======================================================================= */
94 /* Function to get first customer id on Apps side for a cust_id in */
95 /* op_cust_mst_opm passed to it. In This particular case customer id */
96 /* could be bill to as well as ship to. It can not be resolved in OPM */
97 /* How ever Customer_no displayed on the form for old records would still */
98 /* be the same. The forms are all Query Only. */
99 /* ======================================================================= */
100
101 FUNCTION GET_ANYCUST_ID
102 (
103 p_opm_cust_id IN NUMBER
104 )
105 RETURN NUMBER IS
106
107 l_anycust_id NUMBER;
108
109 BEGIN
110
111 SELECT a.cust_id INTO l_anycust_id
112 FROM op_cust_mst_v a, op_cust_mst_opm b
113 WHERE a.cust_no = b.cust_no
114 AND a.of_cust_id = b.of_cust_id --Bug 5651374 Performance issue.
115 AND a.co_code = b.co_code
116 AND b.cust_id = p_opm_cust_id
117 AND rownum < 2;
118
119 RETURN l_anycust_id;
120
121 EXCEPTION WHEN OTHERS THEN
122 RETURN NULL;
123
124 END GET_ANYCUST_ID;
125
126
127 PROCEDURE UPDATE_CUST_ID
128 IS
129
130 l_bill_cust_id NUMBER;
131 l_ship_cust_id NUMBER;
132 l_any_cust_id NUMBER;
133
134 CURSOR Cur_cust_id IS
135 SELECT b.cust_id FROM op_cust_mst a, op_cust_mst_opm b
136 WHERE a.cust_no = b.cust_no
137 AND a.co_code = b.co_code;
138
139
140 BEGIN
141
142 FOR r IN Cur_cust_id
143 LOOP
144
145 l_ship_cust_id := GET_SHIPCUST_ID(r.cust_id);
146 l_bill_cust_id := GET_BILLCUST_ID(r.cust_id);
147 l_any_cust_id := GET_ANYCUST_ID(r.cust_id);
148
149 IF (l_ship_cust_id IS NOT NULL) THEN
150 -- All Ship to updates
151 -- Bug 5383665 Modified where clause to use opm_cust_id instead of cust_id.
152
153 UPDATE gl_acct_map
154 SET cust_id = l_ship_cust_id
155 WHERE opm_cust_id = r.cust_id;
156
157 UPDATE op_alot_prm
158 SET cust_id = l_ship_cust_id
159 WHERE opm_cust_id = r.cust_id;
160
161 -- Not bill ?
162 UPDATE op_cust_asc
163 SET assoccust_id = l_ship_cust_id
164 WHERE opm_assoccust_id = r.cust_id;
165
166 UPDATE op_gnrc_itm
167 SET cust_id = l_ship_cust_id
168 WHERE opm_cust_id = r.cust_id;
169
170 UPDATE op_txcu_asc
171 SET cust_id = l_ship_cust_id
172 WHERE opm_cust_id = r.cust_id;
173
174 UPDATE op_ordr_hdr
175 SET shipcust_id = l_ship_cust_id
176 WHERE opm_shipcust_id = r.cust_id;
177
178 UPDATE op_ordr_hdr
179 SET ultimate_shipcust_id = l_ship_cust_id
180 WHERE opm_ultimate_shipcust_id = r.cust_id;
181
182 UPDATE op_ordr_dtl
183 SET shipcust_id = l_ship_cust_id
184 WHERE opm_shipcust_id = r.cust_id;
185
186 UPDATE op_prsl_hdr
187 SET shipcust_id = l_ship_cust_id
188 WHERE opm_shipcust_id = r.cust_id;
189
190 UPDATE op_prsl_hdr
191 SET ultimate_shipcust_id = l_ship_cust_id
192 WHERE opm_ultimate_shipcust_id = r.cust_id;
193
194 UPDATE op_prsl_dtl
195 SET shipcust_id = l_ship_cust_id
196 WHERE opm_shipcust_id = r.cust_id;
197
198 UPDATE op_prsl_dtl
199 SET ultimate_shipcust_id = l_ship_cust_id
200 WHERE opm_ultimate_shipcust_id = r.cust_id;
201
202 END IF;
203
204 IF (l_bill_cust_id IS NOT NULL) THEN
205 -- All Bill to updates
206
207 UPDATE op_cust_asc
208 SET cust_id = l_bill_cust_id
209 WHERE opm_cust_id = r.cust_id;
210
211 UPDATE op_ordr_hdr
212 SET billcust_id = l_bill_cust_id
213 WHERE opm_billcust_id = r.cust_id;
214
215 UPDATE op_prsl_hdr
216 SET billcust_id = l_bill_cust_id
217 WHERE opm_billcust_id = r.cust_id;
218
219 UPDATE op_prsl_dtl
220 SET billcust_id = l_bill_cust_id
221 WHERE opm_billcust_id = r.cust_id;
222
223 END IF;
224
225 IF (l_any_cust_id IS NOT NULL) THEN
226 -- All Other cust_id updates
227
228 UPDATE op_chrg_itm
229 SET cust_id = l_any_cust_id
230 WHERE opm_cust_id = r.cust_id;
231
232 UPDATE op_cust_con
233 SET cust_id = l_any_cust_id
234 WHERE opm_cust_id = r.cust_id;
235
236 UPDATE op_cust_itm
237 SET cust_id = l_any_cust_id
238 WHERE opm_cust_id = r.cust_id;
239
240 UPDATE op_prce_eff
241 SET cust_id = l_any_cust_id
242 WHERE opm_cust_id = r.cust_id;
243
244 UPDATE op_ordr_hdr
245 SET soldtocust_id = l_any_cust_id
246 WHERE opm_soldtocust_id = r.cust_id;
247
248 UPDATE op_ordr_dtl
249 SET soldtocust_id = l_any_cust_id
250 WHERE opm_soldtocust_id = r.cust_id;
251
252 UPDATE op_ordr_dtl
253 SET ultimate_shipcust_id = l_any_cust_id
254 WHERE opm_ultimate_shipcust_id = r.cust_id;
255
256 UPDATE op_prsl_hdr
257 SET soldtocust_id= l_any_cust_id
258 WHERE opm_soldtocust_id = r.cust_id;
259
260 UPDATE op_prsl_dtl
261 SET soldtocust_id= l_any_cust_id
262 WHERE opm_soldtocust_id = r.cust_id;
263
264 UPDATE op_cust_shp
265 SET cust_id = l_any_cust_id
266 WHERE opm_cust_id = r.cust_id;
267
268 END IF;
269
270 END LOOP;
271
272
273 END UPDATE_CUST_ID;
274
275 END GML_UPDATE_CUST_DATA_PVT;
[Home] [Help]