[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_CATCHALL_WORKFLOW
Source
1 PACKAGE BODY JTF_TTY_CATCHALL_WORKFLOW AS
2 /* $Header: jtfvwkfb.pls 120.0 2005/06/02 18:23:15 appldev ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTF_TTY_CATCHALL_WORKFLOW
6 -- PURPOSE
7 --
8 -- Procedures:
9 -- (see below for specification)
10 --
11 --
12 --
13 --
14 -- NOTES
15 --
16 --
17 --
18 --
19 -- HISTORY
20 -- 12/15/02 JRADHAKR CREATED
21 --
22 --
23 -- End of Comments
24 --
25
26 Procedure Process_catch_all_rec
27 ( x_return_status OUT NOCOPY VARCHAR2
28 , x_error_message OUT NOCOPY VARCHAR2
29 )
30 IS
31
32 l_wf_item_key NUMBER;
33
34 CURSOR c_territory_list IS
35 SELECT TERR_ID
36 , TERR_GROUP_ID
37 FROM jtf_terr_all
38 WHERE CATCH_ALL_FLAG = 'Y';
39
40 CURSOR c_catchall_list (l_terr_id number) IS
41 select -- Leads
42 AAA.ACCESS_ID
43 , ATA.TERRITORY_ID
44 ,upper(PARTY.party_name) party_name
45 , LOC.state state
46 , LOC.postal_code postal_code
47 , PARTY.party_id party_id
48 from HZ_PARTY_SITES SITE
49 , HZ_LOCATIONS LOC
50 , HZ_PARTIES PARTY
51 , as_accesses_all AAA
52 , as_territory_accesses ATA
53 , AS_SALES_LEADS SL
54 where SITE.party_id = PARTY.party_id
55 and LOC.location_id = SITE.location_id
56 and SL.customer_id = PARTY.party_id
57 and SL.address_id = SITE.party_site_id
58 and AAA.ACCESS_ID = ATA.ACCESS_ID
59 and ATA.TERRITORY_ID = l_terr_id
60 and AAA.CUSTOMER_ID = SL.CUSTOMER_ID
61 AND AAA.LEAD_ID IS NULL
62 AND AAA.SALES_LEAD_ID IS NOT NULL
63 UNION ALL
64 select -- Opportunities
65 AAA.ACCESS_ID
66 , ATA.TERRITORY_ID
67 , upper(PARTY.party_name) party_name
68 , LOC.state state
69 , LOC.postal_code postal_code
70 , PARTY.party_id party_id
71 from HZ_PARTY_SITES SITE
72 , HZ_LOCATIONS LOC
73 , HZ_PARTIES PARTY
74 , AS_LEADS_ALL LEAD
75 , as_accesses_all AAA
76 , as_territory_accesses ATA
77 where SITE.party_id = PARTY.party_id
78 and LOC.location_id = SITE.location_id
79 and LEAD.customer_id = PARTY.party_id
80 and LEAD.address_id = SITE.party_site_id
81 AND AAA.ACCESS_ID = ATA.ACCESS_ID
82 AND ATA.TERRITORY_ID = l_terr_id
83 AND AAA.CUSTOMER_ID = LEAD.CUSTOMER_ID
84 AND AAA.LEAD_ID IS NOT NULL
85 AND AAA.SALES_LEAD_ID IS NULL
86 UNION ALL
87 select -- Account
88 AAA.ACCESS_ID
89 , ATA.TERRITORY_ID
90 , upper(PARTY.party_name) party_name
91 , LOC.state state
92 , LOC.postal_code postal_code
93 , PARTY.party_id party_id
94 from HZ_PARTY_SITES SITE
95 , HZ_LOCATIONS LOC
96 , HZ_PARTIES PARTY
97 , as_accesses_all AAA
98 , as_territory_accesses ATA
99 where SITE.party_id = PARTY.party_id
100 and LOC.location_id = SITE.location_id
101 AND AAA.ACCESS_ID = ATA.ACCESS_ID
102 AND ATA.TERRITORY_ID = l_terr_id
103 AND AAA.CUSTOMER_ID = PARTY.PARTY_ID
104 AND AAA.LEAD_ID IS NULL
105 AND AAA.SALES_LEAD_ID IS NULL
106 AND AAA.CUSTOMER_ID IS NOT NULL;
107
108 l_workflow_param JTF_TTY_CATCHALL_WORKFLOW.workflow_param_rec_type;
109 l_item_type varchar2(30);
110 l_wf_process varchar2(30);
111
112 BEGIN
113
114 x_return_status := FND_API.G_RET_STS_SUCCESS;
115
116 -- Validate item type and process
117 --
118
119 for l_terr_list in c_territory_list
120 loop
121 --
122 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('Processing Territory Group ' || l_terr_list.TERR_GROUP_ID);
123
124 JTF_TTY_CATCHALL_WORKFLOW.Get_workflow_details
125 ( p_TERR_GROUP_ID => l_terr_list.TERR_GROUP_ID
126 , x_WORKFLOW_ITEM_TYPE => l_item_type
127 , x_WORKFLOW_PROCESS_NAME => l_wf_process
128 , x_return_status => x_return_status
129 , x_error_message => x_error_message
130 );
131
132 if nvl(x_error_message,'DATA') <> 'NODATA' then
133 --
134
135 for l_list in c_catchall_list(l_terr_list.terr_id)
136 loop
137 --
138 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log('Processing Catch All Territory ' || l_terr_list.TERR_ID);
139
140 l_workflow_param.ACCESS_ID := l_list.ACCESS_ID;
141 l_workflow_param.NAME := l_list.Party_name;
142 l_workflow_param.POSTAL_CODE := l_list.Postal_code;
143 l_workflow_param.STATE := l_list.STATE;
144 l_workflow_param.TERRGROUP_ID := l_terr_list.TERR_GROUP_ID;
145 l_workflow_param.PARTY_ID := l_list.Party_id;
146
147 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log(' Access Id : ' || l_workflow_param.ACCESS_ID );
148 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log(' Party Name : ' || l_workflow_param.NAME );
149 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log(' Postal Code : ' || l_workflow_param.POSTAL_CODE );
150 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log(' State : ' || l_workflow_param.STATE );
151 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log(' TerrGroup Id : ' || l_workflow_param.TERRGROUP_ID );
152 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log(' Party Id : ' || l_workflow_param.PARTY_ID );
153
154
155 JTF_TTY_CATCHALL_WORKFLOW.Start_Workflow_Process
156 ( p_item_type => l_item_type
157 ,p_wf_process => l_wf_process
158 ,p_wf_params => l_workflow_param
159 ,x_return_status => x_return_status
160 );
161
162 end loop;
163 --
164 end if;
165 --
166 end loop;
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171
172 END Process_catch_all_rec;
173
174 Procedure Get_workflow_details
175 ( p_TERR_GROUP_ID IN NUMBER
176 , x_WORKFLOW_ITEM_TYPE OUT NOCOPY VARCHAR2
177 , x_WORKFLOW_PROCESS_NAME OUT NOCOPY VARCHAR2
178 , x_return_status OUT NOCOPY VARCHAR2
179 , x_error_message OUT NOCOPY VARCHAR2
180 )
181 IS
182
183 l_wf_process varchar2(30);
184
185 BEGIN
186
187 x_return_status := FND_API.G_RET_STS_SUCCESS;
188
189 select WORKFLOW_ITEM_TYPE
190 , WORKFLOW_PROCESS_NAME
191 INTO x_WORKFLOW_ITEM_TYPE
192 , x_WORKFLOW_PROCESS_NAME
193 FROM jtf_tty_terr_groups
194 where TERR_GROUP_ID = p_TERR_GROUP_ID;
195
196 EXCEPTION
197 WHEN NO_DATA_FOUND THEN
198 x_error_message := 'NODATA';
199 WHEN OTHERS THEN
200 x_error_message := 'NODATA';
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202
203 END Get_workflow_details;
204
205 Procedure Start_Workflow_Process
206 ( p_item_type IN Varchar2
207 ,p_wf_process IN Varchar2
208 ,p_wf_params IN JTF_TTY_CATCHALL_WORKFLOW.workflow_param_rec_type
209 ,x_return_status OUT NOCOPY VARCHAR2
210 )
211 IS
212
213 l_wf_item_key NUMBER;
214
215 BEGIN
216
217 x_return_status := FND_API.G_RET_STS_SUCCESS;
218
219 -- Validate item type and process
220 --
221
222 IF p_item_type IS NULL
223 OR p_wf_process IS NULL THEN
224 x_return_status := FND_API.G_RET_STS_ERROR;
225
226 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log(' No Workflow or process found ' );
227
228 RETURN;
229 END IF;
230
231 SELECT jtf_tty_workflow_s.nextval
232 INTO l_wf_item_key
233 FROM dual;
234
235 -- create a new workflow process
236 --
237
238 wf_engine.CreateProcess(itemtype=>p_item_type
239 ,itemkey =>l_wf_item_key
240 ,process =>p_wf_process);
241
242 -- set the workflow attributes
243 --
244 wf_engine.SetItemAttrText(itemtype=>p_item_type
245 ,itemkey =>l_wf_item_key
246 ,aname=>'STATE'
247 ,avalue=>p_wf_params.STATE);
248
249 wf_engine.SetItemAttrText(itemtype=>p_item_type
250 ,itemkey =>l_wf_item_key
251 ,aname=>'KEYWORD'
252 ,avalue=>p_wf_params.NAME);
253
254 wf_engine.SetItemAttrText(itemtype=>p_item_type
255 ,itemkey =>l_wf_item_key
256 ,aname=>'POSTAL_CODE'
257 ,avalue=>p_wf_params.POSTAL_CODE);
258
259 wf_engine.SetItemAttrNumber(itemtype=>p_item_type
260 ,itemkey =>l_wf_item_key
261 ,aname=>'TERRGROUP_ID'
262 ,avalue=>p_wf_params.TERRGROUP_ID);
263
264 wf_engine.SetItemAttrNumber(itemtype=>p_item_type
265 ,itemkey =>l_wf_item_key
266 ,aname=>'ACCESS_ID'
267 ,avalue=>p_wf_params.ACCESS_ID);
268
269 wf_engine.SetItemAttrNumber(itemtype=>p_item_type
270 ,itemkey =>l_wf_item_key
271 ,aname=>'PARTY_ID'
272 ,avalue=>p_wf_params.PARTY_ID);
273
274 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log(' Processing WORKFLOW : ' || p_item_type || ' ' || p_wf_process);
275
276 wf_engine.StartProcess(itemtype=>p_item_type
277 ,itemkey => l_wf_item_key);
278
279
280 commit;
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
285 JTF_TTY_WORKFLOW_POP_BIN_PVT.print_log ('Erro Stating workflow '||SQLERRM);
286
287 END Start_Workflow_Process;
288
289
290 END JTF_TTY_CATCHALL_WORKFLOW;