1 PACKAGE pv_check_match_pub AUTHID CURRENT_USER AS
2 /* $Header: pvxvcmps.pls 120.1 2005/12/20 04:39:31 rdsharma noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PV_CHECK_MATCH_PUB';
5
6 -- ---------------------------------------------------------
7 -- Attribute ID's
8 -- ---------------------------------------------------------
9 g_a_FUE CONSTANT NUMBER := 1;
10 g_a_Customer_Name CONSTANT NUMBER := 500;
11 g_a_Customer_Address_Specified CONSTANT NUMBER := 501;
12 g_a_Customer_Annual_Revenue CONSTANT NUMBER := 502;
13 g_a_Primary_Contact_Specified CONSTANT NUMBER := 503;
14 g_a_Contact_Role CONSTANT NUMBER := 504;
15 g_a_Purchase_Timeframe CONSTANT NUMBER := 505;
16 g_a_Budget_Status CONSTANT NUMBER := 506;
17 g_a_Lead_Score CONSTANT NUMBER := 507;
18 g_a_Lead_Status CONSTANT NUMBER := 508;
19 g_a_Total_Budget CONSTANT NUMBER := 509;
20 g_a_Product_Interest CONSTANT NUMBER := 510;
21 g_a_Purchase_Quantity_Product CONSTANT NUMBER := 511;
22 g_a_Purchase_Amount_Product CONSTANT NUMBER := 512;
23 g_a_Response_Channel CONSTANT NUMBER := 513;
24 g_a_Project_ CONSTANT NUMBER := 514;
25 g_a_Country_ CONSTANT NUMBER := 4;
26 g_a_Campaign_ CONSTANT NUMBER := 16;
27 g_a_Qualify_Flag CONSTANT NUMBER := 517;
28 g_a_Lead_Rating CONSTANT NUMBER := 518;
29 g_a_Sales_Channel CONSTANT NUMBER := 519;
30 g_a_Creation_Date CONSTANT NUMBER := 520;
31 g_a_PSS CONSTANT NUMBER := 9;
32 g_a_Total_Pur_Amt_Product CONSTANT NUMBER := 522;
33 g_a_Total_Pur_Amt_Solutions CONSTANT NUMBER := 523;
34 g_a_Purchase_Qty_Solutions CONSTANT NUMBER := 524;
35 g_a_Purchase_Amount_Solutions CONSTANT NUMBER := 525;
36 g_a_State_ CONSTANT NUMBER := 20;
37 g_a_Opportunity_Status CONSTANT NUMBER := 600;
38 g_a_Sales_Stage CONSTANT NUMBER := 601;
39 g_a_Close_Date CONSTANT NUMBER := 602;
40 g_a_Offer_ CONSTANT NUMBER := 603;
41 g_a_Win_Probability CONSTANT NUMBER := 604;
42 g_a_Sales_Methodology CONSTANT NUMBER := 605;
43 g_a_Customer_Account_Type CONSTANT NUMBER := 606;
44 g_a_Routing_Status CONSTANT NUMBER := 607;
45 g_a_Customer_Category CONSTANT NUMBER := 608;
46 --g_a_Additional_Channel_Offer CONSTANT NUMBER := 9;
47 g_a_Classification_ CONSTANT NUMBER := 610;
48 g_a_Functional_Expertise CONSTANT NUMBER := 700;
49 g_a_Partner_Type CONSTANT NUMBER := 3;
50 g_a_Industry_ CONSTANT NUMBER := 5;
51 g_a_Geographic_Coverage CONSTANT NUMBER := 8;
52 g_a_Partnership_Activity CONSTANT NUMBER := 10;
53 g_a_Partner_Name CONSTANT NUMBER := 11;
54 g_a_Capacity_Rating CONSTANT NUMBER := 18;
55 g_a_Partner_Level CONSTANT NUMBER := 19;
56 g_a_Partner_Enrollment_Date CONSTANT NUMBER := 708;
57 g_a_Total_Vendor_Assigned_Opp CONSTANT NUMBER := 709;
58 g_a_Total_PT_Created CONSTANT NUMBER := 710;
59 g_a_VAD_relationship_exists CONSTANT NUMBER := 711;
60
61 g_a_Area_Code CONSTANT NUMBER := 532;
62 g_a_County CONSTANT NUMBER := 533;
63 g_a_Province CONSTANT NUMBER := 534;
64 g_a_City CONSTANT NUMBER := 535;
65 g_a_Postal_Code CONSTANT NUMBER := 536;
66 g_a_Primary_Email_Specified CONSTANT NUMBER := 537;
67 g_a_customer_name CONSTANT NUMBER := 565;
68 g_a_email_address CONSTANT NUMBER := 582;
69 g_a_email_domain CONSTANT NUMBER := 568;
70 g_a_primary_contact_name CONSTANT NUMBER := 566;
71 g_a_primary_contact_job_title CONSTANT NUMBER := 567;
72 g_a_all CONSTANT NUMBER := 571;
73 g_a_lead_note_type CONSTANT NUMBER := 572;
74 g_a_created_within CONSTANT NUMBER := 573;
75 g_a_interaction_score CONSTANT NUMBER := 574;
76 g_a_Campaign_Setup_Type CONSTANT NUMBER := 575;
77 g_a_business_event_type CONSTANT NUMBER := 801;
78
79
80 /*
81 g_FUE_attr_id CONSTANT NUMBER := 510;
82 --g_FUE_attr_id CONSTANT NUMBER := 1;
83 g_purchase_amount_attr_id CONSTANT NUMBER := 512;
84 g_purchase_quantity_attr_id CONSTANT NUMBER := 511;
85 g_pss_attr_id CONSTANT NUMBER := 521;
86 g_pss_amount_attr_id CONSTANT NUMBER := 525;
87 g_pss_quantity_attr_id CONSTANT NUMBER := 524;
88 */
89 -- ---------------------------------------------------------
90 -- This is a dummy attribute ID, which is not a real
91 -- attribute in the seeded database.
92 -- ---------------------------------------------------------
93 g_dummy_attr_id CONSTANT NUMBER := 0;
94
95 -- ---------------------------------------------------------
96 -- Currency Conversion Status Flag. Currency_Conversion will
97 -- return this number if there is anything wrong during the
98 -- conversion.
99 -- ---------------------------------------------------------
100 g_currency_conversion_error CONSTANT NUMBER := FND_API.G_MISS_NUM;
101
102 -- ---------------------------------------------------------
103 -- Operators
104 -- ---------------------------------------------------------
105 g_equal CONSTANT VARCHAR2(30) := 'EQUALS';
106 g_not_equal CONSTANT VARCHAR2(30) := 'NOT_EQUALS';
107 g_greater_than CONSTANT VARCHAR2(30) := 'GREATER_THAN';
108 g_greater_than_equal CONSTANT VARCHAR2(30) := 'GREATER_THAN_OR_EQUALS';
109 g_less_than CONSTANT VARCHAR2(30) := 'LESS_THAN';
110 g_less_than_equal CONSTANT VARCHAR2(30) := 'LESS_THAN_OR_EQUALS';
111 g_is_null CONSTANT VARCHAR2(30) := 'IS_NULL';
112 g_is_not_null CONSTANT VARCHAR2(30) := 'IS_NOT_NULL';
113 g_between CONSTANT VARCHAR2(30) := 'BETWEEN';
114 g_begins_with CONSTANT VARCHAR2(30) := 'BEGINS_WITH';
115 g_ends_with CONSTANT VARCHAR2(30) := 'ENDS_WITH';
116 g_contains CONSTANT VARCHAR2(30) := 'CONTAINS';
117 g_not_contains CONSTANT VARCHAR2(30) := 'NOT_CONTAINS';
118
119
120 -- -----------------------------------------------------------------
121 -- Used in currency_conversion.
122 --
123 -- g_period_set_name = FND_PROFILE.Value('AS_FORECAST_CALENDAR')
124 -- g_period_type = FND_PROFILE.Value('AS_DEFAULT_PERIOD_TYPE');
125 -- -----------------------------------------------------------------
126 g_period_set_name VARCHAR2(100);
127 g_period_type VARCHAR2(100);
128 g_display_message BOOLEAN := TRUE;
129
130
131 -- ---------------------------------------------------------
132 -- This delimiter is used to separate tokens within an
133 -- attribute value for attributes with multiple tokens like
134 -- currency and purchase amount attributes.
135 --
136 -- For example,
137 -- 1000000:::USD:::20020103145608
138 -- ---------------------------------------------------------
139 g_token_delimiter CONSTANT VARCHAR2(3) := ':::';
140
141
142 TYPE r_entity_attr_value IS RECORD (
143 attribute_value VARCHAR2(4000),
144 return_type VARCHAR2(30)
145 );
146
147 TYPE t_entity_attr_value IS TABLE OF r_entity_attr_value
148 INDEX BY BINARY_INTEGER;
149
150 TYPE r_input_filter IS RECORD (
151 attribute_id NUMBER,
152 attribute_value VARCHAR2(4000)
153 );
154
155 TYPE t_input_filter IS TABLE OF r_input_filter
156 INDEX BY BINARY_INTEGER;
157
158
159
160 PROCEDURE Get_Entity_Attr_Values (
161 p_api_version_number IN NUMBER,
162 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
163 p_commit IN VARCHAR2 := FND_API.G_FALSE,
164 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
165 p_attribute_id IN NUMBER,
166 p_entity IN VARCHAR2,
167 p_entity_id IN NUMBER,
168 p_delimiter IN VARCHAR2,
169 p_expand_attr_flag IN VARCHAR2 := 'Y',
170 x_entity_attr_value IN OUT NOCOPY t_entity_attr_value,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2
174 );
175
176 FUNCTION Check_Match (
177 p_attribute_id IN NUMBER,
178 p_entity_attr_value IN VARCHAR2,
179 p_rule_attr_value IN VARCHAR2,
180 p_rule_to_attr_value IN VARCHAR2,
181 p_operator IN VARCHAR2,
182 p_input_filter IN t_input_filter,
183 p_delimiter IN VARCHAR2,
184 p_return_type IN VARCHAR2,
185 p_rule_currency_code IN VARCHAR2
186 )
187 RETURN BOOLEAN;
188
189 -- ------------------------------------------------------------------
190 -- Expose this to public.
191 -- ------------------------------------------------------------------
192 FUNCTION Check_Match (
193 p_attribute_id IN NUMBER,
194 p_entity IN VARCHAR2,
195 p_entity_id IN NUMBER,
196 p_rule_attr_value IN VARCHAR2,
197 p_rule_to_attr_value IN VARCHAR2,
198 p_operator IN VARCHAR2,
199 p_input_filter IN t_input_filter,
200 p_delimiter IN VARCHAR2,
201 p_rule_currency_code IN VARCHAR2,
202 x_entity_attr_value IN OUT NOCOPY t_entity_attr_value
203 )
204 RETURN BOOLEAN;
205
206
207 PROCEDURE Retrieve_Input_Filter (
208 p_api_version_number IN NUMBER,
209 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
210 p_commit IN VARCHAR2 := FND_API.G_FALSE,
211 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
212 p_process_rule_id IN NUMBER,
213 p_delimiter IN VARCHAR2 := '+++',
214 x_input_filter IN OUT NOCOPY t_input_filter,
215 x_return_status OUT NOCOPY VARCHAR2,
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2
218 );
219
220 -- -----------------------------------------------------------------------------------
221 -- Currency_Conversion
222 --
223 -- p_entity_attr_value comes in the form of 3 tokens of
224 -- <currency_amount>:::<currency_code>:::<currency_conversion_date>
225 --
226 -- where <currency_conversion_date> has the following date format:
227 --
228 -- yyyymmddhh24miss
229 --
230 -- e.g.
231 -- 100000:::USD:::20020103145100
232 -- -----------------------------------------------------------------------------------
233 FUNCTION Currency_Conversion(
234 p_entity_attr_value IN VARCHAR2,
235 p_rule_currency_code IN VARCHAR2,
236 p_no_exception_flag IN VARCHAR2 := 'N'
237
238 )
239 RETURN NUMBER;
240
241
242 -- -----------------------------------------------------------------------------------
243 -- Currency_Conversion
244 --
245 -- This function does not take 3 tokens as the one above. This assumes that the token
246 -- string has already been parsed into p_amount, p_currency_code, and
247 -- p_currency_conversion_date.
248 -- -----------------------------------------------------------------------------------
249 FUNCTION Currency_Conversion(
250 p_amount IN NUMBER,
251 p_currency_code IN VARCHAR2,
252 p_currency_conversion_date IN DATE := SYSDATE,
253 p_rule_currency_code IN VARCHAR2,
254 p_no_exception_flag IN VARCHAR2 := 'N'
255 )
256 RETURN NUMBER;
257
258
259 -- -----------------------------------------------------------------------------------
260 -- Currency_Conversion
261 -- -----------------------------------------------------------------------------------
262 FUNCTION Currency_Conversion(
263 p_entity_attr_value IN VARCHAR2
264 )
265 RETURN NUMBER;
266
267
268 -- -----------------------------------------------------------------------------------
269 -- Given a string that contains attribute values separated by p_delimiter, retrieve
270 -- the n th (p_index) token in the string.
271 -- e.g.
272 -- p_attr_value_string = '+++abc+++def+++'; p_delimiter = '+++'; p_index = 2.
273 -- This function will return 'def'.
274 -- If p_index is out of bound, return g_out_of_bound.
275 --
276 -- There are 2 types (p_input_type) of p_attr_value_string:
277 -- (1) +++abc+++def+++ ==> 'STD TOKEN'
278 -- (2) 1000000:::USD:::20011225164500 ==> 'In Token'
279 --
280 -- When the p_input_type is 'In Token', we will pad p_attr_value_string with
281 -- p_delimiter like the following:
282 -- :::1000000:::USD:::20011225164500:::
283 -- -----------------------------------------------------------------------------------
284 FUNCTION Retrieve_Token(
285 p_delimiter VARCHAR2,
286 p_attr_value_string VARCHAR2,
287 p_input_type VARCHAR2,
288 p_index NUMBER
289 )
290 RETURN VARCHAR2;
291
295 --
292 -- -----------------------------------------------------------------------------------
293 -- Given a string, p_string, search for the number of tokens separated by the
294 -- delimiter, p_delimiter.
296 -- e.g.
297 -- p_string = '+++abc+++def+++ghi+++'
298 -- p_delimiter = '+++'
299 -- The function will return 3 because there are 3 tokens in the string.
300 -- -----------------------------------------------------------------------------------
301 FUNCTION Get_Num_Of_Tokens (
302 p_delimiter VARCHAR2,
303 p_string VARCHAR2
304 )
305 RETURN NUMBER;
306
307
308 -- -----------------------------------------------------------------------------------
309 -- For given a Partner_Id, Attribute_id, it returns value for currency related attribute
310 --
311 -- -----------------------------------------------------------------------------------
312 FUNCTION get_attr_curr(
313 p_partner_id IN NUMBER ,
314 p_attribute_id IN NUMBER)
315 RETURN VARCHAR2;
316
317 -- -----------------------------------------------------------------------------------
318 -- For given a Partner_Id, Attribute_id, it returns value for count related attribute
319 --
320 -- -----------------------------------------------------------------------------------
321 FUNCTION get_attr_cnt(
322 p_partner_id IN NUMBER ,
323 p_attribute_id IN NUMBER)
324 RETURN NUMBER;
325
326
327 -- -----------------------------------------------------------------------------------
328 -- For given a Partner_Id, Attribute_id, it returns value for rate related attribute
329 --
330 -- -----------------------------------------------------------------------------------
331 FUNCTION get_attr_rate(
332 p_partner_id IN NUMBER ,
333 p_attribute_id IN NUMBER)
334 RETURN NUMBER;
335
336 -- -----------------------------------------------------------------------------------
337 -- For a given ATTRIBUTE_ID, and ATTR_CODE_ID it returns description of the attribute
338 -- code_id. E.g. It's mainly used for Partner_Level attribute only.
339 --
340 -- -----------------------------------------------------------------------------------
341 FUNCTION get_partner_level_desc(
342 p_attribute_id IN NUMBER,
343 p_attr_code_id IN NUMBER
344 )
345 RETURN VARCHAR2;
346
347 -- -----------------------------------------------------------------------------------
348 -- For a given a ATTRIBUTE_ID, and ATTR_CODE, it returns description of the attribute
349 -- code. E.g. It's mainly used for all attributes other than Partner_Level code.
350 --
351 -- -----------------------------------------------------------------------------------
352 FUNCTION get_attr_code_desc(
353 p_attribute_id IN NUMBER,
354 p_attr_code IN VARCHAR2
355 )
356 RETURN VARCHAR2;
357
358 END pv_check_match_pub;
[Home] [Help]