[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_COGS_F_SZ
Source
1 PACKAGE BODY opi_edw_cogs_f_sz AS
2 /* $Header: OPIOCGZB.pls 120.3 2006/05/31 23:42:59 julzhang noship $*/
3
4 PROCEDURE cnt_rows(p_from_date DATE,
5 p_to_date DATE,
6 p_num_rows OUT NOCOPY NUMBER) IS
7
8 CURSOR c_cnt_rows IS
9 select sum(cnt)
10 from (SELECT count(*) cnt
11 FROM
12 oe_order_headers_all h,
13 oe_order_lines_all pl,
14 oe_order_lines_all l,
15 wsh_delivery_details wdd,
16 mtl_transaction_accounts mta,
17 mtl_material_transactions mmt
18 where mmt.transaction_source_type_id = 2
19 and mta.transaction_source_type_id = 2
20 and mmt.transaction_id = mta.transaction_id
21 and mta.accounting_line_type in (2, 35)
22 and pl.org_id = l.org_id
23 and h.org_id = l.org_id
24 and l.line_id = mmt.trx_source_line_id
25 and l.line_category_code = 'ORDER'
26 and pl.line_category_code = 'ORDER'
27 and pl.line_id = nvl(l.top_model_line_id, l.line_id)
28 and h.header_id = l.header_id
29 and h.header_id = pl.header_id
30 and wdd.delivery_detail_id = mmt.picking_line_id
31 AND greatest(nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
32 nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
33 nvl(mta.last_update_date,to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
34 nvl(mmt.last_update_date,to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
35 nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
36 BETWEEN p_from_date and p_to_date
37 UNION ALL
38 SELECT count(*) cnt
39 from
40 oe_order_headers_all h,
41 oe_order_lines_all pl,
42 oe_order_lines_all cl,
43 oe_order_lines_all l,
44 mtl_transaction_accounts mta,
45 mtl_material_transactions mmt
46 where mmt.transaction_source_type_id = 12
47 and mta.transaction_source_type_id = 12
48 and mmt.transaction_id = mta.transaction_id
49 and mta.accounting_line_type in (2, 35)
50 and h.org_id = l.org_id
51 and l.line_id = mmt.trx_source_line_id
52 and l.line_category_code = 'RETURN'
53 and cl.line_id (+) = l.link_to_line_id
54 and pl.line_id (+) = nvl(cl.top_model_line_id, cl.line_id)
55 and h.header_id = l.header_id
56 AND greatest(nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
57 nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
58 nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
59 nvl(mta.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
60 nvl(mmt.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
61 nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
62 BETWEEN p_from_date and p_to_date
63 UNION ALL
64 SELECT count(*) cnt
65 FROM
66 oe_order_headers_all h,
67 oe_order_lines_all pl, /* parent line */
68 oe_order_lines_all l, /* child line */
69 ra_customer_trx_lines_all rcl,
70 ap_invoice_distributions_all aid,
71 ap_invoices_all ai,
72 mtl_material_transactions mmt,
73 mtl_parameters mp
74 WHERE ai.source = 'Intercompany'
75 AND aid.invoice_id = ai.invoice_id
76 and aid.org_id = ai.org_id
77 and rcl.CUSTOMER_TRX_LINE_ID = to_number(aid.REFERENCE_1)
78 and aid.line_type_lookup_code = 'ITEM'
79 and rcl.interface_line_attribute6 = l.line_id
80 and pl.line_id = nvl(l.top_model_line_id, l.line_id)
81 and pl.org_id = l.org_id
82 and h.org_id = l.org_id
83 and h.header_id = l.header_id
84 and h.header_id = pl.header_id
85 and l.line_category_code = 'ORDER'
86 and pl.line_category_code = 'ORDER'
87 and rcl.interface_line_attribute7 = mmt.transaction_id
88 and nvl(mmt.logical_transaction,0) <> 1
89 and mmt.organization_id = mp.organization_id
90 and mp.process_enabled_flag <> 'Y'
91 AND greatest(
92 nvl(aid.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
93 nvl(ai.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
94 nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
95 nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
96 nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
97 BETWEEN p_from_date and p_to_date
98 );
99
100 BEGIN
101
102 OPEN c_cnt_rows;
103 FETCH c_cnt_rows INTO p_num_rows;
104 CLOSE c_cnt_rows;
105
106 END; -- procedure cnt_rows.
107
108
109 PROCEDURE est_row_len(p_from_date DATE,
110 p_to_date DATE,
111 p_avg_row_len OUT NOCOPY NUMBER) IS
112 x_date number := 7;
113 x_total number := 0;
114 x_constant number := 6;
115
116 CURSOR c_mmt IS
117 SELECT avg(nvl(vsize(transaction_id), 0)) txn_id,
118 avg(nvl(vsize(transaction_uom), 0)) uom,
119 avg(nvl(vsize(currency_code), 0)) currency,
120 avg(nvl(vsize(subinventory_code), 0)) sub_code,
121 avg(nvl(vsize(locator_id), 0)) locator,
122 avg(nvl(vsize(primary_quantity), 0)) qty,
123 avg(nvl(vsize(inventory_item_id), 0)) item_id
124 FROM mtl_material_transactions
125 WHERE last_update_date between p_from_date and p_to_date;
126
127 CURSOR c_mta IS
128 SELECT avg(nvl(vsize(cost_element_id), 0)) cost_element_id,
129 avg(nvl(vsize(transaction_value), 0)) transaction_value,
130 avg(nvl(vsize(reference_account), 0)) account
131 FROM mtl_transaction_accounts
132 WHERE last_update_date between p_from_date and p_to_date;
133
134 CURSOR c_line IS
135 SELECT avg(nvl(vsize(line_id), 0)) line_id,
136 avg(nvl(vsize(project_id), 0)) project_id,
137 avg(nvl(vsize(task_id), 0)) task_id,
138 avg(nvl(vsize(source_type_code), 0)) source_type_code,
139 avg(nvl(vsize(marketing_source_code_id), 0)) marketing_source_code_id
140 FROM oe_order_lines_all
141 WHERE last_update_date between p_from_date and p_to_date;
142
143 CURSOR c_header IS
144 SELECT avg(nvl(vsize(sales_channel_code), 0)) channel_code,
145 avg(nvl(vsize(salesrep_id), 0)) salesrep_id,
146 avg(nvl(vsize(order_category_code), 0)) order_category_code,
147 avg(nvl(vsize(header_id), 0)) header_id,
148 avg(nvl(vsize(order_number), 0)) order_number
149 FROM oe_order_headers_all
150 WHERE last_update_date between p_from_date and p_to_date;
151
152 CURSOR c_lookup IS
153 select avg(nvl(vsize(lookup_code_pk), 0))
154 from edw_lookup_code_fkv;
155
156 CURSOR c_wdd IS
157 SELECT avg(nvl(vsize(lot_number), 0)) LOT,
158 avg(nvl(vsize(revision), 0)) REVISION,
159 avg(nvl(vsize(serial_number), 0)) SERIAL_NUMBER
160 FROM wsh_delivery_details
161 WHERE last_update_date between p_from_date and p_to_date;
162
163 CURSOR c_wnd IS
164 SELECT avg(nvl(vsize(waybill), 0)) waybill
165 FROM wsh_new_deliveries
166 WHERE last_update_date between p_from_date and p_to_date;
167
168 CURSOR c_instance IS
169 SELECT
170 avg(nvl(vsize(instance_code), 0))
171 FROM EDW_LOCAL_INSTANCE ;
172
173 CURSOR c_org IS
174 SELECT avg(nvl(Vsize(organization_id), 0)) org_id,
175 avg(nvl(Vsize(organization_code), 0)) org_code
176 FROM mtl_parameters;
177
178 CURSOR c_act_usage IS
179 SELECT AVG(Nvl(Vsize(primary_quantity), 0))
180 FROM wip_transactions
181 WHERE last_update_date between p_from_date and p_to_date;
182
183 CURSOR c_avail IS
184 SELECT AVG(Nvl(Vsize(24*capacity_units), 0))
185 FROM bom_department_resources
186 WHERE last_update_date between p_from_date and p_to_date;
187
188 CURSOR c_trx_date_fk IS
189 SELECT AVG(Nvl(Vsize(EDW_TIME_PKG.CAL_DAY_FK(Sysdate, set_of_books_id) ),0))
190 FROM gl_sets_of_books;
191
192 CURSOR c_offer IS
193 SELECT AVG(Nvl(Vsize(activity_offer_id), 0))
194 FROM ams_act_offers
195 WHERE last_update_date between p_from_date and p_to_date;
196
197 CURSOR c_target IS
198 SELECT AVG(Nvl(Vsize(cell_code), 0))
199 FROM ams_list_entries
200 WHERE last_update_date between p_from_date and p_to_date;
201
202 CURSOR c_campaign IS
203 SELECT AVG(Nvl(Vsize(user_status_id), 0))
204 FROM ams_campaigns_all_b
205 WHERE last_update_date between p_from_date and p_to_date;
206
207 x_trx_date_fk NUMBER;
208 x_instance_fk NUMBER;
209
210 l_mmt c_mmt%ROWTYPE;
211 l_mta c_mta%ROWTYPE;
212 l_line c_line%ROWTYPE;
213 l_header c_header%ROWTYPE;
214 l_wdd c_wdd%ROWTYPE;
215 l_org c_org%ROWTYPE;
216
217 x_offer NUMBER;
218 x_target NUMBER;
219 x_campaign NUMBER;
220 x_wnd NUMBER;
221 x_lookup NUMBER;
222
223 BEGIN
224 OPEN c_instance;
225 FETCH c_instance INTO x_instance_fk;
226 CLOSE c_instance;
227
228 OPEN c_mmt;
229 FETCH c_mmt INTO l_mmt;
230 CLOSE c_mmt;
231
232 OPEN c_mta;
233 FETCH c_mta INTO l_mta;
234 CLOSE c_mta;
235
236 OPEN c_line;
237 FETCH c_line INTO l_line;
238 CLOSE c_line;
239
240 OPEN c_header;
241 FETCH c_header INTO l_header;
242 CLOSE c_header;
243
244 OPEN c_lookup;
245 FETCH c_lookup INTO x_lookup;
246 CLOSE c_lookup;
247
248 OPEN c_wdd;
249 FETCH c_wdd INTO l_wdd;
250 CLOSE c_wdd;
251
252 OPEN c_wnd;
253 FETCH c_wnd INTO x_wnd;
254 CLOSE c_wnd;
255
256 OPEN c_org;
257 FETCH c_org INTO l_org;
258 CLOSE c_org;
259
260 OPEN c_trx_date_fk;
261 FETCH c_trx_date_fk INTO x_trx_date_fk;
262 CLOSE c_trx_date_fk;
263
264 OPEN c_offer;
265 FETCH c_offer INTO x_offer;
266 CLOSE c_offer;
267
268 OPEN c_target;
269 FETCH c_target INTO x_target;
270 CLOSE c_target;
271
272 OPEN c_campaign;
273 FETCH c_campaign INTO x_campaign;
274 CLOSE c_campaign;
275
276 x_total := 3 + x_total
277 -- COGS_PK
278 + Ceil( l_mmt.txn_id + l_mta.cost_element_id + l_line.line_id
279 + x_instance_fk + 7 + 1)
280 -- INSTANCE_FK
281 + Ceil( x_instance_fk + 1)
282 -- TOP_MODEL_ITEM_FK ITEM_ORG_FK
283 + 2 * Ceil( l_mmt.item_id + l_org.org_id + x_instance_fk + 7 +1)
284 -- OPERATING_UNIT_FK INV_ORG_FK
285 + 2* Ceil( l_org.org_id + x_instance_fk + 1)
286 -- CUSTOMER_FK
287 + Ceil(l_org.org_id + x_instance_fk+ 16+ 1)
288 -- SALES_CHANNEL_FK
289 + Ceil( l_header.channel_code + x_instance_fk + 1 +1)
290 -- PRIM_SALES_REP_FK
291 + Ceil( l_header.salesrep_id + x_instance_fk + 16 +1);
292
293 x_total := x_total
294 -- BILL_TO_LOC_FK SHIP_TO_LOC_FK
295 + 2* Ceil(l_org.org_id + x_instance_fk + 12 + 1)
296 -- PROJECT_FK
297 + Ceil(l_line.project_id + x_instance_fk + 8 + 1)
298 -- TASK_FK
299 + Ceil(l_line.task_id + x_instance_fk + 1 + 1)
300 -- BASE_UOM_FK
301 + Ceil( l_mmt.uom + 1)
302 -- TRX_CURRENCY_FK BASE_CURRENCY_FK
303 + Ceil( l_mmt.currency + 1)
304 -- ORDER_CATEGORY_FK ORDER_TYPE_FK ORDER_SOURCE_FK
305 + 3 *Ceil( x_lookup + 1)
306 -- BILL_TO_SITE_FK SHIP_TO_SITE_FK
307 + 2* Ceil(l_org.org_id + x_instance_fk + 15 +1)
308 -- MONTH_BOOKED_FK DATE_BOOKED_FK DATE_PROMISED_FK
309 -- DATE_REQUESTED_FK DATE_SCHEDULED_FK DATE_SHIPPED_FK COGS_DATE_FK
310 + 7* Ceil( x_trx_date_fk + 1)
311 -- LOCATOR_FK
312 + Ceil(l_mmt.sub_code + l_org.org_code + x_instance_fk + 7 +1)
313 -- SET_OF_BOOKS_FK
314 + Ceil( 3 + x_instance_fk + 1);
315
316 -- this section is to handle marketing team FKs
317 --
318
319 x_total := x_total
320 -- OFFER_HDR_FK OFFER_LINE_FK
321 + 2* Ceil( Nvl(x_offer,0) + x_instance_fk + 1)
322 -- TARGET_SEGMENT_INIT_FK TARGET_SEGMENT_ACTL_FK
323 + 2* Ceil( Nvl(x_target,0) + x_instance_fk + 1)
324 -- CAMPAIGN_STATUS_ACTL_FK CAMPAIGN_STATUS_INIT_FK
325 + 2* Ceil( Nvl(x_campaign,0) + x_instance_fk + 1);
326
327 x_total := x_total
328 -- ORDER_LINE_ID
329 + Ceil(l_line.line_id + x_instance_fk + 1 + 1)
330 -- SHIP_INV_LOCATOR_FK
331 + Ceil(l_org.org_code + x_instance_fk +6 + 1)
332 -- COGS_DATE ORDER_DATE
333 + 2 * x_date
334 -- PROM_EARLY_COUNT PROM_LATE_COUNT REQ_EARLY_COUNT REQ_LATE_COUNT
335 + 4 * 2
336 -- PROM_EARLY_VAL_G PROM_LATE_VAL_G REQ_EARLY_VAL_G REQ_LATE_VAL_G
337 + 4 * Ceil( l_mta.transaction_value)
338 -- REQUEST_LEAD_TIME PROMISE_LEAD_TIME ORDER_LEAD_TIME
339 + 3 * 3
340 -- SHIPPED_QTY_B RMA_QTY_B ICAP_QTY_B
341 + 3 * Ceil(l_mmt.qty + 1)
342 -- COGS_T COGS_B COGS_G RMA_VAL_T RMA_VAL_G
343 + 5 * Ceil(l_mta.transaction_value + 1)
344 -- LAST_UPDATE_DATE,
345 + x_date
346 -- cost_element_id
347 + Ceil(l_mta.cost_element_id + 1)
348 -- ACCOUNT
349 + Ceil(l_mta.account + 1)
350 -- ORDER_NUMBER
351 + Ceil(l_header.order_number + 1)
352 -- WAYBILL_NUMBER
353 + Ceil(x_wnd + 1)
354 -- LOT
355 + Ceil(l_wdd.lot + 1)
356 -- REVISION
357 + Ceil(l_wdd.revision + 1)
358 -- SERIAL_NUMBER
359 + Ceil(l_wdd.serial_number + 1);
360
361 -- dbms_output.put_line('1 x_total is ' || x_total );
362
363 p_avg_row_len := x_total;
364
365
366 END; -- procedure est_row_len.
367
368 END; -- package body OPI_EDW_OPI_RES_UTIL_F_SZ