[Home] [Help]
PACKAGE BODY: APPS.ASP_SVCCONT_CONTENT_PROVIDER
Source
1 PACKAGE BODY ASP_SVCCONT_CONTENT_PROVIDER as
2 /* $Header: aspasccb.pls 120.2 2005/12/13 11:40 axavier noship $ */
3 ---------------------------------------------------------------------------
4 -- Package Name: ASP_SVCCONT_CONTENT_PROVIDER
5 ---------------------------------------------------------------------------
6 -- Description:
7 -- Provides content for the Service Contracts Alert.
8 --
9 -- Procedures:
10 -- (see the specification for details)
11 --
12 -- History:
13 -- 16-Aug-2005 axavier created.
14 ---------------------------------------------------------------------------
15
16 /*-------------------------------------------------------------------------*
17 | Private Constants
18 *-------------------------------------------------------------------------*/
19 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASP_SVCCONT_CONTENT_PROVIDER';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'aspasccb.pls';
21 G_MODULE CONSTANT VARCHAR2(250) := 'asp.plsql.'||G_PKG_NAME||'.';
22 /*-------------------------------------------------------------------------*
23 | Private Datatypes
24 *-------------------------------------------------------------------------*/
25
26 /*-------------------------------------------------------------------------*
27 | Private Variables
28 *-------------------------------------------------------------------------*/
29
30 /*-------------------------------------------------------------------------*
31 | Private Routines Specification
32 *-------------------------------------------------------------------------*/
33
34 /*-------------------------------------------------------------------------*
35 | Public Routines
36 *-------------------------------------------------------------------------*/
37
38 --------------------------------------------------------------------------------
39 -- Procedure: Create_Content
40 -- Provides content for the Service Contracts Alert.
41 --
42 --------------------------------------------------------------------------------
43
44 PROCEDURE Create_Content(
45 itemtype in VARCHAR2,
46 itemkey in VARCHAR2,
47 actid in NUMBER,
48 funcmode in VARCHAR2,
49 resultout in out NOCOPY VARCHAR2)
50 IS
51 l_event_entity varchar2(100);
52 l_api_name varchar2(100);
53 l_qualified_api_name varchar2(200);
54 l_debug_msg varchar2(1000);
55 l_alert_code varchar2(30);
56 l_sms_text varchar2(32767);
57 l_email_sub varchar2(32767);
58 l_contract_header_id number;
59
60 l_customer_id number;
61 l_customer_name varchar2(360);
62 l_contract_number varchar2(360);
63 l_expiration_date date;
64
65 l_email_content varchar2(240);
66
67 l_debug_runtime number;
68 l_debug_exception number;
69 l_debug_procedure number;
70 l_debug_statment number;
71
72
73 CURSOR get_contract_details(c_contract_header_id in number) is
74 SELECT HZP.party_id customer_id
75 ,HZP.party_name customer_name
76 ,CHRB.contract_number || DECODE(CHRB.contract_number_modifier, NULL,NULL,'-'|| CHRB.contract_number_modifier) contract_number
77 ,CHRB.END_DATE expiration_date
78 FROM okc_k_headers_all_b CHRB
79 ,oks_k_headers_b KHRB
80 ,okc_subclasses_v SCSV
81 ,okc_k_party_roles_b CPL
82 ,hz_parties HZP
83 WHERE CHRB.id = c_contract_header_id --<contract header id>
84 AND CHRB.id = CPL.dnz_chr_id
85 AND SCSV.code = CHRB.scs_code
86 AND SCSV.cls_code = 'SERVICE'
87 AND CHRB.id = KHRB.chr_id
88 AND CPL.cle_id IS NULL
89 AND CPL.rle_code in('CUSTOMER','SUBSCRIBER')
90 AND CPL.jtot_object1_code = 'OKX_PARTY'
91 AND CPL.object1_id1 = HZP.party_id
92 and rownum < 2;
93
94 BEGIN
95 l_api_name := 'Create_Content';
96 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
97 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
98 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
99 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
100
101 l_qualified_api_name := G_PKG_NAME||'.'||l_api_name;
102 if(l_debug_procedure >= l_debug_runtime) then
103 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
104 end if;
105
106 If(funcmode = 'RUN') Then
107 l_event_entity := wf_engine.GetItemAttrText( itemtype =>itemtype,
108 itemkey =>itemkey,
109 aname =>'ALERT_SOURCE_OBJECT_CODE');
110
111 l_alert_code := wf_engine.GetItemAttrText( itemtype =>itemtype,
112 itemkey =>itemkey,
113 aname =>'ALERT_NAME');--SVCCONTRACT_PRE_EXPIRE_ALERT
114
115 l_contract_header_id := wf_engine.GetItemAttrText( itemtype =>itemtype,
116 itemkey =>itemkey,
117 aname =>'ALERT_SOURCE_OBJECT_ID');
118
119 if(l_debug_procedure >= l_debug_runtime) then
120 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_event_entity '||l_event_entity);
121 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_alert_code '||l_alert_code);
122 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_contract_header_id '||l_contract_header_id);
123 end if;
124
125 If l_event_entity = 'SERVICE_CONTRACT' Then
126 ----------
127 l_sms_text := '';
128 l_email_sub := '';
129 --ASP_WF_SVCCON_MSG_RN ASP_CONTRACT_WFRN
130 l_email_content := 'JSP:/OA_HTML/OA.jsp?OAFunc=ASP_WF_SVCCON_MSG_RN' ||
131 '&' || 'ContractId=-'||'&'|| 'MSG_CONTRACTID-';
132
133 --***SMS Content
134 BEGIN
135 OPEN get_contract_details(l_contract_header_id);
136 FETCH get_contract_details
137 INTO l_customer_id,
138 l_customer_name,
139 l_contract_number,
140 l_expiration_date;
141 IF (get_contract_details%NOTFOUND) THEN
142 l_sms_text := 'INVALID SERVICE CONTRACT!';
143 l_email_sub := 'INVALID SERVICE CONTRACT!';
144 END IF;
145 CLOSE get_contract_details;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 l_sms_text := 'INVALID SERVICE CONTRACT!';
150 l_email_sub := 'INVALID SERVICE CONTRACT!';
151 END;
152
153 --Message ASP_SVCCON_SMS_TEXT - Non-renewed contract #<CONTRACTNUM> for customer '<CUSTNAME >' expires on <EXPDATE>.
154 fnd_message.set_name('ASP', 'ASP_SVCCON_SMS_TEXT');
155 fnd_message.set_token('CONTRACTNUM', l_contract_number);
156 fnd_message.set_token('CUSTNAME', l_customer_name);
157 fnd_message.set_token('EXPDATE', to_char( l_expiration_date, 'DD-Mon-YYYY HH24:MI:SS'));
158 l_sms_text := fnd_message.get;
159
160 wf_engine.SetItemAttrText(itemtype, itemkey, 'SMS_TEXT', l_sms_text);
161 --***Done SMS Content
162 if(l_debug_procedure >= l_debug_runtime) then
163 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'SMS_TEXT '||l_sms_text);
164 end if;
165
166 --***EMAIL Content
167 --Message ASP_SVCCON_EMAIL_SUB - Contract #<CONTRACTNUM> for customer '<CUSTNAME>' expires shortly
168
169 fnd_message.set_name('ASP', 'ASP_SVCCON_EMAIL_SUB');
170 fnd_message.set_token('CONTRACTNUM', l_contract_number);
171 fnd_message.set_token('CUSTNAME', l_customer_name);
172 l_email_sub := fnd_message.get;
173
174 wf_engine.SetItemAttrText(itemtype, itemkey, 'CONTRACTID', l_contract_header_id);
175 --WF_Attr CONTRACTID => Msg_Attr MSG_CONTRACTID
176
177 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_SUB_TEXT', l_email_sub);
178 --WF_Attr EMAIL_SUB_TEXT => Msg_Attr MSG_SUBJECT_EMAIL
179 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_CONTENT', l_email_content);
180 --WF_Attr EMAIL_CONTENT => Msg_Attr MSG_BODY_EMAIL
181 --***Done EMAIL Content
182 if(l_debug_procedure >= l_debug_runtime) then
183 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'CONTRACTID '||l_contract_header_id);
184 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'EMAIL_SUB_TEXT '||l_email_sub);
185 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'EMAIL_CONTENT '||l_email_content);
186 end if;
187
188 ----------
189 resultout := 'COMPLETE:SUCCESS';
190 return;
191 Else
192 resultout := 'COMPLETE:NULL';
193 return;
194 End If;
195 End If;
196 --
197 -- Other execution modes may be created in the future. Your
198 -- activity will indicate that it does not implement a mode
199 -- by returning null
200 --
201 resultout := '';
202 return;
203
204
205 EXCEPTION
206 When no_data_found Then
207 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
208 l_debug_msg := l_qualified_api_name||':NO_DATA_FOUND:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
209 wf_core.token('ORA_ERROR',l_debug_msg);
210 wf_core.raise('WF_ORA');
211
212 When others Then
213 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
214 l_debug_msg := l_qualified_api_name||':OTHERS:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
215 wf_core.token('ORA_ERROR',l_debug_msg);
216 wf_core.raise('WF_ORA');
217
218 END Create_Content;
219
220 End ASP_SVCCONT_CONTENT_PROVIDER;