1 PACKAGE BODY JAI_AVLIST_VALIDATE_PKG AS
2 --$Header: Jai_AvList_Validate.plb 120.5.12020000.2 2012/07/18 13:27:06 nkodakan ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| Jai_Avlist_Validate.plb |
10 --| |
11 --| DESCRIPTION |
12 --| Validate if there is more than one Item-UOM combination existing |
13 --| in used AV list for the Item selected in the transaction. |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Check_Validation |
17 --| |
18 --| HISTORY |
19 --| 2009/06/08 Jia Li Created |
20 --| |
21 --+======================================================================*/
22
23
24 --==========================================================================
25 -- PROCEDURE NAME:
26 --
27 -- Check_AvList_Validation Public
28 --
29 -- DESCRIPTION:
30 --
31 -- This is a validation procedure which will be used to check
32 -- whether there is more than one Item-UOM combination existing
33 -- in used AV list for the Item selected in the transaction.
34 --
35 -- PARAMETERS:
36 -- In: pn_party_id Identifier of Customer id or Vendor id
37 -- pn_party_site_id Identifier of Customer/Vendor site id
38 -- pn_inventory_item_id Identifier of Inventory item id
39 -- pd_ordered_date Identifier of Ordered date
40 -- pv_party_type Identifier of Party type, 'C' is mean customer, 'V' is mean vendor
41 -- pn_pricing_list_id Identifier of vat/excise assessable price id, for base form used.
42 --
43 --
44 -- DESIGN REFERENCES:
45 -- FDD_R12i_Advanced_Pricing_V1.0.doc
46 --
47 -- CHANGE HISTORY:
48 --
49 -- 08-Jun-2009 Jia Li created
50 -- 20-APR-2012 mmurtua for bug 13915552
51 -- Description: THERE IS MORE THEN ONE SAME ITEM-UOM COMBINATION DEFIEND IN AV LIST THE < PRICEL
52 -- Fix: Changed cursor definition of check_multi_category_cur. Also added product_attirbute conditions in cursor check_item_category_cur
53 --==========================================================================
54 PROCEDURE Check_AvList_Validation
55 ( pn_party_id IN NUMBER
56 , pn_party_site_id IN NUMBER
57 , pn_inventory_item_id IN NUMBER
58 , pd_ordered_date IN DATE
59 , pv_party_type IN VARCHAR2
60 , pn_pricing_list_id IN NUMBER
61 )
62 IS
63 -- Get category_set_name
64 CURSOR category_set_name_cur
65 IS
66 SELECT
67 category_set_name
68 FROM
69 mtl_default_category_sets_fk_v
70 WHERE functional_area_desc = 'Order Entry';
71
72 lv_category_set_name VARCHAR2(30);
73
74 -- Get excise&vat price_list_id based on customer&site or customer&null site
75 CURSOR cust_price_list_cur
76 IS
77 SELECT
78 price_list_id excise_av_list
79 , vat_price_list_id vat_av_list
80 FROM
81 jai_cmn_cus_addresses
82 WHERE customer_id = pn_party_id
83 AND address_id = NVL(pn_party_site_id,0);
84
85 -- Get excise&vat price_list_id based on vendor&site or vendorr&null site
86 CURSOR vend_price_list_cur
87 IS
88 SELECT
89 price_list_id excise_av_list
90 , vat_price_list_id vat_av_list
91 FROM
92 jai_cmn_vendor_sites
93 WHERE vendor_id = pn_party_id
94 AND vendor_site_id = NVL(pn_party_site_id,0);
95
96 -- Get AV list name based on excise/vat price list id.
97 CURSOR av_list_name_cur
98 ( pn_list_header_id NUMBER
99 )
100 IS
101 SELECT
102 name
103 FROM
104 qp_list_headers
105 WHERE list_header_id = pn_list_header_id;
106
107 -- Check same UOM ,whether item and item category that contains the same item existing in a same price list.
108 CURSOR check_item_category_cur
109 ( pn_list_header_id NUMBER
110 )
111 IS
112 SELECT
113 micv.category_id category_id
114 FROM
115 qp_list_lines ql1
116 , qp_pricing_attributes qp1
117 , mtl_item_categories_v micv
118 , qp_list_lines ql2
119 , qp_pricing_attributes qp2
120 WHERE ql1.list_header_id = pn_list_header_id
121 AND ql1.list_line_id = qp1.list_line_id
122 AND qp1.product_attr_value = TO_CHAR(micv.category_id)
123 AND micv.inventory_item_id = NVL( pn_inventory_item_id, micv.inventory_item_id)
124 AND micv.category_set_name = lv_category_set_name
125 AND qp2.product_attr_value = TO_CHAR(micv.inventory_item_id)
126 AND qp1.list_header_id = qp2.list_header_id
127 AND ql2.list_line_id = qp2.list_line_id
128 AND qp1.product_uom_code = qp2.product_uom_code
129 AND pd_ordered_date BETWEEN NVL( ql1.start_date_active, pd_ordered_date)
130 AND NVL( ql1.end_date_active, SYSDATE)
131 AND pd_ordered_date BETWEEN NVL( ql2.start_date_active, pd_ordered_date)
132 AND NVL(ql2.end_date_active, SYSDATE)
133 AND qp1.product_attribute = 'PRICING_ATTRIBUTE2' /*Added both conditions of product_attribute by mmurtuza for bug 13915552*/
134 AND qp2.product_attribute = 'PRICING_ATTRIBUTE1'
135
136 GROUP BY micv.inventory_item_id,micv.category_id;
137
138 -- Check same UOM ,whether multiple item categories that contains the same item existing in a same price list.
139 CURSOR check_multi_category_cur
140 ( pn_list_header_id NUMBER
141 )
142 IS
143 /*Changed cursor definition by mmurtuza for bug 13915552*/
144 /*SELECT
145 qp.product_uom_code uom_code
146 , COUNT(qp.product_attr_value) category_number
147 FROM
148 qp_list_lines ql
149 , qp_pricing_attributes qp
150 WHERE ql.list_header_id = pn_list_header_id
151 AND ql.list_line_id = qp.list_line_id
152 AND EXISTS ( SELECT
153 micv.category_id
154 FROM
155 mtl_item_categories_v micv
156 WHERE micv.inventory_item_id = NVL( pn_inventory_item_id, micv.inventory_item_id)
157 AND micv.category_set_name = lv_category_set_name
158 AND TO_CHAR(micv.category_id) = qp.product_attr_value
159 )
160 AND pd_ordered_date BETWEEN NVL( ql.start_date_active, pd_ordered_date)
161 AND NVL( ql.end_date_active, SYSDATE)
162 GROUP BY qp.product_uom_code;*/
163
164 SELECT
165 qp.product_uom_code uom_code
166 , COUNT(distinct qp.product_attr_value) category_number
167 ,micv.inventory_item_id
168 FROM
169 qp_list_lines ql
170 , qp_pricing_attributes qp
171 , mtl_item_categories_v micv
172 WHERE ql.list_header_id = pn_list_header_id
173 AND ql.list_line_id = qp.list_line_id
174 AND micv.inventory_item_id = NVL( pn_inventory_item_id, micv.inventory_item_id)
175 AND micv.category_set_name = lv_category_set_name
176 AND TO_CHAR(micv.category_id) = qp.product_attr_value
177 AND pd_ordered_date BETWEEN NVL( ql.start_date_active, pd_ordered_date)
178 AND NVL( ql.end_date_active, SYSDATE)
179 GROUP BY qp.product_uom_code, micv.inventory_item_id;
180
181
182
183 /* Added the below cursor for bug 10103931 */
184 CURSOR check_category_exist_cur
185 ( pn_list_header_id NUMBER
186 )
187 IS
188 select count(1)
189 from qp_list_lines a, qp_list_lines_v b
190 where a.list_line_id = b.list_line_id
191 and b.product_attribute = 'PRICING_ATTRIBUTE2'
192 and a.list_header_id = pn_list_header_id;
193
194 ln_excise_list_id NUMBER;
195 ln_vat_list_id NUMBER;
196 lv_av_list_name qp_list_headers.name%type;
197 ln_category_id NUMBER;
198 ln_category_count NUMBER; /* Added for bug 10103931 */
199 le_multi_row EXCEPTION;
200
201 lv_procedure_name VARCHAR2(40) := 'Check_AvList_Validation';
202 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
203 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
204
205 BEGIN
206 --logging for debug
207 IF (ln_proc_level >= ln_dbg_level)
208 THEN
209 FND_LOG.STRING( ln_proc_level
210 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Begin'
211 , 'Enter Procedure'
212 );
213 END IF; --l_proc_level>=l_dbg_level
214
215 -- Get category_set_name
216 OPEN category_set_name_cur;
217 FETCH category_set_name_cur INTO lv_category_set_name;
218 CLOSE category_set_name_cur;
219
220 -- Invoked by form
221 IF pn_pricing_list_id IS NOT NULL
222 THEN
223 /* Added for bug 10103931 */
224 OPEN check_category_exist_cur(pn_pricing_list_id);
225 FETCH check_category_exist_cur INTO ln_category_count;
226 CLOSE check_category_exist_cur;
227 IF ln_category_count > 0 THEN /* This IF condition added for bug 10103931 */
228 -- Get AV List name.
229 OPEN av_list_name_cur(pn_pricing_list_id);
230 FETCH av_list_name_cur INTO lv_av_list_name;
231 CLOSE av_list_name_cur;
232
233 -- Check same UOM ,whether item and item category that contains the same item existing in a same price list.
234 OPEN check_item_category_cur(pn_pricing_list_id);
235 FETCH check_item_category_cur INTO ln_category_id;
236 CLOSE check_item_category_cur;
237
238 IF ln_category_id IS NOT NULL
239 THEN
240 RAISE le_multi_row;
241 END IF;
242
243 -- Check same UOM ,whether multiple item categories that contains the same item existing in a same price list.
244 FOR multi_category_csr IN check_multi_category_cur(pn_pricing_list_id)
245 LOOP
246 IF multi_category_csr.category_number > 1
247 THEN
248 RAISE le_multi_row;
249 END IF;
250 END LOOP;
251 END IF; -- ln_category_count > 0 ends
252 ELSE
253
254 IF pv_party_type = 'C'
255 THEN
256 -- Get excise and vat price list id based on customer and site.
257 OPEN cust_price_list_cur;
258 FETCH
259 cust_price_list_cur
260 INTO
261 ln_excise_list_id
262 , ln_vat_list_id;
263 CLOSE cust_price_list_cur;
264 ELSIF pv_party_type = 'V'
265 THEN
266 -- Get excise and vat price list id based on vendor and site.
267 OPEN vend_price_list_cur;
268 FETCH
269 vend_price_list_cur
270 INTO
271 ln_excise_list_id
272 , ln_vat_list_id;
273 CLOSE vend_price_list_cur;
274 END IF;
275
276 IF ln_excise_list_id IS NOT NULL
277 THEN
278 /* Added for bug 10103931 */
279 OPEN check_category_exist_cur(ln_excise_list_id);
280 FETCH check_category_exist_cur INTO ln_category_count;
281 CLOSE check_category_exist_cur;
282 IF ln_category_count > 0 THEN /* This IF condition added for bug 10103931 */
283 -- Get Excise AV List name.
284 OPEN av_list_name_cur(ln_excise_list_id);
285 FETCH av_list_name_cur INTO lv_av_list_name;
286 CLOSE av_list_name_cur;
287
288 -- Check same UOM ,whether item and item category that contains the same item existing in a same price list.
289 OPEN check_item_category_cur(ln_excise_list_id);
290 FETCH check_item_category_cur INTO ln_category_id;
291 CLOSE check_item_category_cur;
292
293 IF ln_category_id IS NOT NULL
294 THEN
295 RAISE le_multi_row;
296 END IF;
297
298 -- Check same UOM ,whether multiple item categories that contains the same item existing in a same price list.
299 FOR multi_category_csr IN check_multi_category_cur(ln_excise_list_id)
300 LOOP
301 IF multi_category_csr.category_number > 1
302 THEN
303 RAISE le_multi_row;
304 END IF;
305 END LOOP;
306 END IF; -- ln_category_count > 0 ends
307 END IF; --ln_excise_list_id is not null
308
309 IF ln_vat_list_id IS NOT NULL
310 THEN
311 /* Added for bug 10103931 */
312 OPEN check_category_exist_cur(ln_vat_list_id);
313 FETCH check_category_exist_cur INTO ln_category_count;
314 CLOSE check_category_exist_cur;
315 IF ln_category_count > 0 THEN /* This IF condition added for bug 10103931 */
316 -- Get VAT AV List name.
317 OPEN av_list_name_cur(ln_vat_list_id);
318 FETCH av_list_name_cur INTO lv_av_list_name;
319 CLOSE av_list_name_cur;
320
321 -- Check same UOM ,whether item and item category that contains the same item existing in a same price list.
322 OPEN check_item_category_cur(ln_vat_list_id);
323 FETCH check_item_category_cur INTO ln_category_id;
324 CLOSE check_item_category_cur;
325
326 IF ln_category_id IS NOT NULL
327 THEN
328 RAISE le_multi_row;
329 END IF;
330
331 -- Check same UOM ,whether multiple item categories that contains the same item existing in a same price list.
332 FOR multi_category_csr IN check_multi_category_cur(ln_vat_list_id)
333 LOOP
334 IF multi_category_csr.category_number > 1
335 THEN
336 RAISE le_multi_row;
337 END IF;
338 END LOOP;
339 END IF; -- ln_category_count > 0 ends
340 END IF; -- ln_vat_list_id is not null
341
342 END IF; -- pn_pricing_list_id IS NOT NULL
343
344
345 --logging for debug
346 IF (ln_proc_level >= ln_dbg_level)
347 THEN
348 FND_LOG.STRING( ln_proc_level
349 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.End'
350 , 'Enter Procedure'
351 );
352 END IF; --l_proc_level>=l_dbg_level
353
354 EXCEPTION
355 WHEN le_multi_row THEN
356 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
357 THEN
358 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
359 , GV_MODULE_PREFIX ||'.' || lv_procedure_name
360 || '.Multi_Exception '
361 , SQLCODE||SQLERRM);
362 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
363
364 FND_MESSAGE.SET_NAME ('JA','JAI_AV_LIST_VALIDATION');
365 FND_MESSAGE.SET_TOKEN ('AV_LIST_NAME',lv_av_list_name);
366 app_exception.raise_exception;
367
368 END Check_AvList_Validation;
369
370 END JAI_AVLIST_VALIDATE_PKG;
[Home] [Help]