1 Package Body OPIMPDAT AS
2 /*$Header: OPIMDATB.pls 120.1 2005/06/10 12:10:44 appldev $ */
3
4
5 procedure get_push_dates(
6 I_ORG_ID IN NUMBER,
7 I_FROM_DATE IN DATE, -- user entered date
8 I_TO_DATE IN DATE, -- user-entered date
9 I_LAST_PUSH_MIN_DATE IN DATE,
10 O_PUSH_START_INV_TXN_DATE OUT NOCOPY DATE,
11 O_PUSH_START_WIP_TXN_DATE OUT NOCOPY DATE,
12 O_PUSH_LAST_INV_TXN_ID OUT NOCOPY NUMBER,
13 O_PUSH_LAST_WIP_TXN_ID OUT NOCOPY NUMBER,
14 O_PUSH_LAST_INV_TXN_DATE OUT NOCOPY DATE,
15 O_PUSH_LAST_WIP_TXN_DATE OUT NOCOPY DATE,
16 O_PUSH_END_TXN_DATE OUT NOCOPY DATE,
17 O_FIRST_PUSH OUT NOCOPY NUMBER, -- 1=YES, 0=NO
18 O_ERR_NUM OUT NOCOPY NUMBER,
19 O_ERR_CODE OUT NOCOPY VARCHAR2,
20 O_ERR_MSG OUT NOCOPY VARCHAR2,
21 O_TXN_FLAG OUT NOCOPY NUMBER
22 ) IS
23 l_push_inv_start_date DATE;
24 l_push_wip_start_date DATE;
25 l_push_inv_end_date DATE;
26 l_push_wip_end_date DATE;
27 l_push_inv_start_txn_id NUMBER;
28 l_push_wip_start_txn_id NUMBER;
29 l_push_inv_end_txn_id NUMBER;
30 l_push_wip_end_txn_id NUMBER;
31 l_prev_push_inv_txn_date DATE;
32 l_prev_push_inv_txn_id NUMBER;
33 l_prev_push_wip_txn_date DATE;
34 l_prev_push_wip_txn_id NUMBER;
35 l_inv_txn_id NUMBER;
36 l_inv_txn_date DATE;
37 l_wip_txn_id NUMBER;
38 l_wip_txn_date DATE;
39 l_last_push_date DATE;
40 l_from_date DATE;
41 l_inv_from_date DATE;
42 l_wip_from_date DATE;
43 l_first_push_date DATE;
44 l_costed_flag VARCHAR2(1);
45 l_first_push NUMBER;
46 l_txn_flag NUMBER;
47 l_err_num NUMBER;
48 l_err_code VARCHAR2(240);
49 l_err_msg VARCHAR2(240);
50 l_stmt_num NUMBER;
51 process_error EXCEPTION;
52 no_from_date EXCEPTION;
53 no_date_range EXCEPTION;
54 l_look_for_txn_id DATE;
55 l_from_date_per_close DATE;
56 l_per_open_flag VARCHAR2 (1);
57
58 BEGIN
59
60 -- Initialize local variables
61 l_err_num := 0;
62 l_err_code := '';
63 l_err_msg := '';
64 l_first_push := 0; -- not the first push process
65 l_txn_flag := 0; -- have both MMT and WT tranx
66 l_last_push_date := NULL;
67 l_from_date := NULL;
68 l_inv_from_date := NULL;
69 l_wip_from_date := NULL;
70 l_inv_txn_id := 0;
71 l_costed_flag := 'N';
72 l_inv_txn_id := 0;
73 l_wip_txn_id := 0;
74 l_prev_push_inv_txn_id := 0;
75 l_prev_push_wip_txn_id := 0;
76 l_push_inv_start_txn_id := 0;
77 l_push_wip_start_txn_id := 0;
78
79 /*-------------------------------
80 ** Determine start process date
81 --------------------------------*/
82
83 EDW_LOG.PUT_LINE('from date: ' || to_char(i_from_date));
84 EDW_LOG.PUT_LINE('to date: '|| to_char(i_to_date));
85 EDW_LOG.PUT_LINE('org id: ' || to_char(i_org_id));
86
87 -- Get last push date and last push transaction id for organization
88
89 l_stmt_num := 10;
90 select max(last_push_date)
91 into l_last_push_date
92 from opi_ids_push_date_log pdl
93 where pdl.organization_id = i_org_id;
94
95
96 -- if there is no push log record for the organization,
97 -- and no user_entered date, it is because there have not
98 -- been any transactions thru the last push date.
99 -- Notes: if this is the very first push for all orgs, the
100 -- calling program would not invoke this procedure unless it
101 -- has a user-entered start date.
102
103 if l_last_push_date is NULL then -- no push date log record
104 l_first_push := 1; -- it is the first push
105
106 if i_from_date is null then -- use min date of last push
107 l_stmt_num := 20;
108 select trunc (min(transaction_date)) --Dinkar 10/11/02
109 into l_inv_from_date
110 from mtl_material_transactions
111 where organization_id = i_org_id
112 and costed_flag is null;
113
114 l_stmt_num := 30;
115 select trunc (min(wt.transaction_date)) --Dinkar 10/11/02
116 into l_wip_from_date
117 from wip_transactions wt
118 where organization_id = i_org_id;
119 end if; -- end 'from' date is null
120 l_prev_push_inv_txn_id := 0;
121 l_prev_push_wip_txn_id := 0;
122 else -- push date log record exists
123 l_stmt_num := 40;
124 select pdl.last_push_inv_txn_date, -- push date log dates are trunc'ed
125 pdl.last_push_inv_txn_id,
126 pdl.last_push_wip_txn_date,
127 pdl.last_push_wip_txn_id
128 into
129 l_prev_push_inv_txn_date,
130 l_prev_push_inv_txn_id,
131 l_prev_push_wip_txn_date,
132 l_prev_push_wip_txn_id
133 from opi_ids_push_date_log pdl
134 where pdl.organization_id = i_org_id
135 and pdl.last_push_date = l_last_push_date;
136
137 l_inv_from_date := l_prev_push_inv_txn_date;
138 l_wip_from_date := l_prev_push_wip_txn_date;
139
140 -- Check for from date older than the first pushed transaction date.
141 -- If so, set the first push flag so that beginning balance can be
142 -- re-calculated.
143 l_stmt_num := 50;
144 select min(trx_date)
145 into l_first_push_date
146 from opi_ids_push_log
147 where organization_id = i_org_id;
148
149 if i_from_date is not null
150 and i_from_date < l_first_push_date then
151 l_first_push := 1;
152 end if;
153
154 end if; -- end checking for push log record
155
156 -- Get the calculated INV from date
157 l_stmt_num := 60;
158
159 calc_from_date(i_org_id,
160 i_from_date,
161 l_inv_from_date,
162 l_first_push,
163 l_from_date, -- start date got back from call
164 l_err_num,
165 l_err_code,
166 l_err_msg);
167 if l_err_num <> 0 then
168 raise process_error;
169 end if;
170
171
172 EDW_LOG.PUT_LINE('lp_i_txn_date: ' || to_char(l_prev_push_inv_txn_date));
173 EDW_LOG.PUT_LINE('(60) lp_i_txn_id: ' || to_char(l_prev_push_inv_txn_id));
174 EDW_LOG.PUT_LINE('(60) lp_w_txn_date: ' || to_char(l_prev_push_wip_txn_date));
175 EDW_LOG.PUT_LINE('(60) lp_w_txn_id: ' || to_char(l_prev_push_wip_txn_id));
176
177 -- Usually this l_from_date is the last collected date, since there
178 -- could have been data entered on that last collected day after the
179 -- ETL was run.
180 -- The one case we do not want to go back to this date is when
181 -- the last collected date was the ending date of a closed period
182 -- and the period had been closed before the collection was made.
183 -- In that case, there is no need to go back and collect the end of
184 -- period day and consequently all the period start and period end
185 -- rows.
186 -- The one exception is during the first push
187 IF ((l_last_push_date IS NOT NULL) and
188 (trunc (l_last_push_date) > trunc (l_from_date)))
189 THEN
190
191 BEGIN
192 select trunc (schedule_close_date), open_flag
193 into l_from_date_per_close, l_per_open_flag
194 from org_acct_periods
195 where organization_id = i_org_id
196 and period_start_date <= trunc (l_from_date)
197 and schedule_close_date >= trunc (l_from_date);
198
199 -- if the l_from_date is the closing date of the period,
200 -- and the period is closed, then move the from date to
201 -- start of the next period i.e. to the next day
202 IF ((l_from_date_per_close = trunc (l_from_date)) and
203 (l_per_open_flag = 'N')) THEN
204 l_from_date := trunc (l_from_date) + 1;
205 END IF;
206
207 EXCEPTION
208 WHEN NO_DATA_FOUND THEN
209 null; --no need to change l_from_date
210 END;
211
212 END IF;
213
214 /*--------------------------------------------------------------
215 l_push_inv_start_date is one of the following:
216 1. Backdated transaction date (costed backdated trx)
217 2. Period start date
218 3. From date passed by user from conc program
219 4. Previous Push date (from previous collection
220 -------------------------------------------------------------------*/
221 l_stmt_num := 70;
222
223 l_push_inv_start_date := l_from_date;
224
225 /*---------------------------------------------------------------------
226 -- Check for back-dated transaction and change the start date if needed.
227 ----------------------------------------------------------------------*/
228 -- Get first transaction id of the start date
229
230 l_stmt_num := 90;
231 /* select min(mmt.transaction_id)
232 into l_push_inv_start_txn_id
233 from mtl_material_transactions mmt
234 where trunc (mmt.transaction_date) >= l_push_inv_start_date
235 and mmt.organization_id = i_org_id
236 and mmt.costed_flag is null;
237 */
238 -- ltong (02/19/2003). Added filter to Consigned Inventory Transactions.
239 select min(mmt.transaction_id)
240 into l_push_inv_start_txn_id
241 from mtl_material_transactions mmt
242 where mmt.transaction_id > l_prev_push_inv_txn_id
243 and mmt.organization_id = i_org_id
244 and mmt.costed_flag is null
245 AND MMT.organization_id = NVL(MMT.owning_organization_id, MMT.organization_id)
246 AND NVL(MMT.OWNING_TP_TYPE,2) = 2;
247
248 l_stmt_num := 90;
249 select trunc (min(mmt.transaction_date))
250 into l_inv_txn_date
251 from mtl_material_transactions mmt
252 where mmt.transaction_id >= l_push_inv_start_txn_id
253 and mmt.organization_id = i_org_id
254 and mmt.costed_flag is null;
255
256 -- Need to adjust start date only if it is not the first push
257
258 if (l_inv_txn_date < l_prev_push_inv_txn_date)
259 and l_first_push <> 1 then
260 l_push_inv_start_date := l_inv_txn_date;
261 end if;
262
263
264 /*--------------------------------------------------
265 ** Determine end process date and MMT transaction id
266 --------------------------------------------------*/
267 -- Check if user enters 'to' date.
268 -- User does not specify 'to' date ==> process until sysdate if there are
269 -- costed transactions.
270 -- User_specified 'to' date <= last push date ==> process
271 -- until the last push date.
272 -- User_specified 'to' date > last push date ==> process thru 'to' date.
273
274 if i_to_date is null then
275 l_push_inv_end_date := trunc (sysdate);
276 elsif i_to_date <= l_prev_push_inv_txn_date then
277 l_push_inv_end_date := l_prev_push_inv_txn_date;
278 else
279 l_push_inv_end_date := trunc (i_to_date);
280 end if;
281
282
283 l_inv_txn_id := 0;
284
285 -- Get the first uncosted transaction prior to the end date.
286 l_stmt_num := 100;
287 select nvl(min(transaction_id),0)
288 into l_inv_txn_id
289 from mtl_material_transactions mmt
290 where mmt.organization_id = i_org_id
291 AND mmt.transaction_date >= trunc (l_push_inv_start_date) -- rjin 10/31/02
292 and mmt.transaction_date <= trunc (l_push_inv_end_date) + 0.99999
293 and mmt.costed_flag is not null;
294
295 -- If uncosted transactions exist prior to end date,
296 -- change the end date to the day before the uncosted transaction's
297 -- txn date. Otherwise, leave the end date alone.
298 l_stmt_num := 110;
299 if l_inv_txn_id > 0 then
300 select trunc (mmt.transaction_date)
301 into l_inv_txn_date
302 from mtl_material_transactions mmt
303 where mmt.transaction_id = l_inv_txn_id;
304 l_push_inv_end_date := (l_inv_txn_date - 1);
305 end if;
306
307
308 -- before assigning, we need to make the data range is a valid one
309 IF l_push_inv_end_date < l_push_inv_start_date THEN
310 RAISE no_date_range;
311 END IF;
312
313 /*--------------------------------------------------------------------
314 -- Get the final first and last MMT transaction to be procesed,
315 -- now that we have the final transaction date range.
316 ---------------------------------------------------------------------*/
317
318 l_stmt_num := 130;
319 /* Select the min and max transaction id from between the period start
320 date and the collection end date. This is different from the
321 previous approach where we get the date from between collection start
322 and end date. The reason is to avoid occluding collection backdated
323 transactions e.g in the following scenario:
324 Suppose we backdate a trx. on 18th Jan to the 1st Jan with id 100.
325 Then collect 1st Jan to 15th Jan. The last pushed trx id is 100, not
326 the max (transaction_id) of 15th Jan.
327 Then collect 15th Jan to 17th Jan. The last pushed trx id is now
328 max (transaction_id) of 17th Jan which is less than 100.
329 Now collect 18th to 25th.
330 Since the trx id of 100 belonging to a transaction dated on
331 1st Jan is greater than the last pushed transaction id,
332 the collection of 18th to 25th will go back to the first.
333 This can be avoided if we collect the max trx id from the start of
334 period instead of the start of the collection period.
335 However, there might be backdated transactions to within the
336 period collected that might be past the collection period. In the
337 example here, there could be a backdated transaction for the 2nd Jan,
338 created after 25th Jan. If the transaction has already been entered,
339 then it will be collected with the rest of the data from 1st Jan, and
340 so is not a problem. If not, then the transaction has not been made
341 yet and will have a transaction id greater than the last pushed
342 transaction id when it is made.
343 */
344 BEGIN
345 select period_start_date
346 into l_look_for_txn_id
347 from org_acct_periods
348 where organization_id = i_org_id
349 and period_start_date <= trunc (l_push_inv_start_date)
350 and schedule_close_date >= trunc (l_push_inv_start_date);
351 EXCEPTION
352 WHEN NO_DATA_FOUND THEN -- for start date before period
353 l_look_for_txn_id := l_push_inv_start_date;
354
355 END;
356
357 EDW_LOG.PUT_LINE ('Looking for txns from: ' || l_look_for_txn_id);
358
359 select nvl(min(transaction_id),0),
360 nvl(max(transaction_id),0)
361 into l_push_inv_start_txn_id,
362 l_push_inv_end_txn_id
363 from mtl_material_transactions mmt
364 where mmt.organization_id = i_org_id
365 and mmt.transaction_date between l_look_for_txn_id
366 and trunc (l_push_inv_end_date) + 0.99999
367 and mmt.costed_flag is null;
368
369 -- If it's a first push and there are no INV transactions for date range
370 -- set flag to process only beginning balances. Otherwise, set trxn flag
371 -- and set push txn id to previous push's last txn id.
372
373 if (l_push_inv_start_txn_id = 0 or
374 l_push_inv_start_txn_id is null) then
375 l_txn_flag := 2; -- set flag to process WT only
376 EDW_LOG.PUT_LINE('OPIMPDAT.get_push_dates - '
377 || 'No INV transactions for date range');
378 if l_first_push = 1 then
379 l_first_push := 2;
380 else
381 l_push_inv_end_txn_id := l_prev_push_inv_txn_id;
382 end if; -- end checking first push
383 end if; -- end checking null start txn id
384
385 -- since we mean to collect the entire end date, set the end date
386 -- to 23:59:59
387 l_push_inv_end_date := to_date ( to_char (l_push_inv_end_date, 'DD-MM-YYYY') || ' 23:59:59', 'DD-MM-YYYY HH24:MI:SS');
388
389 -- assign output values
390 o_push_start_inv_txn_date := l_push_inv_start_date;
391 o_push_end_txn_date := l_push_inv_end_date;
392 o_push_last_inv_txn_id := l_push_inv_end_txn_id;
393 o_push_last_inv_txn_date := l_push_inv_end_date;
394 o_first_push := l_first_push;
395 o_txn_flag := l_txn_flag;
396
397 -- if start transaction date is beyond the 'to' date, let user know
398 -- that there are no MMT transactions to be processed and move
399 -- the transaction start date back to the calculated from date.
400 if (o_push_start_inv_txn_date > trunc (i_to_date)) -- Dinkar 10/11/02
401 or (o_push_start_inv_txn_date is null) then
402 o_push_start_inv_txn_date := trunc (l_from_date);
403
404 EDW_LOG.PUT_LINE('OPIMPDAT.get_push_dates - no INV transactions to process ');
405 EDW_LOG.PUT_LINE('Org id: ' || to_char(i_org_id));
406 EDW_LOG.PUT_LINE('Start INV Push Date :'
407 || to_char(o_push_start_inv_txn_date,
408 'DD-MON-YYYY HH24:MI:SS'));
409 EDW_LOG.PUT_LINE('End INV Push Date :'
410 || to_char(o_push_end_txn_date,
411 'DD-MON-YYYY HH24:MI:SS'));
412 end if;
413
414 /*********************************************************************
415 == Determine WIP transaction date and id range
416 **********************************************************************/
417 /*-------------------------------------------
418 -- Identify WIP start transaction date and id.
419 --------------------------------------------*/
420 -- Get the calculated WIP start date
421 l_from_date := null;
422 l_err_num := 0;
423 l_err_code := '';
424 l_err_msg := '';
425
426 /**********************************************************************
427 * NOTE - WE ARE NOW CHANGING OUR APPROACH FOR THE WIP START DATE.
428 * THE WIP START DATE WILL BE ALWAYS THE SAME AS THE INV START DATE.
429 * THIS IS NEEDED BECAUSE WE DELETE EVERYTHING FROM THE PUSH LOG STARTING
430 * AT THE INV START DATE.
431 * THE ONE CASE DROPPED IS THAT OF A WIP TRANSACTION THAT IS BACKDATED TO
432 * BEFORE THE INV START DATE.
433 * WE FEEL THAT THIS WOULD BE A VERY RARE CASE, AND NEGLIGIBLE IN
434 * COMPARISON TO THE ONHAND INVENTORY AND CAN THEREFORE BE IGNORED.
435 * digupta - 03/17/02
436 **********************************************************************/
437
438 l_stmt_num := 140;
439 l_push_wip_start_date := o_push_start_inv_txn_date;
440
441 /*
442 calc_from_date(i_org_id,
443 i_from_date,
444 l_wip_from_date,
445 l_first_push,
446 l_from_date, -- start date got from call
447 l_err_num,
448 l_err_code,
449 l_err_msg);
450 if l_err_num <> 0 then
451 raise process_error;
452 end if;
453 */
454 /*
455 DBMS_OUTPUT.PUT_LINE('WIP l_from_date: ' || to_char(l_from_date));
456 */
457
458 /*
459 -- Get the first date that have transactions, including the last push date.
460 l_stmt_num := 150;
461 select trunc (min(wt.transaction_date))
462 into l_wip_txn_date
463 from wip_transactions wt
464 where wt.organization_id = i_org_id
465 and wt.transaction_date >= l_from_date;
466 */
467 /*
468 -- Get the last transaction of that first date
469 l_stmt_num := 160;
470 select max(wt.transaction_id)
471 into l_wip_txn_id
472 from wip_transactions wt
473 where wt.organization_id = i_org_id
474 and wt.transaction_date BETWEEN trunc(l_wip_txn_date) and
475 trunc (l_wip_txn_date) + 0.99999;
476 */
477 /*
478 -- If the start date is the same day than the last push date, we should re-process
479 -- the last push date if there are unpushed transactions. Otherwise, just move on.
480
481 if trunc(l_wip_txn_date) = trunc(l_prev_push_wip_txn_date)
482 and l_wip_txn_id <= l_prev_push_wip_txn_id then
483 l_push_wip_start_date := l_wip_txn_date + 1;
484 else
485 -- have unpushed transaction in the last push date
486 l_push_wip_start_date := l_wip_txn_date;
487 end if;
488 */
489 /*
490 -- Get the first transaction id of the start date
491 l_stmt_num := 170;
492 Select min(wt.transaction_id)
493 into l_push_wip_start_txn_id
494 from wip_transactions wt
495 where wt.organization_id = i_org_id
496 and wt.transaction_date >= l_push_wip_start_date;
497 */
498 /*-------------------------------------------------
499 -- Determine end process date and WT transaction id
500 --------------------------------------------------*/
501 if i_to_date is null then
502 l_push_wip_end_date := trunc (sysdate); -- Dinkar 10/11/02
503 elsif i_to_date <= l_prev_push_wip_txn_date then
504 l_push_wip_end_date := l_prev_push_wip_txn_date;
505 else
506 l_push_wip_end_date := trunc (i_to_date); -- Dinkar 10/11/02
507 end if;
508
509 -- So that INV and WIP are in sync, process both thru the earliest date
510 -- that INV can be processed.
511
512 if l_push_wip_end_date > l_push_inv_end_date
513 and l_push_inv_end_date is not null then
514 l_push_wip_end_date := l_push_inv_end_date;
515 end if;
516
517
518 /*---------------------------------------------------------------------
519 -- Check for back-dated transaction and change the WIP start date if needed
520 ----------------------------------------------------------------------*/
521 /*
522 l_stmt_num := 190;
523 l_wip_txn_date := null;
524 select trunc (min(wt.transaction_date)) -- Dinkar 10/11/02
525 into l_wip_txn_date
526 from wip_transactions wt
527 where wt.organization_id = i_org_id
528 and wt.transaction_id >= l_push_wip_start_txn_id;
529
530 if l_wip_txn_date < l_prev_push_wip_txn_date
531 and l_first_push <> 1 then
532 l_push_wip_start_date := l_wip_txn_date;
533 end if;
534 */
535 /*--------------------------------------------------------------------
536 -- Get the first and last WT transactions to be procesed,
537 -- now that we have the final transaction date range.
538 -- The min transaction id will be found not from the wip start date,
539 -- but the period start date of the period the wip dates lie in.
540 -- This way, we don't oscillate back and forth with the dates --
541 -- see comment for statement 130 above.
542 ---------------------------------------------------------------------*/
543
544 l_stmt_num := 200;
545
546 BEGIN
547 select period_start_date
548 into l_look_for_txn_id
549 from org_acct_periods
550 where organization_id = i_org_id
551 and period_start_date <= trunc (l_push_inv_start_date)
552 and schedule_close_date >= trunc (l_push_inv_start_date);
553 EXCEPTION
554 WHEN NO_DATA_FOUND THEN -- for start date before period
555 l_look_for_txn_id := l_push_inv_start_date;
556
557 END;
558
559 select min(wt.transaction_id), max(wt.transaction_id)
560 into l_push_wip_start_txn_id,
561 l_push_wip_end_txn_id
562 from wip_transactions wt
563 where wt.organization_id = i_org_id
564 and wt.transaction_date between l_look_for_txn_id
565 and trunc (l_push_wip_end_date) + 0.99999;
566
567 -- since we mean to collect the entire end date, set the end date
568 -- to 23:59:59
569 l_push_wip_end_date := to_date ( to_char (l_push_wip_end_date, 'DD-MM-YYYY') || ' 23:59:59', 'DD-MM-YYYY HH24:MI:SS');
570
571 o_push_start_wip_txn_date := l_push_wip_start_date;
572 o_push_last_wip_txn_id := l_push_wip_end_txn_id;
573 o_push_last_wip_txn_date := l_push_wip_end_date;
574
575 o_err_num := l_err_num;
576 o_err_code := l_err_code;
577 o_err_msg := l_err_msg;
578
579 -- If there are no WIP transactions for date range
580 -- set transaction flag and set last tranx id to the previous push's
581 -- last trxn id.
582
583 if (l_push_wip_start_txn_id = 0 or
584 l_push_wip_start_txn_id is null) then
585 o_push_last_wip_txn_id := l_prev_push_wip_txn_id;
586 EDW_LOG.PUT_LINE('OPIMPDAT.get_push_dates - '
587 || 'No WIP transactions for date range');
588 if o_txn_flag = 0 then -- reset flag to process MMT only
589 o_txn_flag := 1;
590 end if;
591 end if;
592
593 -- If the start date is beyond the user-entered end date,
594 -- let user know that there are no wip transactions to process
595 -- and move the start date back to the calculated from date
596
597 if (o_push_start_wip_txn_date > trunc (i_to_date)) -- Dinkar 10/11/02
598 or (o_push_start_wip_txn_date is null) then
599 o_push_start_wip_txn_date := trunc (l_from_date); -- Dinkar 10/11/02
600
601 EDW_LOG.PUT_LINE('OPIMPDAT.get_push_dates - no WIP transactions to process');
602 EDW_LOG.PUT_LINE('Org id: ' || to_char(i_org_id));
603 EDW_LOG.PUT_LINE('Start WIP Push Date :'
604 || to_char(o_push_start_wip_txn_date,
605 'DD-MON-YYYY HH24:MI:SS'));
606 EDW_LOG.PUT_LINE('End WIP Push Date :'
607 || to_char(o_push_end_txn_date,
608 'DD-MON-YYYY HH24:MI:SS'));
609 end if;
610
611 EDW_LOG.PUT_LINE('OPIMPDAT.get_push_dates-');
612 EDW_LOG.PUT_LINE('Org id: ' || to_char(i_org_id));
613 EDW_LOG.PUT_LINE('Start Push Date for INV:'
614 || to_char(o_push_start_inv_txn_date,
615 'DD-MON-YYYY HH24:MI:SS'));
616 EDW_LOG.PUT_LINE('Start Push Date for WIP:'
617 || to_char(o_push_start_wip_txn_date,
618 'DD-MON-YYYY HH24:MI:SS'));
619 EDW_LOG.PUT_LINE('End Push Date: '
620 || to_char(o_push_end_txn_date,
621 'DD-MON-YYYY HH24:MI:SS'));
622
623 EXCEPTION
624 when process_error then
625 o_err_num := l_err_num;
626 o_err_code := l_err_code;
627 o_err_msg := l_err_msg;
628
629 when no_from_date then
630 o_err_num := 9999;
631 o_err_msg := 'OPIMPDAT.get_push_dates ('
632 || to_char(l_stmt_num)
633 || ')';
634 EDW_LOG.PUT_LINE('No user-entered from date for initial push');
635 EDW_LOG.PUT_LINE('Org id: ' ||to_char(i_org_id));
636
637 WHEN no_date_range THEN
638 o_err_num := 9999;
639 o_err_msg := 'OPIMPDAT.get_push_dates ('
640 || to_char(l_stmt_num)
641 || ')';
642 EDW_LOG.PUT_LINE('Not a valid date range since push from date is later than push to date');
643 EDW_LOG.PUT_LINE('INV push start date ' || l_push_inv_start_date );
644 EDW_LOG.PUT_LINE('INV push end date ' || l_push_inv_end_date );
645 EDW_LOG.PUT_LINE('Org id: ' ||to_char(i_org_id));
646
647 when others then
648 o_err_num := SQLCODE;
649 o_err_msg := 'OPIMPDAT.get_push_dates - ('
650 || to_char(l_stmt_num)
651 || '): '
652 || substr(SQLERRM, 1,200);
653 END get_push_dates;
654
655 /*============================================================
656 == PROCEDURE
657 == calc_from_date
658 ==
659 == DESCRIPTION
660 == This procedure will determine the first transaction dates
661 == for INV and WIP. It expects the calling program to always
662 == pass the i_from_parameter with a valid value.
663 ==
664 == If it's a first push:
665 == i_from_date = null ==> from date = beg date of period containing
666 == first transaction date
667 == i_from_date != null ==> from date = beg date of period containing
668 == the user-entered 'from' date
669 ==
670 == If it's not a first push:
671 == i_from_date = null ==> use last push transaction date
672 == i_from_date != null ==> user last push txn date or user's
673 == from date, whichever is older
674 == from date = i_from date if period is open or same closed period
675 == as last push date
676 == otherwise, from date is the beg date of period.
677 ======================================================================*/
678 PROCEDURE calc_from_date(
679 i_org_id IN NUMBER,
680 i_from_date IN DATE,
681 i_txn_date IN DATE,
682 i_first_push IN NUMBER, -- 1=yes, 0=no
683 o_calc_from_date OUT NOCOPY DATE,
684 o_err_num OUT NOCOPY NUMBER,
685 o_err_code OUT NOCOPY VARCHAR2,
686 o_err_msg OUT NOCOPY VARCHAR2
687 ) IS
688 l_from_date DATE;
689 l_last_txn_date DATE;
690 l_calc_from_date DATE;
691 l_per_start_date DATE;
692 l_sched_close_date DATE;
693 l_per_close_date DATE;
694 l_err_num NUMBER;
695 l_err_code VARCHAR2(240);
696 l_err_msg VARCHAR2(240);
697 l_stmt_num NUMBER;
698
699 BEGIN
700
701 l_err_num := 0;
702 l_err_code := null;
703 l_err_msg:= null;
704 l_from_date := trunc (i_from_date);
705
706 -- If user-defined from date is null, use transaction date as
707 -- from date to start with. If there is user's from date, use it
708 -- only if it is older than transaction date; otherwise, use
709 -- transaction date.
710
711 if i_from_date is null then
712 l_from_date := i_txn_date;
713 elsif i_txn_date is not null
714 and i_from_date > i_txn_date then
715 l_from_date := i_txn_date;
716 else
717 l_from_date := i_from_date;
718 end if;
719
720 BEGIN
721 -- Get period dates
722 l_stmt_num := 10;
723 select trunc (oap.period_start_date), --Dinkar 10/11/02
724 trunc (oap.schedule_close_date), --Dinkar 10/11/02
725 trunc (oap.period_close_date) --Dinkar 10/11/02
726 into l_per_start_date,
727 l_sched_close_date,
728 l_per_close_date
729 from org_acct_periods oap
730 where organization_id = i_org_id
731 and oap.period_start_date <= l_from_date
732 and oap.schedule_close_date >= l_from_date;
733
734 if i_first_push <> 1 then -- not a first push
735
736 if (trunc(l_from_date) = trunc(l_sched_close_date))
737 or (l_per_close_date is null) -- period is open
738 or (l_per_close_date is not null -- period is closed
739 /* last push date within same period */
740 and i_txn_date is not null
741 and (i_txn_date >= l_per_start_date
742 and i_txn_date <= l_sched_close_date)) then
743 l_calc_from_date := l_from_date; -- no change in date
744 else
745 l_calc_from_date := l_per_start_date;
746 end if;
747
748 else -- first push
749 l_calc_from_date := l_per_start_date;
750 end if;
751
752 EXCEPTION
753 when no_data_found then
754 l_calc_from_date := i_from_date;
755
756 END;
757
758 o_calc_from_date := l_calc_from_date;
759 o_err_num := l_err_num;
760 o_err_code := l_err_code;
761 o_err_msg := l_err_msg;
762
763 EXCEPTION
764 when others then
765 o_err_num := SQLCODE;
766 o_err_msg := 'OPIMPDAT.calc_from_date - ('
767 || to_char(l_stmt_num)
768 || '): '
769 || substr(SQLERRM, 1,200);
770 END calc_from_date;
771
772 END OPIMPDAT;
[Home] [Help]