1 PACKAGE BODY JTF_TTY_NA_WF AS
2 /* $Header: jtftrwab.pls 120.1 2005/06/24 00:25:40 jradhakr ship $ */
3
4 -- ---------------------------------------------------
5 -- Start of Comments
6 -- ---------------------------------------------------
7 -- PACKAGE NAME: JTF_TTY_NA_WF
8 -- ---------------------------------------------------
9 -- PURPOSE
10 -- Process Catch All territories and create/update named accounts
11 --
12 --
13 -- PROCEDURES:
14 -- (see below for specification)
15 --
16 -- NOTES
17 -- This package is for PRIVATE USE ONLY use
18 --
19 -- HISTORY
20 -- 12/13/02 ARPATEL Package Body Created
21 -- 02/25/03 ARPATEL Added DN_MAPPING_COMPLETE_FLAG='N' when creating record in JTF_TTY_TERR_GRP_ACCTS
22 -- 04/25/03 ARPATEL bug#2878006 fix
23 -- 06/02/03 ARPATEL bug#2987314 fix
24 -- End of Comments
25 --
26
27 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_TTY_NA_WF';
28
29 PROCEDURE AssignRep
30 /*******************************************************************************
31 ** Start of comments
32 ** Procedure : AssignRep
33 ** Description :
34 ** This API modifies the Named Account model to ensure that a lead/opportunity
35 ** does not fall into a catch all territory again.
36 **
37 ** INPUT: details of a Lead/Opportunity which has fallen into a catch-all
38 ** OUTPUT: a modified Named Account model to ensure that lead/opportunity
39 ** doesnt fall into catch all again.
40 **
41 ** PROCESS: 1) Find the Named Account whose keyword mapping rule
42 ** matches the lead/opportunity business name
43 ** AND whose state is the same as the lead/opportunity.
44 **
45 ** 2) If the lead/opportunity business name already exists as a named account then update
46 ** the mapping rule of the NA found in step 1) above to include the new postal code.
47 **
48 ** 3) If the lead/opportunity business name does not exist as a named account then create
49 ** a new Named account for this business name and create default mapping rules.
50 **
51 ** Parameters :
52 ** name direction type required?
53 ** ---- --------- ---- ---------
54 ** itemtype IN VARCHAR2 required
55 ** itemkey IN VARCHAR2 required
56 ** actid IN NUMBER required
57 ** funcmode IN VARCHAR2 required
58 ** resultout OUT VARCHAR2 required
59 **
60 ** Notes :
61 **
62 ** End of comments
63 ******************************************************************************/
64 ( itemtype IN VARCHAR2
65 , itemkey IN VARCHAR2
66 , actid IN NUMBER
67 , funcmode IN VARCHAR2
68 , resultout OUT NOCOPY VARCHAR2
69 )
70 IS
71
72 lp_api_name CONSTANT VARCHAR2(30) := 'AssignRep';
73 lp_api_version_number CONSTANT NUMBER := 1.0;
74 l_lead_state VARCHAR2(360);
75 l_lead_postal_code VARCHAR2(360);
76 l_lead_keyword VARCHAR2(360);
77 l_lead_terrgroup_Id NUMBER;
78 l_named_account_id NUMBER;
79 l_lead_access_Id NUMBER;
80 l_new_account_id NUMBER;
81 l_party_Id NUMBER;
82 query_str VARCHAR2(30000);
83
84 TYPE Ref_Cursor_Type IS REF CURSOR;
85 c_named_accounts Ref_Cursor_Type;
86 l_named_account_rec NA_Rec_Type;
87
88 CURSOR c_resources(c_named_account_id NUMBER, c_terr_group_id NUMBER) IS
89 select NAR.RSC_GROUP_ID, NAR.RESOURCE_ID, RLV.ROLE_ID, NAR.RSC_ROLE_CODE
90 from JTF_TTY_NAMED_ACCTS NA
91 , JTF_TTY_TERR_GRP_ACCTS TGA
92 , JTF_TTY_NAMED_ACCT_RSC NAR
93 , JTF_RS_ROLES_VL RLV
94 where NA.NAMED_ACCOUNT_ID = TGA.NAMED_ACCOUNT_ID
95 AND TGA.TERR_GROUP_ACCOUNT_ID = NAR.TERR_GROUP_ACCOUNT_ID
96 AND RLV.ROLE_CODE = NAR.RSC_ROLE_CODE
97 AND NA.NAMED_ACCOUNT_ID = c_named_account_id
98 AND TGA.TERR_GROUP_ID = c_terr_group_id ;
99
100
101 BEGIN
102 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('Beginning of JTF_TTY_NA_WF.AssignRep');
103
104 --get workflow attributes being passed in
105 l_lead_state := wf_engine.GetItemAttrText
106 ( itemtype => itemtype
107 , itemkey => itemkey
108 , aname => 'STATE'
109 );
110 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('l_lead_state is:'||l_lead_state);
111
112 l_lead_keyword := wf_engine.GetItemAttrText
113 ( itemtype => itemtype
114 , itemkey => itemkey
115 , aname => 'KEYWORD'
116 );
117 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('l_lead_keyword is:'||l_lead_keyword);
118
119 l_lead_postal_code := wf_engine.GetItemAttrText
120 ( itemtype => itemtype
121 , itemkey => itemkey
122 , aname => 'POSTAL_CODE'
123 );
124 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('l_lead_postal_code is:'||l_lead_postal_code);
125
126 l_lead_terrgroup_Id := wf_engine.GetItemAttrNumber
127 ( itemtype => itemtype
128 , itemkey => itemkey
129 , aname => 'TERRGROUP_ID'
130 );
131 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('l_lead_terrgroup_Id is:'||l_lead_terrgroup_Id);
132
133 l_lead_access_Id := wf_engine.GetItemAttrNumber
134 ( itemtype => itemtype
135 , itemkey => itemkey
136 , aname => 'ACCESS_ID'
137 );
138 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('l_lead_access_Id is:'||l_lead_access_Id);
139
140 l_party_Id := wf_engine.GetItemAttrNumber
141 ( itemtype => itemtype
142 , itemkey => itemkey
143 , aname => 'PARTY_ID'
144 );
145 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('l_party_Id is:'||l_party_Id);
146
147
148 query_str :=
149 'SELECT ILV.NAMED_ACCOUNT_ID, TGA.TERR_GROUP_ID ' ||
150 ', decode(ILV.site_type_code, ''BR'', 6, ''SL'', 6, ''HQ'', 5, ''DU'', 4, ''GU'', 3, ''ALL'', 2, ''UN'', 1) SITE_RANK ' ||
151 'FROM ( ' ||
152 'SELECT TNA.NAMED_ACCOUNT_ID, TNA.SITE_TYPE_CODE '||
153 ', case when (QM_1007.VALUE1_CHAR BETWEEN 1000 AND 2799) AND (NVL(QM_1007.VALUE2_CHAR,1000) BETWEEN 1000 AND 2799) then ''MA'' ' ||
154 ' when (QM_1007.VALUE1_CHAR BETWEEN 2800 AND 2999) AND (NVL(QM_1007.VALUE2_CHAR,2800) BETWEEN 2800 AND 2999) then ''RI'' ' ||
155 ' when (QM_1007.VALUE1_CHAR BETWEEN 3000 AND 3899) AND (NVL(QM_1007.VALUE2_CHAR,3000) BETWEEN 3000 AND 3899) then ''NH'' ' ||
156 ' when (QM_1007.VALUE1_CHAR BETWEEN 3900 AND 4999) AND (NVL(QM_1007.VALUE2_CHAR,3900) BETWEEN 3900 AND 4999) then ''ME'' ' ||
157 ' when (QM_1007.VALUE1_CHAR BETWEEN 5000 AND 5999) AND (NVL(QM_1007.VALUE2_CHAR,5000) BETWEEN 5000 AND 5999) then ''VT'' ' ||
158 ' when (QM_1007.VALUE1_CHAR BETWEEN 6000 AND 6999) AND (NVL(QM_1007.VALUE2_CHAR,6000) BETWEEN 6000 AND 6999) then ''CT'' ' ||
159 ' when (QM_1007.VALUE1_CHAR BETWEEN 7000 AND 8999) AND (NVL(QM_1007.VALUE2_CHAR,7000) BETWEEN 7000 AND 8999) then ''NJ'' ' ||
160 ' when (QM_1007.VALUE1_CHAR BETWEEN 9000 AND 14999) AND (NVL(QM_1007.VALUE2_CHAR,9000) BETWEEN 9000 AND 14999) then ''NY'' ' ||
161 ' when (QM_1007.VALUE1_CHAR BETWEEN 15000 AND 19699) AND (NVL(QM_1007.VALUE2_CHAR,15000) BETWEEN 15000 AND 19699) then ''PA'' '||
162 ' when (QM_1007.VALUE1_CHAR BETWEEN 19700 AND 19999) AND (NVL(QM_1007.VALUE2_CHAR,19700) BETWEEN 19700 AND 19999) then ''DE'' '||
163 ' when (QM_1007.VALUE1_CHAR BETWEEN 20000 AND 20099) AND (NVL(QM_1007.VALUE2_CHAR,20000) BETWEEN 20000 AND 20099) then ''DC'' '||
164 ' when (QM_1007.VALUE1_CHAR BETWEEN 20600 AND 21999) AND (NVL(QM_1007.VALUE2_CHAR,20600) BETWEEN 20600 AND 21999) then ''MD'' '||
165 ' when (QM_1007.VALUE1_CHAR BETWEEN 20100 AND 20200) AND (NVL(QM_1007.VALUE2_CHAR,20100) BETWEEN 20100 AND 20200) then ''VA'' '||
166 ' when (QM_1007.VALUE1_CHAR BETWEEN 24700 AND 26899) AND (NVL(QM_1007.VALUE2_CHAR,24700) BETWEEN 24700 AND 26899) then ''WV'' '||
167 ' when (QM_1007.VALUE1_CHAR BETWEEN 27000 AND 28999) AND (NVL(QM_1007.VALUE2_CHAR,27000) BETWEEN 27000 AND 28999) then ''NC'' '||
168 ' when (QM_1007.VALUE1_CHAR BETWEEN 29000 AND 29999) AND (NVL(QM_1007.VALUE2_CHAR,29000) BETWEEN 29000 AND 29999) then ''SC'' '||
169 ' when (QM_1007.VALUE1_CHAR BETWEEN 30000 AND 31999) AND (NVL(QM_1007.VALUE2_CHAR,30000) BETWEEN 30000 AND 31999) then ''GA'' '||
170 ' when (QM_1007.VALUE1_CHAR BETWEEN 32000 AND 34999) AND (NVL(QM_1007.VALUE2_CHAR,32000) BETWEEN 32000 AND 34999) then ''FL'' '||
171 ' when (QM_1007.VALUE1_CHAR BETWEEN 35000 AND 36999) AND (NVL(QM_1007.VALUE2_CHAR,35000) BETWEEN 35000 AND 36999) then ''AL'' '||
172 ' when (QM_1007.VALUE1_CHAR BETWEEN 37000 AND 38599) AND (NVL(QM_1007.VALUE2_CHAR,37000) BETWEEN 37000 AND 38599) then ''TN'' '||
173 ' when (QM_1007.VALUE1_CHAR BETWEEN 38600 AND 39799) AND (NVL(QM_1007.VALUE2_CHAR,38600) BETWEEN 38600 AND 39799) then ''MS'' '||
174 ' when (QM_1007.VALUE1_CHAR BETWEEN 40000 AND 42799) AND (NVL(QM_1007.VALUE2_CHAR,40000) BETWEEN 40000 AND 42799) then ''KY'' '||
175 ' when (QM_1007.VALUE1_CHAR BETWEEN 43000 AND 45899) AND (NVL(QM_1007.VALUE2_CHAR,43000) BETWEEN 43000 AND 45899) then ''OH'' '||
176 ' when (QM_1007.VALUE1_CHAR BETWEEN 46000 AND 47999) AND (NVL(QM_1007.VALUE2_CHAR,46000) BETWEEN 46000 AND 47999) then ''IN'' '||
177 ' when (QM_1007.VALUE1_CHAR BETWEEN 48000 AND 49999) AND (NVL(QM_1007.VALUE2_CHAR,48000) BETWEEN 48000 AND 49999) then ''MI'' '||
178 ' when (QM_1007.VALUE1_CHAR BETWEEN 50000 AND 52899) AND (NVL(QM_1007.VALUE2_CHAR,50000) BETWEEN 50000 AND 52899) then ''IA'' '||
179 ' when (QM_1007.VALUE1_CHAR BETWEEN 53000 AND 54999) AND (NVL(QM_1007.VALUE2_CHAR,53000) BETWEEN 53000 AND 54999) then ''WI'' '||
180 ' when (QM_1007.VALUE1_CHAR BETWEEN 55000 AND 56799) AND (NVL(QM_1007.VALUE2_CHAR,55000) BETWEEN 55000 AND 56799) then ''MN'' '||
181 ' when (QM_1007.VALUE1_CHAR BETWEEN 57000 AND 57799) AND (NVL(QM_1007.VALUE2_CHAR,57000) BETWEEN 57000 AND 57799) then ''SD'' '||
182 ' when (QM_1007.VALUE1_CHAR BETWEEN 58000 AND 58899) AND (NVL(QM_1007.VALUE2_CHAR,58000) BETWEEN 58000 AND 58899) then ''ND'' '||
183 ' when (QM_1007.VALUE1_CHAR BETWEEN 59000 AND 59999) AND (NVL(QM_1007.VALUE2_CHAR,59000) BETWEEN 59000 AND 59999) then ''MT'' '||
184 ' when (QM_1007.VALUE1_CHAR BETWEEN 60000 AND 62999) AND (NVL(QM_1007.VALUE2_CHAR,60000) BETWEEN 60000 AND 62999) then ''IL'' '||
185 ' when (QM_1007.VALUE1_CHAR BETWEEN 63000 AND 65899) AND (NVL(QM_1007.VALUE2_CHAR,63000) BETWEEN 63000 AND 65899) then ''MO'' '||
186 ' when (QM_1007.VALUE1_CHAR BETWEEN 66000 AND 67999) AND (NVL(QM_1007.VALUE2_CHAR,66000) BETWEEN 66000 AND 67999) then ''KS'' '||
187 ' when (QM_1007.VALUE1_CHAR BETWEEN 68000 AND 69399) AND (NVL(QM_1007.VALUE2_CHAR,68000) BETWEEN 68000 AND 69399) then ''NE'' '||
188 ' when (QM_1007.VALUE1_CHAR BETWEEN 70000 AND 71499) AND (NVL(QM_1007.VALUE2_CHAR,70000) BETWEEN 70000 AND 71499) then ''LA'' '||
189 ' when (QM_1007.VALUE1_CHAR BETWEEN 71600 AND 72999) AND (NVL(QM_1007.VALUE2_CHAR,71600) BETWEEN 71600 AND 72999) then ''AR'' '||
190 ' when (QM_1007.VALUE1_CHAR BETWEEN 73000 AND 74999) AND (NVL(QM_1007.VALUE2_CHAR,73000) BETWEEN 73000 AND 74999) then ''OK'' '||
191 ' when (QM_1007.VALUE1_CHAR BETWEEN 75000 AND 79999) AND (NVL(QM_1007.VALUE2_CHAR,75000) BETWEEN 75000 AND 79999) then ''TX'' '||
192 ' when (QM_1007.VALUE1_CHAR BETWEEN 80000 AND 81699) AND (NVL(QM_1007.VALUE2_CHAR,80000) BETWEEN 80000 AND 81699) then ''CO'' '||
193 ' when (QM_1007.VALUE1_CHAR BETWEEN 82000 AND 83199) AND (NVL(QM_1007.VALUE2_CHAR,82000) BETWEEN 82000 AND 83199) then ''WY'' '||
194 ' when (QM_1007.VALUE1_CHAR BETWEEN 83200 AND 83899) AND (NVL(QM_1007.VALUE2_CHAR,83200) BETWEEN 83200 AND 83899) then ''ID'' '||
195 ' when (QM_1007.VALUE1_CHAR BETWEEN 84000 AND 84799) AND (NVL(QM_1007.VALUE2_CHAR,84000) BETWEEN 84000 AND 84799) then ''UT'' '||
196 ' when (QM_1007.VALUE1_CHAR BETWEEN 85000 AND 86599) AND (NVL(QM_1007.VALUE2_CHAR,85000) BETWEEN 85000 AND 86599) then ''AZ'' '||
197 ' when (QM_1007.VALUE1_CHAR BETWEEN 87000 AND 88499) AND (NVL(QM_1007.VALUE2_CHAR,87000) BETWEEN 87000 AND 88499) then ''NM'' '||
198 ' when (QM_1007.VALUE1_CHAR BETWEEN 89000 AND 89899) AND (NVL(QM_1007.VALUE2_CHAR,89000) BETWEEN 89000 AND 89899) then ''NV'' '||
199 ' when (QM_1007.VALUE1_CHAR BETWEEN 90000 AND 96699) AND (NVL(QM_1007.VALUE2_CHAR,90000) BETWEEN 90000 AND 96699) then ''CA'' '||
200 ' when (QM_1007.VALUE1_CHAR BETWEEN 96700 AND 96899) AND (NVL(QM_1007.VALUE2_CHAR,96700) BETWEEN 96700 AND 96899) then ''HI'' '||
201 ' when (QM_1007.VALUE1_CHAR BETWEEN 97000 AND 97999) AND (NVL(QM_1007.VALUE2_CHAR,97000) BETWEEN 97000 AND 97999) then ''OR'' '||
202 ' when (QM_1007.VALUE1_CHAR BETWEEN 98000 AND 99499) AND (NVL(QM_1007.VALUE2_CHAR,98000) BETWEEN 98000 AND 99499) then ''WA'' '||
203 ' when (QM_1007.VALUE1_CHAR BETWEEN 99500 AND 99999) AND (NVL(QM_1007.VALUE2_CHAR,99500) BETWEEN 99500 AND 99999) then ''AK'' '||
204 ' when (QM_1007.VALUE1_CHAR BETWEEN 20000 AND 20099) AND (NVL(QM_1007.VALUE2_CHAR,20000) BETWEEN 20000 AND 20099) then ''DC'' '||
205 ' when (QM_1007.VALUE1_CHAR BETWEEN 20201 AND 20599) AND (NVL(QM_1007.VALUE2_CHAR,20201) BETWEEN 20201 AND 20599) then ''DC'' '||
206 ' when (QM_1007.VALUE1_CHAR BETWEEN 20100 AND 20200) AND (NVL(QM_1007.VALUE2_CHAR,20100) BETWEEN 20100 AND 20200) then ''VA'' '||
207 ' when (QM_1007.VALUE1_CHAR BETWEEN 22000 AND 24699) AND (NVL(QM_1007.VALUE2_CHAR,22000) BETWEEN 22000 AND 24699) then ''VA'' end '||
208 ' CALCULATED_STATE '||
209 ' , QM_1012.VALUE1_CHAR CUSTOMER_NAME '||
210 ' FROM JTF_TTY_NAMED_ACCTS TNA '||
211 ' , JTF_TTY_ACCT_QUAL_MAPS QM_1007 '||
212 ' , JTF_TTY_ACCT_QUAL_MAPS QM_1012 '||
213 ' WHERE '||
214 ' TNA.NAMED_ACCOUNT_ID = QM_1007.NAMED_ACCOUNT_ID '||
215 ' AND QM_1007.QUAL_USG_ID = -1007 '||
216 ' AND QM_1007.NAMED_ACCOUNT_ID = QM_1012.NAMED_ACCOUNT_ID '||
217 ' AND QM_1012.QUAL_USG_ID = -1012 '||
218 ' )ILV , JTF_TTY_TERR_GRP_ACCTS TGA '||
219 ' WHERE ILV.NAMED_ACCOUNT_ID = TGA.NAMED_ACCOUNT_ID '||
220 ' AND ILV.CALCULATED_STATE = '''||l_lead_state||
221 ''' AND ( ILV.CUSTOMER_NAME = '''||l_lead_keyword || ''' OR ''' || l_lead_keyword || ''' LIKE ILV.CUSTOMER_NAME )'||
222 ' AND rownum < 2 '||
223 ' order by site_rank ';
224
225 --find one named account which satisfies keyword and state requirement
226
227 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('query_str is: '||query_str);
228
229 --bug#2878006 fix ARPATEL 04/21/03
230 IF l_lead_state is not null and l_lead_state <> '' and l_lead_postal_code is not null and l_lead_postal_code <> ''
231 THEN
232
233 --find Reps for each named account and assign to the lead
234 OPEN c_named_accounts FOR query_str;
235 FETCH c_named_accounts INTO l_named_account_rec;
236
237 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('l_named_account_rec.named_account_id is:'||l_named_account_rec.named_account_id);
238 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('l_named_account_rec.terr_group_id is:'||l_named_account_rec.terr_group_id);
239
240 FOR rs_rec IN c_resources(l_named_account_rec.named_account_id, l_named_account_rec.terr_group_id)
241 LOOP
242 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('rs_rec.rsc_group_id is:'||rs_rec.rsc_group_id);
243 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('rs_rec.resource_id is:'||rs_rec.resource_id);
244
245 --create record in JTF_TTY_NAMED_ACCOUNT, JTF_TTY_ACCT_QUAL_MAPS, JTF_TTY_NAMED_ACCT_RSC
246 --so that NA territory is created next time
247
248 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('calling add_org_to_terrgp');
249 /** creates a NA, if one for this party_id does not already exist else
250 ** update the existing named account to include postal code as a mapping rule
251 **/
252 add_org_to_terrgp(p_terr_gp_id => l_named_account_rec.terr_group_id,
253 p_ref_account_id => l_named_account_rec.named_account_id,
254 p_party_id => l_party_Id,
255 p_resource_id => rs_rec.resource_id,
256 p_role_code => rs_rec.rsc_role_code,
257 p_user_id => G_USER,
258 p_rsc_group_id => rs_rec.rsc_group_id,
259 p_lead_keyword => l_lead_keyword,
260 p_lead_postal_code => l_lead_postal_code,
261 x_account_id => l_new_account_id);
262
263 END LOOP;
264
265 END IF; --end of bug#2878006 fix
266
267 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('End of JTF_TTY_NA_WF.AssignRep');
268 commit;
269 EXCEPTION
270 WHEN OTHERS
271 THEN
272 /*****************************************************************************
273 ** Something went wrong return 'ERROR' and set the ERROR_MESSAGE
274 *****************************************************************************/
275 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('ERROR_MESSAGE: '||to_char(SQLCODE)||':'||SQLERRM);
276 wf_engine.SetItemAttrText( itemtype => itemtype
277 , itemkey => itemkey
278 , aname => 'ERROR_MESSAGE'
279 , avalue => to_char(SQLCODE)||':'||SQLERRM
280 );
281
282 resultout := 'COMPLETE:ERROR';
283
284 END AssignRep;
285
286 PROCEDURE add_org_to_terrgp( p_terr_gp_id IN NUMBER,
287 p_ref_account_id IN NUMBER,
288 p_party_id IN NUMBER,
289 p_resource_id IN NUMBER,
290 p_role_code IN VARCHAR2,
291 p_user_id in NUMBER,
292 p_rsc_group_id IN NUMBER,
293 p_lead_keyword IN VARCHAR2,
294 p_lead_postal_code IN VARCHAR2,
295 x_account_id OUT NOCOPY NUMBER)
296 AS
297 p_site_type_code varchar2(30);
298 p_account_count number(30);
299 p_rsc_acct_count number(30);
300 p_terr_gp_acct_id number(30);
301 p_terr_gp_acct_rsc_id number(30);
302 p_terr_gp_acct_rsc_dn_id number(30) := 0;
303 l_acct_qual_map_id number;
304
305 BEGIN
306 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('begin add_org_to_terrgp');
307
308 select count(*)
309 into p_account_count
310 from jtf_tty_named_accts
311 where party_id = p_party_id;
312
313 if p_account_count = 1 then
314 select named_account_id
315 into x_account_id
316 from jtf_tty_named_accts
317 where party_id = p_party_id;
318 else
319 -- create a new named account for the party, if one does not exist
320 select JTF_TTY_NAMED_ACCTS_S.nextval
321 into x_account_id
322 from dual;
323 end if;
324
325 p_site_type_code := get_site_type_code(p_party_id);
326
327 if (p_account_count < 1) then
328 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('create a named account');
329
330 insert into jtf_tty_named_accts
331 (NAMED_ACCOUNT_ID,
332 OBJECT_VERSION_NUMBER ,
333 PARTY_ID ,
334 MAPPING_COMPLETE_FLAG,
335 SITE_TYPE_CODE,
336 CREATED_BY ,
337 CREATION_DATE ,
338 LAST_UPDATED_BY ,
339 LAST_UPDATE_DATE ,
340 LAST_UPDATE_LOGIN
341 )
342 VALUES(x_account_id,
343 2,
344 p_party_id,
345 'N',
346 p_site_type_code,
347 p_user_id,
348 sysdate,
349 p_user_id,
350 sysdate,
351 p_user_id
352 );
353
354 select JTF_TTY_TERR_GRP_ACCTS_S.nextval
355 into p_terr_gp_acct_id
356 from dual;
357
358 -- assign a named account for the party to terr gp, if one does not exist
359
360 p_site_type_code := get_site_type_code(p_party_id);
361
362 insert into JTF_TTY_TERR_GRP_ACCTS
363 (TERR_GROUP_ACCOUNT_ID,
364 OBJECT_VERSION_NUMBER ,
365 TERR_GROUP_ID ,
366 NAMED_ACCOUNT_ID,
367 DN_JNA_MAPPING_COMPLETE_FLAG,
368 DN_JNA_SITE_TYPE_CODE,
369 DN_JNR_ASSIGNED_FLAG,
370 CREATED_BY ,
371 CREATION_DATE ,
372 LAST_UPDATED_BY ,
373 LAST_UPDATE_DATE ,
374 LAST_UPDATE_LOGIN
375 )
376 VALUES(p_terr_gp_acct_id,
377 2,
378 p_terr_gp_id,
379 x_account_id,
380 'N',
381 p_site_type_code,
382 'N',
383 p_user_id,
384 sysdate,
385 p_user_id,
386 sysdate,
387 p_user_id
388 );
389
390 -- assign resource to the named account
391
392 select jtf_tty_named_acct_rsc_s.nextval
393 into p_terr_gp_acct_rsc_id
394 from dual;
395
396 insert into jtf_tty_named_acct_rsc
397 (ACCOUNT_RESOURCE_ID,
398 OBJECT_VERSION_NUMBER ,
399 TERR_GROUP_ACCOUNT_ID,
400 RESOURCE_ID ,
401 RSC_GROUP_ID,
402 RSC_ROLE_CODE,
403 ASSIGNED_FLAG ,
404 RSC_RESOURCE_TYPE,
405 CREATED_BY ,
406 CREATION_DATE ,
407 LAST_UPDATED_BY ,
408 LAST_UPDATE_DATE ,
409 LAST_UPDATE_LOGIN
410 )
411 VALUES(p_terr_gp_acct_rsc_id,
412 2,
413 p_terr_gp_acct_id,
414 p_resource_id,
415 p_rsc_group_id,
416 p_role_code,
417 'N',
418 'RS_EMPLOYEE',
419 p_user_id,
420 sysdate,
421 p_user_id,
422 sysdate,
423 p_user_id
424 );
425
426 --Insert into denorm table, the resource hierarchy records (similar to those of the candidate resource territory)
427 -- ARPATEL 01/30/03
428 INSERT INTO jtf_tty_acct_rsc_dn
429 (ACCOUNT_RESOURCE_DN_ID,
430 OBJECT_VERSION_NUMBER ,
431 TERR_GROUP_ACCOUNT_ID,
432 RESOURCE_ID ,
433 RSC_GROUP_ID,
434 ASSIGNED_TO_DIRECT_FLAG,
435 RSC_ROLE_CODE,
436 RSC_RESOURCE_TYPE,
437 CREATED_BY ,
438 CREATION_DATE ,
439 LAST_UPDATED_BY ,
440 LAST_UPDATE_DATE ,
441 LAST_UPDATE_LOGIN
442 )
443 SELECT jtf_tty_acct_rsc_dn_s.nextval,
444 1.0 ,
445 p_terr_gp_acct_id,
446 RESOURCE_ID ,
447 RSC_GROUP_ID,
448 ASSIGNED_TO_DIRECT_FLAG,
449 RSC_ROLE_CODE,
450 RSC_RESOURCE_TYPE,
451 p_user_id,
452 sysdate,
453 p_user_id,
454 sysdate,
455 p_user_id
456 FROM jtf_tty_acct_rsc_dn
457 WHERE terr_group_account_id = (select TGA.terr_group_account_id
458 from JTF_TTY_TERR_GRP_ACCTS TGA
459 where TGA.named_account_id = p_ref_account_id
460 and TGA.terr_group_id = p_terr_gp_id);
461
462 --API to re-create the summation table
463 JTF_TTY_NA_TERRGP.sum_accts(p_user_id => p_user_id);
464
465 --create mapping rules for this named account
466 create_mapping_rules (p_account_id => x_account_id
467 , p_keyword => p_lead_keyword
468 , p_postal_code => p_lead_postal_code );
469 commit;
470
471 elsif (p_account_count = 1)
472 then
473 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('p_account_count = 1: Update mapping rules');
474 -- Update mapping rules for this existing named account by adding the postal code mapping
475 select JTF_TTY_ACCT_QUAL_MAPS_S.nextval
476 into l_acct_qual_map_id
477 from dual;
478
479 INSERT INTO JTF_TTY_ACCT_QUAL_MAPS
480 (account_qual_map_id,
481 object_version_number,
482 named_account_id,
483 qual_usg_id,
484 comparison_operator,
485 value1_char,
486 value2_char,
487 created_by,
488 creation_date,
489 last_updated_by,
490 last_update_date
491 ) VALUES
492 (
493 l_acct_qual_map_id
494 , 2.0
495 , x_account_id
496 , -1007 --Postal Code
497 , '='
498 , p_lead_postal_code
499 , null
500 , G_USER
501 , sysdate
502 , G_USER
503 , sysdate );
504
505 end if; --ending if (p_account_count < 1)
506
507 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('end add_org_to_terrgp');
508 END add_org_to_terrgp;
509
510 function get_site_type_code( p_party_id NUMBER ) return varchar2
511 is
512 l_site_type_code VARCHAR2(30);
513 l_chk_done VARCHAR2(1) := 'N' ;
514
515 begin
516
517 hz_common_pub.disable_cont_source_security;
518
519 -- check for global ultimate
520
521 begin
522
523 SELECT 'Y'
524 INTO l_chk_done
525 FROM DUAL
526 WHERE EXISTS ( select 'Y'
527 from hz_relationships hzr
528 where hzr.subject_table_name = 'HZ_PARTIES'
529 and hzr.object_table_name = 'HZ_PARTIES'
530 and hzr.relationship_type = 'GLOBAL_ULTIMATE'
531 and hzr.relationship_code = 'GLOBAL_ULTIMATE_OF'
532 and hzr.status = 'A'
533 and sysdate between hzr.start_date and nvl(hzr.end_date, sysdate)
534 and hzr.subject_id = p_party_id );
535 exception
536 when no_data_found then null;
537 end;
538
539 IF l_chk_done = 'Y'
540 THEN
541 l_site_type_code := 'GU' ;
542 RETURN l_site_type_code;
543 END IF;
544
545 -- check for domestic ultimate
546
547 begin
548 SELECT 'Y'
549 INTO l_chk_done
550 FROM DUAL
551 WHERE EXISTS ( select 'Y'
552 from hz_relationships hzr
553 where hzr.subject_table_name = 'HZ_PARTIES'
554 and hzr.object_table_name = 'HZ_PARTIES'
555 and hzr.relationship_type = 'DOMESTIC_ULTIMATE'
556 and hzr.relationship_code = 'DOMESTIC_ULTIMATE_OF'
557 and hzr.status = 'A'
558 and sysdate between hzr.start_date and nvl(hzr.end_date, sysdate)
559 and hzr.subject_id = p_party_id );
560 exception
561 when no_data_found then null;
562 end;
563
564
565
566 IF l_chk_done = 'Y'
567 THEN
568 l_site_type_code := 'DU' ;
569 RETURN l_site_type_code;
570 END IF;
571
572 BEGIN
573
574 select lkp.lookup_code
575 into l_site_type_code
576 from fnd_lookups lkp,
577 hz_parties hzp
578 where lkp.lookup_type = 'JTF_TTY_SITE_TYPE_CODE'
579 and hzp.hq_branch_ind = lkp.lookup_code
580 and hzp.party_id = p_party_id;
581
582
583 EXCEPTION
584 when no_data_found then
585 l_site_type_code := 'UN';
586
587 END;
588
589
590
591 RETURN( l_site_type_code);
592
593 exception
594
595 when others then
596 null;
597
598 end get_site_type_code;
599
600 PROCEDURE get_site_type(p_party_id IN Number,
601 x_party_type OUT NOCOPY VARCHAR2)
602 AS
603 site_type_code varchar2(30);
604 BEGIN
605 site_type_code := get_site_type_code(p_party_id);
606 select lkp.meaning
607 into x_party_type
608 from fnd_lookups lkp
609 where lkp.lookup_type = 'JTF_TTY_SITE_TYPE_CODE'
610 and lkp.lookup_code = site_type_code;
611
612 END get_site_type;
613
614 PROCEDURE create_mapping_rules (p_account_id IN NUMBER
615 , p_keyword IN VARCHAR2
616 , p_postal_code IN VARCHAR2)
617 AS
618 l_acct_qual_map_id number;
619 BEGIN
620 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('begin create_mapping_rules');
621
622 select JTF_TTY_ACCT_QUAL_MAPS_S.nextval
623 into l_acct_qual_map_id
624 from dual;
625
626 INSERT INTO JTF_TTY_ACCT_QUAL_MAPS
627 (account_qual_map_id,
628 object_version_number,
629 named_account_id,
630 qual_usg_id,
631 comparison_operator,
632 value1_char,
633 value2_char,
634 created_by,
635 creation_date,
636 last_updated_by,
637 last_update_date
638 ) VALUES
639 (
640 l_acct_qual_map_id
641 , 2.0
642 , p_account_id
643 , -1012 --Customer Name Range
644 , '='
645 , p_keyword
646 , null
647 , G_USER
648 , sysdate
649 , G_USER
650 , sysdate );
651
652 select JTF_TTY_ACCT_QUAL_MAPS_S.nextval
653 into l_acct_qual_map_id
654 from dual;
655
656 INSERT INTO JTF_TTY_ACCT_QUAL_MAPS
657 (account_qual_map_id,
658 object_version_number,
659 named_account_id,
660 qual_usg_id,
661 comparison_operator,
662 value1_char,
663 value2_char,
664 created_by,
665 creation_date,
666 last_updated_by,
667 last_update_date
668 ) VALUES
669 (
670 l_acct_qual_map_id
671 , 2.0
672 , p_account_id
673 , -1007 --Postal Code
674 , '='
675 , p_postal_code
676 , null
677 , G_USER
678 , sysdate
679 , G_USER
680 , sysdate );
681
682 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('end create_mapping_rules');
683
684 END create_mapping_rules;
685
686
687
688 END JTF_TTY_NA_WF;
689
[Home] [Help]