[Home] [Help]
PACKAGE BODY: APPS.PA_XLA_INTF_REV_EVENTS
Source
1 PACKAGE BODY PA_XLA_INTF_REV_EVENTS AS
2 /* $Header: PAXLARVB.pls 120.13 2006/09/20 10:28:14 smaroju noship $ */
3
4 /*----------------------------------------------------------------------------------------+
5 | Procedure : create_events |
6 | Purpose : Will create accounting event for revenues eligible for transfer to SLA |
7 | by calling XLA Create_Event API.
8 | Parameters : |
9 | ================================================================================== |
10 | Name Mode Description |
11 | ================================================================================== |
12 | |
13 | p_request_id IN Request id of the run |
14 | |
15 | p_return_status OUT NOCOPY Return status of the API |
16 | |
17 | ================================================================================== |
18 +----------------------------------------------------------------------------------------*/
19 PROCEDURE create_events (p_request_id NUMBER,
20 p_return_status OUT NOCOPY VARCHAR2)
21 IS
22
23 l_project_id_tab PA_PLSQL_DATATYPES.Num15TabTyp;
24 l_project_id_tab_tmp PA_PLSQL_DATATYPES.Num15TabTyp;
25 l_revenue_num_tab PA_PLSQL_DATATYPES.Num15TabTyp;
26 l_revenue_num_tab_tmp PA_PLSQL_DATATYPES.Num15TabTyp;
27 l_gl_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
28 l_event_entity_info XLA_EVENTS_PUB_PKG.t_array_entity_event_info_s;
29 l_event_entity_info_out XLA_EVENTS_PUB_PKG.t_array_entity_event_info_s;
30 l_fetch_complete BOOLEAN DEFAULT FALSE;
31 l_event_id_tab PA_PLSQL_DATATYPES.Num15TabTyp;
32 l_commit_size NUMBER DEFAULT 200;
33 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
34 l_ledger_id NUMBER;
35 l_legal_entity_id NUMBER;
36 l_org_id NUMBER;
37 l_reject_reason VARCHAR2(250);
38 l_project_num_tab PA_PLSQL_DATATYPES.Char25TabTyp;
39
40 --for Revenue Adjustments
41 l_revenue_num_cr_tab PA_PLSQL_DATATYPES.Num15TabTyp;
42
43 /*Reverted Changes of Bug 5488439 for Bug 5533484
44 --For Bug 5488439
45 l_source_appl_id NUMBER := fnd_profile.value('RESP_APPL_ID'); */
46 l_source_appl_id NUMBER := 275;
47
48 ---Select the eligible revenue(s)for creating corresponding events in SLA.
49 CURSOR c_revenue_cursor
50 IS
51 SELECT rev.project_id,
52 rev.draft_revenue_num,
53 rev.gl_date,
54 p.segment1,
55 rev.draft_revenue_num_credited -- for Revenue Adjustments
56 FROM pa_draft_revenues rev,
57 pa_projects p
58 WHERE rev.transfer_rejection_reason IS NULL
59 AND rev.transfer_status_code = 'R'
60 AND rev.request_id = p_request_id
61 AND rev.event_id IS NULL
62 AND rev.project_id = p.project_id
63 ORDER BY rev.project_id, rev.draft_revenue_num;
64
65 --Cursor to fetch the Legal Entity and Ledger attached to an Operating Unit
66 CURSOR c_ledger
67 IS
68 SELECT set_of_books_id,
69 org_id
70 FROM pa_implementations;
71
72 CURSOR c_legal_entity(p_org_id number)
73 IS
74 SELECT TO_NUMBER(org_information2)
75 FROM hr_organization_information
76 WHERE organization_id = p_org_id
77 AND org_information_context = 'Operating Unit Information';
78
79
80 CURSOR c_event_cursor
81 Is Select Event_ID, source_id_int_1 , source_id_int_2
82 From XLA_EVENTS_INT_GT;
83
84
85 --Fetch the revenues eligible for transfer and create a plsql record for each of the revenue header.
86 BEGIN
87
88
89
90 IF l_debug_mode = 'Y' THEN
91 PA_MCB_INVOICE_PKG.log_message('Into PA_XLA_REVENUE_EVENTS.Create_Events');
92 END IF;
93
94 select meaning
95 into l_reject_reason
96 from pa_lookups
97 where lookup_type = 'TRANSFER REJECTION REASON'
98 and lookup_code = 'PA_SLA_AC_CR_FAIL';
99
100 OPEN c_ledger;
101 FETCH c_ledger INTO l_ledger_id, l_org_id;
102 CLOSE c_ledger;
103
104 OPEN c_legal_entity(l_org_id);
105 FETCH c_legal_entity INTO l_legal_entity_id;
106 CLOSE c_legal_entity;
107
108 OPEN c_revenue_cursor;
109
110 IF l_debug_mode = 'Y' THEN
111 PA_MCB_INVOICE_PKG.log_message('PA_XLA_REVENUE_EVENTS.Create_Events : Before Loop');
112 END IF;
113
114 LOOP
115 FETCH c_revenue_cursor BULK COLLECT INTO l_project_id_tab,
116 l_revenue_num_tab,
117 l_gl_date_tab,
118 l_project_num_tab,
119 l_revenue_num_cr_tab LIMIT l_commit_size; -- for Revenue Adjustments
120
121
122 IF c_revenue_cursor%NOTFOUND THEN
123 CLOSE c_revenue_cursor;
124 l_fetch_complete := TRUE;
125 END IF;
126
127 DELETE FROM XLA_EVENTS_INT_GT;
128
129
130 IF l_debug_mode = 'Y' THEN
131 PA_MCB_INVOICE_PKG.log_message('PA_XLA_REVENUE_EVENTS.Create_Events : Before Insert into XLA_EVENTS_INT_GT');
132 END IF;
133
134 FORALL i IN 1..l_revenue_num_tab.count
135 INSERT INTO XLA_EVENTS_INT_GT
136 (
137 event_type_code ,
138 event_date ,
139 event_status_code ,
140 source_id_int_1 ,
141 source_id_int_2 ,
142 security_id_int_1 ,
143 APPLICATION_ID ,
144 LEDGER_ID ,
145 LEGAL_ENTITY_ID ,
146 ENTITY_CODE ,
147 event_id ,
148 security_id_char_1 ,
149 transaction_number ,
150 event_class_code -- inserting for Revenue Adjustments
151 )
152 VALUES
153 (
154 decode(l_revenue_num_cr_tab(i), NULL, 'REVENUE', 'REVENUE_ADJ') ,
155 l_gl_date_tab(i) ,
156 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED ,
157 l_project_id_tab(i) ,
158 l_revenue_num_tab(i) ,
159 l_org_id ,
160 275 ,
161 l_ledger_id ,
162 l_legal_entity_id ,
163 'REVENUE' ,
164 xla_events_s.nextval * -1 ,
165 NULL,
166 --'Revenue' ,
167 l_project_num_tab(i)||'-'||l_revenue_num_tab(i) ,
168 decode(l_revenue_num_cr_tab(i), NULL, 'REVENUE', 'REVENUE_ADJ')
169 );
170
171 IF l_debug_mode = 'Y' THEN
172 PA_MCB_INVOICE_PKG.log_message('PA_XLA_REVENUE_EVENTS.Create_Events : Before call to CREATE_BULK_EVENTS');
173 END IF;
174
175 --For Bug 5488439 :Changed source_application_id
176 xla_events_pub_pkg.create_bulk_events ( p_source_application_id => l_source_appl_id,
177 p_application_id => 275,
178 p_legal_entity_id => NULL,
179 p_ledger_id => l_ledger_id,
180 p_entity_type_code => 'REVENUE');
181
182 IF l_debug_mode = 'Y' THEN
183 PA_MCB_INVOICE_PKG.log_message('PA_XLA_REVENUE_EVENTS.Create_Events : After call to CREATE_BULK_EVENTS');
184 END IF;
185
186
187 Open c_event_cursor;
188 Fetch c_event_cursor BULK COLLECT INTO l_event_id_tab, l_project_id_tab_tmp, l_revenue_num_tab_tmp;
189 Close c_event_cursor;
190
191
192 --Update event_id of pa_draft_revenues_all with the generated event_id in successful
193 --cases.
194
195 IF l_debug_mode = 'Y' THEN
196 PA_MCB_INVOICE_PKG.log_message('PA_XLA_REVENUE_EVENTS.Create_Events : Before draft revenue updation ');
197 END IF;
198
199 FORALL l_index IN l_revenue_num_tab_tmp.first .. l_revenue_num_tab_tmp.last
200 UPDATE pa_draft_revenues_all
201 SET event_id = l_event_id_tab(l_index),
202 transfer_rejection_reason = DECODE(sign(nvl(l_event_id_tab(l_index), -1)), 1 , NULL, l_reject_reason)
203 WHERE project_id = l_project_id_tab_tmp(l_index)
204 AND draft_revenue_num = l_revenue_num_tab_tmp(l_index);
205
206
207 l_project_id_tab.delete;
208 l_revenue_num_tab_tmp.delete;
209 l_revenue_num_tab.delete;
210 l_project_id_tab_tmp.delete;
211 l_gl_date_tab.delete;
212 l_event_id_tab.delete;
213 l_project_num_tab.delete;
214 l_revenue_num_cr_tab.delete;
215
216 IF l_fetch_complete THEN
217 EXIT;
218 END IF;
219
220 END LOOP;
221
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 PA_MCB_INVOICE_PKG.log_message('CREATE_EVENTS: In Exception');
226 PA_MCB_INVOICE_PKG.log_message(sqlerrm);
227 p_return_status := sqlerrm;
228 RAISE;
229
230 END create_events;
231
232 FUNCTION Get_Sla_Ccid(
233 P_Acct_Event_Id IN PA_Draft_Revenues_All.Event_Id%TYPE
234 ,P_Transfer_Status_Code IN PA_Draft_Revenues_All.Transfer_Status_Code%TYPE
235 ,P_Source_Distribution_Id_Num_1 IN XLA_Distribution_Links.Source_Distribution_Id_Num_1%TYPE
236 ,P_Source_Distribution_Id_Num_2 IN XLA_Distribution_Links.Source_Distribution_Id_Num_2%TYPE
237 ,P_Distribution_Type IN XLA_Distribution_Links.SOURCE_DISTRIBUTION_TYPE%TYPE
238 ,P_Ledger_Id IN PA_Implementations_All.Set_Of_Books_Id%TYPE
239 )
240 RETURN NUMBER
241 IS
242 l_ccid PA_Cost_Distribution_Lines_All.Dr_Code_Combination_Id%TYPE;
243 BEGIN
244 SELECT code_combination_id
245 INTO l_ccid
246 FROM xla_distribution_links xdl,
247 xla_ae_headers aeh,
248 xla_ae_lines ael
249 WHERE xdl.source_distribution_id_num_1 = P_Source_Distribution_Id_Num_1
250 AND xdl.source_distribution_id_num_2 = P_Source_Distribution_Id_Num_2
251 AND xdl.Source_Distribution_Type = P_Distribution_Type
252 AND xdl.application_id = 275
253 AND xdl.ae_header_id = aeh.ae_header_id
254 AND xdl.ae_line_num = ael.ae_line_num
255 AND xdl.ae_header_id = ael.ae_header_id
256 AND aeh.application_id = ael.application_id
257 AND ael.application_id = xdl.application_id
258 AND aeh.balance_type_code = 'A'
259 AND aeh.accounting_entry_status_code = 'F'
260 AND aeh.ledger_id = P_Ledger_Id
261 AND xdl.event_id = P_Acct_Event_Id;
262
263 return(l_ccid);
264 EXCEPTION
265 WHEN OTHERS THEN
266 return(NULL);
267 END Get_Sla_CCID;
268
269 END PA_XLA_INTF_REV_EVENTS;