[Home] [Help]
PACKAGE BODY: APPS.ASP_INVOICE_CONTENT_PROVIDER
Source
1 PACKAGE BODY ASP_INVOICE_CONTENT_PROVIDER as
2 /* $Header: aspaincb.pls 120.4 2005/09/28 13:45 axavier noship $ */
3 ---------------------------------------------------------------------------
4 -- Package Name: ASP_INVOICE_CONTENT_PROVIDER
5 ---------------------------------------------------------------------------
6 -- Description:
7 -- Provides content for the past due invoices.
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_INVOICE_CONTENT_PROVIDER';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'aspaincb.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 past due invoices.
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_delinquency_id number;
60
61 l_customer_id number;
62 l_customer_name varchar2(360);
63 l_transaction_number varchar2(360);
64 l_due_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_invoice_details(c_delinquency_id in number) is
75 SELECT
76 PARTY.PARTY_ID as customer_id,
77 PARTY.PARTY_NAME as customer_name,
78 CT.TRX_NUMBER as transaction_number,
79 PS.DUE_DATE
80 FROM
81 AR_PAYMENT_SCHEDULES_ALL PS
82 ,HZ_CUST_ACCOUNTS_ALL CUST_ACCT
83 ,HZ_PARTIES PARTY
84 ,IEX_DEL_ALERTS_PUB_V DEL -- IEX_DELINQUENCIES.STATUS in ('DELINQUENT', 'PREDELINQUENT')
85 ,RA_CUSTOMER_TRX_ALL CT
86 WHERE
87 PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
88 AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
89 AND PS.CUSTOMER_TRX_ID = DEL.TRANSACTION_ID
90 AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
91 AND PS.PAYMENT_SCHEDULE_ID = DEL.PAYMENT_SCHEDULE_ID
92 AND DEL.DELINQUENCY_ID = c_delinquency_id
93 AND rownum < 2;
94
95 BEGIN
96 l_api_name := 'Create_Content';
97 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
98 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
99 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
100 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
101
102 l_qualified_api_name := G_PKG_NAME||'.'||l_api_name;
103 if(l_debug_procedure >= l_debug_runtime) then
104 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
105 end if;
106
107 If(funcmode = 'RUN') Then
108 l_event_entity := wf_engine.GetItemAttrText( itemtype =>itemtype,
109 itemkey =>itemkey,
110 aname =>'ALERT_SOURCE_OBJECT_CODE');
111
112 l_alert_code := wf_engine.GetItemAttrText( itemtype =>itemtype,
113 itemkey =>itemkey,
114 aname =>'ALERT_NAME');--INVOICE_OVERDUE_ALERT
115
116 l_delinquency_id := wf_engine.GetItemAttrText( itemtype =>itemtype,
117 itemkey =>itemkey,
118 aname =>'ALERT_SOURCE_OBJECT_ID');
119
120 if(l_debug_procedure >= l_debug_runtime) then
121 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_event_entity '||l_event_entity);
122 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_alert_code '||l_alert_code);
123 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_delinquency_id '||l_delinquency_id);
124 end if;
125
126
127 If l_event_entity = 'INVOICE' Then
128 ----------
129 l_sms_text := '';
130 l_email_sub := '';
131 --ASP_WF_INVOICE_MSG_RN ASP_INVOICE_WFRN
132 l_email_content := 'JSP:/OA_HTML/OA.jsp?OAFunc=ASP_WF_INVOICE_MSG_RN' ||
133 '&' || 'DelinquencyId=-'||'&'|| 'MSG_DELINQUENCYID-';
134
135 --***SMS Content
136 BEGIN
137 OPEN get_invoice_details(l_delinquency_id);
138 FETCH get_invoice_details
139 INTO l_customer_id,
140 l_customer_name,
141 l_transaction_number,
142 l_due_date;
143 IF (get_invoice_details%NOTFOUND) THEN
144 l_sms_text := 'INVALID DELINQUENT INVOICE!';
145 l_email_sub := 'INVALID DELINQUENT INVOICE!';
146 END IF;
147 CLOSE get_invoice_details;
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 l_sms_text := 'INVALID DELINQUENT INVOICE!';
152 l_email_sub := 'INVALID DELINQUENT INVOICE!';
153 END;
154
155 --Message ASP_INVOICE_SMS_TEXT - Customer '<CUSTNAME>' has overdue invoice. Transaction <TXNNUM > is past due date <DUEDATE>
156 fnd_message.set_name('ASP', 'ASP_INVOICE_SMS_TEXT');
157 fnd_message.set_token('CUSTNAME', l_customer_name);
158 fnd_message.set_token('TXNNUM', l_transaction_number);
159 fnd_message.set_token('DUEDATE', to_char( l_due_date, 'DD-Mon-YYYY HH24:MI:SS'));
160 l_sms_text := fnd_message.get;
161
162 wf_engine.SetItemAttrText(itemtype, itemkey, 'SMS_TEXT', l_sms_text);
163 --***Done SMS Content
164 if(l_debug_procedure >= l_debug_runtime) then
165 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'SMS_TEXT '||l_sms_text);
166 end if;
167
168 --***EMAIL Content
169 --Message ASP_INVOICE_EMAIL_SUB - Overdue invoice #<TXNNUM> for <CUSTNAME>
170
171 fnd_message.set_name('ASP', 'ASP_INVOICE_EMAIL_SUB');
172 fnd_message.set_token('TXNNUM', l_transaction_number);
173 fnd_message.set_token('CUSTNAME', l_customer_name);
174 l_email_sub := fnd_message.get;
175
176 wf_engine.SetItemAttrText(itemtype, itemkey, 'DELINQUENCYID', l_delinquency_id);
177 --WF_Attr DELINQUENCYID => Msg_Attr MSG_DELINQUENCYID
178
179 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_SUB_TEXT', l_email_sub);
180 --WF_Attr EMAIL_SUB_TEXT => Msg_Attr MSG_SUBJECT_EMAIL
181 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_CONTENT', l_email_content);
182 --WF_Attr EMAIL_CONTENT => Msg_Attr MSG_BODY_EMAIL
183 --***Done EMAIL Content
184 if(l_debug_procedure >= l_debug_runtime) then
185 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'DELINQUENCYID '||l_delinquency_id);
186 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'EMAIL_SUB_TEXT '||l_email_sub);
187 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'EMAIL_CONTENT '||l_email_content);
188 end if;
189
190 ----------
191 resultout := 'COMPLETE:SUCCESS';
192 return;
193 Else
194 resultout := 'COMPLETE:NULL';
195 return;
196 End If;
197 End If;
198 --
199 -- Other execution modes may be created in the future. Your
200 -- activity will indicate that it does not implement a mode
201 -- by returning null
202 --
203 resultout := '';
204 return;
205
206
207 EXCEPTION
208 When no_data_found Then
212 wf_core.raise('WF_ORA');
209 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
210 l_debug_msg := l_qualified_api_name||':NO_DATA_FOUND:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
211 wf_core.token('ORA_ERROR',l_debug_msg);
213
214 When others Then
215 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
216 l_debug_msg := l_qualified_api_name||':OTHERS:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
217 wf_core.token('ORA_ERROR',l_debug_msg);
218 wf_core.raise('WF_ORA');
219
220 END Create_Content;
221
222 End ASP_INVOICE_CONTENT_PROVIDER;