[Home] [Help]
PACKAGE BODY: APPS.OEXCPDST
Source
1 PACKAGE BODY OEXCPDST AS
2 /* $Header: OECPDILB.pls 115.1 99/07/16 08:10:43 porting shi $ */
3
4 PROCEDURE OE_CP_DISCOUNT
5 ( source_id IN NUMBER
6 , destination_id IN NUMBER
7 , destination_price_list_id IN NUMBER
8 , msg_text OUT VARCHAR2
9 , return_status OUT NUMBER
10 , line_item_not_copied OUT NUMBER
11 , line_exist OUT NUMBER
12 )
13
14 IS
15 actbuf VARCHAR2(100);
16 s_entity_code VARCHAR2(30);
17 count_of_exist_items NUMBER;
18 s_discount_line_id NUMBER := NULL;
19 d_discount_line_id NUMBER := NULL;
20
21 CURSOR C1 IS SELECT DISCOUNT_ID
22 , DISCOUNT_LINE_ID
23 , ENTITY_ID
24 , ENTITY_VALUE
25 , PERCENT
26 , AMOUNT
27 , PRICE
28 , START_DATE_ACTIVE
29 , END_DATE_ACTIVE
30 , CONTEXT
31 , ATTRIBUTE1
32 , ATTRIBUTE2
33 , ATTRIBUTE3
34 , ATTRIBUTE4
35 , ATTRIBUTE5
36 , ATTRIBUTE6
37 , ATTRIBUTE7
38 , ATTRIBUTE8
39 , ATTRIBUTE9
40 , ATTRIBUTE10
41 , ATTRIBUTE11
42 , ATTRIBUTE12
43 , ATTRIBUTE13
44 , ATTRIBUTE14
45 , ATTRIBUTE15
46 FROM SO_DISCOUNT_LINES
47 WHERE DISCOUNT_ID = source_id;
48
49 CURSOR C2 IS SELECT PRICE_BREAK_LINES_LOW_RANGE
50 , PRICE_BREAK_LINES_HIGH_RANGE
51 , DISCOUNT_LINE_ID
52 , METHOD_TYPE_CODE
53 , PERCENT
54 , AMOUNT
55 , PRICE
56 , UNIT_CODE
57 , CONTEXT
58 , ATTRIBUTE1
59 , ATTRIBUTE2
60 , ATTRIBUTE3
61 , ATTRIBUTE4
62 , ATTRIBUTE5
63 , ATTRIBUTE6
64 , ATTRIBUTE7
65 , ATTRIBUTE8
66 , ATTRIBUTE9
67 , ATTRIBUTE10
68 , ATTRIBUTE11
69 , ATTRIBUTE12
70 , ATTRIBUTE13
71 , ATTRIBUTE14
72 , ATTRIBUTE15
73 FROM SO_PRICE_BREAK_LINES
74 WHERE DISCOUNT_LINE_ID = s_discount_line_id;
75
76 BEGIN
77 line_item_not_copied := 0;
78 line_exist := 0;
79
80 FOR C1REC IN C1 LOOP
81
82 s_discount_line_id := C1REC.DISCOUNT_LINE_ID;
83
84 SELECT ENTITY_CODE
85 INTO s_entity_code
86 FROM SO_ENTITIES
87 WHERE ENTITY_ID = C1REC.ENTITY_ID;
88
89 IF (s_entity_code = 'I') THEN
90
91 SELECT COUNT(*)
92 INTO count_of_exist_items
93 FROM SO_PRICE_LIST_LINES
94 WHERE PRICE_LIST_ID = destination_price_list_id
95 AND INVENTORY_ITEM_ID = C1REC.ENTITY_VALUE;
96
97 END IF;
98
99 IF (s_entity_code = 'I') AND (count_of_exist_items = 0) THEN
100
101 line_item_not_copied := 1;
102
103 ELSE
104
108
105 SELECT SO_DISCOUNT_LINES_S.NEXTVAL
106 INTO d_discount_line_id
107 FROM DUAL;
109 actbuf := 'Inserting discount lines.';
110 line_exist := 1;
111
112 INSERT INTO SO_DISCOUNT_LINES
113 ( DISCOUNT_LINE_ID
114 , CREATION_DATE
115 , CREATED_BY
116 , LAST_UPDATE_DATE
117 , LAST_UPDATED_BY
118 , LAST_UPDATE_LOGIN
119 , PROGRAM_APPLICATION_ID
120 , PROGRAM_ID
121 , PROGRAM_UPDATE_DATE
122 , REQUEST_ID
123 , DISCOUNT_ID
124 , ENTITY_ID
125 , ENTITY_VALUE
126 , PERCENT
127 , AMOUNT
128 , PRICE
129 , START_DATE_ACTIVE
130 , END_DATE_ACTIVE
131 , CONTEXT
132 , ATTRIBUTE1
133 , ATTRIBUTE2
134 , ATTRIBUTE3
135 , ATTRIBUTE4
136 , ATTRIBUTE5
137 , ATTRIBUTE6
138 , ATTRIBUTE7
139 , ATTRIBUTE8
140 , ATTRIBUTE9
141 , ATTRIBUTE10
142 , ATTRIBUTE11
143 , ATTRIBUTE12
144 , ATTRIBUTE13
145 , ATTRIBUTE14
146 , ATTRIBUTE15
147 )
148 VALUES (d_discount_line_id
149 , SYSDATE
150 , 1
151 , SYSDATE
152 , 1
153 , NULL
154 , NULL
155 , NULL
156 , NULL
157 , NULL
158 , destination_id
159 , C1REC.ENTITY_ID
160 , C1REC.ENTITY_VALUE
161 , C1REC.PERCENT
162 , C1REC.AMOUNT
163 , C1REC.PRICE
164 , C1REC.START_DATE_ACTIVE
165 , C1REC.END_DATE_ACTIVE
166 , C1REC.CONTEXT
167 , C1REC.ATTRIBUTE1
168 , C1REC.ATTRIBUTE2
169 , C1REC.ATTRIBUTE3
170 , C1REC.ATTRIBUTE4
171 , C1REC.ATTRIBUTE5
172 , C1REC.ATTRIBUTE6
173 , C1REC.ATTRIBUTE7
174 , C1REC.ATTRIBUTE8
175 , C1REC.ATTRIBUTE9
176 , C1REC.ATTRIBUTE10
180 , C1REC.ATTRIBUTE14
177 , C1REC.ATTRIBUTE11
178 , C1REC.ATTRIBUTE12
179 , C1REC.ATTRIBUTE13
181 , C1REC.ATTRIBUTE15);
182
183 FOR C2REC IN C2 LOOP
184
185 actbuf := 'Inserting price break lines.';
186
187 INSERT INTO SO_PRICE_BREAK_LINES
188 ( PRICE_BREAK_LINES_LOW_RANGE
189 , PRICE_BREAK_LINES_HIGH_RANGE
190 , DISCOUNT_LINE_ID
191 , METHOD_TYPE_CODE
192 , CREATION_DATE
193 , CREATED_BY
194 , LAST_UPDATE_DATE
195 , LAST_UPDATED_BY
196 , LAST_UPDATE_LOGIN
197 , PROGRAM_APPLICATION_ID
198 , PROGRAM_ID
199 , PROGRAM_UPDATE_DATE
200 , REQUEST_ID
201 , PERCENT
202 , AMOUNT
203 , PRICE
204 , UNIT_CODE
205 , CONTEXT
206 , ATTRIBUTE1
207 , ATTRIBUTE2
208 , ATTRIBUTE3
209 , ATTRIBUTE4
210 , ATTRIBUTE5
211 , ATTRIBUTE6
212 , ATTRIBUTE7
213 , ATTRIBUTE8
214 , ATTRIBUTE9
215 , ATTRIBUTE10
216 , ATTRIBUTE11
217 , ATTRIBUTE12
218 , ATTRIBUTE13
219 , ATTRIBUTE14
220 , ATTRIBUTE15
221 )
222 VALUES (C2REC.PRICE_BREAK_LINES_LOW_RANGE
223 , C2REC.PRICE_BREAK_LINES_HIGH_RANGE
224 , d_discount_line_id
225 , C2REC.METHOD_TYPE_CODE
226 , SYSDATE
227 , 1
228 , SYSDATE
229 , 1
230 , NULL
231 , NULL
232 , NULL
233 , NULL
234 , NULL
235 , C2REC.PERCENT
236 , C2REC.AMOUNT
237 , C2REC.PRICE
238 , C2REC.UNIT_CODE
239 , C2REC.CONTEXT
240 , C2REC.ATTRIBUTE1
241 , C2REC.ATTRIBUTE2
242 , C2REC.ATTRIBUTE3
243 , C2REC.ATTRIBUTE4
244 , C2REC.ATTRIBUTE5
245 , C2REC.ATTRIBUTE6
246 , C2REC.ATTRIBUTE7
247 , C2REC.ATTRIBUTE8
248 , C2REC.ATTRIBUTE9
249 , C2REC.ATTRIBUTE10
250 , C2REC.ATTRIBUTE11
251 , C2REC.ATTRIBUTE12
252 , C2REC.ATTRIBUTE13
253 , C2REC.ATTRIBUTE14
254 , C2REC.ATTRIBUTE15);
255
256 END LOOP;
260 END LOOP;
257
258 END IF;
259
261
262 actbuf := 'Inserting discount customers.';
263
264 INSERT INTO SO_DISCOUNT_CUSTOMERS
265 ( DISCOUNT_CUSTOMER_ID
266 , CREATION_DATE
267 , CREATED_BY
268 , LAST_UPDATE_DATE
269 , LAST_UPDATED_BY
270 , LAST_UPDATE_LOGIN
271 , PROGRAM_APPLICATION_ID
272 , PROGRAM_ID
273 , PROGRAM_UPDATE_DATE
274 , REQUEST_ID
275 , DISCOUNT_ID
276 , CUSTOMER_ID
277 , SITE_USE_ID
278 , START_DATE_ACTIVE
279 , END_DATE_ACTIVE
280 , CONTEXT
281 , ATTRIBUTE1
282 , ATTRIBUTE2
283 , ATTRIBUTE3
284 , ATTRIBUTE4
285 , ATTRIBUTE5
286 , ATTRIBUTE6
287 , ATTRIBUTE7
288 , ATTRIBUTE8
289 , ATTRIBUTE9
290 , ATTRIBUTE10
291 , ATTRIBUTE11
292 , ATTRIBUTE12
293 , ATTRIBUTE13
294 , ATTRIBUTE14
295 , ATTRIBUTE15
296 , CUSTOMER_CLASS_CODE
297 )
298 SELECT SO_DISCOUNT_CUSTOMERS_S.NEXTVAL
299 , SYSDATE
300 , 1
301 , SYSDATE
302 , 1
303 , NULL
304 , NULL
305 , NULL
306 , NULL
307 , NULL
308 , destination_id
309 , CUSTOMER_ID
310 , SITE_USE_ID
311 , START_DATE_ACTIVE
312 , END_DATE_ACTIVE
313 , CONTEXT
314 , ATTRIBUTE1
315 , ATTRIBUTE2
316 , ATTRIBUTE3
317 , ATTRIBUTE4
318 , ATTRIBUTE5
319 , ATTRIBUTE6
320 , ATTRIBUTE7
321 , ATTRIBUTE8
322 , ATTRIBUTE9
323 , ATTRIBUTE10
324 , ATTRIBUTE11
325 , ATTRIBUTE12
326 , ATTRIBUTE13
327 , ATTRIBUTE14
328 , ATTRIBUTE15
329 , customer_class_code
330 FROM SO_DISCOUNT_CUSTOMERS
331 WHERE DISCOUNT_ID = source_id;
332
333 return_status := 0; --success
334 msg_text := 'OEXCPDST: success';
335
336 EXCEPTION
340
337 WHEN NO_DATA_FOUND THEN
338 return_status := SQLCODE;
339 msg_text := 'OEXCPDST:' || SUBSTR(SQLERRM, 1, 70) || actbuf;
341 WHEN OTHERS THEN
342 return_status := SQLCODE;
343 msg_text := 'OEXCPDST:' || SUBSTR(SQLERRM, 1, 70) || actbuf;
344 END;
345
346 END OEXCPDST;