[Home] [Help]
PACKAGE: APPS.XLA_PERIOD_CLOSE_EXP_PKG
Source
1 PACKAGE XLA_PERIOD_CLOSE_EXP_PKG AUTHID CURRENT_USER AS
2 -- $Header: xlarppcl.pkh 120.12 2011/05/11 12:29:05 vgopiset ship $
3 /*===========================================================================+
4 | Copyright (c) 2003 Oracle Corporation BelmFont, California, USA |
5 | ALL rights reserved. |
6 +============================================================================+
7 | FILENAME |
8 | xlarppcl.pkh |
9 | |
10 | PACKAGE NAME |
11 | xla_period_close_exp_pkg |
12 | |
13 | DESCRIPTION |
14 | This package generates an XML extract for the Period Close Validation |
15 | program unit. A dynamic query is created based on the parameters that are |
16 | input and data template is used to generate XML. The extract is |
17 | called either when the user submits a concurrent request or when a General |
18 | Ledger Period is closed. |
19 | |
20 | HISTORY |
21 | 26/07/2005 VS Koushik Created |
22 | 15/02/2006 VamsiKrishna Kasina Changed the package to use |
23 | Data Template. |
24 | 30/03/2011 VGOPISET Bug11830219 to change XTE to use the |
25 | XLA_TRANSACTION_ENTITIES_UPG so that |
26 | Trx Security is not applied |
27 +===========================================================================*/
28 --
29 -- To be used in query as bind variable
30 --
31
32 p_application_id NUMBER;
33 p_je_source VARCHAR2(80);
34 p_ledger_id NUMBER;
35 p_ledger VARCHAR2(30);
36 p_period_from VARCHAR2(30);
37 p_period_to VARCHAR2(30);
38 p_dummy_param_1 NUMBER;
39 p_event_class VARCHAR2(30);
40 p_event_class_code VARCHAR2(30);
41 p_dummy_param_2 NUMBER;
42 p_je_category VARCHAR2(30);
43 p_je_category_name VARCHAR2(30);
44 p_mode VARCHAR2(1);
45
46 p_je_source_name VARCHAR2(25);
47 p_object_type_code VARCHAR2(1);
48 C_RETURN_CODE NUMBER;
49 --p_trx_identifiers VARCHAR2(32000):= ' '; commented by preeti/6204675
50 --added by preeti/6204675
51 p_trx_identifiers_1 VARCHAR2(32000):= ' ';
52 p_trx_identifiers_2 VARCHAR2(32000):= ' ';
53 p_trx_identifiers_3 VARCHAR2(32000):= ' ';
54 p_trx_identifiers_4 VARCHAR2(32000):= ' ';
55 p_trx_identifiers_5 VARCHAR2(32000):= ' ';
56 -- end preeti/6204675
57
58 p_ledger_ids VARCHAR2(2000):= ' ';
59 p_event_filter VARCHAR2(2000):= ' ';
60
61 p_header_filter VARCHAR2(2000):= ' ';
62 p_je_source_filter VARCHAR2(2000):= ' ';
63
64 C_EVENTS_COLS_QUERY VARCHAR2(10000):= ' ';
65
66 C_EVENTS_FROM_QUERY VARCHAR2(10000):= ' ';
67
68 C_HEADERS_COLS_QUERY VARCHAR2(10000):= ' ';
69 C_HEADERS_FROM_QUERY VARCHAR2(10000):= ' ';
70
71 CURSOR period_close_cur_evt(p_application_id NUMBER,p_ledger_id NUMBER,p_period_name VARCHAR2)
72 IS
73
74 SELECT xte.ledger_id LEDGER_ID,
75 xte.source_id_int_1 SOURCE_ID_INT_1,
76 xte.security_id_int_1 SECURITY_ID_INT_1,
77 xte.entity_code ENTITY_CODE,
78 xte.legal_entity_id LEGAL_ENTITY_ID,
79 xle.event_status_code EVENT_STATUS_CODE,
80 xle.process_status_code PROCESS_STATUS_CODE,
81 xle.application_id APPLICATION_ID,
82 xle.event_id EVENT_ID,
83 xle.event_number EVENT_NUMBER,
84 xle.on_hold_flag ON_HOLD_FLAG,
85 xle.event_type_code EVENT_TYPE_CODE,
86 xle.event_date EVENT_DATE,
87 xte.transaction_number TRANSACTION_NUMBER,
88 xle.last_update_date LAST_UPDATE_DATE ,
89 xle.creation_date CREATION_DATE,
90 xle.transaction_date TRANSACTION_DATE
91
92 FROM xla_events xle,
93 xla_transaction_entities_upg xte, --bug11830219 changed from XTE to UPG synonym
94 xla_ledger_options xlo,
95 gl_period_statuses glp
96 WHERE xle.entity_id = xte.entity_id
97 AND xle.application_id = xte.application_id
98 AND xle.event_date BETWEEN glp.start_date
99 AND glp.end_date
100 AND xle.application_id = p_application_id
101 AND xle.event_status_code IN ('I',
102 'U')
103 AND xle.process_status_code IN ('I',
104 'U',
105 'R',
106 'D',
107 'E')
108 AND xle.application_id = xlo.application_id
109 AND xlo.capture_event_flag = 'Y'
110 AND EXISTS (SELECT 1
111 FROM gl_ledger_relationships glr1,
112 gl_ledger_relationships glr2
113 WHERE glr1.target_ledger_id = xlo.ledger_id
114 AND glr2.target_ledger_id = p_ledger_id
115 AND glr2.source_ledger_id = glr1.source_ledger_id
116 AND glr2.application_id = glr1.application_id
117 AND (glr1.target_ledger_id = xte.ledger_id
118 OR glr1.primary_ledger_id = xte.ledger_id)
119 AND (glr1.relationship_type_code = 'SUBLEDGER'
120 OR (glr1.target_ledger_category_code = 'PRIMARY'
121 AND glr1.relationship_type_code = 'NONE'))
122 AND glr2.application_id = 101)
123 AND xte.application_id = p_application_id
124 AND glp.period_name = p_period_name
125 AND glp.ledger_id = p_ledger_id
126 AND glp.adjustment_period_flag = 'N'
127 AND glp.application_id = p_application_id;
128
129 CURSOR period_close_cur_header(p_application_id NUMBER,p_ledger_id NUMBER,p_period_name VARCHAR2)
130 IS
131 SELECT aeh.ledger_id LEDGER_ID,
132 aeh.ae_header_id AE_HEADER_ID,
133 xte.source_id_int_1 SOURCE_ID_INT_1,
134 xte.entity_code ENTITY_CODE,
135 xte.security_id_int_1 SECURITY_ID_INT_1,
136 xte.transaction_number TRANSACTION_NUMBER,
137 xte.legal_entity_id LEGAL_ENTITY_ID,
138 xle.event_type_code EVENT_TYPE_CODE,
139 xle.event_date EVENT_DATE,
140 xle.last_update_date LAST_UPDATE_DATE ,
141 xle.creation_date CREATION_DATE,
142 xle.transaction_date TRANSACTION_DATE,
143 aeh.event_id EVENT_ID,
144 aeh.application_id APPLICATION_ID,
145 aeh.accounting_entry_status_code ACCOUNTING_ENTRY_STATUS_CODE,
146 aeh.gl_transfer_status_code GL_TRANSFER_STATUS_CODE
147 FROM xla_ae_headers aeh,
148 xla_events xle,
149 xla_transaction_entities_upg xte, --bug11830219 changed from XTE to UPG synonym
150 gl_period_statuses glp
151 WHERE EXISTS (SELECT 1
152 FROM gl_ledger_relationships glr1,
153 gl_ledger_relationships glr2
154 WHERE aeh.ledger_id = glr2.target_ledger_id
155 AND glr2.source_ledger_id = glr1.source_ledger_id
156 AND glr2.application_id = glr1.application_id
157 AND glr1.target_ledger_id = p_ledger_id
158 AND glr1.application_id = 101)
159 AND xte.entity_id = aeh.entity_id
160 AND xte.application_id = aeh.application_id
161 AND aeh.gl_transfer_status_code IN ('N',
162 'E')
163 AND xle.event_status_code = 'P'
164 AND xle.event_id = aeh.event_id
165 AND xle.application_id = aeh.application_id
166 AND aeh.accounting_date BETWEEN glp.start_date
167 AND glp.end_date
168 AND xte.application_id = p_application_id
169 AND glp.period_name = p_period_name
170 AND glp.ledger_id = p_ledger_id
171 AND glp.adjustment_period_flag = 'N'
172 AND glp.application_id = p_application_id;
173
174
175
176 CURSOR period_close_evt_date_cur(p_application_id NUMBER,p_ledger_id NUMBER,p_start_date DATE,p_end_date DATE)
177 IS
178
179 SELECT xte.ledger_id LEDGER_ID,
180 xte.source_id_int_1 SOURCE_ID_INT_1,
181 xte.security_id_int_1 SECURITY_ID_INT_1,
182 xte.entity_code ENTITY_CODE,
183 xte.legal_entity_id LEGAL_ENTITY_ID,
184 xle.event_status_code EVENT_STATUS_CODE,
185 xle.process_status_code PROCESS_STATUS_CODE,
186 xle.application_id APPLICATION_ID,
187 xle.event_id EVENT_ID,
188 xle.event_number EVENT_NUMBER,
189 xle.on_hold_flag ON_HOLD_FLAG,
190 xle.event_type_code EVENT_TYPE_CODE,
191 xle.event_date EVENT_DATE,
192 xte.transaction_number TRANSACTION_NUMBER,
193 xle.last_update_date LAST_UPDATE_DATE ,
194 xle.creation_date CREATION_DATE,
195 xle.transaction_date TRANSACTION_DATE
196
197 FROM xla_events xle,
198 xla_transaction_entities_upg xte, --bug11830219 changed from XTE to UPG synonym
199 xla_ledger_options xlo
200 WHERE xle.entity_id = xte.entity_id
201 AND xle.application_id = xte.application_id
202 AND xle.event_date BETWEEN p_start_date AND p_end_date
203 AND xle.application_id = p_application_id
204 AND xle.event_status_code IN ('I',
205 'U')
206 AND xle.process_status_code IN ('I',
207 'U',
208 'R',
209 'D',
210 'E')
211 AND xle.application_id = xlo.application_id
212 AND xlo.capture_event_flag = 'Y'
213 AND EXISTS (SELECT 1
214 FROM gl_ledger_relationships glr1,
215 gl_ledger_relationships glr2
216 WHERE glr1.target_ledger_id = xlo.ledger_id
217 AND glr2.target_ledger_id = p_ledger_id
218 AND glr2.source_ledger_id = glr1.source_ledger_id
219 AND glr2.application_id = glr1.application_id
220 AND (glr1.target_ledger_id = xte.ledger_id
221 OR glr1.primary_ledger_id = xte.ledger_id)
222 AND (glr1.relationship_type_code = 'SUBLEDGER'
223 OR (glr1.target_ledger_category_code = 'PRIMARY'
224 AND glr1.relationship_type_code = 'NONE'))
225 AND glr2.application_id = 101)
226 AND xte.application_id = p_application_id;
227
228 CURSOR period_close_hdr_date_cur(p_application_id NUMBER,p_ledger_id NUMBER,p_start_date DATE,p_end_date DATE)
229 IS
230 SELECT aeh.ledger_id LEDGER_ID,
231 aeh.ae_header_id AE_HEADER_ID,
232 xte.source_id_int_1 SOURCE_ID_INT_1,
233 xte.entity_code ENTITY_CODE,
234 xte.security_id_int_1 SECURITY_ID_INT_1,
235 xte.transaction_number TRANSACTION_NUMBER,
236 xte.legal_entity_id LEGAL_ENTITY_ID,
237 xle.event_type_code EVENT_TYPE_CODE,
238 xle.event_date EVENT_DATE,
239 xle.last_update_date LAST_UPDATE_DATE ,
240 xle.creation_date CREATION_DATE,
241 xle.transaction_date TRANSACTION_DATE,
242 aeh.event_id EVENT_ID,
243 aeh.application_id APPLICATION_ID,
244 aeh.accounting_entry_status_code ACCOUNTING_ENTRY_STATUS_CODE,
245 aeh.gl_transfer_status_code GL_TRANSFER_STATUS_CODE
246 FROM xla_ae_headers aeh,
247 xla_events xle,
248 xla_transaction_entities_upg xte --bug11830219 changed from XTE to UPG synonym
249 WHERE EXISTS (SELECT 1
250 FROM gl_ledger_relationships glr1,
251 gl_ledger_relationships glr2
252 WHERE aeh.ledger_id = glr2.target_ledger_id
253 AND glr2.source_ledger_id = glr1.source_ledger_id
254 AND glr2.application_id = glr1.application_id
255 AND glr1.target_ledger_id = p_ledger_id
256 AND glr1.application_id = 101)
257 AND xte.entity_id = aeh.entity_id
258 AND xte.application_id = aeh.application_id
259 AND aeh.gl_transfer_status_code IN ('N',
260 'E')
261 AND xle.event_status_code = 'P'
262 AND xle.event_id = aeh.event_id
263 AND xle.application_id = aeh.application_id
264 AND aeh.accounting_date BETWEEN p_start_date AND p_end_date
265 AND xte.application_id = p_application_id;
266
267
268 FUNCTION beforeReport RETURN BOOLEAN;
269
270 FUNCTION check_period_close(p_application_id IN NUMBER
271 ,p_period_name IN VARCHAR2
272 ,p_ledger_id IN NUMBER) RETURN NUMBER;
273
274 END xla_period_close_exp_pkg;