[Home] [Help]
PACKAGE BODY: APPS.OPIMXRU
Source
1 PACKAGE BODY OPIMXRU AS
2 /* $Header: OPIMXRUB.pls 115.4 2002/05/06 21:59:19 ltong noship $ */
3
4 PROCEDURE extract_opi_res_util (p_from_date DATE ,
5 p_to_date DATE )
6 IS
7
8 CURSOR wip_used (l_uom_class VARCHAR2,
9 l_hr_base_rate NUMBER ) is
10 SELECT
11 trunc(wt.transaction_date) ||'-'|| wt.organization_id ||'-'||
12 bd.department_id || '-' || wt.resource_id res_util_pk,
13 br.resource_code,
14 mp.organization_code, wt.organization_id,
15 trunc(wt.transaction_date) trx_date,
16 bd.department_code,
17 To_number(hoi.org_information1) sob_id,
18 SUM(primary_quantity* m2.conversion_rate/l_hr_base_rate) act_res_usage
19 FROM
20 bom_resources br,
21 bom_departments bd,
22 bom_department_resources bdr,
23 wip_transactions wt,
24 mtl_parameters mp,
25 mtl_uom_conversions m2,
26 HR_ORGANIZATION_INFORMATION hoi
27 WHERE
28 -- 1->resource trx 3-> outside processing,
29 -- both involve resource, other types don't have resource_id
30 wt.transaction_type IN (1,3)
31 and wt.transaction_date >= trunc(p_from_date)
32 and wt.transaction_date < trunc(p_to_date+1)
33 AND wt.organization_id = mp.organization_id
34 AND bdr.department_id = wt.department_id
35 AND bdr.resource_id = wt.resource_id
36 AND bd.department_id = Nvl(bdr.share_from_dept_id, bdr.department_id)
37 AND br.resource_id = wt.resource_id
38 AND m2.uom_code = wt.primary_uom
39 AND m2.inventory_item_id = 0
40 AND m2.uom_class = l_uom_class
41 AND hoi.organization_id = wt.organization_id
42 AND hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
43 GROUP BY
44 wt.organization_id,
45 bd.department_id, -- owning dept
46 wt.resource_id,
47 trunc(wt.transaction_date),
48 mp.organization_code,
49 hoi.org_information1,
50 bd.department_code,
51 br.resource_code ;
52
53 p_uom_class VARCHAR2(10);
54 l_count NUMBER := 0;
55 l_opi_schema VARCHAR2(30);
56 l_status VARCHAR2(30);
57 l_industry VARCHAR2(30);
58 p_hr_base_rate NUMBER := 0;
59 p_uom_code VARCHAR2(10);
60
61
62 BEGIN
63
64 -- --------------------------------------------------------
65 -- get the uom_class
66 -- --------------------------------------------------------
67 -- --------------------------------------------------------
68 -- get the conversion rate from 'BOM:HOUR_UOM_CODE' to its
69 -- class uom
70 -- --------------------------------------------------------
71 SELECT uom_code, uom_class, conversion_rate
72 INTO p_uom_code, p_uom_class, p_hr_base_rate
73 FROM mtl_uom_conversions
74 WHERE inventory_item_id = 0
75 AND uom_code = fnd_profile.value('BOM:HOUR_UOM_CODE');
76
77 -- --------------------------------------------------------
78 -- Insert 24 hr available resource into the push_log table
79 -- actual usage is 0
80 -- --------------------------------------------------------
81 insert into opi_edw_res_util_push_log
82 ( res_util_pk, organization_code, resource_code,
83 department_code, trx_date, sob_id,
84 uom, act_res_usage, avail_res )
85 SELECT /*+ ALL_ROWS */
86 Trunc(bcd.calendar_date) ||'-'|| mp.organization_id ||'-'||
87 bd.department_id || '-' || br.resource_id,
88 mp.organization_code, br.resource_code,
89 bd.department_code, bcd.calendar_date, To_number(hoi.org_information1),
90 p_uom_code,
91 0,
92 24* bdr.capacity_units
93 FROM bom_resources br,
94 bom_departments bd,
95 bom_department_resources bdr,
96 bom_calendar_dates bcd,
97 mtl_parameters mp,
98 mtl_units_of_measure m2,
99 HR_ORGANIZATION_INFORMATION hoi
100 WHERE bdr.available_24_hours_flag = 1 -- 24 hr available
101 AND bdr.share_from_dept_id IS NULL -- owing dept
102 AND br.resource_id = bdr.resource_id
103 AND m2.uom_code = br.unit_of_measure
104 AND m2.uom_class = p_uom_class
105 AND bd.department_id = bdr.department_id
106 AND bd.organization_id = mp.organization_id
107 AND bcd.calendar_code = mp.calendar_code
108 AND bcd.exception_set_id = mp.calendar_exception_set_id
109 AND bcd.seq_num IS NOT NULL -- scheduled to be on
110 and bcd.calendar_date >= Trunc(p_from_date)
111 and bcd.calendar_date < Trunc(p_to_date+1)
112 AND ( bd.disable_date IS NULL OR bcd.calendar_date < bd.disable_date)
113 AND ( br.disable_date IS NULL OR bcd.calendar_date < br.disable_date)
114 AND hoi.organization_id = mp.organization_id
115 AND hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
116 ;
117
118 edw_log.put_line('Inserting into push_log for 24 hr availalbe res count ' || SQL%rowcount);
119 edw_log.put_line('system time is ' || To_char(Sysdate, 'MM/DD/YYYY HH24:MI:SS') );
120 -- --------------------------------------------------------
121 -- commit since there might be a large set data
122 -- --------------------------------------------------------
123 COMMIT;
124
125
126 -- --------------------------------------------------------
127 -- Insert shift based available resource into push log
128 -- --------------------------------------------------------
129 INSERT INTO opi_edw_res_util_push_log
130 ( res_util_pk, organization_code, resource_code,
131 department_code, trx_date, sob_id,
132 uom, act_res_usage, avail_res )
133 SELECT
134 Trunc(bsd.shift_date) ||'-'|| mp.organization_id ||'-'||
135 bd.department_id || '-' || br.resource_id res_util_pk,
136 mp.organization_code, br.resource_code,
137 bd.department_code, bsd.shift_date, To_number(hoi.org_information1),
138 p_uom_code,
139 0,
140 SUM((bst.to_time - bst.from_time)/3600*bdr.capacity_units) avail_res
141 FROM
142 bom_resources br,
143 bom_departments bd,
144 bom_department_resources bdr,
145 bom_resource_shifts brs,
146 bom_shift_dates bsd,
147 bom_shift_times bst,
148 mtl_parameters mp,
149 mtl_units_of_measure m2,
150 HR_ORGANIZATION_INFORMATION hoi
151 WHERE bdr.available_24_hours_flag = 2 -- shift based
152 AND bdr.share_from_dept_id IS NULL -- owning dept
153 AND br.resource_id = bdr.resource_id
154 AND m2.uom_code = br.unit_of_measure
155 AND m2.uom_class = p_uom_class
156 AND bd.department_id = bdr.department_id
157 AND bd.organization_id = mp.organization_id
158 AND brs.department_id = bd.department_id
159 AND brs.resource_id = br.resource_id
160 AND bsd.calendar_code = mp.calendar_code
161 AND bsd.exception_set_id = mp.calendar_exception_set_id
162 AND bsd.shift_num = brs.shift_num
163 AND bsd.seq_num IS NOT NULL -- schedule to be available
164 AND bsd.shift_date >= Trunc(p_from_date)
165 AND bsd.shift_date < Trunc(p_to_date + 1)
166 AND ( bd.disable_date IS NULL OR bsd.shift_date < bd.disable_date)
167 AND ( br.disable_date IS NULL OR bsd.shift_date < br.disable_date)
168 AND bst.calendar_code = mp.calendar_code
169 AND bst.shift_num = brs.shift_num
170 AND hoi.organization_id = mp.organization_id
171 AND hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
172 GROUP BY
173 mp.organization_id,
174 bd.department_id, -- owning dept
175 br.resource_id,
176 bsd.shift_date,
177 mp.organization_code,
178 bd.department_code,
179 br.unit_of_measure,
180 br.resource_code,
181 hoi.org_information1
182 ;
183
184 edw_log.put_line('Inserting into push_log for shift availalbe res count ' || SQL%rowcount);
185 edw_log.put_line('system time is ' || To_char(Sysdate, 'MM/DD/YYYY HH24:MI:SS') );
186 COMMIT;
187
188 -- --------------------------------------------------------
189 -- For the those resoruce actually used in WIP
190 -- a). If records already exists in push_log, update it with
191 -- the actual usage value
192 -- b). If not already exists (those resource actually used but
193 -- not scheduled to be available, create a new record
194 -- in push log avail_usage = actual_usage
195 --
196 -- Potentially, there can be millions records updated/inserted
197 -- We need to periodically commit in order to prevent rbs from
198 -- running out
199 -- --------------------------------------------------------
200
201 FOR l_wip IN wip_used(p_uom_class, p_hr_base_rate) LOOP
202 UPDATE opi_edw_res_util_push_log
203 SET act_res_usage = l_wip.act_res_usage
204 WHERE res_util_pk = l_wip.res_util_pk;
205
206 IF sql%rowcount = 0 THEN -- not existed
207
208 INSERT INTO opi_edw_res_util_push_log
209 ( res_util_pk, organization_code, resource_code,
210 department_code, trx_date, sob_id,
211 uom, act_res_usage, avail_res )
212 values ( l_wip.res_util_pk, l_wip.organization_code, l_wip.resource_code,
213 l_wip.department_code, l_wip.trx_date, l_wip.sob_id,
214 p_uom_code, l_wip.act_res_usage, l_wip.act_res_usage );
215 END IF;
216
217 IF l_count = 10000 THEN
218 COMMIT;
219 l_count := 0;
220 ELSE
221 l_count := l_count + 1;
222 END IF;
223
224 END LOOP;
225
226 edw_log.put_line('After WIP used res update /insert system time is ' || To_char(Sysdate, 'MM/DD/YYYY HH24:MI:SS') );
227
228 COMMIT;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 edw_log.put_line('Inserting into push log has failed. ');
233 RAISE;
234
235 END extract_opi_res_util;
236
237 END opimxru;