[Home] [Help]
PACKAGE BODY: APPS.MRP_REPORTING_BUCKETS
Source
1 PACKAGE BODY mrp_reporting_buckets AS
2 /* $Header: MRPPRBKB.pls 115.0 99/07/16 12:33:44 porting ship $ */
3
4 /*-------------------------- PUBLIC ROUTINES ---------------------------------*/
5
6 PROCEDURE mrp_weeks_months(
7 arg_query_id IN NUMBER,
8 arg_user_id IN NUMBER,
9 arg_weeks IN NUMBER,
10 arg_periods IN NUMBER,
11 arg_start_date IN DATE,
12 arg_org_id IN NUMBER) IS
13 var_week_counter NUMBER;
14 var_month_counter NUMBER;
15 var_week_bucket_date mrp_form_query.date1%TYPE;
16 var_month_bucket_date mrp_form_query.date1%TYPE;
17 var_month_start_date mrp_form_query.date1%TYPE;
18 var_calendar_code VARCHAR2(10);
19 var_exception_set_id NUMBER;
20 var_weeks NUMBER;
21 var_periods NUMBER;
22 /*-------------------------------------------------------------------------+
23 | Weeks cursor |
24 +-------------------------------------------------------------------------*/
25 CURSOR weeks_cur IS
26 SELECT week_start_date
27 FROM bom_cal_week_start_dates
28 WHERE calendar_code = var_calendar_code
29 AND exception_set_id = var_exception_set_id
30 AND week_start_date >=
31 (SELECT max(week_start_date)
32 FROM bom_cal_week_start_dates
33 WHERE week_start_date <= TRUNC(arg_start_date)
34 AND calendar_code = var_calendar_code
35 AND exception_set_id = var_exception_set_id)
36 ORDER BY week_start_date;
37 /*-------------------------------------------------------------------------+
38 | Months cursor |
39 +-------------------------------------------------------------------------*/
40 CURSOR months_cur IS
41 SELECT period_start_date
42 FROM bom_period_start_dates
43 WHERE calendar_code = var_calendar_code
44 AND exception_set_id = var_exception_set_id
45 AND period_start_date >=
46 (SELECT max(period_start_date)
47 FROM bom_period_start_dates
48 WHERE period_start_date <= var_month_start_date
49 AND calendar_code = var_calendar_code
50 AND exception_set_id = var_exception_set_id)
51 ORDER BY period_start_date;
52 BEGIN
53 /*-------------------------------------------------------------------------+
54 | Select calendar defaults |
55 +-------------------------------------------------------------------------*/
56 mrp_calendar.select_calendar_defaults(
57 arg_org_id,
58 var_calendar_code,
59 var_exception_set_id);
60
61 -- Add one to either the weeks or the periods...need to get one extra
62 -- date in order to calculate the end date
63 IF arg_weeks = arg_periods
64 THEN
65 var_weeks := arg_weeks + 1;
66 var_periods := arg_periods;
67 ELSE
68 var_weeks := arg_weeks;
69 var_periods := arg_periods + 1;
70 END IF;
71 /*-------------------------------------------------------------------------+
72 | For each week ... |
73 +-------------------------------------------------------------------------*/
74
75 OPEN weeks_cur;
76 FOR var_week_counter IN 1..var_weeks
77 LOOP
78 FETCH weeks_cur INTO
79 var_week_bucket_date;
80
81 IF weeks_cur%NOTFOUND
82 THEN
83 raise_application_error(-20000, 'Cannot select week');
84 END IF;
85 /*---------------------------------------------------------------------+
86 | Insert a row for each week... |
87 +---------------------------------------------------------------------*/
88 INSERT INTO mrp_form_query
89 (query_id,
90 date1,
91 last_update_date,
92 last_updated_by,
93 creation_date,
94 created_by,
95 last_update_login)
96 VALUES (arg_query_id,
97 var_week_bucket_date,
98 SYSDATE,
99 arg_user_id,
100 SYSDATE,
101 arg_user_id,
102 -1);
103 END LOOP;
104 CLOSE weeks_cur;
105 /*-------------------------------------------------------------------------+
106 | Now do the same for months |
107 +-------------------------------------------------------------------------*/
108 IF var_week_bucket_date IS NULL
109 THEN
110 var_month_start_date := arg_start_date;
111 ELSE
112 SELECT min(period_start_date)
113 INTO var_month_start_date
114 FROM bom_period_start_dates
115 WHERE period_start_date > var_week_bucket_date
116 AND calendar_code = var_calendar_code
117 AND exception_Set_id = var_exception_set_id;
118 END IF;
119 OPEN months_cur;
120 FOR var_month_counter IN arg_weeks+1..var_periods
121 LOOP
122 FETCH months_cur INTO
123 var_month_bucket_date;
124
125 IF months_cur%NOTFOUND
126 THEN
127 raise_application_error(-20001, 'Cannot select month');
128 END IF;
129 /*---------------------------------------------------------------------+
130 | Insert a row for each week... |
131 +---------------------------------------------------------------------*/
132 INSERT INTO mrp_form_query
133 (query_id,
134 date1,
135 last_update_date,
136 last_updated_by,
137 creation_date,
138 created_by,
139 last_update_login)
140 VALUES (arg_query_id,
141 var_month_bucket_date,
142 SYSDATE,
143 arg_user_id,
144 SYSDATE,
145 arg_user_id,
146 -1);
147 END LOOP;
148 CLOSE months_cur;
149 UPDATE mrp_form_query q
150 SET date2 =
151 (SELECT MIN(q2.date1)
152 FROM mrp_form_query q2
153 WHERE q2.query_id = q.query_id
154 AND q2.date1 > q.date1)
155 WHERE q.query_id = arg_query_id;
156
157 DELETE FROM mrp_form_query
158 WHERE query_id = arg_query_id
159 AND date2 IS NULL;
160
161 END mrp_weeks_months;
162 END mrp_reporting_buckets;