[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_TRD_PNTR_M_SIZE
Source
1 PACKAGE BODY POA_EDW_TRD_PNTR_M_SIZE AS
2 /*$Header: poasztpb.pls 120.0.12010000.2 2008/08/04 08:43:53 rramasam ship $ */
3
4 PROCEDURE cnt_rows (p_from_date IN DATE,
5 p_to_date IN DATE,
6 p_num_rows OUT NOCOPY NUMBER) IS
7
8 BEGIN
9
10 -- dbms_output.enable(100000);
11
12 select sum(cnt) into p_num_rows
13 from (
14 select count(*) cnt
15 from
16 po_vendors vnd,
17 po_vendor_sites_all vns
18 WHERE vns.vendor_id = vnd.vendor_id
19 and greatest(vns.last_update_date, vnd.last_update_date)
20 between p_from_date and p_to_date
21 union all
22 select count(*) cnt
23 from
24 HZ_PARTIES parties,
25 HZ_CUST_ACCOUNTS hzca,
26 HZ_CUST_ACCT_SITES_ALL hcas,
27 HZ_CUST_SITE_USES_ALL hcss
28 WHERE hcss.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID
29 AND hcas.CUST_ACCOUNT_ID = hzca.CUST_ACCOUNT_ID
30 AND hzca.party_id = parties.party_id
31 and greatest(hcss.last_update_date, hzca.last_update_date,
32 hcas.last_update_date, parties.last_update_date)
33 between p_from_date and p_to_date
34 union all
35 select count(*) cnt
36 from
37 po_vendors
38 where last_update_date between p_from_date and p_to_date
39 union all
40 select count(*) cnt
41 FROM hz_cust_accounts hzca,
42 hz_parties parties
43 WHERE hzca.party_id = parties.party_id
44 and greatest(parties.last_update_date, hzca.last_update_date)
45 between p_from_date and p_to_date
46 union all
47 select count(*) cnt
48 from hz_parties
49 where last_update_date between p_from_date and p_to_date);
50
51 -- dbms_output.put_line('The number of rows for trading partner is: '
52 -- || to_char(p_num_rows));
53
54 EXCEPTION
55 WHEN OTHERS THEN p_num_rows := 0;
56 END;
57
58 -------------------------------------------------------
59
60 PROCEDURE est_row_len (p_from_date IN DATE,
61 p_to_date IN DATE,
62 p_avg_row_len OUT NOCOPY NUMBER) IS
63
64 x_total1 number := 0;
65 x_total2 number := 0;
66 x_total3 number := 0;
67 x_total4 number := 0;
68 x_total5 number := 0;
69 x_total6 number := 0;
70 x_total number := 0;
71 x_date number := 7;
72
73 -- Definition for edw_tprt_p1_tpartner_lstg;
74 x_TPARTNER_PK NUMBER := 0;
75 x_PARENT_TPARTNER_FK NUMBER := 0;
76 x_LAST_UPDATE_DATE NUMBER := 0;
77 x_CREATION_DATE NUMBER := 0;
78 x_TPARTNER_DP NUMBER := 0;
79 x_NAME NUMBER := 0;
80 x_ALTERNATE_NAME NUMBER := 0;
81 x_START_ACTIVE_DATE NUMBER := 0;
82 x_END_ACTIVE_DATE NUMBER := 0;
83 x_SIC_CODE NUMBER := 0;
84 x_TAX_REG_NUM NUMBER := 0;
85 x_TAXPAYER_ID NUMBER := 0;
86 x_PAYMENT_TERMS NUMBER := 0;
87 x_VENDOR_NUMBER NUMBER := 0;
88 x_VENDOR_TYPE NUMBER := 0;
89 x_ONE_TIME_FLAG NUMBER := 0;
90 x_MINORITY_GROUP NUMBER := 0;
91 x_WOMEN_OWNED NUMBER := 0;
92 x_SMALL_BUSINESS NUMBER := 0;
93 x_HOLD_FLAG NUMBER := 0;
94 x_INSPECT_REQUIRED NUMBER := 0;
95 x_RECEIPT_REQUIRED NUMBER := 0;
96 x_ALLOW_SUB_RECEIPT NUMBER := 0;
97 x_ALLOW_UNORDER_RCV NUMBER := 0;
98 x_INSTANCE NUMBER := 0;
99 x_VENDOR_ID NUMBER := 0;
100
101 x_TRADE_PARTNER_PK NUMBER := 0;
102 x_TRADE_PARTNER_DP NUMBER := 0;
103 x_VNDR_NUMBER NUMBER := 0;
104 x_VNDR_TYPE NUMBER := 0;
105 x_VNDR_ONE_TIME NUMBER := 0;
106 x_VNDR_MINORITY_GRP NUMBER := 0;
107 x_VNDR_WOMEN_OWNED NUMBER := 0;
108 x_VNDR_SMALL_BUS NUMBER := 0;
109 x_VNDR_HOLD_FLAG NUMBER := 0;
110 x_VNDR_INSPECT_REQ NUMBER := 0;
111 x_VNDR_RECEIPT_REQ NUMBER := 0;
112 x_VNDR_SUB_RECEIPT NUMBER := 0;
113 x_VNDR_UNORDER_RCV NUMBER := 0;
114 x_CUST_NUMBER NUMBER := 0;
115 x_CUST_ORIG_SYS_REF NUMBER := 0;
116 x_CUST_STATUS NUMBER := 0;
117 x_CUST_TYPE NUMBER := 0;
118 x_CUST_PROSPECT NUMBER := 0;
119 x_CUST_CLASS NUMBER := 0;
120 x_CUST_SALES_REP NUMBER := 0;
121 x_CUST_SALES_CHNL NUMBER := 0;
122 x_CUST_ORDER_TYPE NUMBER := 0;
123 x_CUST_PRICE_LIST NUMBER := 0;
124 x_CUST_ANALYSIS_FY NUMBER := 0;
125 x_CUST_CAT_CODE NUMBER := 0;
126 x_CUST_KEY NUMBER := 0;
127 x_CUST_FISCAL_END NUMBER := 0;
128 x_CUST_NUM_EMP NUMBER := 0;
129 x_CUST_REVENUE_CURR NUMBER := 0;
130 x_CUST_REVENUE_NEXT NUMBER := 0;
131 x_CUST_REF_USE_FLAG NUMBER := 0;
132 x_CUST_TAX_CODE NUMBER := 0;
133 x_CUST_THIRD_PARTY NUMBER := 0;
134 x_CUST_ACCESS_TMPL NUMBER := 0;
135 x_CUST_COMPETITOR NUMBER := 0;
136 x_CUST_ORIG_SYS NUMBER := 0;
137 x_CUST_YEAR_EST NUMBER := 0;
138 x_CUST_COTERM_DATE NUMBER := 0;
139 x_CUST_FOB_POINT NUMBER := 0;
140 x_CUST_FREIGHT NUMBER := 0;
141 x_CUST_GSA_IND NUMBER := 0;
142 x_CUST_SHIP_PARTIAL NUMBER := 0;
143 x_CUST_SHIP_VIA NUMBER := 0;
144 x_CUST_DO_NOT_MAIL NUMBER := 0;
145 x_CUST_TAX_HDR_FLAG NUMBER := 0;
146 x_CUST_TAX_ROUND NUMBER := 0;
147 x_USER_ATTRIBUTE1 NUMBER := 0;
148 x_USER_ATTRIBUTE2 NUMBER := 0;
149 x_USER_ATTRIBUTE3 NUMBER := 0;
150 x_USER_ATTRIBUTE4 NUMBER := 0;
151 x_USER_ATTRIBUTE5 NUMBER := 0;
152 x_CUSTOMER_ID NUMBER := 0;
153
154 x_TPARTNER_LOC_PK NUMBER := 0;
155 x_TRADE_PARTNER_FK NUMBER := 0;
156 x_ADDRESS_LINE1 NUMBER := 0;
157 x_ADDRESS_LINE2 NUMBER := 0;
158 x_ADDRESS_LINE3 NUMBER := 0;
159 x_ADDRESS_LINE4 NUMBER := 0;
160 x_CITY NUMBER := 0;
161 x_COUNTY NUMBER := 0;
162 x_STATE NUMBER := 0;
163 x_POSTAL_CODE NUMBER := 0;
164 x_PROVINCE NUMBER := 0;
165 x_COUNTRY NUMBER := 0;
166 x_BUSINESS_TYPE NUMBER := 0;
167 x_TPARTNER_LOC_DP NUMBER := 0;
168 x_DATE_FROM NUMBER := 0;
169 x_DATE_TO NUMBER := 0;
170 x_VNDR_PURCH_SITE NUMBER := 0;
171 x_VNDR_RFQ_ONLY NUMBER := 0;
172 x_VNDR_PAY_SITE NUMBER := 0;
173 x_VNDR_PAY_TERMS NUMBER := 0;
174 x_CUST_SITE_USE NUMBER := 0;
175 x_CUST_LOCATION NUMBER := 0;
176 x_CUST_PRIMARY_FLAG NUMBER := 0;
177 x_CUST_PAY_TERMS NUMBER := 0;
178 X_CUST_SIC_CODE NUMBER := 0;
179 x_CUST_TERRITORY NUMBER := 0;
180 x_CUST_TAX_REF NUMBER := 0;
181 x_CUST_SORT_PRTY NUMBER := 0;
182 x_CUST_DEMAND_CLASS NUMBER := 0;
183 x_CUST_TAX_CLASSFN NUMBER := 0;
184 x_TPARTNER_LOC_ID NUMBER := 0;
185 x_LEVEL_NAME NUMBER := 0;
186
187 cursor c1 is
188 select avg(nvl(vsize(vendor_id), 0)),
189 avg(nvl(vsize(parent_vendor_id), 0)),
190 avg(nvl(vsize(vendor_name), 0)),
191 avg(nvl(vsize(vendor_name_alt), 0)),
192 avg(nvl(vsize(standard_industry_class), 0)),
193 avg(nvl(vsize(vat_registration_num),0)),
194 avg(nvl(vsize(num_1099),0)),
195 avg(nvl(vsize(terms_id),0)),
196 avg(nvl(vsize(segment1),0)),
197 avg(nvl(vsize(vendor_type_lookup_code),0)),
198 avg(nvl(vsize(one_time_flag),0)),
199 avg(nvl(vsize(minority_group_lookup_code),0)),
200 avg(nvl(vsize(women_owned_flag),0)),
201 avg(nvl(vsize(small_business_flag),0)),
202 avg(nvl(vsize(hold_flag),0)),
203 avg(nvl(vsize(inspection_required_flag),0)),
204 avg(nvl(vsize(receipt_required_flag),0)),
205 avg(nvl(vsize(allow_substitute_receipts_flag),0)),
206 avg(nvl(vsize(allow_unordered_receipts_flag),0))
207 from po_vendors where last_update_date
208 between p_from_date and p_to_date;
209
210 cursor c2 is
211 select avg(nvl(vsize(instance_code),0))
212 from edw_local_instance;
213
214 cursor c3 is
215 select avg(nvl(vsize(cust_account_id),0)),
216 avg(nvl(vsize(account_number),0)),
217 avg(nvl(vsize(orig_system_reference),0)),
218 avg(nvl(vsize(status),0)),
219 avg(nvl(vsize(customer_type),0)),
220 avg(nvl(vsize(customer_class_code),0)),
221 avg(nvl(vsize(sales_channel_code),0)),
222 avg(nvl(vsize(tax_code),0)),
223 avg(nvl(vsize(coterminate_day_month),0)),
224 avg(nvl(vsize(fob_point),0)),
225 avg(nvl(vsize(freight_term),0)),
226 avg(nvl(vsize(ship_partial),0)),
227 avg(nvl(vsize(ship_via),0)),
228 avg(nvl(vsize(tax_header_level_flag),0)),
229 avg(nvl(vsize(tax_rounding_rule),0)),
230 avg(nvl(vsize(party_id),0))
231 from hz_cust_accounts where last_update_date
232 between p_from_date and p_to_date;
233
234 cursor c4 is
235 select avg(nvl(vsize(party_name),0)),
236 avg(nvl(vsize(organization_name_phonetic),0)),
237 avg(nvl(vsize(sic_code),0)),
238 avg(nvl(vsize(tax_reference),0)),
239 avg(nvl(vsize(jgzz_fiscal_code),0)),
240 avg(nvl(vsize(party_number),0)),
241 avg(nvl(vsize(party_type),0)),
242 avg(nvl(vsize(analysis_fy),0)),
243 avg(nvl(vsize(customer_key),0)),
244 avg(nvl(vsize(fiscal_yearend_month),0)),
245 avg(nvl(vsize(employees_total),0)),
246 avg(nvl(vsize(curr_fy_potential_revenue),0)),
247 avg(nvl(vsize(next_fy_potential_revenue),0)),
248 avg(nvl(vsize(year_established),0)),
249 avg(nvl(vsize(gsa_indicator_flag),0)),
250 avg(nvl(vsize(do_not_mail_flag),0))
251 from hz_parties;
252
253 cursor c5 is
254 select avg(nvl(vsize(name),0))
255 from ap_terms_tl;
256
257 cursor c6 is
258 select avg(nvl(vsize(name),0))
259 from oe_transaction_types_tl;
260
261 cursor c7 is
262 select avg(nvl(vsize(name),0))
263 from qp_list_headers_tl;
264
265
266 cursor c8 is
267 select avg(nvl(vsize(vendor_site_id), 0)),
268 avg(nvl(vsize(address_line1),0)),
269 avg(nvl(vsize(address_line2),0)),
270 avg(nvl(vsize(address_line3),0)),
271 avg(nvl(vsize(city),0)),
272 avg(nvl(vsize(county),0)),
273 avg(nvl(vsize(state),0)),
274 avg(nvl(vsize(zip),0)),
275 avg(nvl(vsize(province),0)),
276 avg(nvl(vsize(country),0)),
277 avg(nvl(vsize(vendor_site_code),0)),
278 avg(nvl(vsize(purchasing_site_flag),0)),
279 avg(nvl(vsize(rfq_only_site_flag),0)),
280 avg(nvl(vsize(pay_site_flag),0))
281 from po_vendor_sites_all where last_update_date
282 between p_from_date and p_to_date;
283
284 cursor c9 is
285 select avg(nvl(vsize(site_use_code),0)),
286 avg(nvl(vsize(location),0)),
287 avg(nvl(vsize(primary_flag),0)),
288 avg(nvl(vsize(status),0)),
289 avg(nvl(vsize(orig_system_reference),0)),
290 avg(nvl(vsize(sic_code),0)),
291 avg(nvl(vsize(gsa_indicator),0)),
292 avg(nvl(vsize(ship_partial),0)),
293 avg(nvl(vsize(ship_via),0)),
294 avg(nvl(vsize(fob_point),0)),
295 avg(nvl(vsize(freight_term),0)),
296 avg(nvl(vsize(tax_reference),0)),
297 avg(nvl(vsize(sort_priority),0)),
298 avg(nvl(vsize(tax_code),0)),
299 avg(nvl(vsize(demand_class_code),0)),
300 avg(nvl(vsize(tax_header_level_flag),0)),
301 avg(nvl(vsize(tax_rounding_rule),0))
302 from HZ_CUST_SITE_USES_ALL where last_update_date
303 between p_from_date and p_to_date;
304
305 cursor c10 is
306 select avg(nvl(vsize(name), 0))
307 from ra_territories where last_update_date
308 between p_from_date and p_to_date;
309
310 cursor c11 is
311 select avg(nvl(vsize(name), 0))
312 from ra_salesreps_all where last_update_date
313 between p_from_date and p_to_date;
314
315
316 BEGIN
317 -- dbms_output.enable(100000);
318
319 OPEN c1;
323 x_VENDOR_NUMBER, x_VENDOR_TYPE, x_ONE_TIME_FLAG, x_MINORITY_GROUP,
320 FETCH c1 into x_tpartner_pk, x_PARENT_TPARTNER_FK,
321 x_TPARTNER_DP, x_alternate_name,
322 x_SIC_CODE, x_TAX_REG_NUM, x_TAXPAYER_ID, x_PAYMENT_TERMS,
324 x_WOMEN_OWNED, x_SMALL_BUSINESS, x_HOLD_FLAG, x_INSPECT_REQUIRED,
325 x_RECEIPT_REQUIRED, x_ALLOW_SUB_RECEIPT, x_ALLOW_UNORDER_RCV;
326 CLOSE c1;
327
328 x_last_update_date := x_date;
329 x_creation_date := x_date;
330 x_NAME := x_TPARTNER_DP;
331 x_START_ACTIVE_DATE := x_date;
332 x_END_ACTIVE_DATE := x_date;
333 x_VENDOR_ID := x_tpartner_pk;
334
335 x_total1 := 3 + x_total1 + NVL (ceil(x_tpartner_pk + 1), 0) +
336 NVL (ceil(x_TPARTNER_DP + 1), 0) + NVL (ceil(x_alternate_name + 1), 0) +
337 NVL (ceil(x_TAXPAYER_ID + 1), 0) + NVL (ceil(x_PAYMENT_TERMS + 1), 0) +
338 NVL (ceil(x_VENDOR_NUMBER + 1), 0) + NVL (ceil(x_VENDOR_TYPE + 1), 0) +
339 NVL (ceil(x_ONE_TIME_FLAG + 1), 0) + NVL (ceil(x_VENDOR_ID + 1), 0) +
340 NVL (ceil(x_vndr_minority_grp + 1), 0) + NVL (ceil(x_vndr_women_owned + 1), 0) +
341 NVL (ceil(x_vndr_small_bus + 1), 0) + NVL (ceil(x_vndr_hold_flag + 1), 0) +
342 NVL (ceil(x_vndr_inspect_req + 1), 0) + NVL (ceil(x_vndr_receipt_req + 1), 0) +
343 NVL (ceil(x_vndr_sub_receipt + 1), 0) + NVL (ceil(x_vndr_unorder_rcv + 1), 0) +
344 NVL (ceil(x_PARENT_TPARTNER_FK + 1), 0) + (x_creation_date + 1) +
345 (x_last_update_date + 1) + NVL (ceil(x_NAME + 1), 0) +
346 (x_START_ACTIVE_DATE + 1) + (x_END_ACTIVE_DATE + 1);
347
348 OPEN c2;
349 FETCH c2 into x_instance;
350 CLOSE c2;
351
352 x_total1 := x_total1 + NVL (ceil(x_instance + 1), 0);
353
354 -- dbms_output.put_line(' ');
355 -- dbms_output.put_line('input_m from source tables for the following staging tables are: ');
356 -- dbms_output.put_line(' ');
357 -- dbms_output.put_line('for EDW_TPRT_P1_TPARTNER_LSTG : ' || to_char(x_total1));
358
359 x_total2 := x_total1;
360
361 -- dbms_output.put_line(' ');
362 -- dbms_output.put_line('for EDW_TPRT_P2_TPARTNER_LSTG : ' || to_char(x_total2));
363
364 x_total3 := x_total1;
365
366 -- dbms_output.put_line(' ');
367 -- dbms_output.put_line('for EDW_TPRT_P3_TPARTNER_LSTG : ' || to_char(x_total3));
368
369 x_total4 := x_total1 - NVL (ceil(x_PARENT_TPARTNER_FK),0);
370
371 -- dbms_output.put_line(' ');
372 -- dbms_output.put_line('for EDW_TPRT_P4_TPARTNER_LSTG : ' || to_char(x_total4));
373
374 ---------------- For TP ----------------------------
375
376 x_total5 := x_total1; -- vendors' part
377
378 OPEN c3;
379 FETCH c3 into x_trade_partner_pk, x_cust_number,
380 x_cust_orig_sys_ref, x_cust_status, x_cust_type, x_cust_class,
381 x_cust_sales_chnl, x_cust_tax_code,
382 x_cust_coterm_date, x_cust_fob_point, x_cust_freight,
383 x_cust_ship_partial, x_cust_ship_via, x_cust_tax_hdr_flag,
384 x_cust_tax_round, x_vendor_id;
385 CLOSE c3;
386
387 x_start_active_date := x_date;
388 x_end_active_date := x_date;
389 x_last_update_date := x_date;
390 x_creation_date := x_date;
391 x_customer_id := x_trade_partner_pk;
392
393 OPEN c4;
394 FETCH c4 into x_trade_partner_dp, x_alternate_name, x_sic_code,
395 x_tax_reg_num, x_taxpayer_id, x_vndr_number, x_vndr_type,
396 x_cust_analysis_fy, x_cust_key, x_cust_fiscal_end,
397 x_cust_num_emp, x_cust_revenue_curr, x_cust_revenue_next,
398 x_cust_year_est, x_cust_gsa_ind, x_cust_do_not_mail;
399 CLOSE c4;
400
401 x_cust_prospect := 8;
402 x_name := x_trade_partner_dp;
403
404 x_total5 := greatest(x_total5, 3 + NVL (ceil(x_PARENT_TPARTNER_FK + 1), 0) +
405 NVL (ceil(x_trade_partner_pk + 1), 0) +
406 (x_start_active_date + 1) + (x_end_active_date + 1) +
407 NVL (ceil(x_cust_number + 1), 0) +
408 NVL (ceil(x_cust_orig_sys_ref + 1), 0) + NVL (ceil(x_cust_status + 1), 0) +
409 NVL (ceil(x_cust_type + 1), 0) + NVL (ceil(x_cust_class + 1), 0) +
410 NVL (ceil(x_cust_sales_chnl + 1), 0) + NVL (ceil(x_cust_cat_code + 1), 0) +
411 NVL (ceil(x_cust_ref_use_flag + 1), 0) + NVL (ceil(x_cust_tax_code + 1), 0) +
412 NVL (ceil(x_cust_third_party + 1), 0) + NVL (ceil(x_cust_competitor + 1), 0) +
413 NVL (ceil(x_cust_coterm_date + 1), 0) + NVL (ceil(x_cust_fob_point + 1), 0) +
414 NVL (ceil(x_cust_freight + 1), 0) + NVL (ceil(x_cust_ship_partial + 1), 0) +
415 NVL (ceil(x_cust_ship_via + 1), 0) + NVL (ceil(x_cust_tax_hdr_flag + 1), 0) +
416 NVL (ceil(x_cust_tax_round + 1), 0) + (x_last_update_date + 1) +
417 (x_creation_date + 1) + NVL (ceil(x_customer_id + 1), 0)
418 + NVL (ceil(x_trade_partner_dp + 1), 0) +
419 NVL (ceil(x_alternate_name + 1), 0) +
420 NVL (ceil(x_sic_code + 1), 0) + NVL (ceil(x_tax_reg_num + 1), 0) +
421 NVL (ceil(x_taxpayer_id + 1), 0) +
422 NVL (ceil(x_cust_prospect + 1), 0) +
423 NVL (ceil(x_cust_analysis_fy + 1), 0) + NVL (ceil(x_cust_key + 1), 0) +
424 NVL (ceil(x_cust_fiscal_end + 1), 0) + NVL (ceil(x_cust_num_emp + 1), 0) +
425 NVL (ceil(x_cust_revenue_curr + 1), 0) + NVL (ceil(x_cust_revenue_next + 1), 0) +
426 NVL (ceil(x_cust_year_est + 1), 0) + NVL (ceil(x_cust_gsa_ind + 1), 0) +
427 NVL (ceil(x_cust_do_not_mail + 1), 0) + NVL (ceil(x_name + 1), 0));
428
429 OPEN c5;
430 FETCH c5 into x_payment_terms;
431 CLOSE c5;
435 OPEN c6;
432
433 x_total5 := x_total5 + NVL (ceil(x_payment_terms + 1), 0);
434
436 FETCH c6 into x_cust_order_type;
437 CLOSE c6;
438
439 x_total5 := x_total5 + NVL (ceil(x_cust_order_type + 1), 0);
440
441 OPEN c7;
442 FETCH c7 into x_cust_price_list;
443 CLOSE c7;
444
445 x_total5 := x_total5 + NVL (ceil(x_cust_price_list + 1), 0);
446
447 -- dbms_output.put_line('for EDW_TPRT_TRADE_PARTNER_LSTG : ' || to_char(x_total5));
448
449
450 ----------------- For TP_LOC -----------------
451
452 -- extra part for tp_loc from hz_customer_accounts, hz_parties, etc...
453
454 x_total6 := x_total6 + 3
455 + NVL (ceil(x_TPARTNER_DP + 1), 0)
456 + NVL (ceil(x_cust_ship_partial + 1), 0)
457 + NVL (ceil(x_cust_ship_via + 1), 0)
458 + NVL (ceil(x_cust_fob_point + 1), 0)
459 + NVL (ceil(x_cust_freight + 1), 0)
460 + NVL (ceil(x_cust_tax_hdr_flag + 1), 0)
461 + NVL (ceil(x_cust_tax_round + 1), 0)
462 + NVL (ceil(x_cust_tax_code + 1), 0)
463 + NVL (ceil(x_cust_gsa_ind + 1), 0)
464 + NVL (ceil(x_trade_partner_dp + 1), 0) * 2;
465
466
467 OPEN c8;
468 FETCH c8 into x_TPARTNER_LOC_PK, x_ADDRESS_LINE1, x_ADDRESS_LINE2,
469 x_ADDRESS_LINE3, x_CITY, x_COUNTY, x_STATE, x_POSTAL_CODE,
470 x_PROVINCE, x_COUNTRY, x_TPARTNER_LOC_DP, x_VNDR_PURCH_SITE,
471 x_VNDR_RFQ_ONLY, x_VNDR_PAY_SITE;
472 CLOSE c8;
473
474 x_TRADE_PARTNER_FK := x_tpartner_pk;
475 x_BUSINESS_TYPE := 11;
476 x_NAME := x_TPARTNER_LOC_DP;
477 x_DATE_FROM := x_date;
478 x_DATE_TO := x_date;
479 x_VNDR_PAY_TERMS := x_payment_terms;
480 x_TPARTNER_LOC_ID := x_TPARTNER_LOC_PK;
481 x_LAST_UPDATE_DATE := x_date;
482 x_CREATION_DATE := x_date;
483 x_LEVEL_NAME := 8;
484
485 x_total6 := greatest(x_total6, 3 + NVL (ceil(x_TPARTNER_LOC_PK + 1), 0) +
486 NVL (ceil(x_ADDRESS_LINE1 + 1), 0) + NVL (ceil(x_ADDRESS_LINE2 + 1), 0) +
487 NVL (ceil(x_ADDRESS_LINE3 + 1), 0) + NVL (ceil(x_CITY + 1), 0) +
488 NVL (ceil(x_COUNTY + 1), 0) + NVL (ceil(x_STATE + 1), 0) +
489 NVL (ceil(x_POSTAL_CODE + 1), 0) + NVL (ceil(x_PROVINCE + 1), 0) +
490 NVL (ceil(x_COUNTRY + 1), 0) + NVL (ceil(x_TPARTNER_LOC_DP + 1), 0) +
491 NVL (ceil(x_VNDR_PURCH_SITE + 1), 0) + NVL (ceil(x_VNDR_RFQ_ONLY + 1), 0) +
492 NVL (ceil(x_VNDR_PAY_SITE + 1), 0) + NVL (ceil(x_TRADE_PARTNER_FK + 1), 0) +
493 NVL (ceil(x_BUSINESS_TYPE + 1), 0) + NVL (ceil(x_NAME + 1), 0) +
494 NVL (ceil(x_DATE_TO + 1), 0) + NVL (ceil(x_VNDR_PAY_TERMS + 1), 0) +
495 NVL (ceil(x_TPARTNER_LOC_ID + 1), 0) + NVL (ceil(x_DATE_FROM + 1), 0) +
496 NVL (ceil(x_LAST_UPDATE_DATE + 1), 0) + NVL (ceil(x_CREATION_DATE + 1), 0) +
497 NVL (ceil(x_LEVEL_NAME + 1), 0));
498
499 open c9;
500 fetch c9 into x_CUST_SITE_USE, x_CUST_LOCATION, x_CUST_PRIMARY_FLAG,
501 x_cust_status, x_cust_orig_sys_ref, x_cust_sic_code,
502 x_cust_gsa_ind, x_cust_ship_partial, x_cust_ship_via,
503 x_cust_fob_point, x_cust_freight, x_cust_tax_ref,
504 x_cust_sort_prty, x_cust_tax_code,
505 x_cust_demand_class, x_cust_tax_hdr_flag,
506 x_cust_tax_round;
507 close c9;
508
509 x_total6 := greatest(x_total6, 3
510 + NVL (ceil(x_CUST_SITE_USE + 1), 0)
511 + NVL (ceil(x_CUST_LOCATION + 1), 0)
512 + NVL (ceil(x_CUST_PRIMARY_FLAG + 1), 0)
513 + NVL (ceil(x_cust_status + 1), 0)
514 + NVL (ceil(x_cust_orig_sys_ref + 1), 0)
515 + NVL (ceil(x_cust_sic_code + 1), 0)
516 + NVL (ceil(x_cust_gsa_ind + 1), 0)
517 + NVL (ceil(x_cust_ship_partial + 1), 0)
518 + NVL (ceil(x_cust_ship_via + 1), 0)
519 + NVL (ceil(x_cust_fob_point + 1), 0)
520 + NVL (ceil(x_cust_freight + 1), 0)
521 + NVL (ceil(x_cust_tax_ref + 1), 0)
522 + NVL (ceil(x_cust_sort_prty + 1), 0)
523 + NVL (ceil(x_cust_tax_code + 1), 0)
524 + NVL (ceil(x_cust_demand_class + 1), 0)
525 + NVL (ceil(x_cust_tax_hdr_flag + 1), 0)
526 + NVL (ceil(x_cust_tax_round + 1), 0)
527 + NVL (ceil(x_cust_pay_terms + 1), 0)
528 + NVL (ceil(x_cust_order_type + 1), 0)
529 + NVL (ceil(x_cust_price_list + 1), 0));
530
531
532 open c10;
533 fetch c10 into x_cust_territory;
534 close c10;
535
536 open c11;
537 fetch c11 into x_cust_sales_rep;
538 close c11;
539
540 x_total6 := x_total6
541 + NVL (ceil(x_cust_territory + 1), 0)
542 + NVL (ceil(x_cust_sales_rep + 1), 0);
543
544 -- dbms_output.put_line('for EDW_TPRT_TPARTNER_LOC_LSTG : ' || to_char(x_total6));
545
546 ---------------------------------------------------------------
547
548 x_total := x_total1 + x_total2 + x_total3 + x_total4 + x_total5 + x_total6;
549
550 -- dbms_output.put_line('-------------------------------------');
551 -- dbms_output.put_line('(total) input_m for trade partner dimension is: ' || to_char(x_total));
552
553 p_avg_row_len := x_total;
554
555 EXCEPTION
556 WHEN OTHERS THEN p_avg_row_len := 0;
557
558 END; -- procedure est_row_len.
559
560 END;