1 PACKAGE QP_UTIL AS
2 /* $Header: QPXUTILS.pls 120.9.12000000.1 2007/01/17 22:33:40 appldev ship $ */
3
4 TYPE v_segs_upg is record
5 (
6 context_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE,
7 segment_name fnd_descr_flex_col_usage_vl.application_column_name%TYPE,
8 sequence fnd_descr_flex_col_usage_vl.column_seq_num%TYPE,
9 datatype varchar2(1)
10 );
11
12 TYPE v_segs_upg_tab is table of v_segs_upg index by binary_integer;
13
14
15 G_PRODUCT_STATUS VARCHAR2(30) := FND_API.G_MISS_CHAR;
16 G_VALIDATE_FLAG boolean :=TRUE;
17 G_ORGANIZATION_ID NUMBER;
18
19 FUNCTION Get_Schema RETURN VARCHAR2;
20
21 --BUG#5523416 RAVI
22 FUNCTION Is_Valid_Category(p_item_id IN NUMBER) RETURN VARCHAR2;
23
24 FUNCTION Attrmgr_Installed RETURN VARCHAR2;
25
26 PROCEDURE Get_Sourcing_Info(p_context_type IN VARCHAR2,
27 p_context IN VARCHAR2,
28 p_attribute IN VARCHAR2,
29 x_sourcing_enabled OUT NOCOPY VARCHAR2,
30 x_sourcing_status OUT NOCOPY VARCHAR2,
31 x_sourcing_method OUT NOCOPY VARCHAR2);
32
33 FUNCTION Get_Context(p_flexfield_name IN VARCHAR2,
34 p_context IN VARCHAR2) RETURN VARCHAR2;
35
36 PROCEDURE Get_Context_Type(p_flexfield_name IN VARCHAR2,
37 p_context_name IN VARCHAR2,
38 x_context_type OUT NOCOPY VARCHAR2,
39 x_error_code OUT NOCOPY VARCHAR2);
40 PROCEDURE Get_Context_Attribute( p_attribute_code IN VARCHAR2,
41 x_context OUT NOCOPY VARCHAR2,
42 x_attribute_name OUT NOCOPY VARCHAR2
43 );
44 FUNCTION Is_PricingAttr( p_attribute_code IN VARCHAR2) RETURN VARCHAR2 ;
45
46 FUNCTION Is_qualifier( p_attribute_code IN VARCHAR2) RETURN VARCHAR2;
47
48 FUNCTION Get_cust_context RETURN VARCHAR2;
49 FUNCTION Get_sold_to_attrib RETURN VARCHAR2;
50 FUNCTION Get_cust_class_attrib RETURN VARCHAR2;
51 FUNCTION Get_site_use_attrib RETURN VARCHAR2;
52 FUNCTION Get_EntityValue(p_attribute_code IN VARCHAR2) RETURN NUMBER;
53 FUNCTION Get_entityname(p_entity_id IN NUMBER ) RETURN VARCHAR2;
54 FUNCTION Get_QP_Status
55 RETURN VARCHAR2;
56
57 PROCEDURE validate_qp_flexfield(flexfield_name IN VARCHAR2,
58 context IN VARCHAR2,
59 attribute IN VARCHAR2,
60 value IN VARCHAR2,
61 application_short_name IN VARCHAR2,
62 -- added by svdeshmu after the conference call among
63 -- jay/ravi/renga/kannan/swati/nitin on april 10
64 context_flag OUT NOCOPY VARCHAR2,
65 attribute_flag OUT NOCOPY VARCHAR2,
66 value_flag OUT NOCOPY VARCHAR2,
67 datatype OUT NOCOPY VARCHAR2,
68 precedence OUT NOCOPY VARCHAR2,
69 error_code OUT NOCOPY NUMBER ,
70 check_enabled IN BOOLEAN := TRUE);
71
72 PROCEDURE validate_context_code(p_flexfield_name IN VARCHAR2,
73 p_application_short_name IN VARCHAR2,
74 p_context_name IN VARCHAR2,
75 p_error_code OUT NOCOPY NUMBER);
76
77 PROCEDURE validate_attribute_name(p_application_short_name IN VARCHAR2,
78 p_flexfield_name IN VARCHAR2,
79 p_context_name IN VARCHAR2,
80 p_attribute_name IN VARCHAR2,
81 p_error_code OUT NOCOPY NUMBER);
82 PROCEDURE get_valueset_id(p_flexfield_name In varchar2,
83 p_context IN VARCHAR2 ,
84 p_seg IN VARCHAR2 ,
85 x_vsid OUT NOCOPY number,
86 x_format_type OUT NOCOPY varchar2,
87 x_validation_type OUT NOCOPY VARCHAR2
88 );
89 PROCEDURE Get_Prod_Flex_Properties(pric_attribute_context IN VARCHAR2,
90 pric_attribute IN VARCHAR2,
91 pric_attr_value IN VARCHAR2,
92 x_datatype OUT NOCOPY VARCHAR2,
93 x_precedence OUT NOCOPY NUMBER,
94 x_error_code OUT NOCOPY NUMBER);
95
96 PROCEDURE Get_Qual_Flex_Properties(qual_attribute_context IN VARCHAR2,
97 qual_attribute IN VARCHAR2,
98 qual_attr_value IN VARCHAR2,
99 x_datatype OUT NOCOPY VARCHAR2,
100 x_precedence OUT NOCOPY NUMBER,
101 x_error_code OUT NOCOPY NUMBER);
102
103 FUNCTION get_attribute_name(p_application_short_name IN VARCHAR2,
104 p_flexfield_name IN VARCHAR2,
105 p_context_name IN VARCHAR2,
106 p_attribute_name IN VARCHAR2) RETURN VARCHAR2;
107
108
109
110
111
112 PROCEDURE QP_Upgrade_Context( p_product IN VARCHAR2
113 ,p_new_product IN VARCHAR2
114 ,p_flexfield_name IN VARCHAR2
115 ,p_new_flexfield_name IN VARCHAR2);
116
117
118 -- ===========================================================================
119 -- Function value_exists_in_table
120 -- funtion type Public
121 -- Returns BOOLEAN
122 -- out parameters : x_Value
123 -- DESCRIPTION
124 -- Searches for value if it exist by building dynamic query stmt when when va
125 --lueset validation type is F
126 -- the list populated by get_valueset call.
127 -- returns the value in the out variable
128 -- ===========================================================================
129
130
131 FUNCTION value_exists_in_table(p_table_r fnd_vset.table_r,
132 p_value VARCHAR2,
133 x_id OUT NOCOPY VARCHAR2,
134 x_value OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
135
136 -- ===========================================================================
137
138
139
140 -- ===========================================================================
141 -- Overloaded Function value_exists_in_table
142 -- funtion type Public
143 -- Returns BOOLEAN
144 -- out parameters : x_value, x_meaning
145 -- DESCRIPTION
146 -- Searches for value if it exist by building dynamic query stmt when when va
147 --lueset validation type is F
148 -- the list populated by get_valueset call.
149 -- returns the value,meaning in the out variable
150 -- ===========================================================================
151
152
153 FUNCTION value_exists_in_table(p_table_r fnd_vset.table_r,
154 p_value VARCHAR2,
155 x_id OUT NOCOPY VARCHAR2,
156 x_value OUT NOCOPY VARCHAR2,
157 x_meaning OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
158
159 -- ===========================================================================
160
161
162 FUNCTION validate_num_date(p_datatype in varchar2
163 ,p_value in varchar2
164 )return number;
165
166
167 --==========================================================================
168 -- Function to check if a value exists in the given valueset
169 --==========================================================================
170 FUNCTION value_exists(p_vsid IN NUMBER,p_value IN VARCHAR2) RETURN BOOLEAN;
171
172
173 PROCEDURE Log_Error (p_id1 VARCHAR2,
174 p_id2 VARCHAR2 :=null,
175 p_id3 VARCHAR2 :=null,
176 p_id4 VARCHAR2 :=null,
177 p_id5 VARCHAR2 :=null,
178 p_id6 VARCHAR2 :=null,
179 p_id7 VARCHAR2 :=null,
180 p_id8 VARCHAR2 :=null,
181 p_error_type VARCHAR2,
182 p_error_desc VARCHAR2,
183 p_error_module VARCHAR2);
184
185 PROCEDURE get_segs_for_flex( flexfield_name IN VARCHAR2,
186 application_short_name IN VARCHAR2,
187 x_segs_upg_t OUT NOCOPY v_segs_upg_tab,
188 error_code OUT NOCOPY number);
189
190 PROCEDURE get_segs_flex_precedence(p_segs_upg_t IN v_segs_upg_tab,
191 p_context IN VARCHAR2,
192 p_attribute IN VARCHAR2,
193 x_precedence OUT NOCOPY NUMBER,
194 x_datatype OUT NOCOPY VARCHAR2);
195
196 PROCEDURE GET_VALUESET_ID_R(P_FLEXFIELD_NAME IN VARCHAR2,
197 P_CONTEXT IN VARCHAR2 ,
198 P_SEG IN VARCHAR2 ,
199 X_VSID OUT NOCOPY NUMBER,
200 X_FORMAT_TYPE OUT NOCOPY VARCHAR2,
201 X_VALIDATION_TYPE OUT NOCOPY VARCHAR2
202 );
203
204
205 FUNCTION Get_Attribute_Value(p_FlexField_Name IN VARCHAR2
206 ,p_Context_Name IN VARCHAR2
207 ,p_segment_name IN VARCHAR2
208 ,p_attr_value IN VARCHAR2
209 ,p_comparison_operator_code IN VARCHAR2 := NULL
210 ) RETURN VARCHAR2 ;
211
212 FUNCTION Get_Attribute_Value_Meaning(p_FlexField_Name IN VARCHAR2
213 ,p_Context_Name IN VARCHAR2
214 ,p_segment_name IN VARCHAR2
215 ,p_attr_value IN VARCHAR2
216 ,p_comparison_operator_code IN VARCHAR2 := NULL
217 ) RETURN VARCHAR2 ;
218
219 FUNCTION Get_Salesrep(p_salesrep_id IN NUMBER) RETURN VARCHAR2;
220
221 FUNCTION Get_Term(p_term_id IN NUMBER) RETURN VARCHAR2;
222
223 PROCEDURE CORRECT_ACTIVE_DATES(p_active_date_first_type IN OUT NOCOPY VARCHAR2,
224 p_start_date_active_first IN OUT NOCOPY DATE,
225 p_end_date_active_first IN OUT NOCOPY DATE,
226 p_active_date_second_type IN OUT NOCOPY VARCHAR2,
227 p_start_date_active_second IN OUT NOCOPY DATE,
228 p_end_date_active_second IN OUT NOCOPY DATE);
229
230 -- mkarya for bug 1728764, Prevent update of Trade Management Data in QP
231 -- New procedure created
232 PROCEDURE Check_Source_System_Code( p_list_header_id IN qp_list_headers_b.list_header_id%type
233 ,p_list_line_id IN qp_list_lines.list_line_id%type
234 ,x_return_status OUT NOCOPY VARCHAR2);
235
236 PROCEDURE Get_Attribute_Code(p_FlexField_Name IN VARCHAR2,
237 p_Context_Name IN VARCHAR2,
238 p_attribute IN VARCHAR2,
239 x_attribute_code OUT NOCOPY VARCHAR2,
240 x_segment_name OUT NOCOPY VARCHAR2);
241
242 FUNCTION Get_Segment_Level(p_list_header_id IN NUMBER
243 ,p_Context IN VARCHAR2
244 ,p_attribute IN VARCHAR2
245 ) RETURN VARCHAR2 ;
246
247 TYPE create_context_out_rec IS RECORD
248 (
249 context_code VARCHAR2(30),
250 context_name VARCHAR2(240)
251 );
252
253 TYPE create_context_out_tbl IS TABLE OF create_context_out_rec
254 INDEX BY BINARY_INTEGER;
255
256 TYPE create_attribute_out_rec IS RECORD
257 (
258 segment_mapping_column VARCHAR2(30),
259 segment_name VARCHAR2(240),
260 segment_code VARCHAR2(30),
261 precedence NUMBER,
262 valueset_id NUMBER
263 );
264
265 TYPE create_attribute_out_tbl IS TABLE OF create_attribute_out_rec
266 INDEX BY BINARY_INTEGER;
267
268 PROCEDURE Web_Create_Context_Lov(
269 p_field_context IN VARCHAR2 DEFAULT NULL,
270 p_context_type IN VARCHAR2 DEFAULT NULL,
271 p_check_enabled IN VARCHAR2 DEFAULT 'Y',
272 p_limits IN VARCHAR2 DEFAULT 'N',
273 p_list_line_type_code IN VARCHAR2 DEFAULT NULL,
274 x_return_status OUT NOCOPY VARCHAR2,
275 x_context_out_tbl OUT NOCOPY CREATE_CONTEXT_OUT_TBL);
276
277 PROCEDURE Web_Create_Attribute_Lov(
278 p_context_code IN VARCHAR2,
279 p_context_type IN VARCHAR2,
280 p_check_enabled IN VARCHAR2 DEFAULT 'Y',
281 p_limits IN VARCHAR2 DEFAULT 'N',
282 p_list_line_type_code IN VARCHAR2 DEFAULT NULL,
283 p_segment_level IN NUMBER DEFAULT 6,
284 p_field_context IN VARCHAR2 DEFAULT NULL,
285 x_return_status OUT NOCOPY VARCHAR2,
286 x_attribute_out_tbl OUT NOCOPY CREATE_ATTRIBUTE_OUT_TBL);
287
288
289 FUNCTION Is_Used(p_context_type IN VARCHAR2,
290 p_context_code IN VARCHAR2,
291 p_attribute_code IN VARCHAR2) RETURN VARCHAR2;
292
293 FUNCTION Get_Item_Validation_Org RETURN NUMBER;
294
295 --[prarasto] added for MOAC. Used by the engine to get the org id.
296 FUNCTION get_org_id RETURN NUMBER; --[prarasto] Changed function signature
297
298 --[prarasto] added for MOAC. Used by the engine for validating the org id
299 FUNCTION validate_org_id (p_org_id NUMBER) RETURN VARCHAR2;
300
301 --added for moac used by HTML PL/ML VOs
302 FUNCTION Get_OU_Name(p_org_id IN NUMBER) RETURN VARCHAR2;
303
304 --[sfiresto] added for Product Catalog, Used to get where clause for functional area
305 FUNCTION merge_fnarea_where_clause(p_where_clause IN VARCHAR2,
306 p_pte_code IN VARCHAR2 DEFAULT NULL,
307 p_ss_code IN VARCHAR2 DEFAULT NULL,
308 p_table_alias IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
309
310 --[sfiresto] added for Hierarchical Categories, used to get name/description for
311 -- a category id
312 PROCEDURE get_item_cat_info(p_item_id IN NUMBER,
313 p_item_pte IN VARCHAR2 DEFAULT NULL,
314 p_item_ss IN VARCHAR2 DEFAULT NULL,
315 x_item_name OUT NOCOPY VARCHAR2,
316 x_item_desc OUT NOCOPY VARCHAR2,
317 x_is_valid OUT NOCOPY BOOLEAN);
318
319 PROCEDURE get_pte_and_ss (p_list_header_id IN NUMBER,
320 x_pte_code OUT NOCOPY VARCHAR2,
321 x_source_system_code OUT NOCOPY VARCHAR2);
322
323 --[sfiresto] Returns TRUE if DB is a seed DB and user is DATAMERGE
324 FUNCTION is_seed_user RETURN BOOLEAN;
325
326 --Continuous Price Breaks
327 TYPE price_brk_attr_val IS RECORD(
328 price_break_header_id NUMBER,
329 list_line_no VARCHAR2(30),
330 product_attribute VARCHAR2(30),
331 product_attr_value VARCHAR2(240),
332 start_date_active DATE,
333 end_date_active DATE
334 );
335
336 TYPE price_brk_attr_val_tab is table of price_brk_attr_val index by binary_integer;
337
338 FUNCTION Validate_Item(p_product_context IN VARCHAR2,
339 p_product_attribute IN VARCHAR2,
340 p_product_attr_val IN VARCHAR2) RETURN VARCHAR2;
341
342 END QP_UTIL;
[Home] [Help]