[Home] [Help]
PACKAGE BODY: APPS.OE_RMA_LINE_VALIDATE
Source
1 PACKAGE BODY OE_RMA_LINE_VALIDATE as
2 /* $Header: oexrllvb.pls 115.2 99/07/16 08:29:05 porting shi $ */
3
4
5 /*
6 ** Called by OE ORDER to validate that the referenced order
7 ** line's quantity is not exceeded by the return lines
8 */
9 PROCEDURE CHECK_REF_QUANTITY (
10 P_LINE_ID IN NUMBER,
11 P_RETURN_REFERENCE_ID IN NUMBER,
12 P_RETURN_REFERENCE_TYPE_CODE IN VARCHAR2,
13 P_ORDERED_QUANTITY IN NUMBER,
14 P_RESULT OUT NUMBER
15 ) IS
16
17 L_SUCCESS VARCHAR2(20);
18 temp BOOLEAN;
19 L_ORIGINAL NUMBER;
20 L_CURRENT NUMBER;
21 L_SO_PO_SUM NUMBER;
22 L_INV_SUM NUMBER;
23 L_TEMP_LINE_ID NUMBER;
24
25
26 BEGIN
27
28 P_RESULT := 0;
29
30
31 IF (P_RETURN_REFERENCE_TYPE_CODE IN ('ORDER', 'PO')) THEN
32
33 /* Get Original Ordered Quantity from Sales Order Line */
34 SELECT (L1.ORDERED_QUANTITY - NVL(L1.CANCELLED_QUANTITY,0))
35 INTO L_ORIGINAL
36 FROM SO_LINES L1
37 WHERE L1.LINE_ID = P_RETURN_REFERENCE_ID;
38
39 /* Returns using SO or PO reference */
40
41 SELECT NVL(SUM(L2.ORDERED_QUANTITY - NVL(L2.CANCELLED_QUANTITY,0)), 0)
42 INTO L_SO_PO_SUM
43 FROM SO_LINES L2
44 WHERE L2.RETURN_REFERENCE_ID = P_RETURN_REFERENCE_ID
45 AND L2.RETURN_REFERENCE_TYPE_CODE IN ('ORDER', 'PO')
46 AND L2.LINE_ID <> P_LINE_ID;
47
48 /* Returns using Inv Reference */
49
50 SELECT NVL(SUM(L2.ORDERED_QUANTITY - NVL(L2.CANCELLED_QUANTITY,0)), 0)
51 INTO L_INV_SUM
52 FROM SO_LINES L2
53 WHERE L2.RETURN_REFERENCE_ID IN (SELECT CUSTOMER_TRX_LINE_ID
54 FROM RA_CUSTOMER_TRX_LINES
55 WHERE INTERFACE_LINE_CONTEXT
56 = FND_PROFILE.Value_Specific('SO_SOURCE_CODE')
57 AND INTERFACE_LINE_ATTRIBUTE6
58 = TO_CHAR(P_RETURN_REFERENCE_ID))
59 AND L2.RETURN_REFERENCE_TYPE_CODE = 'INVOICE'
60 AND L2.LINE_ID <> P_LINE_ID;
61
62 ELSIF (P_RETURN_REFERENCE_TYPE_CODE = 'INVOICE') THEN
63
64 /* Get Original Ordered Quantity from Sales Order Line */
65
66 SELECT TO_NUMBER(INTERFACE_LINE_ATTRIBUTE6)
67 INTO L_TEMP_LINE_ID
68 FROM RA_CUSTOMER_TRX_LINES
69 WHERE CUSTOMER_TRX_LINE_ID = P_RETURN_REFERENCE_ID;
70
71 /* Get Original Ordered Quantity from Sales Order Line */
72 SELECT (L1.ORDERED_QUANTITY - NVL(L1.CANCELLED_QUANTITY,0))
73 INTO L_ORIGINAL
74 FROM SO_LINES L1
75 WHERE L1.LINE_ID = L_TEMP_LINE_ID;
76
77 /* Returns using SO or PO reference */
78
79 SELECT NVL(SUM(L2.ORDERED_QUANTITY - NVL(L2.CANCELLED_QUANTITY,0)), 0)
80 INTO L_SO_PO_SUM
81 FROM SO_LINES L2
82 WHERE L2.RETURN_REFERENCE_ID = L_TEMP_LINE_ID
83 AND L2.RETURN_REFERENCE_TYPE_CODE IN ('ORDER', 'PO')
84 AND L2.LINE_ID <> P_LINE_ID;
85
86 /* Returns using Inv Reference */
87
88 SELECT NVL(SUM(L2.ORDERED_QUANTITY - NVL(L2.CANCELLED_QUANTITY,0)), 0)
89 INTO L_INV_SUM
90 FROM SO_LINES L2
91 WHERE L2.RETURN_REFERENCE_ID = P_RETURN_REFERENCE_ID
92 AND L2.RETURN_REFERENCE_TYPE_CODE = 'INVOICE'
93 AND L2.LINE_ID <> P_LINE_ID;
94
95
96 END IF;
97
98
99 L_CURRENT := L_SO_PO_SUM + L_INV_SUM;
100
101 IF (L_ORIGINAL < (P_ORDERED_QUANTITY + L_CURRENT)) THEN
102 temp := OE_MSG.SET_MESSAGE_NAME('SO_OE_INVALID_QTY');
103 P_RESULT := 1;
104 END IF;
105
106 RETURN;
107
108 END;
109
110 /*
111 ** Called by OE ORDER to validate that the duplicate line numbers
112 ** do not exist
113 */
114 PROCEDURE CHECK_DUPLICATE_LINE_NUMBER (
115 P_ROW_ID IN VARCHAR2,
116 P_LINE_NUMBER IN NUMBER,
117 P_HEADER_ID IN NUMBER,
118 P_RESULT OUT NUMBER
119 ) IS
120
121 L_SUCCESS VARCHAR2(20);
122 temp BOOLEAN;
123
124 BEGIN
125
126 P_RESULT := 0;
127
128 SELECT 'duplicate'
129 INTO L_SUCCESS
130 FROM SO_LINES
131 WHERE ((P_ROW_ID IS NULL) OR
132 (ROWID <> P_ROW_ID))
133 and parent_line_id is NULL
134 AND LINE_NUMBER = P_LINE_NUMBER
135 AND HEADER_ID = P_HEADER_ID;
136
137 IF (L_SUCCESS = 'duplicate') THEN
138 temp := OE_MSG.SET_MESSAGE_NAME('SO_OE_UNIQUE_RMA_LINE_NUM');
139 P_RESULT := 1;
140 END IF;
141
142 RETURN;
143
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 RETURN;
147
148 END;
149
150
151 /*
152 ** Insert reference's sales credits and price adjustments.
153 ** Called by OE ORDER
154 */
155 PROCEDURE INSERT_REF_DEFAULT (
156 L_RETURN_REFERENCE_ID IN NUMBER,
157 L_USER_ID IN NUMBER,
158 L_LOGIN_ID IN NUMBER,
159 L_LINK_TO_LINE_ID IN NUMBER,
160 L_LINE_ID IN NUMBER,
161 L_HEADER_ID IN NUMBER,
162 L_REFERENCE_ORDER_HEADER_ID IN NUMBER
163 ) IS
164
165 BEGIN
166
167 IF (L_RETURN_REFERENCE_ID IS NOT NULL) THEN
168 INSERT INTO SO_SALES_CREDITS
169 ( SALES_CREDIT_ID
170 , CREATION_DATE
171 , CREATED_BY
172 , LAST_UPDATE_DATE
173 , LAST_UPDATED_BY
174 , LAST_UPDATE_LOGIN
175 , HEADER_ID
176 , SALES_CREDIT_TYPE_ID
177 , SALESREP_ID
178 , PERCENT
179 , LINE_ID
180 , ATTRIBUTE1
181 , ATTRIBUTE2
182 , ATTRIBUTE3
183 , ATTRIBUTE4
184 , ATTRIBUTE5
185 , ATTRIBUTE6
186 , ATTRIBUTE7
187 , ATTRIBUTE8
188 , ATTRIBUTE9
189 , ATTRIBUTE10
190 , ATTRIBUTE11
191 , ATTRIBUTE12
192 , ATTRIBUTE13
193 , ATTRIBUTE14
194 , ATTRIBUTE15
195 , CONTEXT
196 )
197 SELECT SO_SALES_CREDITS_S.NEXTVAL
198 , SYSDATE
199 , L_USER_ID
200 , SYSDATE
201 , L_USER_ID
202 , L_LOGIN_ID
203 , L_HEADER_ID
204 , SO_SALES_CREDITS.SALES_CREDIT_TYPE_ID
205 , SO_SALES_CREDITS.SALESREP_ID
206 , SO_SALES_CREDITS.PERCENT
207 , L_LINE_ID
208 , SO_SALES_CREDITS.ATTRIBUTE1
209 , SO_SALES_CREDITS.ATTRIBUTE2
210 , SO_SALES_CREDITS.ATTRIBUTE3
211 , SO_SALES_CREDITS.ATTRIBUTE4
212 , SO_SALES_CREDITS.ATTRIBUTE5
213 , SO_SALES_CREDITS.ATTRIBUTE6
214 , SO_SALES_CREDITS.ATTRIBUTE7
215 , SO_SALES_CREDITS.ATTRIBUTE8
216 , SO_SALES_CREDITS.ATTRIBUTE9
217 , SO_SALES_CREDITS.ATTRIBUTE10
218 , SO_SALES_CREDITS.ATTRIBUTE11
219 , SO_SALES_CREDITS.ATTRIBUTE12
220 , SO_SALES_CREDITS.ATTRIBUTE13
221 , SO_SALES_CREDITS.ATTRIBUTE14
222 , SO_SALES_CREDITS.ATTRIBUTE15
223 , SO_SALES_CREDITS.CONTEXT
224 FROM SO_SALES_CREDITS
225 WHERE SO_SALES_CREDITS.LINE_ID = L_LINK_TO_LINE_ID;
226
227
228 INSERT INTO SO_PRICE_ADJUSTMENTS
229 ( PRICE_ADJUSTMENT_ID
230 , CREATION_DATE
231 , CREATED_BY
232 , LAST_UPDATE_DATE
233 , LAST_UPDATED_BY
234 , LAST_UPDATE_LOGIN
235 , HEADER_ID
236 , DISCOUNT_ID
237 , DISCOUNT_LINE_ID
238 , AUTOMATIC_FLAG
239 , PERCENT
240 , LINE_ID
241 , ATTRIBUTE1
242 , ATTRIBUTE2
243 , ATTRIBUTE3
244 , ATTRIBUTE4
245 , ATTRIBUTE5
246 , ATTRIBUTE6
247 , ATTRIBUTE7
248 , ATTRIBUTE8
249 , ATTRIBUTE9
250 , ATTRIBUTE10
251 , ATTRIBUTE11
252 , ATTRIBUTE12
253 , ATTRIBUTE13
254 , ATTRIBUTE14
255 , ATTRIBUTE15
256 , CONTEXT
257 )
258 SELECT SO_PRICE_ADJUSTMENTS_S.NEXTVAL
259 , SYSDATE
260 , L_USER_ID
261 , SYSDATE
262 , L_USER_ID
263 , L_LOGIN_ID
264 , L_HEADER_ID
265 , SO_PRICE_ADJUSTMENTS.DISCOUNT_ID
266 , SO_PRICE_ADJUSTMENTS.DISCOUNT_LINE_ID
267 , SO_PRICE_ADJUSTMENTS.AUTOMATIC_FLAG
268 , SO_PRICE_ADJUSTMENTS.PERCENT
269 , L_LINE_ID
270 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE1
271 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE2
272 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE3
273 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE4
274 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE5
275 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE6
276 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE7
277 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE8
278 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE9
279 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE10
280 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE11
281 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE12
282 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE13
283 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE14
284 , SO_PRICE_ADJUSTMENTS.ATTRIBUTE15
285 , SO_PRICE_ADJUSTMENTS.CONTEXT
286 FROM SO_PRICE_ADJUSTMENTS
287 WHERE SO_PRICE_ADJUSTMENTS.HEADER_ID = L_REFERENCE_ORDER_HEADER_ID
288 AND NVL(SO_PRICE_ADJUSTMENTS.LINE_ID,L_LINK_TO_LINE_ID) =
289 L_LINK_TO_LINE_ID
290 AND NOT EXISTS (SELECT 'HEADER LEVEL DISCOUNT ALREADY EXISTS'
291 FROM SO_PRICE_ADJUSTMENTS SPA
292 WHERE SPA.DISCOUNT_ID = SO_PRICE_ADJUSTMENTS.DISCOUNT_ID
293 AND SPA.HEADER_ID = L_HEADER_ID
294 AND SPA.LINE_ID IS NULL);
295
296 END IF;
297
298 EXCEPTION
299 WHEN NO_DATA_FOUND THEN
300 NULL;
301
302 END;
303
304
305 END OE_RMA_LINE_VALIDATE;