1 PACKAGE BODY PO_CATEGORIES_SV1 AS
2 /* $Header: POXPICTB.pls 120.0.12000000.1 2007/01/16 23:03:11 appldev ship $ */
3
4 /*================================================================
5
6 FUNCTION NAME: val_item_category_id()
7
8 ==================================================================*/
9 FUNCTION val_item_category_id(x_category_id IN NUMBER,
10 x_item_id IN NUMBER,
11 x_organization_id IN NUMBER) RETURN BOOLEAN
12 IS
13
14 x_progress varchar2(3) := null;
15 x_temp binary_integer := 0;
16
17 BEGIN
18 x_progress := '010';
19
20 /* first find out the default category_set_id for function_area of
21 "PURCHASING", then validate if X_category_id belong to the X_item
22 */
23
24 SELECT count(*)
25 INTO x_temp
26 FROM mtl_item_categories mic,
27 mtl_categories mcs
28 WHERE mic.category_id = mcs.category_id
29 AND mic.category_set_id = (SELECT category_set_id
30 FROM mtl_default_category_sets
31 WHERE functional_area_id = 2 /* 2=purchasing*/)
32 AND mic.category_id = x_category_id
33 AND mic.inventory_item_id = x_item_id
34 AND mic.organization_id = x_organization_id
35 AND sysdate < nvl(mcs.disable_date, sysdate+1)
36 AND mcs.enabled_flag = 'Y';
37
38 IF x_temp = 0 THEN
39 RETURN FALSE; /* validation fails */
40 ELSE
41 RETURN TRUE; /* validation succeeds */
42 END IF;
43
44 EXCEPTION
45 WHEN others THEN
46 po_message_s.sql_error
47 ('val_item_category_id', x_progress, sqlcode);
48 raise;
49 END val_item_category_id;
50
51 /*================================================================
52
53 FUNCTION NAME: derive_category_id()
54
55 ==================================================================*/
56 FUNCTION derive_category_id(X_category IN VARCHAR2)
57 return NUMBER IS
58
59 X_progress varchar2(3) := NULL;
60 X_category_id_v number := NULL;
61 ret_code NUMBER := 0;
62 x_category_set_id number;
63 x_err_text VARCHAR2(2000);
64 x_structure_id number;
65 x_category_id number;
66
67 BEGIN
68
69 X_progress := '010';
70
71 /* bug 609728 added join to mcs and mdcs to get the default category_set and structure_id */
72 /*Bug1730946
73 Bcos of a performance issue splitting the below query
74 and calling the BOM api to achieve the same functionality.
75
76 get the category_id from mtl_categories_kfv based on x_category
77 SELECT category_id
78 INTO X_category_id_v
79 FROM mtl_categories_kfv mck, mtl_category_sets mcs, mtl_default_category_sets mdcs
80 WHERE concatenated_segments = X_category
81 AND mck.structure_id = mcs.structure_id
82 AND mcs.category_set_id = mdcs.category_set_id
83 AND mdcs.functional_area_id = 2;
84 BUG 1760539
85 THE PERFORMANCE WITH THE BOM API WAS NOT GOOD EITHER AND SO
86 USING THE FLEX API INSTEAD
87 ret_code := INVPUOPI.mtl_pr_parse_flex_name
88
89 */
90 select structure_id
91 into x_structure_id
92 from mtl_default_category_sets mdcs,mtl_category_sets_b mcsb
93 where mdcs.functional_area_id = 2 /* 2 = Purchasing */
94 and mdcs.category_set_id = mcsb.category_set_id;
95
96 ret_code := FND_FLEX_EXT.GET_CCID('INV', 'MCAT', x_structure_id, to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'),x_category);
97
98 IF (( ret_code is not null) and (ret_code <> 0 )) THEN
99 X_category_id_v := ret_code;
100 ELSE
101 X_category_id_v := null;
102 END IF;
103
104
105 RETURN X_category_id_v;
106
107 EXCEPTION
108 When no_data_found then
109 RETURN NULL;
110 When others then
111 po_message_s.sql_error('derive_category_id',X_progress, sqlcode);
112 raise;
113
114 END derive_category_id;
115
116 /*================================================================
117
118 FUNCTION NAME: get_default_purch_category_id()
119
120 ==================================================================*/
121 FUNCTION get_default_purch_category_id return NUMBER IS
122
123 X_progress varchar2(3) := NULL;
124 X_category_id number;
125
126 BEGIN
127
128 X_progress := '010';
129 /* get the default category_id */
130
131 SELECT mcs.default_category_id
132 INTO X_category_id
133 FROM mtl_category_sets mcs,
134 mtl_default_category_sets mdcs
135 WHERE mdcs.functional_area_id = 2 /* 2= purchasing */
136 AND mdcs.category_set_id = mcs.category_set_id;
137
138 RETURN X_category_id;
139
140 EXCEPTION
141 When no_data_found then
142 RETURN NULL;
143 When others then
144 po_message_s.sql_error('get_default_purch_category_id',
145 X_progress, sqlcode);
146 raise;
147
148 END get_default_purch_category_id;
149
150 /*================================================================
151
152 FUNCTION NAME: get_default_purch_category_id()
153
154 ==================================================================*/
155 FUNCTION val_category_id(x_category_id IN NUMBER)
156 RETURN BOOLEAN IS
157
158 x_progress varchar2(3) := null;
159 x_temp binary_integer := 0;
160 -- Bug# 3129778
161 x_validate_flag mtl_category_sets_v.validate_flag%TYPE;
162
163 BEGIN
164 x_progress := '010';
165
166 /** validate and make sure x_category_id is a valid category
167 within the default category set for Purchasing. **/
168 /* Bug# 3129778, We will check if the Purchasing Category set
169 has 'Validate flag' ON. If Yes, we will validate the Category
170 to exist in the 'Valid Category List'. If No, we will just validate
171 if the category is Enable and Active */
172
173 BEGIN
174 SELECT validate_flag
175 INTO x_validate_flag
176 FROM mtl_category_sets_v
177 WHERE category_set_id=
178 (SELECT category_set_id
179 FROM mtl_default_category_sets
180 WHERE functional_area_id = 2 /*** purchasing ***/
181 ) ;
182 EXCEPTION
183 when others then
184 NULL;
185 END;
186
187 IF x_validate_flag = 'Y' then
188
189 SELECT count(*)
190 INTO x_temp
191 FROM mtl_categories_vl mcs,
192 mtl_category_set_valid_cats mcsvc
193 WHERE mcs.category_id = x_category_id
194 AND mcs.category_id = mcsvc.category_id
195 AND mcsvc.category_set_id =
196 (SELECT category_set_id
197 FROM mtl_default_category_sets
198 WHERE functional_area_id = 2 /*** purchasing ***/
199 )
200 AND sysdate < nvl(mcs.disable_date, sysdate+1)
201 AND mcs.enabled_flag = 'Y';
202
203 IF x_temp = 0 THEN
204 RETURN FALSE; /* validation fails */
205 ELSE
206 RETURN TRUE; /* validation succeeds */
207 END IF;
208
209 ELSE
210
211 SELECT count(*)
212 INTO x_temp
213 FROM mtl_categories_vl mcs
214 WHERE mcs.category_id = x_category_id
215 AND sysdate < nvl(mcs.disable_date, sysdate+1)
216 AND mcs.enabled_flag = 'Y';
217
218 IF x_temp = 0 THEN
219 RETURN FALSE; /* validation fails */
220 ELSE
221 RETURN TRUE; /* validation succeeds */
222 END IF;
223
224 END IF;
225
226 EXCEPTION
227 WHEN others THEN
228 po_message_s.sql_error
229 ('val_category_id', x_progress, sqlcode);
230 raise;
231 END val_category_id;
232
233 END PO_CATEGORIES_SV1;
[Home] [Help]