[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEAD_AGING_CONC_PUB
Source
1 PACKAGE BODY AS_SALES_LEAD_AGING_CONC_PUB AS
2 /* $Header: asxslacb.pls 115.3 2004/01/23 07:05:51 subabu ship $ */
3
4 g_debug_flag VARCHAR2(1) := 'N';
5
6
7 PROCEDURE Debug(p_msg IN VARCHAR2)
8 IS
9 l_length NUMBER;
10 l_start NUMBER := 1;
11 l_substring VARCHAR2(255);
12
13 l_base VARCHAR2(12);
14 BEGIN
15 IF g_debug_flag = 'Y'
16 THEN
17 -- chop the message to 255 long
18 l_length := length(p_msg);
19 WHILE l_length > 255 LOOP
20 l_substring := substr(p_msg, l_start, 255);
21 FND_FILE.PUT_LINE(FND_FILE.LOG, l_substring);
22 l_start := l_start + 255;
23 l_length := l_length - 255;
24 END LOOP;
25 l_substring := substr(p_msg, l_start);
26 FND_FILE.PUT_LINE(FND_FILE.LOG,l_substring);
27 END IF;
28
29 EXCEPTION
30 WHEN others THEN
31 Debug('Exception: others in Debug');
32 Debug('SQLCODE ' || to_char(SQLCODE) || ' SQLERRM ' ||
33 substr(SQLERRM, 1, 100));
34 END Debug;
35
36
37 PROCEDURE Run_Aging_Main(
38 ERRBUF OUT VARCHAR2,
39 RETCODE OUT VARCHAR2,
40 p_trace_mode IN VARCHAR2,
41 p_debug_mode IN VARCHAR2)
42 IS
43 CURSOR c_sales_lead IS
44 SELECT sales_lead_id, assign_to_salesforce_id
45 FROM as_sales_leads
46 WHERE nvl(status_code, 'NULL') <> 'DECLINED'
47 AND nvl(deleted_flag,'NULL') <> 'Y';
48
49 l_request_id NUMBER;
50 l_seq_no NUMBER;
51 l_msg VARCHAR2(2000);
52 l_status BOOLEAN;
53 l_submit_request_id NUMBER;
54 l_aging_days_noact NUMBER;
55 l_aging_days_abandon NUMBER;
56 l_aging_abandon_actions VARCHAR2(240);
57 l_aging_noact_actions VARCHAR2(240);
58 BEGIN
59
60 g_debug_flag := p_debug_mode;
61
62 Debug('*** ASXSLAGMA starts ***');
63
64 l_request_id := to_number(fnd_profile.value('CONC_REQUEST_ID'));
65 l_aging_days_noact := to_number(fnd_profile.value('AS_AGING_DAYS_NOACT'));
66 l_aging_days_abandon
67 := to_number(fnd_profile.value('AS_AGING_DAYS_ABANDON'));
68 l_aging_abandon_actions := fnd_profile.value('AS_AGING_ABANDON_ACTIONS');
69 l_aging_noact_actions := fnd_profile.value('AS_AGING_ABANDON_ACTIONS');
70
71 Debug('aging_days_noact=' || to_char(l_aging_days_noact) ||
72 ' aging_noact_actions=' || l_aging_noact_actions);
73 Debug('aging_days_abandon=' || to_char(l_aging_days_abandon) ||
74 ' aging_abandon_actions=' || l_aging_abandon_actions);
75
76 l_seq_no := 0;
77
78 FOR l_sales_lead_rec IN c_sales_lead LOOP
79 l_seq_no := l_seq_no + 1;
80
81 Debug('Submit ASXSLAGWF');
82 Debug('Sales Lead ID : ' || to_char(l_sales_lead_rec.sales_lead_id));
83
84 l_submit_request_id := FND_REQUEST.SUBMIT_REQUEST(
85 'ASX',
86 'ASXSLAGWF',
87 'Concurrent Request for Sales Lead Aging',
88 '',
89 FALSE,
90 p_trace_mode,
91 p_debug_mode,
92 l_request_id,
93 l_seq_no,
94 l_sales_lead_rec.sales_lead_id
95 );
96
97 IF l_submit_request_id = 0
98 THEN
99 l_msg := FND_MESSAGE.GET;
100 Debug(l_msg);
101 END IF;
102 commit;
103 Debug('Submitted Request ID : ' || to_char(l_submit_request_id));
104 END LOOP;
105
106 EXCEPTION
107 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
108 Debug('Cannot restart');
109
110 WHEN others THEN
111 Debug('Exception: others in Assign_Territory_Accesses');
112 Debug('SQLCODE ' || to_char(SQLCODE) ||
113 ' SQLERRM ' || substr(SQLERRM, 1, 100));
114 errbuf := SQLERRM;
115 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
116 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
117 END Run_Aging_Main;
118
119
120 PROCEDURE Run_Aging_Workflow(
121 ERRBUF OUT VARCHAR2,
122 RETCODE OUT VARCHAR2,
123 p_trace_mode IN VARCHAR2,
124 p_debug_mode IN VARCHAR2,
125 p_parent_request_id IN NUMBER,
126 p_sequence_number IN NUMBER,
127 p_sales_lead_id IN NUMBER )
128 IS
129 l_status BOOLEAN;
130 l_request_id NUMBER;
131 l_return_status VARCHAR2(100);
132 l_itemtype VARCHAR2(8);
133 l_itemkey VARCHAR2(50);
134 l_submit_request_id NUMBER;
135 l_aging_days_noact NUMBER;
136 l_aging_days_abandon NUMBER;
137 l_aging_abandon_actions VARCHAR2(240);
138 l_aging_noact_actions VARCHAR2(240);
139 l_assigned_resource_id NUMBER;
140 l_sales_lead_id NUMBER;
141
142 CURSOR c_sales_lead (sales_lead_id_in NUMBER) IS
143 SELECT assign_to_salesforce_id
144 FROM as_sales_leads
145 WHERE sales_lead_id = sales_lead_id_in;
146
147 BEGIN
148 Debug('*** ASXSLAGWF start ***');
149
150 -----------------------------------------------------------------------------
151 l_aging_days_noact := to_number(fnd_profile.value('AS_AGING_DAYS_NOACT'));
152 l_aging_days_abandon
153 := to_number(fnd_profile.value('AS_AGING_DAYS_ABANDON'));
154 l_aging_abandon_actions := fnd_profile.value('AS_AGING_ABANDON_ACTIONS');
155 l_aging_noact_actions := fnd_profile.value('AS_AGING_ABANDON_ACTIONS');
156 -----------------------------------------------------------------------------
157 Debug('Parent Request ID : ' || to_char( p_parent_request_id));
158 Debug('Sequence Number : ' || to_char(p_sequence_number));
159 Debug('Sales Lead ID : ' || to_char(p_sales_lead_id));
160 Debug('Aging Days Noact : ' || to_char(l_aging_days_noact));
161 Debug('Aging Noact Actions : ' || l_aging_noact_actions);
162 Debug('Aging Days Abandon : ' || to_char(l_aging_days_abandon));
163 Debug('Aging Abandon Actions : ' || l_aging_abandon_actions);
164
165 l_request_id := to_number(fnd_profile.value('CONC_REQUEST_ID'));
166 l_sales_lead_id := p_sales_lead_id;
167
168 OPEN c_sales_lead(l_sales_lead_id);
169 FETCH c_sales_lead INTO l_assigned_resource_id;
170 CLOSE c_sales_lead;
171
172 Debug('Request Id : ' || to_char(l_request_id));
173
174 Debug('Calling StartSalesLeadAgingProcess');
175 AS_SALES_LEAD_AGING_WF_PUB.StartSalesLeadAgingProcess(
176 p_request_id => l_request_id,
177 p_sales_lead_id => l_sales_lead_id,
178 p_assigned_resource_id => l_assigned_resource_id,
179 p_aging_days_noact => l_aging_days_noact,
180 p_aging_days_abandon => l_aging_days_abandon,
181 p_aging_abandon_actions => l_aging_abandon_actions,
182 p_aging_noact_actions => l_aging_noact_actions,
183 x_item_type => l_itemtype ,
184 x_item_key => l_itemkey,
185 x_return_status => l_return_status
186 );
187
188 -- Defer to wf_engine background process
189 wf_engine.Background (
190 itemtype => l_itemtype,
191 minthreshold => null,
192 maxthreshold => null,
193 process_deferred => TRUE,
194 process_timeout => TRUE);
195
196 Debug('After StartSalesLeadAgingProcess');
197 COMMIT;
198 Debug('Commit Successfully');
199 EXCEPTION
200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
201 Debug('Cannot restart');
202
203 WHEN others THEN
204 Debug('Exception: others in Assign_Territory_Accesses');
205 Debug('SQLCODE ' || to_char(SQLCODE) ||
206 ' SQLERRM ' || substr(SQLERRM, 1, 100));
207 errbuf := SQLERRM;
208 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
209 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
210
211 END Run_Aging_Workflow;
212
213 END AS_SALES_LEAD_AGING_CONC_PUB;
214