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