[Home] [Help]
PACKAGE BODY: APPS.ASP_ALERTS_INVOICE
Source
1 PACKAGE BODY ASP_ALERTS_INVOICE as
2 /* $Header: aspaodib.pls 120.3 2005/09/28 13:45 axavier noship $ */
3 ---------------------------------------------------------------------------
4 -- Package Name: ASP_ALERTS_INVOICE
5 ---------------------------------------------------------------------------
6 -- Description:
7 -- Alerts for overdue invoice is obtained by this
8 -- Concurrent Program, which periodically looks at the transaction tables
9 -- in Oracle Collections.
10 --
11 -- Procedures:
12 -- (see the specification for details)
13 --
14 -- History:
15 -- 16-Aug-2005 axavier created.
16 ---------------------------------------------------------------------------
17
18 /*-------------------------------------------------------------------------*
19 | Private Constants
20 *-------------------------------------------------------------------------*/
21 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASP_ALERTS_INVOICE';
22 G_FILE_NAME CONSTANT VARCHAR2(12) := 'aspaodib.pls';
23 G_MODULE CONSTANT VARCHAR2(250) := 'asp.plsql.'||G_PKG_NAME||'.';
24
25
26 /*-------------------------------------------------------------------------*
27 | Private Datatypes
28 *-------------------------------------------------------------------------*/
29
30 /*-------------------------------------------------------------------------*
31 | Private Variables
32 *-------------------------------------------------------------------------*/
33
34 /*-------------------------------------------------------------------------*
35 | Private Routines Specification
36 *-------------------------------------------------------------------------*/
37
38 /*-------------------------------------------------------------------------*
39 | Public Routines
40 *-------------------------------------------------------------------------*/
41
42 --------------------------------------------------------------------------------
43 --
44 -- Procedure: Alert_Overdue_Invoice
45 -- Finds all the overdue invoices.
46 --
47 --------------------------------------------------------------------------------
48
49 PROCEDURE Alert_Overdue_Invoice(
50 errbuf OUT NOCOPY VARCHAR2,
51 retcode OUT NOCOPY VARCHAR2)
52 IS
53
54 l_api_name varchar2(100);
55 l_program_ref_date date;
56 l_current_run_ref_date date;
57
58 l_event_key number;
59 l_item_key varchar2(240);
60 l_delinquency_id number;
61
62
63 l_PROGRAM_START_DATE date;
64 l_REQUEST_ID number;
65 l_PROGRAM_APPLICATION_ID number;
66 l_PROGRAM_ID number;
67 l_PROGRAM_LOGIN_ID number;
68 l_CREATED_BY number;
69 l_LAST_UPDATED_BY number;
70 l_LAST_UPDATE_LOGIN number;
71 save_threshold number;
72 l_debug_runtime number;
73 l_debug_exception number;
74 l_debug_procedure number;
75 l_debug_statment number;
76
77 CURSOR getLastRunDate is
78 SELECT program_ref_date
79 FROM asp_program_run_dates
80 WHERE program_object_code = 'ASPODINV' --'INVOICE'
81 and status_code = 'S'
82 and rownum < 2;
83
84 CURSOR getDelinquencyDeltaRun is
85 Select d.delinquency_id
86 from IEX_DEL_ALERTS_PUB_V d, AR_PAYMENT_SCHEDULES_ALL ps
87 where trunc(d.last_update_date) between trunc(l_program_ref_date+1)
88 and trunc(l_current_run_ref_date)
89 and ps.payment_schedule_id = d.payment_schedule_id
90 and ps.class = 'INV' and ps.status = 'OP';
91
92
93
94 BEGIN
95 l_api_name := 'Alert_Overdue_Invoice';
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 if(l_debug_procedure >= l_debug_runtime) then
101 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
102 end if;
103
104 save_threshold := wf_engine.threshold;
105 l_PROGRAM_START_DATE := sysdate;
106 l_REQUEST_ID := TO_NUMBER(fnd_profile.value('CONC_REQUEST_ID'));
107 l_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
108 l_PROGRAM_ID := TO_NUMBER(fnd_profile.value('CONC_PROGRAM_ID'));
109 l_PROGRAM_LOGIN_ID := TO_NUMBER(fnd_profile.value('USER_ID'));
110 l_CREATED_BY := l_PROGRAM_LOGIN_ID;
111 l_LAST_UPDATED_BY := l_PROGRAM_LOGIN_ID;
112 l_LAST_UPDATE_LOGIN := TO_NUMBER(fnd_profile.value('CONC_LOGIN_ID'));
113
114 open getLastRunDate;
115 fetch getLastRunDate into l_program_ref_date;
116 close getLastRunDate;
117 if(l_debug_procedure >= l_debug_runtime) then
118 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After opening cursor getLastRunDate');
119 end if;
120
121 l_current_run_ref_date := sysdate;
122
123 IF(l_program_ref_date is null) THEN
124 if(l_debug_procedure >= l_debug_runtime) then
125 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_program_ref_date is null');
126 end if;
127 delete asp_program_run_dates where PROGRAM_OBJECT_CODE = 'ASPODINV';
128 insert into asp_program_run_dates(
129 PROGRAM_RUN_ID,
130 PROGRAM_OBJECT_CODE,
131 STATUS_CODE,
132 DESCRIPTION,
133 PROGRAM_START_DATE,
134 PROGRAM_END_DATE,
135 PROGRAM_REF_DATE,
136 RELATED_OBJECT_INFO,
137 REQUEST_ID,
138 PROGRAM_APPLICATION_ID,
139 PROGRAM_ID,
140 PROGRAM_LOGIN_ID,
141 OBJECT_VERSION_NUMBER,
142 CREATION_DATE,
143 CREATED_BY,
144 LAST_UPDATE_DATE,
145 LAST_UPDATED_BY,
146 LAST_UPDATE_LOGIN
147 )values(
148 asp_program_run_dates_s.nextval,
149 'ASPODINV', --'INVOICE'
150 'S',
151 null,
152 l_PROGRAM_START_DATE,
153 null,
154 l_current_run_ref_date,
155 null,
156 l_REQUEST_ID,
157 l_PROGRAM_APPLICATION_ID,
158 l_PROGRAM_ID,
159 l_PROGRAM_LOGIN_ID,
160 1,
161 sysdate,
162 l_CREATED_BY,
163 sysdate,
164 l_LAST_UPDATED_BY,
165 l_LAST_UPDATE_LOGIN
166 );
167
168 commit;
169 ELSE
170 if(l_debug_procedure >= l_debug_runtime) then
171 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_program_ref_date is not null');
172 end if;
173 if(l_debug_procedure >= l_debug_runtime) then
174 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Before opening getDelinquencyDeltaRun');
175 end if;
176
177 for del_rec in getDelinquencyDeltaRun
178 loop
179 wf_engine.threshold := -1;
180 l_delinquency_id := del_rec.delinquency_id;
181 if(l_debug_procedure >= l_debug_runtime) then
182 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_delinquency_id '||l_delinquency_id);
183 end if;
184
185 SELECT l_delinquency_id ||'-'|| to_char(asp_wf_alerts_s.nextval) INTO l_item_key FROM DUAL;
186 if(l_debug_procedure >= l_debug_runtime) then
187 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_item_key '||l_item_key);
188 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Before wf_engine.CreateProcess ASP_ALERT_PROCESS');
189 end if;
190 -- Start the ASP Alert Manager Process (ASP_ALERT_PROCESS) with the following info:
191 wf_engine.CreateProcess( itemtype => 'ASPALERT', itemkey => l_item_key, process => 'ASP_ALERT_PROCESS',user_key=>l_item_key);
192 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_NAME', 'INVOICE_OVERDUE_ALERT');
193 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_SOURCE_OBJECT_CODE', 'INVOICE');
194 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_SOURCE_OBJECT_ID', l_delinquency_id);
195 wf_engine.StartProcess(itemtype => 'ASPALERT', itemkey => l_item_key);
196 if(l_debug_procedure >= l_debug_runtime) then
197 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_item_key '||l_item_key);
198 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After wf_engine.CreateProcess ASP_ALERT_PROCESS');
199 end if;
200 commit;
201 end loop;
202 if(l_debug_procedure >= l_debug_runtime) then
203 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After opening getDelinquencyDeltaRun');
204 end if;
205
206 wf_engine.threshold := save_threshold;
207 update asp_program_run_dates
208 set program_ref_date = l_current_run_ref_date
209 where program_object_code = 'ASPODINV' --'INVOICE'
210 and status_code = 'S';
211 commit;
212 END IF;
213
214
215 EXCEPTION
216 WHEN others THEN
217 wf_engine.threshold := save_threshold;
218 raise;
219
220 END Alert_Overdue_Invoice;
221
222
223 END ASP_ALERTS_INVOICE;
224