[Home] [Help]
PACKAGE BODY: APPS.QP_VIEW_UTIL
Source
1 PACKAGE BODY QP_VIEW_UTIL AS
2 /* $Header: QPXVVUTB.pls 120.1 2005/06/16 02:10:46 appldev $ */
3
4 G_QUALIFIER_ATTRIBUTE6 CONSTANT NUMBER := 1004; /*'Customer PONumber';*/
5 G_NEW_QUALIFIER_ATTRIBUTE6 CONSTANT NUMBER := 1053; /*'Customer PONumber';*/
6 G_QUALIFIER_ATTRIBUTE7 CONSTANT NUMBER := 1007; /*'Order Type';*/
7 G_NEW_QUALIFIER_ATTRIBUTE7 CONSTANT NUMBER := 1325; /*'Order Type';*/
8 G_QUALIFIER_ATTRIBUTE8 CONSTANT NUMBER := 1005; /*'Agreement Type';*/
9 G_NEW_QUALIFIER_ATTRIBUTE8 CONSTANT NUMBER := 1468; /*'Agreement Type';*/
10 G_QUALIFIER_ATTRIBUTE9 CONSTANT NUMBER := 1006; /*'Agreement Name';*/
11 G_NEW_QUALIFIER_ATTRIBUTE9 CONSTANT NUMBER := 1467; /*'Agreement Name';*/
12 G_PRODUCT_ATTRIBUTE1 CONSTANT NUMBER := 1001; /*'Item Number';*/
13 G_NEW_PRODUCT_ATTRIBUTE1 CONSTANT NUMBER := 1208; /*'Item Number';*/
14 G_PRODUCT_ATTRIBUTE2 CONSTANT NUMBER := 1045; /*'Item Catego
15 ry';*/
16
17
18 FUNCTION Get_Entity_Id( p_list_line_id IN NUMBER
19 ) RETURN VARCHAR2 IS
20
21 x_return VARCHAR2(30) :=FND_API.G_MISS_CHAR;
22
23 v_qualifier_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
24 l_order_type_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
25 l_customer_po_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
26 l_agreement_type_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
27 l_agreement_name_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
28 v_qualifier_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
29 l_order_type_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
30 l_customer_po_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
31 l_agreement_type_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
32 l_agreement_name_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
33
34 v_product_attribute QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE%TYPE;
35 l_item_no_attribute QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE%TYPE;
36 l_item_category_attribute QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE%TYPE;
37 v_product_attribute_context QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE_CONTEXT%TYPE;
38 l_item_no_context QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE_CONTEXT%TYPE;
39 l_item_category_context QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE_CONTEXT%TYPE;
40
41
42 BEGIN
43
44 QP_UTIL.Get_Context_Attribute(1007,l_order_type_context,l_order_type_attribute);
45 QP_UTIL.Get_Context_Attribute(1004,l_customer_po_context,l_customer_po_attribute);
46 QP_UTIL.Get_Context_Attribute(1005,l_agreement_type_context,l_agreement_type_attribute);
47 QP_UTIL.Get_Context_Attribute(1006,l_agreement_name_context,l_agreement_name_attribute);
48
49
50 BEGIN
51
52 select QUALIFIER_CONTEXT, QUALIFIER_ATTRIBUTE
53 into v_qualifier_context, v_qualifier_attribute
54 from QP_QUALIFIERS
55 where LIST_LINE_ID = p_list_line_id
56 and ( (QUALIFIER_CONTEXT = l_order_type_context
57 and QUALIFIER_ATTRIBUTE = l_order_type_attribute)
58 or (QUALIFIER_CONTEXT = l_customer_po_context
59 and QUALIFIER_ATTRIBUTE = l_customer_po_attribute)
60 or (QUALIFIER_CONTEXT = l_agreement_type_context
61 and QUALIFIER_ATTRIBUTE = l_agreement_type_attribute)
62 or (QUALIFIER_CONTEXT = l_agreement_name_context
63 and QUALIFIER_ATTRIBUTE = l_agreement_name_attribute));
64
65
66 x_return := Get_Attribute_Code(v_qualifier_context,v_qualifier_attribute);
67 RETURN x_return;
68
69
70 EXCEPTION
71 when no_data_found then
72
73 QP_UTIL.Get_Context_Attribute(1001,l_item_no_context,l_item_no_attribute);
74 QP_UTIL.Get_Context_Attribute(1045,l_item_category_context,l_item_category_attribute);
75
76 BEGIN
77
78 select PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTRIBUTE
79 into v_product_attribute_context, v_product_attribute
80 from QP_PRICING_ATTRIBUTES
81 where LIST_LINE_ID = p_list_line_id
82 and ( (PRODUCT_ATTRIBUTE_CONTEXT = l_item_no_context
83 and PRODUCT_ATTRIBUTE = l_item_no_attribute)
84 or (PRODUCT_ATTRIBUTE_CONTEXT = l_item_category_context
85 and PRODUCT_ATTRIBUTE = l_item_category_attribute));
86
87 x_return := Get_Attribute_Code(v_product_attribute_context,v_product_attribute);
88 RETURN x_return;
89
90 EXCEPTION
91 when no_data_found then
92 x_return := 0;
93 RETURN x_return;
94
95
96 END;
97
98 END;
99
100
101 END Get_Entity_Id;
102
103
104 FUNCTION Get_Entity_Value( p_list_line_id IN NUMBER
105 ) RETURN VARCHAR2 IS
106
107 x_return VARCHAR2(240) :=FND_API.G_MISS_CHAR;
108
109 l_order_type_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
110 l_customer_po_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
111 l_agreement_type_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
112 l_agreement_name_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
113 l_order_type_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
114 l_customer_po_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
115 l_agreement_type_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
116 l_agreement_name_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
117
118 l_item_no_attribute QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE%TYPE;
119 l_item_category_attribute QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE%TYPE;
120 l_item_no_context QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE_CONTEXT%TYPE;
121 l_item_category_context QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE_CONTEXT%TYPE;
122
123 v_qualifier_attr_value QP_QUALIFIERS.QUALIFIER_ATTR_VALUE%TYPE;
124 v_product_attr_value QP_PRICING_ATTRIBUTES.PRODUCT_ATTR_VALUE%TYPE;
125
126
127 BEGIN
128
129 QP_UTIL.Get_Context_Attribute(1007,l_order_type_context,l_order_type_attribute);
130 QP_UTIL.Get_Context_Attribute(1004,l_customer_po_context,l_customer_po_attribute);
131 QP_UTIL.Get_Context_Attribute(1005,l_agreement_type_context,l_agreement_type_attribute);
132 QP_UTIL.Get_Context_Attribute(1006,l_agreement_name_context,l_agreement_name_attribute);
133
134
135 select QUALIFIER_ATTR_VALUE
136 into v_qualifier_attr_value
137 from QP_QUALIFIERS
138 where LIST_LINE_ID = p_list_line_id
139 and ( (QUALIFIER_CONTEXT = l_order_type_context
140 and QUALIFIER_ATTRIBUTE = l_order_type_attribute)
141 or (QUALIFIER_CONTEXT = l_customer_po_context
142 and QUALIFIER_ATTRIBUTE = l_customer_po_attribute)
143 or (QUALIFIER_CONTEXT = l_agreement_type_context
144 and QUALIFIER_ATTRIBUTE = l_agreement_type_attribute)
145 or (QUALIFIER_CONTEXT = l_agreement_name_context
146 and QUALIFIER_ATTRIBUTE = l_agreement_name_attribute));
147
148
149 if v_qualifier_attr_value is null then
150
151 QP_UTIL.Get_Context_Attribute(1001,l_item_no_context,l_item_no_attribute);
152 QP_UTIL.Get_Context_Attribute(1045,l_item_category_context,l_item_category_attribute);
153
154
155 select PRODUCT_ATTR_VALUE
156 into v_product_attr_value
157 from QP_PRICING_ATTRIBUTES
158 where LIST_LINE_ID = p_list_line_id
159 and ( (PRODUCT_ATTRIBUTE_CONTEXT = l_item_no_context
160 and PRODUCT_ATTRIBUTE = l_item_no_attribute)
161 or (PRODUCT_ATTRIBUTE_CONTEXT = l_item_category_context
162 and PRODUCT_ATTRIBUTE = l_item_category_attribute));
163
164 x_return := v_product_attr_value;
165 RETURN x_return;
166
167 else
168
169 x_return := v_qualifier_attr_value;
170 RETURN x_return;
171
172 end if;
173
174
175 exception
176 when no_data_found then
177 QP_UTIL.Get_Context_Attribute(1001,l_item_no_context,l_item_no_attribute);
178 QP_UTIL.Get_Context_Attribute(1045,l_item_category_context,l_item_category_attribute);
179
180
181 select PRODUCT_ATTR_VALUE
182 into v_product_attr_value
183 from QP_PRICING_ATTRIBUTES
184 where LIST_LINE_ID = p_list_line_id
185 and ( (PRODUCT_ATTRIBUTE_CONTEXT = l_item_no_context
186 and PRODUCT_ATTRIBUTE = l_item_no_attribute)
187 or (PRODUCT_ATTRIBUTE_CONTEXT = l_item_category_context
188 and PRODUCT_ATTRIBUTE = l_item_category_attribute));
189
190 x_return := v_product_attr_value;
191 RETURN x_return;
192 END Get_Entity_Value;
193
194 FUNCTION Are_There_Breaks( p_list_line_id IN NUMBER
195 ) RETURN VARCHAR2 IS
196
197 x_return VARCHAR2(1) :=FND_API.G_MISS_CHAR;
198
199 v_pricing_attr_value_from QP_PRICING_ATTRIBUTES.PRICING_ATTR_VALUE_FROM%TYPE;
200 v_pricing_attr_value_to QP_PRICING_ATTRIBUTES.PRICING_ATTR_VALUE_TO%TYPE;
201
202
203 BEGIN
204
205 select 'X' into v_pricing_attr_value_to from qp_pricing_attributes
206 where list_line_id = p_list_line_id and pricing_attr_value_to is not null;
207
208 -- select X
209 -- PRICING_ATTR_VALUE_TO
210 -- into v_pricing_attr_value_to
211 -- from QP_PRICING_ATTRIBUTES
212 -- where LIST_LINE_ID = p_list_line_id;
213
214
215 x_return := 'Y';
216 RETURN x_return;
217
218
219 exception
220 when no_data_found then
221 x_return := 'N';
222 RETURN x_return;
223
224 END Are_There_Breaks;
225
226 FUNCTION Get_Price_List_Attribute RETURN VARCHAR2 IS
227
228 x_return VARCHAR2(30) :=FND_API.G_MISS_CHAR;
229
230 l_price_list_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
231 l_price_list_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
232
233
234 BEGIN
235
236 QP_UTIL.Get_Context_Attribute('PRICE_LIST_ID',l_price_list_context,l_price_list_attribute);
237
238 x_return:= l_price_list_attribute;
239
240 return x_return;
241
242 END Get_Price_List_Attribute;
243
244 FUNCTION Get_Price_List_Context RETURN VARCHAR2 IS
245
246 x_return VARCHAR2(30) :=FND_API.G_MISS_CHAR;
247
248 l_price_list_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
249 l_price_list_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
250
251
252 BEGIN
253
254 QP_UTIL.Get_Context_Attribute('PRICE_LIST_ID',l_price_list_context,l_price_list_attribute);
255
256 x_return:= l_price_list_context;
257
258 return x_return;
259
260 END Get_Price_List_Context;
261
262 FUNCTION Get_Parent_Discount_Line_Id( p_list_line_id IN NUMBER
263 ) RETURN NUMBER IS
264
265 x_return NUMBER :=FND_API.G_MISS_NUM;
266
267 BEGIN
268
269 select MIN(qpa.LIST_LINE_ID)
270 into x_return
271 from QP_PRICING_ATTRIBUTES qpa
272 where ATTRIBUTE_GROUPING_NO in ( select qpb.ATTRIBUTE_GROUPING_NO
273 from QP_PRICING_ATTRIBUTES qpb
274 where qpb.LIST_LINE_ID = p_list_line_id) ;
275
276 RETURN x_return;
277
278 END Get_Parent_Discount_Line_Id;
279
280 FUNCTION Get_Attribute_Code(p_context IN VARCHAR2,
281 p_attribute_name IN VARCHAR2
282 ) RETURN VARCHAR2 IS
283
284 x_return VARCHAR2(240) :=FND_API.G_MISS_CHAR;
285
286
287 BEGIN
288
289 --Agreement Name
290 IF p_context = 'CUSTOMER' and p_attribute_name = 'QUALIFIER_ATTRIBUTE7'then
291 x_return := '1467';
292 RETURN x_return;
293
294 --Agreement Type
295 ELSIF p_context = 'CUSTOMER' and p_attribute_name = 'QUALIFIER_ATTRIBUTE8'then
296 x_return := '1468';
297 RETURN x_return;
298
299 -- Order Type
300 ELSIF p_context = 'ORDER' and p_attribute_name = 'QUALIFIER_ATTRIBUTE9'then
301 x_return := '1325';
302 RETURN x_return;
303
304 -- Customer PO
305 ELSIF p_context = 'ORDER' and p_attribute_name = 'QUALIFIER_ATTRIBUTE12'then
306 x_return := '1053';
307 RETURN x_return;
308
309 -------Pricing Attributes
310 -- Item Number
311 ELSIF p_context = 'ITEM' and p_attribute_name = 'PRICING_ATTRIBUTE1'then
312 x_return := '1208';
313 RETURN x_return;
314
315 -- Item Category
316 ELSIF p_context = 'ITEM' and p_attribute_name = 'PRICING_ATTRIBUTE2'then
317 x_return := '1045';
318 RETURN x_return;
319
320 -- Units
321 ELSIF p_context = 'VOLUME' and p_attribute_name = 'PRICING_ATTRIBUTE10'then --Changed for 2159318
322 --ELSIF p_context = 'VOLUME' and p_attribute_name = 'PRICING_ATTRIBUTE3'then
323 x_return := 'UNITS';
324 RETURN x_return;
325
326 /* Added for 2159318 */
327
328 ELSIF p_context = 'VOLUME' and p_attribute_name = 'PRICING_ATTRIBUTE12'then
329 x_return := 'DOLLARS';
330 RETURN x_return;
331
332
333
334 /* Commented out for 2159318
335 -- Amount
336 ELSIF p_context = 'LINEAMT' and p_attribute_name = 'PRICING_ATTRIBUTE4'then
337 x_return := 'DOLLARS';
338 RETURN x_return;
339 */
340
341 END IF;
342
343 END Get_Attribute_Code;
344
345 PROCEDURE Get_Context_Attributes( p_entity_id NUMBER,
346 x_context OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
347 x_attribute OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
348 x_product_flag OUT NOCOPY /* file.sql.39 change */ BOOLEAN,
349 x_qualifier_flag OUT NOCOPY /* file.sql.39 change */ BOOLEAN) AS
350 BEGIN
351 -- Init the variables to null
352
353 x_context := NULL;
354 x_attribute := NULL;
355
356 IF (p_entity_id = G_PRODUCT_ATTRIBUTE1 OR p_entity_id = G_NEW_PRODUCT_ATTRIBUTE1) THEN
357 -- Get the attribute and context for item
358 QP_UTIL.get_context_attribute(p_entity_id,x_context,x_attribute);
359 x_product_flag := TRUE;
360 x_qualifier_flag := FALSE;
361 ELSIF (p_entity_id = G_PRODUCT_ATTRIBUTE2) THEN
362 -- Get the attribute and context for item category
363 QP_UTIL.get_context_attribute(p_entity_id,x_context,x_attribute);
364 x_product_flag := TRUE;
365 x_qualifier_flag := FALSE;
366 ELSIF (p_entity_id = G_QUALIFIER_ATTRIBUTE6 OR p_entity_id = G_NEW_QUALIFIER_ATTRIBUTE6) THEN
367 -- Get the attribute and context for customer po
368 QP_UTIL.get_context_attribute(p_entity_id,x_context,x_attribute);
369 x_product_flag := FALSE;
370 x_qualifier_flag := TRUE;
371 -- For creating record in qp_pricing_attributes table with Units or Dollars for these qualifiers
372 ELSIF (p_entity_id = G_QUALIFIER_ATTRIBUTE7 OR p_entity_id = G_NEW_QUALIFIER_ATTRIBUTE7) THEN
373 -- Get the attribute and context for order type
374 QP_UTIL.get_context_attribute(p_entity_id,x_context,x_attribute);
375 x_product_flag := FALSE;
376 x_qualifier_flag := TRUE;
377 -- For creating record in qp_pricing_attributes table with Units or Dollars for these qualifiers
378 ELSIF (p_entity_id = G_QUALIFIER_ATTRIBUTE8 OR p_entity_id = G_NEW_QUALIFIER_ATTRIBUTE8) THEN
379 -- Get the attribute and context for agreement type
380 QP_UTIL.get_context_attribute(p_entity_id,x_context,x_attribute);
381 x_product_flag := FALSE;
382 x_qualifier_flag := TRUE;
383 -- For creating record in qp_pricing_attributes table with Units or Dollars for these qualifiers
384 ELSIF (p_entity_id = G_QUALIFIER_ATTRIBUTE9 OR p_entity_id = G_NEW_QUALIFIER_ATTRIBUTE9) THEN
385 -- Get the attribute and context for agreement name
386 QP_UTIL.get_context_attribute(p_entity_id,x_context,x_attribute);
387 x_product_flag := FALSE;
388 x_qualifier_flag := TRUE;
389 -- For creating record in qp_pricing_ attributes table with Units or Dollars for these qualifiers
390 ELSE
391 x_product_flag := FALSE;
392 x_qualifier_flag := FALSE;
393 END IF;
394 END Get_Context_Attributes;
395
396
397
398
399 END QP_VIEW_UTIL;