[Home] [Help]
PACKAGE BODY: APPS.JTF_TERRITORY_CUSTOM
Source
1 Package Body JTF_TERRITORY_CUSTOM AS
2 /* $Header: jtfptrcb.pls 120.0 2005/06/02 18:20:49 appldev ship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERRITORY_CUSTOM
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- This package will contain all of the user defined function
10 -- that can be used in Territory assignment rules. This can be
11 -- used by customers to include complex processing as part of
12 -- Territory rules.
13 --
14 -- PROCEDURES:
15 -- (see below for specification)
16 --
17 -- NOTES
18 -- This package is publicly available for use
19 --
20 -- HISTORY
21 -- 06/09/99 VNEDUNGA Created
22 -- 04/10/00 VNEDUNGA Adding new validation routines
23 -- 05/08/00 VNEDUNGA Adding inventory ietem validation
24 -- 06/19/00 VNEDUNGA Adding check_partnership function call
25 -- 07/24/00 JDOCHERT Adding Chk_party_id function call
26 -- Adding Chk_comp_name_range function call
27 -- 08/18/00 JDOCHERT Changed functions that call AS and HZ schemas
28 -- to use dynamic SQl so that there are no dependencies
29 -- 09/05/00 JDOCHERT Updated SQl for check_account_Hierarchy to remove
30 -- extra ")" that was causing error
31 -- 12/12/00 EIHSU Added like condition for BETWEEN in comp_name_range comparison
32 -- as we want upper limit to be inclusive of all comp names like HIGH_VALUE_CHAR
33 -- 10/05/01 jdochert 2035543 bug fix
34 --
35 -- End of Comments
36 --
37
38 FUNCTION chk_party_id
39 ( p_Party_Id IN NUMBER,
40 p_Terr_Id IN NUMBER,
41 p_Terr_Qual_Id IN NUMBER) RETURN BOOLEAN
42 AS
43 l_dummy NUMBER;
44 --
45 BEGIN
46
47 --dbms_output.put_line('chk_party_id '|| p_party_id || ' ' || p_terr_id || ' ' || p_terr_qual_id);
48
49 Select 1
50 into l_dummy
51 from jtf_terr_values jtv
52 where jtv.terr_qual_id = p_Terr_Qual_Id and
53 ( ( jtv.COMPARISON_OPERATOR IN ('!=', '<>') and jtv.low_value_char_id <> p_party_id )
54 or ( jtv.COMPARISON_OPERATOR = '=' and jtv.low_value_char_id = p_party_id )
55 )
56 and rownum < 2;
57
58 RETURN TRUE;
59
60 Exception
61 When NO_DATA_FOUND Then
62 return FALSE;
63 --
64 End chk_party_id;
65
66
67 FUNCTION chk_comp_name_range
68 ( p_company_name IN VARCHAR2,
69 p_Terr_Id IN NUMBER,
70 p_Terr_Qual_Id IN NUMBER) RETURN BOOLEAN
71 AS
72 l_dummy NUMBER;
73 --
74 BEGIN
75
76 --dbms_output.put_line('chk_comp_name_range '|| p_company_name || ' ' || p_terr_id || ' ' || p_terr_qual_id);
77
78 Select 1
79 into l_dummy
80 from jtf_terr_values jtv
81 where jtv.terr_qual_id = p_Terr_Qual_Id and
82 ( ( jtv.COMPARISON_OPERATOR = '<' and jtv.low_value_char < p_company_name )
83 or ( jtv.COMPARISON_OPERATOR = '<=' and jtv.low_value_char <= p_company_name )
84 or ( jtv.COMPARISON_OPERATOR = '<>' and jtv.low_value_char <> p_company_name )
85 or ( jtv.COMPARISON_OPERATOR = '=' and jtv.low_value_char = p_company_name )
86 or ( jtv.COMPARISON_OPERATOR = '>' and jtv.low_value_char > p_company_name )
87 or ( jtv.COMPARISON_OPERATOR = '>=' and jtv.low_value_char >= p_company_name )
88 or ( UPPER(jtv.COMPARISON_OPERATOR) = 'BETWEEN'
89 and ( (p_company_name between jtv.low_value_char and jtv.high_value_char )
90 or (p_company_name like jtv.high_value_char )
91 )
92 )
93 or ( UPPER(jtv.COMPARISON_OPERATOR) = 'LIKE' and p_company_name like jtv.low_value_char )
94 or ( UPPER(jtv.COMPARISON_OPERATOR) = 'NOT BETWEEN' AND
95 (p_company_name not between jtv.low_value_char and jtv.high_value_char )
96 )
97 or ( UPPER(jtv.COMPARISON_OPERATOR) = 'NOT LIKE' and p_company_name not like jtv.low_value_char )
98 )
99 and rownum < 2;
100
101 RETURN TRUE;
102
103 Exception
104 When NO_DATA_FOUND Then
105 return FALSE;
106 --
107 End chk_comp_name_range;
108
109 FUNCTION check_account_Hierarchy
110 ( p_Hierarchy_Id IN NUMBER,
111 p_Terr_Id IN NUMBER,
112 p_Terr_Qual_Id IN NUMBER) RETURN BOOLEAN
113 AS
114
115 query_str VARCHAR2(1000);
116
117 l_dummy NUMBER;
118 BEGIN
119
120
121
122 query_str :=
123 ' SELECT 1 ' ||
124 ' FROM jtf_terr_values jtv ' ||
125 ' WHERE jtv.terr_qual_id = :p_terr_qual_id ' ||
126 ' AND rownum < 2 ' ||
127 ' AND :p_hierarchy_id IN ( ' ||
128 ' SELECT hpr.subject_id ' ||
129 ' FROM hz_party_relationships hpr ' ||
130 ' WHERE hpr.party_relationship_type = ''SUBSIDIARY_OF'' ' ||
131 ' START WITH hpr.object_id = jtv.low_value_char_id ' ||
132 ' CONNECT BY hpr.object_id = PRIOR hpr.subject_id ' ||
133 ' AND hpr.party_relationship_type = ''SUBSIDIARY_OF'' ) ';
134
135 EXECUTE IMMEDIATE query_str
136 INTO l_dummy
137 USING p_terr_qual_id, p_hierarchy_id ;
138
139 return TRUE;
140 Exception
141 When NO_DATA_FOUND Then
142 return FALSE;
143 --
144 END check_account_Hierarchy;
145
146
147 -- Function check whether the party is a partner
148 FUNCTION check_partnership
149 ( p_Partner_Id IN NUMBER,
150 p_Terr_Id IN NUMBER,
151 p_Terr_Qual_Id IN NUMBER) RETURN BOOLEAN
152 AS
153
154 query_str VARCHAR2(1000);
155 l_dummy NUMBER;
156
157 BEGIN
158
159 query_str :=
160 ' SELECT 1 ' ||
161 ' FROM jtf_terr_values jtv ' ||
162 ' , hz_party_relationships hpr ' ||
163 ' WHERE jtv.terr_qual_id = :p_terr_qual_id ' ||
164 ' AND hpr.subject_id = :p_partner_Id ' ||
165 ' AND hpr.object_id = jtv.low_value_char_id ' ||
166 ' AND hpr.party_relationship_type = ''PARTNER_OF'' ' ||
167 ' AND rownum < 2 ';
168
169 EXECUTE IMMEDIATE query_str
170 INTO l_dummy
171 USING p_terr_qual_id, p_partner_id ;
172
173 return TRUE;
174 Exception
175 When NO_DATA_FOUND Then
176 return FALSE;
177 --
178 END check_partnership;
179
180
181
182 -- Check account classification
183 FUNCTION check_account_classification
184 ( p_Party_Id IN NUMBER,
185 p_Terr_Id IN NUMBER,
186 p_Terr_Qual_Id IN NUMBER) RETURN BOOLEAN
187 AS
188 query_str VARCHAR2(1000);
189
190 l_dummy NUMBER;
191 BEGIN
192
193 query_str :=
194 ' SELECT 1 ' ||
195 ' FROM jtf_terr_values jtv ' ||
196 ' , as_interests ai ' ||
197 ' WHERE jtv.terr_qual_id = :p_terr_qual_id ' ||
198 ' AND ai.customer_id = :p_Party_Id ' ||
199 ' AND ai.interest_type_id = jtv.interest_type_id ' ||
200 ' AND ( NVL(jtv.primary_interest_code_id, -1) = -1 ' ||
201 ' OR ai.primary_interest_code_id = jtv.primary_interest_code_id ) ' ||
202 ' AND ( NVL(jtv.secondary_interest_code_id, -1) = -1 ' ||
203 ' OR ai.secondary_interest_code_id = jtv.secondary_interest_code_id ) ' ||
204 ' AND ai.interest_use_code = ''COMPANY_CLASSIFICATION'' ' ||
205 ' AND rownum < 2 ';
206
207 EXECUTE IMMEDIATE query_str
208 INTO l_dummy
209 USING p_terr_qual_id, p_party_id ;
210
211 return TRUE;
212 Exception
213 When NO_DATA_FOUND Then
214 return FALSE;
215 END check_account_classification;
216
217
218 -- Check Oppor classification rule
219 FUNCTION check_Oppor_classification
220 ( p_Lead_Id IN NUMBER,
221 p_Terr_Id IN NUMBER,
222 p_Terr_Qual_Id IN NUMBER) RETURN BOOLEAN
223 AS
224 query_str VARCHAR2(1000);
225
226 l_dummy NUMBER;
227 BEGIN
228
229 query_str :=
230 ' SELECT 1 ' ||
231 ' FROM jtf_terr_values jtv ' ||
232 ' , as_interests ai ' ||
233 ' WHERE jtv.terr_qual_id = :p_terr_qual_id ' ||
234 ' AND ai.lead_id = :p_Lead_Id ' ||
235 ' AND ai.interest_type_id = jtv.interest_type_id ' ||
236 ' AND ( NVL(jtv.primary_interest_code_id, -1) = -1 ' ||
237 ' OR ai.primary_interest_code_id = jtv.primary_interest_code_id ) '||
238 ' AND ( NVL(jtv.secondary_interest_code_id, -1) = -1 ' ||
239 ' OR ai.secondary_interest_code_id = jtv.secondary_interest_code_id ) ' ||
240 ' AND ai.interest_use_code = ''LEAD_CLASSIFICATION'' ' ||
241 ' AND rownum < 2 ';
242
243
244 EXECUTE IMMEDIATE query_str
245 INTO l_dummy
246 USING p_terr_qual_id, p_lead_id ;
247
248
249 return TRUE;
250 Exception
251 When NO_DATA_FOUND Then
252 return FALSE;
253
254 END check_Oppor_classification;
255
256
257 -- Check Opportunity Expected purchase
258 FUNCTION check_Oppor_Exp_Purchase
259 ( p_Lead_Id IN NUMBER,
260 p_Terr_Id IN NUMBER,
261 p_Terr_Qual_Id IN NUMBER) RETURN BOOLEAN
262 AS
263 query_str VARCHAR2(1000);
264
265 l_dummy NUMBER;
266 BEGIN
267
268 query_str :=
269 ' SELECT 1 ' ||
270 ' FROM jtf_terr_values jtv ' ||
271 ' , as_lead_lines al ' ||
272 ' WHERE jtv.terr_qual_id = :p_terr_qual_id ' ||
273 ' AND al.lead_id = :p_Lead_Id ' ||
274 ' AND al.interest_type_id = jtv.interest_type_id ' ||
275 ' AND ( NVL(jtv.primary_interest_code_id, -1) = -1 ' ||
276 ' OR al.primary_interest_code_id = jtv.primary_interest_code_id ) ' ||
277 ' AND ( NVL(jtv.secondary_interest_code_id, -1) = -1 ' ||
278 ' OR al.secondary_interest_code_id = jtv.secondary_interest_code_id) ' ||
279 ' AND rownum < 2 ';
280
281 EXECUTE IMMEDIATE query_str
282 INTO l_dummy
283 USING p_terr_qual_id, p_lead_id ;
284
285 return TRUE;
286 Exception
287 When NO_DATA_FOUND Then
288 return FALSE;
289 END check_Oppor_Exp_Purchase;
290
291 -- Check lead Expected purchase
292 FUNCTION check_Lead_Exp_Purchase
293 ( p_Sales_Lead_Id IN NUMBER,
294 p_Terr_Id IN NUMBER,
295 p_Terr_Qual_Id IN NUMBER) RETURN BOOLEAN
296 AS
297 query_str VARCHAR2(1000);
298
299 l_dummy NUMBER;
300 BEGIN
301
302 query_str :=
303 ' SELECT 1 ' ||
304 ' FROM jtf_terr_values jtv ' ||
305 ' , as_sales_lead_lines asl ' ||
306 ' WHERE jtv.terr_qual_id = :p_terr_qual_id ' ||
307 ' AND asl.sales_lead_id = :p_Sales_Lead_Id ' ||
308 ' AND asl.interest_type_id = jtv.interest_type_id ' ||
309 ' AND ( NVL(jtv.primary_interest_code_id, -1) = -1 ' ||
310 ' OR asl.primary_interest_code_id = jtv.primary_interest_code_id ) ' ||
311 ' AND ( NVL(jtv.secondary_interest_code_id, -1) = -1 ' ||
312 ' OR asl.secondary_interest_code_id = jtv.secondary_interest_code_id ) ' ||
313 ' AND rownum < 2 ';
314
315 EXECUTE IMMEDIATE query_str
316 INTO l_dummy
317 USING p_terr_qual_id, p_sales_lead_id ;
318
319 return TRUE;
320 EXCEPTION
321 WHEN OTHERS THEN
322 RETURN FALSE;
323 END check_Lead_Exp_Purchase;
324 --
325 -- Check inventory item
326 FUNCTION check_Inventory_Item
327 ( p_Lead_Id IN NUMBER,
328 p_Terr_Id IN NUMBER,
329 p_Terr_Qual_Id IN NUMBER) RETURN BOOLEAN
330 AS
331 query_str VARCHAR2(1000);
332
333 l_dummy NUMBER;
334
335 BEGIN
336
337 query_str :=
338 ' SELECT 1 ' ||
339 ' FROM jtf_terr_values jtv ' ||
340 ' , as_lead_lines al ' ||
341 ' WHERE jtv.terr_qual_id = :p_terr_qual_id ' ||
342 ' AND al.Lead_id = :p_Lead_Id ' ||
343 ' AND al.inventory_item_id = jtv.low_value_char_id ' ||
344 ' AND rownum < 2 ';
345
346 EXECUTE IMMEDIATE query_str
347 INTO l_dummy
348 USING p_terr_qual_id, p_lead_id ;
349
350 return TRUE;
351 Exception
352 When NO_DATA_FOUND Then
353 return FALSE;
354 END check_Inventory_Item;
355 --
356
357 END JTF_TERRITORY_CUSTOM;