[Home] [Help]
PACKAGE BODY: APPS.ISC_EDW_BACKLOGS_F_SIZE
Source
1 PACKAGE BODY ISC_EDW_BACKLOGS_F_SIZE AS
2 /* $Header: ISCSGF1B.pls 115.3 2002/12/19 00:46:33 scheung ship $ */
3
4 /* ------------------------------------------
5 PROCEDURE NAME : cnt_rows
6 INPUT PARAMETERS : p_from_date, p_to_date
7 OUTPUT PARAMETERS: p_num_rows
8 DESCRIPTION : Count the number of rows
9 ------------------------------------------- */
10
11 PROCEDURE cnt_rows(p_from_date DATE,
12 p_to_date DATE,
13 p_num_rows OUT NOCOPY NUMBER) IS
14
15 BEGIN
16
17 SELECT count(distinct backlogs_pk)
18 INTO p_num_rows
19 FROM (SELECT trunc(sysdate,'DD')
20 ||'-'|| inst.instance_code
21 ||'-'|| l.order_quantity_uom
22 ||'-'|| nvl( pl.inventory_item_id,l.inventory_item_id)
23 ||'-'|| decode( pl.ship_from_org_id, NULL,
24 pl.inventory_item_id||'-'||pl.org_id,
25 pl.inventory_item_id||'-'||pl.ship_from_org_id)
26 ||'-'|| decode( l.ship_from_org_id, NULL,
27 l.inventory_item_id||'-'||l.org_id,
28 l.inventory_item_id||'-'||l.ship_from_org_id)
29 ||'-'|| nvl( l.ship_from_org_id,h.ship_from_org_id)
30 ||'-'|| l.org_id
31 ||'-'|| l.sold_to_org_id
32 ||'-'|| h.sales_channel_code
33 ||'-'|| l.salesrep_id
34 ||'-'|| l.task_id
35 ||'-'|| h.invoice_to_org_id
36 ||'-'|| l.ship_to_org_id
37 ||'-'|| nvl( l.demand_class_code,h.demand_class_code)
38 ||'-'|| h.transactional_curr_code
39 ||'-'|| l.line_category_code
40 ||'-'|| ot.transaction_type_id
41 ||'-'|| nvl( os_2.order_source_id, os_1.order_source_id)
42 ||'-'|| fspa.set_of_books_id BACKLOGS_PK
43 FROM edw_local_instance inst,
44 oe_transaction_types_tl ot,
45 oe_order_lines_all pl,
46 oe_order_headers_all h,
47 oe_order_lines_all l,
48 oe_order_sources os_1,
49 oe_order_sources os_2,
50 mtl_system_items_b p_mtl,
51 mtl_system_items_b mtl,
52 financials_system_params_all fspa,
53 gl_sets_of_books gl
54 WHERE (h.last_update_date between p_from_date and p_to_date
55 OR l.last_update_date between p_from_date and p_to_date)
56 AND h.open_flag = 'Y'
57 AND h.booked_flag = 'Y'
58 AND nvl(l.ordered_quantity,0) > 0
59 AND nvl(l.source_document_type_id,0) <> 10
60 AND l.line_category_code = ('ORDER')
61 AND l.header_id = h.header_id
62 AND h.header_id = pl.header_id
63 AND nvl(l.top_model_line_id, l.line_id) = pl.line_id
64 AND h.order_type_id = ot.transaction_type_id
65 AND ot.language = userenv('LANG')
66 AND l.order_source_id = os_1.order_source_id (+)
67 AND l.source_document_type_id = os_2.order_source_id (+)
68 AND l.org_id = fspa.org_id
69 AND fspa.set_of_books_id = gl.set_of_books_id
70 AND l.inventory_item_id = mtl.inventory_item_id(+)
71 AND l.ship_from_org_id = mtl.organization_id(+)
72 AND pl.inventory_item_id = p_mtl.inventory_item_id(+)
73 AND pl.ship_from_org_id = p_mtl.organization_id(+));
74
75 Exception When others then
76 rollback;
77
78 END;
79
80
81 /* ------------------------------------------
82 PROCEDURE NAME : est_row_len
83 INPUT PARAMETERS : p_from_date, p_to_date
84 OUTPUT PARAMETERS: p_avg_row_len
85 DESCRIPTION : Estimate input_f
86 ------------------------------------------ */
87
88 PROCEDURE est_row_len(p_from_date DATE,
89 p_to_date DATE,
90 p_avg_row_len OUT NOCOPY NUMBER) IS
91
92 x_date number := 7;
93 x_total number := 0;
94 x_l_inv number := 0;
95 x_salesrep_id number := 0;
96 x_mtl_inv number := 0;
97 x_mtl_org_id number := 0;
98 x_h_salesrep_id number := 0;
99 x_cate_code number := 0;
100 x_set_of_books_id number := 0;
101 x_BACKLOGS_PK NUMBER;
102 x_BASE_UOM_FK NUMBER;
103 x_BILL_TO_CUST_FK NUMBER;
104 x_BILL_TO_LOCATION_FK NUMBER;
105 x_CUSTOMER_FK NUMBER;
106 x_DATE_BALANCE_FK NUMBER;
107 x_DEMAND_CLASS_FK NUMBER;
108 x_GL_BOOK_FK NUMBER;
109 x_INV_ORG_FK NUMBER;
110 x_ITEM_ORG_FK NUMBER;
111 x_OPERATING_UNIT_FK NUMBER;
112 x_ORDER_CATEGORY_FK NUMBER;
113 x_ORDER_SOURCE_FK NUMBER;
114 x_ORDER_TYPE_FK NUMBER;
115 x_SALES_CHANNEL_FK NUMBER;
116 x_SALES_PERSON_FK NUMBER;
117 x_SHIP_TO_CUST_FK NUMBER;
118 x_SHIP_TO_LOCATION_FK NUMBER;
119 x_TASK_FK NUMBER;
120 x_TOP_MODEL_ITEM_FK NUMBER;
121 x_TRX_CURRENCY_FK NUMBER;
122 x_BILL_BKLG_REV_T NUMBER;
123
124 CURSOR c_1 IS
125 SELECT nvl(avg(nvl(vsize(sold_to_org_id),0)),0),
126 nvl(avg(nvl(vsize(demand_class_code),0)),0),
127 nvl(avg(nvl(vsize(ship_from_org_id),0)),0),
128 nvl(avg(nvl(vsize(inventory_item_id),0)),0),
129 nvl(avg(nvl(vsize(org_id),0)),0),
130 nvl(avg(nvl(vsize(line_category_code),0)),0),
131 nvl(avg(nvl(vsize(salesrep_id),0)),0),
132 nvl(avg(nvl(vsize(ship_to_org_id),0)),0),
133 nvl(avg(nvl(vsize(task_id),0)),0)
134 FROM OE_ORDER_LINES_ALL
135 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
136
137 CURSOR c_2 IS
138 SELECT nvl(avg(nvl(vsize(UOM_EDW_BASE_UOM),0)),0)
139 FROM EDW_MTL_LOCAL_UOM_M;
140
141 CURSOR c_3 IS
142 SELECT nvl(avg(nvl(vsize(invoice_to_org_id),0)),0),
143 nvl(avg(nvl(vsize(sales_channel_code),0)),0),
144 nvl(avg(nvl(vsize(transactional_curr_code),0)),0),
145 nvl(avg(nvl(vsize(salesrep_id),0)),0),
146 nvl(avg(nvl(vsize(order_category_code),0)),0)
147 FROM OE_ORDER_HEADERS_ALL
148 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
149
150 CURSOR c_4 IS
151 SELECT nvl(avg(nvl(vsize(party_site_id),0)),0)
152 FROM HZ_CUST_ACCT_SITES_ALL;
153
154 CURSOR c_5 IS
155 SELECT nvl(avg(nvl(vsize(period_set_name),0)),0) + nvl(avg(nvl(vsize(accounted_period_type),0)),0)
156 FROM GL_SETS_OF_BOOKS;
157
158 CURSOR c_6 IS
159 SELECT nvl(avg(nvl(vsize(name),0)),0)
160 FROM OE_ORDER_SOURCES;
161
162 CURSOR c_7 IS
163 SELECT nvl(avg(nvl(vsize(name),0)),0)
164 FROM OE_TRANSACTION_TYPES_TL;
165
166 CURSOR c_8 IS
167 SELECT nvl(avg(nvl(vsize(to_char(set_of_books_id)),0)),0),
168 nvl(avg(nvl(vsize(set_of_books_id),0)),0)
169 FROM FINANCIALS_SYSTEM_PARAMS_ALL;
170
171 CURSOR c_9 IS
172 SELECT nvl(avg(nvl(vsize(inventory_item_id),0)),0),
173 nvl(avg(nvl(vsize(organization_id),0)),0)
174 FROM mtl_system_items_b;
175
176 CURSOR c_10 IS
177 SELECT nvl(avg(nvl(vsize(nvl(l.unit_selling_price,0)
178 *(nvl(l.ordered_quantity,0)-nvl(l.invoiced_quantity,0))
179 *nvl(EDW_CONVERSION_RATE,0)),0)),0)
180 FROM oe_order_lines_all l,
181 OPI_EDW_LOCAL_UOM_CONV_F;
182
183 BEGIN
184
185 OPEN c_1;
186 FETCH c_1 INTO x_CUSTOMER_FK, x_DEMAND_CLASS_FK, x_INV_ORG_FK,
187 x_l_inv, x_OPERATING_UNIT_FK, x_ORDER_CATEGORY_FK,
188 x_salesrep_id, x_SHIP_TO_CUST_FK, x_TASK_FK;
189 CLOSE c_1;
190
191 x_ITEM_ORG_FK := x_INV_ORG_FK + x_l_inv;
192 x_SALES_PERSON_FK := x_salesrep_id + x_OPERATING_UNIT_FK;
193 x_TOP_MODEL_ITEM_FK := x_ITEM_ORG_FK;
194
195 x_total := 3 + x_total + ceil(x_CUSTOMER_FK + 1) + ceil(x_DEMAND_CLASS_FK + 1) +
196 ceil(x_INV_ORG_FK + 1) + ceil(x_ITEM_ORG_FK + 1) +
197 ceil(x_OPERATING_UNIT_FK + 1) + ceil(x_ORDER_CATEGORY_FK + 1) +
198 ceil(x_SALES_PERSON_FK + 1) + ceil(x_SHIP_TO_CUST_FK + 1) +
199 ceil(x_TASK_FK + 1) + ceil(x_TOP_MODEL_ITEM_FK + 1);
200
201 OPEN c_2;
202 FETCH c_2 INTO x_BASE_UOM_FK;
203 CLOSE c_2;
204
205 x_total := x_total + ceil(x_BASE_UOM_FK+ 1);
206
207 OPEN c_3;
208 FETCH c_3 INTO x_BILL_TO_CUST_FK, x_SALES_CHANNEL_FK, x_TRX_CURRENCY_FK, x_h_salesrep_id, x_cate_code;
209 CLOSE c_3;
210
211 x_total := x_total + ceil(x_BILL_TO_CUST_FK + 1) + ceil(x_SALES_CHANNEL_FK + 1) +
212 ceil(x_TRX_CURRENCY_FK + 1);
213
214 OPEN c_4;
215 FETCH c_4 INTO x_BILL_TO_LOCATION_FK;
216 CLOSE c_4;
217
218 x_SHIP_TO_LOCATION_FK := x_BILL_TO_LOCATION_FK;
219 x_total := x_total + ceil(x_BILL_TO_LOCATION_FK + 1) + ceil(x_SHIP_TO_LOCATION_FK + 1);
220
221 OPEN c_5;
222 FETCH c_5 INTO x_DATE_BALANCE_FK;
223 CLOSE c_5;
224
225 x_total := x_total + ceil(x_DATE_BALANCE_FK + 1);
226
227 OPEN c_6;
228 FETCH c_6 INTO x_ORDER_SOURCE_FK;
229 CLOSE c_6;
230
231 x_total := x_total + ceil(x_ORDER_SOURCE_FK + 1);
232
233 OPEN c_7;
234 FETCH c_7 INTO x_ORDER_TYPE_FK;
235 CLOSE c_7;
236
237 x_total := x_total + ceil(x_ORDER_TYPE_FK + 1);
238
239 OPEN c_8;
240 FETCH c_8 INTO x_GL_BOOK_FK, x_set_of_books_id;
241 CLOSE c_8;
242
243 x_total := x_total + ceil(x_GL_BOOK_FK + 1);
244
245 OPEN c_9;
246 FETCH c_9 INTO x_mtl_inv, x_mtl_org_id;
247 CLOSE c_9;
248
249 x_BACKLOGS_PK := x_l_inv + x_INV_ORG_FK + x_mtl_inv + x_mtl_org_id +
250 x_OPERATING_UNIT_FK + x_CUSTOMER_FK + x_SALES_CHANNEL_FK +
251 x_h_salesrep_id + x_TASK_FK + x_INV_ORG_FK +
252 x_BILL_TO_CUST_FK + x_SHIP_TO_CUST_FK + x_DEMAND_CLASS_FK +
253 x_TRX_CURRENCY_FK + x_cate_code + x_ORDER_TYPE_FK +
254 x_ORDER_SOURCE_FK + x_set_of_books_id;
255
256 x_total := x_total + ceil(x_BACKLOGS_PK + 1);
257
258 OPEN c_10;
259 FETCH c_10 INTO x_BILL_BKLG_REV_T;
260 CLOSE c_10;
261
262 x_total := x_total + 28 * ceil(x_BILL_BKLG_REV_T + 1);
263
264 p_avg_row_len := x_total;
265
266 Exception When others then
267 rollback;
268
269 END;
270
271 END ISC_EDW_BACKLOGS_F_SIZE;