1 PACKAGE BODY AR_MRC_ENGINE AS
2 /* $Header: ARMCENGB.pls 120.7 2005/04/14 23:21:06 hyu noship $ */
3
4 mc_init_rec ar_mrc_init_rec_type;
5
6 /*=============================================================================
7 | Public Functions / Procedures
8 *============================================================================*/
9
10 /*=============================================================================
11 | PUBLIC PROCEDURE Maintain_MRC_Data
12 |
13 | DESCRIPTION:
14 | Initial Entry point for all AR code in order to maintain,
15 | create, and delete any MRC data
16 |
17 | This procedure will call the appropriate MRC api with the
18 | information required.
19 |
20 | CALLS PROCEDURES / FUNCTIONS (local to this package body)
21 |
22 | PARAMETERS
23 | p_event_mode IN event to preform on MRC tables
24 | p_table_name IN Base Table Name.
25 | p_mode IN SINGLE /BATCH
26 | p_key_value IN primary key value
27 | p_key_value_list IN list of primarty key values
28 |
29 | KNOWN ISSUES:
30 |
31 | NOTES:
32 |
33 | MODIFICATION HISTORY
34 | Date Author Description of Change
35 | 10/03/00 Debbie Sue Jancis Created
36 | 02/27/01 Debbie Sue Jancis Modified due to complete rewrite of
37 | MRC API's.
38 | 07/10/01 Debbie Sue Jancis Modified due to change in parameters
39 | for MRC API's which include changes
40 | implemented to AP implementation.
41 | 08/27/01 Debbie Sue Jancis Modified due to changes in parameters
42 | for MRC API's which include performance
43 | changes and removal of dynamic sql.
44 *============================================================================*/
45 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
46
47 PROCEDURE Maintain_MRC_Data(
48 p_event_mode IN VARCHAR2,
49 p_table_name IN VARCHAR2,
50 p_mode IN VARCHAR2,
51 p_key_value IN NUMBER default NULL,
52 p_key_value_list IN gl_ca_utility_pkg.r_key_value_arr default NULL
53 ) IS
54 BEGIN
55 --{BUG4301323
56 NULL;
57 -- IF PG_DEBUG in ('Y', 'C') THEN
58 -- arp_standard.debug( 'AR_MRC_ENGINE.Maintain_MRC_Data(+)');
59 -- END IF;
60
61 /*-----------------------------------------------------------------+
62 | Dump the input parameters for debugging purposes |
63 +-----------------------------------------------------------------*/
64
65 -- IF PG_DEBUG in ('Y', 'C') THEN
66 -- arp_standard.debug('Maintain_MRC_Data: ' || ' EVENT Mode : ' || p_event_mode);
67 -- arp_standard.debug('Maintain_MRC_Data: ' || ' Table Name : ' || p_table_name);
68 -- arp_standard.debug('Maintain_MRC_Data: ' || ' mode : ' || p_mode);
69 -- arp_standard.debug('Maintain_MRC_Data: ' || 'key_value : ' || to_char(p_key_value));
70 -- END IF;
71
72 -- IF (p_key_value is NULL) THEN
73 -- IF PG_DEBUG in ('Y', 'C') THEN
74 -- arp_standard.debug('Maintain_MRC_Data: ' || 'count of value list =' || to_char(p_key_value_list.count));
75 -- END IF;
76 -- END IF;
77
78 /*-----------------------------------------------------------------+
79 | In order to work for backwards compatiability, we need to check |
80 | for the table names which have had the trigger replaced. So |
81 | each time a new table is added, it needs to be added here, |
82 | until all tables are added and this outside if statement can be |
83 | removed. For the first iteration, only AR_ADJUSTMENTS and |
84 | AR_RATE_ADJUSTMENTS will be considered |
85 +-----------------------------------------------------------------*/
86
87 -- IF (p_table_name = 'AR_ADJUSTMENTS' or
88 -- p_table_name = 'AR_RATE_ADJUSTMENTS' or
89 -- p_table_name = 'AR_MISC_CASH_DISTRIBUTIONS' or
90 -- p_table_name = 'RA_BATCHES' or
91 -- p_table_name = 'AR_BATCHES' or
92 -- p_table_name = 'AR_CASH_RECEIPTS' or
93 -- p_table_name = 'AR_PAYMENT_SCHEDULES' or
94 -- p_table_name = 'RA_CUSTOMER_TRX' or
95 -- p_table_name = 'AR_CASH_RECEIPT_HISTORY' or
96 -- p_table_name = 'AR_DISTRIBUTIONS' or
97 -- p_table_name = 'RA_CUST_TRX_LINE_GL_DIST' or
98 -- p_table_name = 'AR_RECEIVABLE_APPLICATIONS' OR
99 -- 3339072{
100 -- p_table_name = 'RA_CUSTOMER_TRX_LINES'
101 --}
102 -- ) THEN
103
104 -- IF PG_DEBUG in ('Y', 'C') THEN
105 -- arp_standard.debug('Maintain_MRC_Data: ' || 'Called with one of the supported table names ');
106 -- END IF;
107 /*-----------------------------------------------------------------+
108 | First we need to check if MRC is enabled. If it is than we |
109 | continue processing. If it is not then we are finished. |
110 +-----------------------------------------------------------------*/
111 -- IF PG_DEBUG in ('Y', 'C') THEN
112 -- arp_standard.debug('Maintain_MRC_Data: ' || 'before checking to see if mrc is enabled..');
113 -- END IF;
114 -- IF (gl_ca_utility_pkg.mrc_enabled(p_sob_id => ar_mc_info.primary_sob_id,
115 -- p_org_id => ar_mc_info.org_id,
116 -- p_appl_id => 222
117 -- )) THEN
118
119 -- IF PG_DEBUG in ('Y', 'C') THEN
120 -- arp_standard.debug('Maintain_MRC_Data: ' || 'MRC is enabled... ');
121 -- END IF;
122 /* initialize stucture which will be used to call the mrc api */
123 -- ar_mrc_engine.init_struct (p_table_name,
124 -- p_mode,
125 -- p_key_value,
126 -- p_key_value_list);
127 --
128 /*------------------------------------------------------------+
129 | Branch based upon the mode of operation |
130 +------------------------------------------------------------*/
131 -- IF ( p_event_mode = 'INSERT' and
132 -- p_table_name <> 'AR_DISTRIBUTIONS' and
133 -- p_table_name <> 'AR_RECEIVABLE_APPLICATIONS') THEN
134 -- IF PG_DEBUG in ('Y', 'C') THEN
135 -- arp_standard.debug('Maintain_MRC_Data: ' || 'Before calling MRC api for Insertion');
136 -- arp_standard.debug('Maintain_MRC_Data: ' || '**** table name ' || mc_init_rec.p_table_name);
137 -- arp_standard.debug('Maintain_MRC_Data: ' || '**** mc name ' || mc_init_rec.p_mc_table_name );
138 -- arp_standard.debug('Maintain_MRC_Data: ' || '**** app id ' || to_char(mc_init_rec.p_application_id ) );
139 -- arp_standard.debug('Maintain_MRC_Data: ' || '**** mode ' || mc_init_rec.p_mode );
140 -- arp_standard.debug('Maintain_MRC_Data: ' || '**** key_value ' || to_char(mc_init_rec.p_key_value) );
141 -- END IF;
142 -- BEGIN
143
144 -- ar_mc_info.insert_mc_data(
145 -- p_table_name => mc_init_rec.p_table_name,
146 -- p_mc_table_name => mc_init_rec.p_mc_table_name,
147 -- p_application_id => mc_init_rec.p_application_id,
148 -- p_mode => mc_init_rec.p_mode,
149 -- p_key_value => mc_init_rec.p_key_value,
150 -- p_key_value_list => mc_init_rec.p_key_value_list
151 -- );
152
153 -- EXCEPTION
154 -- WHEN OTHERS THEN
155 -- IF PG_DEBUG in ('Y', 'C') THEN
156 -- arp_standard.debug('Maintain_MRC_Data: ' || SQLERRM);
157 -- arp_standard.debug('Maintain_MRC_Data: ' || 'error during Insert for ' || p_table_name);
158 -- END IF;
159 -- APP_EXCEPTION.RAISE_EXCEPTION;
160 -- END;
161 -- END IF; /* end p_event_mode = INSERT */
162
163 -- IF (p_event_mode = 'UPDATE' and
164 -- p_table_name <> 'AR_DISTRIBUTIONS' and
165 -- p_table_name <> 'AR_RECEIVABLE_APPLICATIONS') THEN
166
167 -- IF PG_DEBUG in ('Y', 'C') THEN
168 -- arp_standard.debug('Maintain_MRC_Data: ' || 'Before calling MRC api for Update');
169 -- END IF;
170 -- BEGIN
171 -- ar_mc_info.update_mc_data(
172 -- p_table_name => mc_init_rec.p_table_name,
173 -- p_mc_table_name => mc_init_rec.p_mc_table_name,
174 -- p_application_id => mc_init_rec.p_application_id,
175 -- p_mode => mc_init_rec.p_mode,
176 -- p_key_value => mc_init_rec.p_key_value,
177 -- p_key_value_list => mc_init_rec.p_key_value_list
178 -- );
179
180 -- EXCEPTION
181 -- WHEN OTHERS THEN
182 -- IF PG_DEBUG in ('Y', 'C') THEN
183 -- arp_standard.debug('Maintain_MRC_Data: ' || 'error during update for ' || p_table_name);
184 -- END IF;
185 -- APP_EXCEPTION.RAISE_EXCEPTION;
186 -- END;
187 -- END IF; /* end p_event_mode = UPDATE */
188
189 -- IF (p_event_mode = 'DELETE') THEN
190
191 -- IF PG_DEBUG in ('Y', 'C') THEN
192 -- arp_standard.debug('Maintain_MRC_Data: ' || 'Before calling MRC api for Deletion');
193 -- END IF;
194 -- BEGIN
195 -- ar_mc_info.delete_mc_data(
196 -- p_table_name => mc_init_rec.p_table_name,
197 -- p_mc_table_name => mc_init_rec.p_mc_table_name,
198 -- p_mode => mc_init_rec.p_mode,
199 -- p_key_value => mc_init_rec.p_key_value,
200 -- p_key_value_list => mc_init_rec.p_key_value_list
201 -- );
202
203 -- EXCEPTION
204 -- WHEN OTHERS THEN
205 -- IF PG_DEBUG in ('Y', 'C') THEN
206 -- arp_standard.debug('Maintain_MRC_Data: ' || 'Error deleting from: ' ||
207 -- mc_init_rec.p_mc_table_name);
208 -- END IF;
209 -- APP_EXCEPTION.RAISE_EXCEPTION;
210 -- END;
211 -- END IF; /* end p_event_mode = DELETE */
212
213 -- END IF; /* end of mrc is enabled */
214
215 -- END IF; /* end of checking for specific tables */
216 -- IF PG_DEBUG in ('Y', 'C') THEN
217 -- arp_standard.debug( 'AR_MRC_ENGINE.Maintain_MRC_Data(-)');
218 -- END IF;
219
220 END Maintain_MRC_Data;
221
222 /*===========================================================================
223 | PROCEDURE init_struct
224 |
225 | DESCRIPTION:
226 | This procedure will initialize the global structure for MRC
227 | which will provide the values necessary to call the MRC
228 | api's for insert/update and delete into the MRC tables based
229 | upon the base table name passed in.
230 |
231 | CALLS PROCEDURES / FUNCTIONS
232 |
233 | PARAMETERS
234 | p_table_name IN AR Base table name
235 | p_mode IN SINGLE OR BATCH
236 | p_key_value IN
237 | p_key_value_list IN
238 |
239 | KNOWN ISSUES:
240 | At first attempt this is a prototype for the AR_ADJUSTMENTS and
241 | AR_RATE_ADJUSTMENTS tables. Once the prototype is complete, the
242 | other tables affected by MRC trigger logic will be incorporated.
243 |
244 | NOTES:
245 |
246 | MODIFICATION HISTORY
247 | Date Author Description of Change
248 | 10/09/00 Debbie Sue Jancis Created
249 | 02/27/01 Debbie Sue Jancis Modified due to complete rewrite of
250 | MRC API's.
251 | 07/10/01 Debbie Sue Jancis Modified due to change in parameters
252 | for MRC API's which include changes
253 | implemented to AP implementation.
254 | 08/27/01 Debbie Sue Jancis Modified due to complete rewrite of
255 | MRC API's
256 |
257 *============================================================================*/
258 PROCEDURE init_struct(
259 p_table_name IN VARCHAR2,
260 p_mode IN VARCHAR2,
261 p_key_value IN NUMBER,
262 p_key_value_list IN gl_ca_utility_pkg.r_key_value_arr
263 ) IS
264 BEGIN
265 --{BUG#4301323
266 NULL;
267 -- IF PG_DEBUG in ('Y', 'C') THEN
268 -- arp_standard.debug('AR_MRC_ENGINE.init_struct(+)');
269 -- arp_standard.debug('init_struct: ' || 'Table Name : ' || p_table_name);
270 -- END IF;
271
272 /*----------------------------------+
273 | Populate items that are the same |
274 | for each table |
275 +----------------------------------*/
276
277 -- mc_init_rec.p_table_name := p_table_name;
278 -- mc_init_rec.p_application_id := 222;
279 -- mc_init_rec.p_mode := p_mode;
280 -- mc_init_rec.p_key_value_list := p_key_value_list;
281 -- mc_init_rec.p_key_value := p_key_value;
282
283 /*----------------------------------+
284 | Populate Table specific items |
285 +----------------------------------*/
286
287 -- IF (p_table_name = 'AR_ADJUSTMENTS') THEN
288 -- mc_init_rec.p_mc_table_name := 'AR_MC_ADJUSTMENTS';
289
290 -- ELSIF (p_table_name = 'AR_RATE_ADJUSTMENTS') THEN
291 -- mc_init_rec.p_mc_table_name := 'AR_MC_RATE_ADJUSTMENTS' ;
292
293 -- ELSIF (p_table_name = 'AR_MISC_CASH_DISTRIBUTIONS') THEN
294 -- mc_init_rec.p_mc_table_name := 'AR_MC_MISC_CASH_DISTS' ;
295
296 -- ELSIF (p_table_name = 'AR_BATCHES') THEN
297 -- mc_init_rec.p_mc_table_name := 'AR_MC_BATCHES' ;
298
299 -- ELSIF (p_table_name = 'RA_BATCHES') THEN
300 -- mc_init_rec.p_mc_table_name := 'RA_MC_BATCHES' ;
301
302 -- ELSIF (p_table_name = 'AR_CASH_RECEIPTS') THEN
303 -- mc_init_rec.p_mc_table_name := 'AR_MC_CASH_RECEIPTS' ;
304
305 -- ELSIF (p_table_name = 'AR_PAYMENT_SCHEDULES') THEN
306 -- mc_init_rec.p_mc_table_name := 'AR_MC_PAYMENT_SCHEDULES' ;
307
308 -- ELSIF (p_table_name = 'RA_CUSTOMER_TRX') THEN
309 -- mc_init_rec.p_mc_table_name := 'RA_MC_CUSTOMER_TRX' ;
310
311 -- ELSIF (p_table_name = 'AR_CASH_RECEIPT_HISTORY') THEN
312 -- mc_init_rec.p_mc_table_name := 'AR_MC_CASH_RECEIPT_HIST' ;
313
314 -- ELSIF (p_table_name = 'AR_DISTRIBUTIONS') THEN
315 -- mc_init_rec.p_mc_table_name := 'AR_MC_DISTRIBTIONS' ;
316
317 -- ELSIF (p_table_name = 'AR_RECEIVABLE_APPLICATIONS') THEN
318 -- mc_init_rec.p_mc_table_name := 'AR_MC_RECEIVABLE_APPS' ;
319 --{3339072
320 -- ELSIF (p_table_name = 'RA_CUSTOMER_TRX_LINES') THEN
321 -- mc_init_rec.p_mc_table_name := 'RA_MC_CUSTOMER_TRX_LINES' ;
322 --}
323 -- ELSIF (p_table_name = 'RA_CUST_TRX_LINE_GL_DIST') THEN
324 -- mc_init_rec.p_mc_table_name := 'RA_MC_TRX_LINE_GL_DIST' ;
325 -- END IF;
326
327 -- IF PG_DEBUG in ('Y', 'C') THEN
328 -- arp_standard.debug('AR_MRC_ENGINE.init_struct(-)');
329 -- END IF;
330 END init_struct;
331
332
333 /*===========================================================================
334 | PROCEDURE mrc_bulk_process
335 |
336 | DESCRIPTION:
337 | This procedure will be called by autoinvoice to insert
338 | records into MRC tables using BULK processing
339 |
340 | CALLS PROCEDURES / FUNCTIONS
341 |
342 | ar_mc_info.inv_mrc_bulk_process (MRC bulk processing API
343 | called from AutoInvoice and copy trx
344 | for ar_adjustments, RA_CUSTOMER_TRX
345 | and ar_payment_schedules inserts)
346 | PARAMETERS
347 | p_request_id IN VARCHAR2
348 | p_tablename IN VARCHAR2 - AR BASE TABLE NAME
349 |
350 | KNOWN ISSUES:
351 |
352 | NOTES:
353 |
354 | For the initial coding, it was determined that this was required for
355 | the AR_ADJUSTMENTS table processing used in AUTOINVOICE to improve
356 | performance. Because a full analysis on the code has not been done
357 | for other tables, I am leaving in placeholders for those tables.
358 | if it is found that other calls will not be needed, those placeholders
359 | will be removed.
360 |
361 | MODIFICATION HISTORY
362 | Date Author Description of Change
363 | 07/18/01 Debbie Sue Jancis Created
364 | 03/11/02 Debbie Sue Jancis Modified for new strategy in calling
365 | MRC API once for autoinv and
366 | cpy trx processing. p_tablename will
367 | no longer represent a tablename but
368 | will have to represent a calling
369 | program. it will have RAXTRX or
370 | CPYTRX populated. Not changing the
371 | name as that would require the STUB
372 | program to have a change which would
373 | make a maintainance nightmare.
374 | 08/30/02 Debbie Sue Jancis Added additional program name
375 | 'GL_DIST' to process just the
376 | gl_dist lines by request id.
377 *============================================================================*/
378 PROCEDURE mrc_bulk_process (
379 p_request_id IN VARCHAR2,
380 p_table_name IN VARCHAR2
381 ) IS
382 BEGIN
383 --{BUG4301323
384 NULL;
385 -- IF PG_DEBUG in ('Y', 'C') THEN
386 -- arp_standard.debug('AR_MRC_ENGINE.mrc_bulk_process(+)');
387 -- arp_standard.debug('mrc_bulk_process: ' || 'CALLING PROGRAM : ' || p_table_name);
388 -- arp_standard.debug('mrc_bulk_process: ' || 'before checking to see if mrc is enabled..');
389 -- END IF;
390 -- IF (gl_ca_utility_pkg.mrc_enabled(p_sob_id => ar_mc_info.primary_sob_id,
391 -- p_org_id => ar_mc_info.org_id,
392 -- p_appl_id => 222
393 -- )) THEN
394
395 -- IF PG_DEBUG in ('Y', 'C') THEN
396 -- arp_standard.debug('mrc_bulk_process: ' || 'MRC is enabled... ');
397 -- END IF;
398 -- IF (p_table_name = 'GL_DIST') THEN
399 -- ar_mc_info.inv_import_cld(p_request_id);
400 -- ELSE
401 -- ar_mc_info.inv_mrc_bulk_process(to_number(p_request_id),
402 -- p_table_name);
403 -- END IF;
404 -- END IF;
405
406 -- IF PG_DEBUG in ('Y', 'C') THEN
407 -- arp_standard.debug('AR_MRC_ENGINE.mrc_bulk_process(-)');
408 -- END IF;
409 END mrc_bulk_process;
410
411 END AR_MRC_ENGINE;
[Home] [Help]