[Home] [Help]
PACKAGE BODY: APPS.QP_MASS_MAINTAIN_UTIL
Source
1 PACKAGE BODY QP_MASS_MAINTAIN_UTIL AS
2 /* $Header: QPXMMUTB.pls 120.4.12010000.6 2008/10/21 21:24:56 rbadadar ship $ */
3
4 -- This Function returns 'N' if pte_code and source_system_code of passed list_header_id matches
5 -- with the corresponding profile values else returns 'Y'
6 -- Its confusing but has been done intentionally
7
8 FUNCTION Check_SS_PTE_Codes_Match (p_list_header_id IN NUMBER )
9 return VARCHAR2
10 IS
11 l_source_system_code VARCHAR2(30);
12 l_pte_code VARCHAR2(30);
13 l_saved_source_system_code VARCHAR2(30);
14 l_saved_pte_code VARCHAR2(30);
15
16 BEGIN
17
18 begin
19 select source_system_code, pte_code
20 into l_saved_source_system_code, l_saved_pte_code
21 from qp_list_headers_b
22 where list_header_id = p_list_header_id;
23 exception
24 when no_data_found then
25 return 'Y';
26 end;
27
28 FND_PROFILE.GET('QP_SOURCE_SYSTEM_CODE', l_source_system_code);
29 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY', l_pte_code);
30
31 IF l_saved_source_system_code = l_source_system_code AND
32 l_saved_pte_code = l_pte_code
33 THEN
34 return 'N';
35 ELSE
36 return 'Y';
37 END IF;
38
39 EXCEPTION
40 when others then
41 raise;
42
43 END Check_SS_PTE_Codes_Match;
44
45
46 -- This function return the product description
47 FUNCTION get_product_desc(p_product_attr_context varchar2,
48 p_product_attr varchar2,
49 p_product_attr_val varchar2)
50 RETURN VARCHAR2
51 is
52 l_product_desc varchar2(240) := null;
53 l_org_id number;
54 l_Inventory_Item_Id number;
55 l_category_id number;
56 l_segment_name VARCHAR2(240);
57 l_attribute_name VARCHAR2(240);
58 begin
59 if p_product_attr_context = 'ITEM' THEN
60 IF p_product_attr = 'PRICING_ATTRIBUTE1' THEN
61 l_Inventory_Item_Id := p_product_attr_val;
62 l_org_id := QP_UTIL.Get_Item_Validation_Org;
63
64 select Description
65 into l_product_desc
66 From Mtl_System_Items_Vl
67 Where Inventory_Item_Id = l_Inventory_Item_Id
68 And Organization_Id = l_Org_Id;
69
70 ELSIF p_product_attr = 'PRICING_ATTRIBUTE2' THEN
71 l_category_id := p_product_attr_val;
72 Select Description
73 Into l_product_desc
74 From Mtl_categories_vl
75 Where category_id = l_category_id;
76 ELSE
77 QP_UTIL.Get_Attribute_Code('QP_ATTR_DEFNS_PRICING',
78 p_product_attr_context,
79 p_product_attr,
80 l_attribute_name,
81 l_segment_name);
82 --dbms_output.put_line('l_segment_name = ' || l_segment_name);
83
84 l_product_desc := QP_UTIL.Get_Attribute_Value_Meaning('QP_ATTR_DEFNS_PRICING',
85 p_product_attr_context,
86 l_segment_name,
87 p_product_attr_val,
88 '=');
89 --dbms_output.put_line('l_product_desc = ' || l_product_desc);
90 END IF;
91 END IF; -- context is ITEM
92
93 return(l_product_desc);
94 exception
95 when no_data_found then
96 return(l_product_desc);
97
98 when others then
99 return(l_product_desc);
100 end get_product_desc;
101
102 Function Get_Product_UOM_Code ( p_list_line_id IN NUMBER,
103 p_product_attr_context IN VARCHAR2,
104 p_product_attr IN VARCHAR2 ) return VARCHAR2
105 IS
106 l_context varchar2(30);
107 l_pricing_attribute varchar2(240);
108 l_attribute varchar2(30);
109 l_uom_code varchar2(3);
110 begin
111
112 select product_uom_code
113 into l_uom_code
114 from qp_pricing_attributes
115 where list_line_id = p_list_line_id
116 and product_attribute_context = p_product_attr_context
117 and product_attribute = p_product_attr
118 and rownum = 1;
119
120 return l_uom_code;
121
122
123 end Get_Product_UOM_Code;
124
125 -- This procedure gets the select statement associated with context_code
126 -- and segment_code and return it.
127 PROCEDURE get_valueset_select(p_context_code IN VARCHAR2,
128 p_segment_code IN VARCHAR2,
129 x_select_stmt OUT NOCOPY VARCHAR2,
130 p_segment_map_col IN VARCHAR2 DEFAULT NULL, -- sfiresto fix
131 p_pte IN VARCHAR2 DEFAULT NULL, -- Hierarchical Categories
132 p_ss IN VARCHAR2 DEFAULT NULL) -- Hierarchical Categories
133 is
134 v_value_set_id NUMBER;
135 v_valueset_r fnd_vset.valueset_r;
136 v_valueset_dr fnd_vset.valueset_dr;
137 v_table_r fnd_vset.table_r;
138 v_select_clause varchar2(4000);
139 v_cols varchar2(3000);
140 v_fnarea_where_clause VARCHAR2(500);
141 l_appl_id NUMBER;
142
143 begin
144 if p_context_code = 'ITEM' AND p_segment_code = 'INVENTORY_ITEM_ID' then
145 /* v_select_clause := 'select INVENTORY_ITEM_ID attribute_id, SEGMENT1 attribute_name, nvl(DESCRIPTION, SEGMENT1) attribute_meaning from MTL_SYSTEM_ITEMS_B where ORGANIZATION_ID = QP_UTIL.Get_Item_Validation_Org';*/
146 -- fix for bug 6850999
147 v_select_clause := 'select INVENTORY_ITEM_ID attribute_id,concatenated_segments attribute_name,nvl(DESCRIPTION, concatenated_segments) attribute_meaning from MTL_SYSTEM_ITEMS_B_KFV where ORGANIZATION_ID = QP_UTIL.Get_Item_Validation_Org';
148 if p_pte = 'PO' then
149 v_select_clause := v_select_clause||' '||'AND PURCHASING_ITEM_FLAG = '||'''Y''';
150 end if;
151 x_select_stmt := 'Select * from (' || v_select_clause || ') AVVO';
152 return;
153 /*
154 * Commented out for Hierarchical Categories
155 *
156 elsif p_segment_code = 'ITEM_CATEGORY' then
157 l_appl_id := FND_GLOBAL.RESP_APPL_ID;
158 v_select_clause := 'select cat.CATEGORY_ID attribute_id, cat.CONCATENATED_SEGMENTS attribute_name, nvl(cat_vl.DESCRIPTION, cat.CONCATENATED_SEGMENTS) attribute_meaning ';
159 v_select_clause := v_select_clause || 'from mtl_categories_b_kfv cat, mtl_categories_vl cat_vl ';
160 v_select_clause := v_select_clause || 'where cat.category_id = cat_vl.category_id and cat.STRUCTURE_ID in ';
161 v_select_clause := v_select_clause || '(select structure_id from mtl_category_sets where category_set_id = ( select category_set_id from mtl_default_category_sets where functional_area_id = decode(' || l_appl_id || ',201,2,7)) and rownum < 2) ';
162 v_select_clause := v_select_clause || 'order by attribute_name';
163 x_select_stmt := 'Select * from (' || v_select_clause || ') AVVO';
164 return;
165 *
166 */
167 end if;
168
169 -- select nvl(SEEDED_VALUESET_ID, USER_VALUESET_ID)
170 select nvl(USER_VALUESET_ID, SEEDED_VALUESET_ID)
171 into v_value_set_id
172 from qp_segments_b seg, qp_prc_contexts_b cont, fnd_flex_value_sets vs
173 where cont.PRC_CONTEXT_CODE = p_context_code
174 and seg.SEGMENT_CODE = p_segment_code
175 and nvl(user_valueset_id, seeded_valueset_id) = vs.flex_value_set_id
176 and cont.PRC_CONTEXT_ID = seg.PRC_CONTEXT_ID
177 and seg.SEGMENT_MAPPING_COLUMN = nvl(p_segment_map_col, seg.SEGMENT_MAPPING_COLUMN) -- sfiresto fix
178 and vs.validation_type <> 'N'; -- sfiresto for bug 5136873, all value set types but 'NONE'
179
180 fnd_vset.get_valueset(v_value_set_id,v_valueset_r,v_valueset_dr);
181
182 v_table_r := v_valueset_r.table_info;
183
184 v_cols := nvl(v_table_r.ID_COLUMN_NAME, nvl(v_table_r.VALUE_COLUMN_NAME, 'null')) || ' attribute_id, '; -- sfiresto fix
185 v_cols := v_cols || nvl(v_table_r.VALUE_COLUMN_NAME, 'null') || ' attribute_name, ';
186 v_cols := v_cols || 'nvl(' || nvl(v_table_r.MEANING_COLUMN_NAME, 'null') || ', ' || nvl(v_table_r.VALUE_COLUMN_NAME, 'null') || ') attribute_meaning '; -- sfiresto fix
187
188 if v_table_r.TABLE_NAME is not null then
189
190 v_select_clause := 'select ';
191
192 -- Hierarchical Categories distinct clause addition (sfiresto)
193 if p_context_code = 'ITEM' AND p_segment_code = 'ITEM_CATEGORY' then
194 v_select_clause := v_select_clause || 'distinct ';
195 end if;
196
197 v_select_clause := v_select_clause || v_cols || ' from ' || v_table_r.TABLE_NAME;
198
199 else
200 v_select_clause := 'select flex_value attribute_id, flex_value_meaning attribute_name, nvl(description, flex_value_meaning) attribute_meaning FROM fnd_flex_values_vl WHERE flex_value_set_id = '|| v_value_set_id;
201 end if;
202
203 if v_table_r.WHERE_CLAUSE is not null then
204 -- Hierarchical Categories where clause addition (sfiresto)
205 if p_context_code = 'ITEM' AND p_segment_code = 'ITEM_CATEGORY' then
206 v_select_clause := v_select_clause || ' ' || QP_UTIL.merge_fnarea_where_clause(v_table_r.WHERE_CLAUSE, p_pte, p_ss);
207 else
208 v_select_clause := v_select_clause || ' ' || v_table_r.WHERE_CLAUSE;
209 end if;
210 end if;
211
212 x_select_stmt := 'Select * from (' || v_select_clause || ') AVVO';
213
214 exception
215 when no_data_found then
216 -- Commented out these two lines to allow for differentiation between an LOV that has no value set and an
217 -- invalid value set/no row value set
218 --
219 -- v_select_clause := 'select flex_value attribute_id, flex_value_meaning attribute_name, nvl(description, flex_value_meaning) attribute_meaning FROM fnd_flex_values_vl WHERE flex_value_set_id = 0';
220 -- x_select_stmt := 'Select * from (' || v_select_clause || ') AVVO';
221 x_select_stmt := null;
222 when others then
223 x_select_stmt := null;
224 raise;
225
226 end get_valueset_select;
227
228 END QP_MASS_MAINTAIN_UTIL;