[Home] [Help]
PACKAGE BODY: APPS.PO_DRAFT_EVENTS_PKG
Source
1 PACKAGE BODY PO_DRAFT_EVENTS_PKG AS
2 /* $Header: PO_DRAFT_EVENTS_PKG.plb 120.3.12020000.2 2013/02/11 13:22:40 vegajula noship $*/
3
4 g_pkg_name CONSTANT
5 VARCHAR2(30)
6 := 'PO_DRAFT_EVENTS_PKG';
7
8 g_log_head CONSTANT
9 VARCHAR2(50)
10 := 'po.plsql.' || g_pkg_name || '.';
11
12
13 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
14 g_debug_unexp BOOLEAN := PO_DEBUG.is_debug_unexp_on;
15
16
17 -------------------------------------------------------------------------------
18 --Start of Comments
19 --Name: delete_draft_events
20 --Pre-reqs:
21 -- None.
22 --Modifies:
23 -- None.
24 --Locks:
25 -- None.
26 --Function:
27 -- This procedure will be used to delete invalid/draft events
28 -- for all PO,PA and Requistion encumbrance events
29 --Parameters:
30 --IN:
31 -- p_init_msg_list
32 -- p_ledger_id
33 -- p_start_date
34 -- p_end_date
35 -- p_calling_sequence
36 -- p_currency_code_func: currency code of the functional currency.
37 --IN OUT:
38 -- None.
39 --OUT:
40 -- x_return_status
41 -- x_msg_count
42 -- x_msg_data
43 --Notes:
44 -- This procedure will be called from PSA BC optimizer to delete
45 -- invalid/draft encumbrance events. This is required to avoid showing
46 -- in subledger exception report.
47 --Testing:
48 --
49 --End of Comments
50 -------------------------------------------------------------------------------
51 PROCEDURE delete_draft_events (
52 p_init_msg_list IN VARCHAR2,
53 p_ledger_id IN NUMBER,
54 p_start_date IN DATE,
55 p_end_date IN DATE,
56 p_calling_sequence IN VARCHAR2,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2
60 ) IS
61
62 CURSOR c_get_unprocessed_events IS
63 SELECT DISTINCT xe.event_id,
64 xe.event_type_code,
65 xe.event_date,
66 xe.event_status_code,
67 xe.process_status_code,
68 xte.entity_id,
69 xte.legal_entity_id,
70 xte.entity_code,
71 xte.source_id_int_1,
72 xte.source_id_int_2,
73 xte.source_id_int_3,
74 xte.source_id_int_4,
75 xte.source_id_char_1
76 FROM xla_transaction_entities xte,
77 xla_events xe
78 WHERE NVL(xe.budgetary_control_flag, 'N') ='Y'
79 AND xte.entity_code IN ('REQUISITION','PURCHASE_ORDER','RELEASE')
80 AND xte.application_id = 201
81 AND xe.application_id =xte.application_id
82 AND xte.entity_id = xe.entity_id
83 AND xe.EVENT_STATUS_CODE in ('U' ,'I')
84 AND xe.PROCESS_STATUS_CODE IN ('I','D')
85 AND xte.ledger_id = p_ledger_id
86 AND xe.event_date BETWEEN p_start_date AND p_end_date;
87
88 TYPE Event_tab_type IS TABLE OF XLA_EVENTS_INT_GT%ROWTYPE INDEX BY BINARY_INTEGER;
89 l_events_Tab Event_tab_type;
90 l_event_count NUMBER;
91
92
93 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_DRAFT_EVENTS';
94 l_log_head CONSTANT VARCHAR2(200) := g_log_head || l_api_name;
95 l_curr_calling_sequence VARCHAR2(2000);
96 l_progress VARCHAR2(3);
97
98 BEGIN
99
100 fnd_file.put_line(fnd_file.log ,'>> PO_DRAFT_EVENTS_PKG.DELETE_DRAFT_EVENTS');
101
102 l_progress := '000';
103
104 IF g_debug_stmt THEN
105
106 PO_DEBUG.debug_begin(l_log_head);
107 PO_DEBUG.debug_var(l_log_head,l_progress,'p_ledger_id',p_ledger_id);
108 PO_DEBUG.debug_var(l_log_head,l_progress,'p_start_date',p_start_date);
109 PO_DEBUG.debug_var(l_log_head,l_progress,'p_end_date',p_end_date);
110 PO_DEBUG.debug_var(l_log_head,l_progress,'p_calling_sequence',p_calling_sequence);
111
112 END IF;
113
114 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
115 Fnd_Msg_Pub.Initialize;
116 END IF;
117
118 l_curr_calling_sequence := l_log_head||'<-'||p_calling_sequence;
119
120 xla_security_pkg.set_security_context(602); --passing SLA application_id
121
122 l_progress := '001';
123
124 DELETE FROM XLA_EVENTS_INT_GT;
125
126 IF g_debug_stmt THEN
127 PO_DEBUG.debug_stmt(l_log_head,l_progress,'# Rows deleted from xla_events_int_gt'|| SQL%ROWCOUNT );
128 END IF;
129
130 l_progress := '002';
131
132 l_event_count := 0;
133 FOR rec_events IN c_get_unprocessed_events
134 LOOP
135 l_event_count := l_event_count+1;
136 l_events_tab(l_event_count).entity_id := rec_events.entity_id;
137 l_events_tab(l_event_count).application_id := 201;
138 l_events_tab(l_event_count).ledger_id := p_ledger_id;
139 l_events_tab(l_event_count).legal_entity_id := rec_events.legal_entity_id;
140 l_events_tab(l_event_count).entity_code := rec_events.entity_code;
141 l_events_tab(l_event_count).event_id := rec_events.event_id;
142 l_events_tab(l_event_count).event_status_code := rec_events.event_status_code;
143 l_events_tab(l_event_count).process_status_code := rec_events.process_status_code;
144 l_events_tab(l_event_count).source_id_int_1 := rec_events.source_id_int_1;
145 END LOOP;
146
147 l_progress := '003';
148
149 IF l_event_count > 0 THEN
150
151 FORALL i IN 1..l_event_count
152 INSERT INTO XLA_EVENTS_INT_GT
153 VALUES l_events_tab(i) ;
154
155 IF g_debug_stmt THEN
156 PO_DEBUG.debug_stmt(l_log_head,l_progress,'#Rows inserted into xla_events_int_gt table:' || l_event_count);
157 END IF;
158
159 END IF;
160
161 l_progress := '004';
162
163 IF g_debug_stmt THEN
164 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENT ');
165 END IF;
166
167 XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 201);
168
169 IF g_debug_stmt THEN
170 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Deletion of Unprocessed Events');
171 END IF;
172
173 fnd_file.put_line(fnd_file.log ,'The following BC unprocessed/Error events have been deleted');
174 fnd_file.put_line(fnd_file.log ,'Event_id Event_status_code Process_status_code');
175 fnd_file.put_line(fnd_file.log ,'--------- ----------------- -------------------');
176
177 FOR i IN 1..l_event_count LOOP
178 fnd_file.put_line(fnd_file.log ,l_events_tab(i).event_id||' '||
179 l_events_tab(i).event_status_code ||' '||
180 l_events_tab(i).process_status_code);
181
182
183 fnd_file.put_line(fnd_file.log ,'Count of BC events deleted:' || l_event_count);
184 fnd_file.put_line(fnd_file.log ,'>> PO_DRAFT_EVENTS_PKG.DELETE_DRAFT_EVENTS');
185
186 l_progress := '005';
187
188 IF g_debug_stmt THEN
189 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Deleting the events from po_bc_distributions');
190 END IF;
191 -- Bug 14832613: Removed the FORALL statement and included this in the FOR LOOP above
192 -- as FORALL Does not support usage of variable of record type for db versions less than 11g.
193
194 DELETE FROM po_bc_distributions
195 WHERE ae_event_id = l_events_tab(i).event_id;
196
197 END LOOP;
198
199 x_return_status := Fnd_Api.G_Ret_Sts_Success;
200
201 IF g_debug_stmt THEN
202 PO_DEBUG.debug_end(l_log_head);
203 END IF;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207
208 IF g_debug_unexp THEN
209 PO_DEBUG.debug_exc(l_log_head,l_progress);
210 END IF;
211
212 x_return_status := Fnd_Api.G_Ret_Sts_Error;
213
214 IF (SQLCODE <> -20001) THEN
215 FND_MESSAGE.SET_NAME('SQLAP','PO_DEBUG');
216 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
217 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
218 END IF;
219
220 Fnd_Msg_Pub.Count_And_Get
221 (
222 p_count => x_msg_count,
223 p_data => x_msg_data
224 );
225
226 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
227 fnd_msg_pub.add;
228 RAISE;
229 END delete_draft_events;
230
231 END PO_DRAFT_EVENTS_PKG;