[Home] [Help]
PACKAGE BODY: APPS.RCV_SLA_MRC_UPDATE_PKG
Source
1 PACKAGE BODY RCV_SLA_MRC_UPDATE_PKG AS
2 /* $Header: RCVPUMCB.pls 120.4 2006/04/24 00:00 bigoyal noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'RCV_SLA_MRC_UPDATE_PKG';
5 G_LOG_LEVEL CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
6 gUserId number := nvl(fnd_global.user_id, -888);
7 gLoginId number := nvl(fnd_global.login_id, -888);
8 gUpdateDate DATE := sysdate;
9
10 -------------------------------------------------------------------------------------
11 -- API name : Update_Receiving_MRC_Subledger
12 -- Type : Private
13 -- Function : To update Receiving MRC Sub Ledger to SLA data model
14 -- Pre-reqs :
15 -- Parameters :
16 -- IN : X_upg_batch_id in number(15),
17 -- X_je_category_name in varchar2(30)
18 -- OUT : X_errbuf out NOCOPY varchar2,
19 -- X_retcode out NOCOPY varchar2
20 --
21 -- Notes : The API is called from CST_SLA_UPDATE_PKG.Update_RCV_Subledger
22 --
23 -- End of comments
24 -------------------------------------------------------------------------------------
25
26 PROCEDURE Update_Receiving_MRC_Subledger (
27 X_errbuf out NOCOPY varchar2,
28 X_retcode out NOCOPY varchar2,
29 X_upg_batch_id in number,
30 X_je_category_name in varchar2 default 'Receiving')
31 IS
32 l_upg_batch_id number(15):=0;
33 l_je_category_name varchar2(30);
34
35 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.RCV_SLA_MRC_UPDATE_PKG.Update_Receiving_MRC_Subledger';
36
37 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= G_LOG_LEVEL AND
38 fnd_log.TEST(fnd_log.level_unexpected, l_module);
39 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= G_LOG_LEVEL;
40 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= G_LOG_LEVEL;
41 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_procedure >= G_LOG_LEVEL;
42
43 l_stmt_num number;
44
45 BEGIN
46 l_stmt_num :=0;
47
48 IF l_plog THEN
49 fnd_log.string(
50 fnd_log.level_procedure,
51 l_module||'.'||l_stmt_num,
52 'Entering RCV_SLA_MRC_UPDATE_PKG.Update_Receiving_MRC_Subledger with '||
53 'X_upg_batch_id = '||X_upg_batch_id||','||
54 'X_je_category_name = '||X_je_category_name
55 );
56 END IF;
57
58 l_upg_batch_id := X_upg_batch_id;
59
60 l_je_category_name := X_je_category_name;
61
62 x_retcode := FND_API.G_RET_STS_SUCCESS;
63
64 insert all
65 when (line_id=1) then
66 into xla_ae_headers (
67 upg_batch_id,
68 application_id,
69 AMB_CONTEXT_CODE,
70 entity_id,
71 event_id,
72 event_type_code,
73 ae_header_id,
74 ledger_id,
75 je_category_name,
76 ACCOUNTING_DATE,
77 PERIOD_NAME,
78 BALANCE_TYPE_CODE,
79 BUDGET_VERSION_ID,
80 DOC_SEQUENCE_ID,
81 DOC_SEQUENCE_VALUE,
82 GL_TRANSFER_STATUS_CODE,
83 GL_TRANSFER_DATE,
84 ACCOUNTING_ENTRY_STATUS_CODE,
85 ACCOUNTING_ENTRY_TYPE_CODE,
86 CREATION_DATE,
87 created_by,
88 last_update_date,
89 last_updated_by,
90 LAST_UPDATE_LOGIN,
91 PROGRAM_UPDATE_DATE,
92 PROGRAM_APPLICATION_ID,
93 PROGRAM_ID,
94 REQUEST_ID,
95 UPG_SOURCE_APPLICATION_ID,
96 description)
97 values (
98 upg_batch_id,
99 application_id,
100 AMB_CONTEXT_CODE,
101 entity_id,
102 event_id,
103 event_type_code,
104 xla_ae_headers_s.NEXTVAL,
105 ledger_id,
106 je_category_name,
107 ACCOUNTING_DATE,
108 PERIOD_NAME,
109 BALANCE_TYPE_CODE,
110 BUDGET_VERSION_ID,
111 DOC_SEQUENCE_ID,
112 DOC_SEQUENCE_VALUE,
113 GL_TRANSFER_STATUS_CODE,
114 GL_TRANSFER_DATE,
115 ACCOUNTING_ENTRY_STATUS_CODE,
116 ACCOUNTING_ENTRY_TYPE_CODE,
117 CREATION_DATE,
118 created_by,
119 last_update_date,
120 last_updated_by,
121 LAST_UPDATE_LOGIN,
122 PROGRAM_UPDATE_DATE,
123 PROGRAM_APPLICATION_ID,
124 PROGRAM_ID,
125 REQUEST_ID,
126 UPG_SOURCE_APPLICATION_ID,
127 description)
128 select
129 /*+ leading(cxs) use_nl(rrs rt rmt) index(rrs) index(rt) index(rmt) index(rae) index(gcc)*/
130 l_upg_batch_id upg_batch_id,
131 707 application_id,
132 'DEFAULT' AMB_CONTEXT_CODE,
133 cxs.entity_id entity_id,
134 cxs.event_id event_id,
135 event_type_code,
136 rrs.set_of_books_id ledger_id,
137 l_je_category_name je_category_name,
138 rrs.accounting_date accounting_date,
139 rrs.PERIOD_NAME PERIOD_NAME,
140 rrs.actual_flag BALANCE_TYPE_CODE,
141 rrs.budget_version_id BUDGET_VERSION_ID,
142 rrs.SUBLEDGER_DOC_SEQUENCE_ID DOC_SEQUENCE_ID,
143 rrs.SUBLEDGER_DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE,
144 'Y' GL_TRANSFER_STATUS_CODE,
145 nvl(rrs.DATE_CREATED_IN_GL,rrs.accounting_date) GL_TRANSFER_DATE,
146 'F' ACCOUNTING_ENTRY_STATUS_CODE,
147 'STANDARD' ACCOUNTING_ENTRY_TYPE_CODE,
148 rrs.creation_date creation_date,
149 rrs.created_by created_by,
150 rrs.last_update_date last_update_date,
151 rrs.last_updated_by last_updated_by,
152 rrs.last_update_login LAST_UPDATE_LOGIN,
153 rrs.program_update_date PROGRAM_UPDATE_DATE,
154 rrs.program_application_id PROGRAM_APPLICATION_ID,
155 rrs.program_id PROGRAM_ID,
156 rrs.request_id REQUEST_ID,
157 201 UPG_SOURCE_APPLICATION_ID,
158 rrs.je_header_name description,
159 row_number() over(partition by rt.transaction_id,rae.accounting_event_id,NVL(rae.ORGANIZATION_ID, rt.ORGANIZATION_ID),rrs.set_of_books_id order by rt.transaction_id) as line_id
160 from rcv_mc_transactions rmt,
161 rcv_transactions rt,
162 rcv_accounting_events rae,
163 rcv_mc_rec_sub_ledger rrs,
164 cst_xla_rcv_event_map cem,
165 GL_CODE_COMBINATIONS gcc,
166 cst_xla_seq_gt cxs
167 where rt.transaction_id=cxs.source_id_int_1
168 and rt.transaction_id = rmt.transaction_id
169 and rmt.set_of_books_id = rrs.set_of_books_id
170 and nvl(rae.organization_id,rt.organization_id)=cxs.source_id_int_3
171 and rrs.rcv_transaction_id = rt.transaction_id
172 and ((rae.accounting_event_id is not null
173 and rae.accounting_event_id = rrs.accounting_event_id
174 and rae.accounting_event_id = cxs.source_id_int_2)
175 or (rae.accounting_event_id is null
176 and rrs.accounting_event_id is null)
177 )
178 and rrs.ENCUMBRANCE_TYPE_ID is null
179 and rt.transaction_id = rae.rcv_transaction_id (+)
180 and nvl(rae.EVENT_TYPE_ID,decode(rt.transaction_type,
181 'RECEIVE', 1, 'DELIVER', 2, 'CORRECT',3,
182 'MATCH', 4, 'RETURN TO RECEIVING', 5,
183 'RETURN TO VENDOR', 6, -1))=cem.transaction_type_id
184 and (cem.transaction_type_id <> 3
185 or
186 (cem.transaction_type_id = 3
187 and rt.PARENT_TRANSACTION_ID is not null
188 and cem.attribute = (SELECT TRANSACTION_TYPE
189 FROM RCV_TRANSACTIONS rt1
190 WHERE rt1.transaction_id = rt.PARENT_TRANSACTION_ID))
191 )
192 and gcc.CODE_COMBINATION_ID=rrs.CODE_COMBINATION_ID;
193
194
195
196
197
198
199 insert into xla_ae_lines (
200 upg_batch_id,
201 application_id,
202 ae_header_id,
203 ae_line_num,
204 code_combination_id,
205 gl_transfer_mode_code,
206 ACCOUNTED_DR,
207 ACCOUNTED_CR,
208 CURRENCY_CODE,
209 CURRENCY_CONVERSION_DATE,
210 CURRENCY_CONVERSION_RATE,
211 CURRENCY_CONVERSION_TYPE,
212 ENTERED_DR,
213 ENTERED_CR,
214 accounting_class_code,
215 gl_sl_link_id,
216 gl_sl_link_table,
217 USSGL_TRANSACTION_CODE,
218 CONTROL_BALANCE_FLAG,
219 GAIN_OR_LOSS_FLAG,
220 CREATION_DATE,
221 created_by,
222 last_update_date,
223 last_updated_by,
224 LAST_UPDATE_LOGIN,
225 PROGRAM_UPDATE_DATE,
226 PROGRAM_APPLICATION_ID,
227 PROGRAM_ID,
228 REQUEST_ID,
229 description,
230 accounting_date,
231 ledger_id)
232 SELECT
233 /*+ leading(cxs) use_nl(rrs rt rmt xla) index(rrs) index(rt) index(rmt) index(rae) index(gcc) index(xla)*/
234 l_upg_batch_id,
235 707,
236 xla.ae_header_id,
237 row_number() over(partition by rt.transaction_id,rae.accounting_event_id,NVL(rae.ORGANIZATION_ID, rt.ORGANIZATION_ID),rrs.set_of_books_id order by rt.transaction_id) as line_id,
238 rrs.CODE_COMBINATION_ID ccid,
239 'D' GL_Update_code,
240 rrs.accounted_dr ACCOUNTED_DR,
241 rrs.accounted_cr ACCOUNTED_CR,
242 rrs.currency_code,
243 rrs.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE,
244 rrs.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE,
245 rrs.USER_CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE,
246 rrs.entered_dr ENTERED_DR,
247 rrs.entered_cr ENTERED_CR,
248 decode(nvl(rrs.accounting_line_type,'888'),
249 'Accrual','ACCRUAL',
250 'Charge','CHARGE',
251 'Clearing','CLEARING',
252 'IC Accrual','INTERCOMPANY_ACCRUAL',
253 'IC Cost of Sales','INTERCOMPANY_COGS',
254 'Receiving Inspection','RECEIVING_INSPECTION',
255 'Retroprice Adjustment','RETROACTIVE_PRICE_ADJUSTMENT',
256 '888',decode(cem.transaction_type_id,
257 1, decode(sign(rrs.accounted_cr),1,'ACCRUAL','RECEIVING_INSPECTION'),
258 2, decode(sign(rrs.accounted_cr),1,'RECEIVING_INSPECTION','CHARGE'),
259 3, decode(cem.attribute,
260 'RECEIVE', decode(sign(rrs.accounted_cr),1,'ACCRUAL','RECEIVING_INSPECTION'),
261 'MATCH', decode(sign(rrs.accounted_cr),1,'ACCRUAL','RECEIVING_INSPECTION'),
262 'DELIVER', decode(sign(rrs.accounted_cr),1,'RECEIVING_INSPECTION','CHARGE'),
263 'RETURN TO VENDOR',decode(sign(rrs.accounted_cr),1,'RECEIVING_INSPECTION','ACCRUAL'),
264 'RETURN TO RECEIVING', decode(sign(rrs.accounted_cr),1,'CHARGE','RECEIVING_INSPECTION'),
265 ''),
266 4, decode(sign(rrs.accounted_cr),1,'ACCRUAL','RECEIVING_INSPECTION'),
267 5, decode(sign(rrs.accounted_cr),1,'CHARGE','RECEIVING_INSPECTION'),
268 6, decode(sign(rrs.accounted_cr),1,'RECEIVING_INSPECTION','ACCRUAL'),
269 ''),
270 'UNKNOWN') accounting_class_code,
271 rrs.gl_sl_link_id link_id,
272 'RSL' link_table,
273 rrs.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
274 decode(gcc.reference3,'Y', 'P', null) CONTROL_BALANCE_FLAG,
275 'N',
276 rrs.creation_date creation_date,
277 rrs.created_by created_by,
278 rrs.last_update_date last_update_date,
279 rrs.last_updated_by last_updated_by,
280 rrs.last_update_login LAST_UPDATE_LOGIN,
281 rrs.program_update_date PROGRAM_UPDATE_DATE,
282 rrs.program_application_id PROGRAM_APPLICATION_ID,
283 rrs.program_id PROGRAM_ID,
284 rrs.request_id REQUEST_ID,
285 rrs.je_line_description je_line_description,
286 rrs.accounting_date accounting_date,
287 rrs.set_of_books_id ledger_id
288 from rcv_transactions rt,
289 rcv_accounting_events rae,
290 xla_ae_headers xla,
291 rcv_mc_rec_sub_ledger rrs,
292 cst_xla_rcv_event_map cem,
293 GL_CODE_COMBINATIONS gcc,
294 cst_xla_seq_gt cxs
295 where rt.transaction_id=cxs.source_id_int_1
296 and xla.event_id = cxs.event_id
297 and xla.entity_id = cxs.entity_id
298 and xla.upg_batch_id = l_upg_batch_id
299 and xla.ledger_id = rrs.set_of_books_id
300 and xla.application_id = 707
301 and nvl(rae.organization_id,rt.organization_id)=cxs.source_id_int_3
302 and rrs.rcv_transaction_id = rt.transaction_id
303 and ((rae.accounting_event_id is not null
304 and rae.accounting_event_id = rrs.accounting_event_id
305 and rae.accounting_event_id = cxs.source_id_int_2)
306 or (rae.accounting_event_id is null
307 and rrs.accounting_event_id is null)
308 )
309 and rrs.ENCUMBRANCE_TYPE_ID is null
310 and rt.transaction_id = rae.rcv_transaction_id (+)
311 and nvl(rae.EVENT_TYPE_ID,decode(rt.transaction_type, 'RECEIVE', 1, 'DELIVER', 2, 'CORRECT', 3,
312 'MATCH', 4, 'RETURN TO RECEIVING', 5, 'RETURN TO VENDOR', 6, -1))=cem.transaction_type_id
313 and (cem.transaction_type_id <> 3
314 or
315 (cem.transaction_type_id = 3
316 and rt.PARENT_TRANSACTION_ID is not null
317 and cem.attribute = (SELECT TRANSACTION_TYPE
318 FROM RCV_TRANSACTIONS rt1
319 WHERE rt1.transaction_id = rt.PARENT_TRANSACTION_ID))
320 )
321 and gcc.CODE_COMBINATION_ID=rrs.CODE_COMBINATION_ID;
322
323
324 update /*+ leading(cxs) use_nl(rrs) index(rrs) */
325 rcv_mc_rec_sub_ledger rrs
326 set reference10 = 'Migrated to SLA',
327 rcv_sub_ledger_id = nvl(rcv_sub_ledger_id, rcv_receiving_sub_ledger_s.nextval),
328 last_update_date = gUpdateDate,
329 last_updated_by = gUserId,
330 last_update_login = gLoginId
331 where rrs.rcv_transaction_id in (select source_id_int_1 from cst_xla_seq_gt cxs)
332 and rrs.ENCUMBRANCE_TYPE_ID is null;
333
334 <<out_arg_log>>
335
336 IF l_plog THEN
337 fnd_log.string(
338 fnd_log.level_procedure,
339 l_module||'.end',
340 'Exiting RCV_SLA_MRC_UPDATE_PKG.Update_Receiving_MRC_Subledger with '||
341 'X_errbuf = '||X_errbuf||','||
342 'X_retcode = '||X_retcode
343 );
344 END IF;
345
346 EXCEPTION
347
348 WHEN fnd_api.g_exc_unexpected_error THEN
349 ROLLBACK;
350 X_retcode := FND_API.g_ret_sts_unexp_error;
351 IF l_exceptionlog THEN
352 fnd_msg_pub.add_exc_msg(
353 p_pkg_name => 'RCV_SLA_MRC_UPDATE_PKG',
354 p_procedure_name => 'Update_Receiving_MRC_Subledger',
355 p_error_text => 'An exception has occurred.'
356 );
357 fnd_log.string(
358 fnd_log.level_exception,
359 l_module||'.'||l_stmt_num,
360 'An exception has occurred.'
361 );
362 END IF;
363 X_errbuf:=l_module||'.'||l_stmt_num||': An exception has occurred.';
364 WHEN fnd_api.g_exc_error THEN
365 ROLLBACK;
366 X_retcode := FND_API.g_ret_sts_error;
367 IF l_errorLog THEN
368 fnd_message.set_name('BOM','CST_ERROR');
369 fnd_message.set_token('SQLERRM',SQLERRM);
370 fnd_msg_pub.add;
371 fnd_log.message(
372 fnd_log.level_error,
373 l_module||'.'||l_stmt_num,
374 FALSE
375 );
376 END IF;
377 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
378 WHEN OTHERS THEN
379 ROLLBACK;
380 X_retcode := FND_API.g_ret_sts_unexp_error;
381 IF (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL) THEN
382 fnd_message.set_name('BOM','CST_UNEXPECTED');
383 fnd_message.set_token('SQLERRM',SQLERRM);
384 fnd_msg_pub.add;
385 fnd_log.message(
386 fnd_log.level_unexpected,
387 l_module||'.'||l_stmt_num,
388 FALSE
389 );
390 END IF;
391 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
392
393 end Update_Receiving_MRC_Subledger;
394
395
396 END RCV_SLA_MRC_UPDATE_PKG;