[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_CONTRACT_F_SIZE
Source
1 PACKAGE BODY POA_EDW_CONTRACT_F_SIZE AS
2 /*$Header: poaszctb.pls 120.0 2005/06/02 02:46:34 appldev noship $ */
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 count(*) into p_num_rows
13 from po_headers_all
14 WHERE type_lookup_code in ('CONTRACT', 'BLANKET')
15 and approved_flag = 'Y'
16 and last_update_date between p_from_date and p_to_date;
17
18 -- dbms_output.put_line('The number of rows for contract is: '
19 -- || to_char(p_num_rows));
20
21 EXCEPTION
22 WHEN OTHERS THEN p_num_rows := 0;
23 END;
24
25 ---------------------------------------------------------------
26
27 PROCEDURE est_row_len (p_from_date IN DATE,
28 p_to_date IN DATE,
29 p_avg_row_len OUT NOCOPY NUMBER) IS
30
31 x_date number := 7;
32 x_total number := 0;
33 x_constant number := 6;
34 x_float number := 11;
35 x_int number := 6;
36
37 x_SIC_CODE_FK NUMBER;
38 x_DUNS_FK NUMBER;
39 x_SUPPLIER_SITE_FK NUMBER;
40 x_TXN_CUR_DATE_FK NUMBER;
41 x_PRINTED_DATE_FK NUMBER;
42 x_END_DATE_FK NUMBER;
43 x_START_DATE_FK NUMBER;
44 x_ACCPT_DUE_DATE_FK NUMBER;
45 x_REVISED_DATE_FK NUMBER;
46 x_APPROVED_DATE_FK NUMBER;
47 x_CREATION_DATE_FK NUMBER;
48 x_APPROVER_FK NUMBER;
49 x_BUYER_FK NUMBER;
50 x_AP_TERMS_FK NUMBER;
51 x_OPERATING_UNIT_FK NUMBER;
52 x_USER_HOLD_FK NUMBER;
53 x_CANCELLED_FK NUMBER;
54 x_FROZEN_FK NUMBER;
55 x_ACCPT_REQUIRED_FK NUMBER;
56 x_FREIGHT_TERMS_FK NUMBER;
57 x_FOB_FK NUMBER;
58 x_SHIP_VIA_FK NUMBER;
59 x_PO_TYPE_FK NUMBER;
60 x_CONTRACT_EFFECTIVE_FK NUMBER;
61 x_EDI_PROCESSED_FK NUMBER;
62 x_APPROVED_FK NUMBER;
63 x_CONFIRM_ORDER_FK NUMBER;
64 x_CLOSED_FK NUMBER;
65 x_INSTANCE_FK NUMBER;
66 x_SHIP_LOCATION_FK NUMBER;
67 x_BILL_LOCATION_FK NUMBER;
68 x_SUP_SITE_GEOG_FK NUMBER;
69 x_TXN_CUR_CODE_FK NUMBER;
70 x_TXN_CUR_RATE NUMBER;
71 x_REVISION_NUM NUMBER;
72 x_PO_HEADER_ID NUMBER;
73 x_NUM_DAYS_CREATE_TO_APP NUMBER;
74 x_NUM_DAYS_APP_TO_SEND NUMBER;
75 x_NUM_DAYS_APP_SEND_TO_ACCPT NUMBER;
76 x_AMT_RELEASED_T NUMBER;
77 x_AMT_RELEASED_G NUMBER;
78 x_AMT_MIN_RELEASE_T NUMBER;
79 x_AMT_MIN_RELEASE_G NUMBER;
80 x_AMT_LIMIT_T NUMBER;
81 x_AMT_LIMIT_G NUMBER;
82 x_AMT_AGREED_T NUMBER;
83 x_AMT_AGREED_G NUMBER;
84 x_CONTRACT_NUM NUMBER;
85 x_TXN_CUR_RATE_TYPE NUMBER;
86 x_SUPPLIER_NOTE NUMBER;
87 x_RECEIVER_NOTE NUMBER;
88 x_CONTRACT_PK NUMBER;
89 x_COMMENTS NUMBER;
90 x_LAST_UPDATE_DATE NUMBER;
91 x_CREATION_DATE NUMBER;
92
93 -------------------------------------------------------------
94
95 CURSOR c_1 IS
96 SELECT avg(nvl(vsize(agent_id), 0)),
97 avg(nvl(vsize(po_header_id), 0)),
98 avg(nvl(vsize(vendor_site_id), 0)),
99 avg(nvl(vsize(org_id), 0)),
100 avg(nvl(vsize(terms_id), 0)),
101 avg(nvl(vsize(closed_code), 0)),
102 avg(nvl(vsize(type_lookup_code), 0)),
103 avg(nvl(vsize(ship_via_lookup_code), 0)),
104 avg(nvl(vsize(fob_lookup_code), 0)),
105 avg(nvl(vsize(freight_terms_lookup_code), 0)),
106 avg(nvl(vsize(acceptance_required_flag), 0)),
107 avg(nvl(vsize(frozen_flag), 0)),
108 avg(nvl(vsize(bill_to_location_id), 0)),
109 avg(nvl(vsize(ship_to_location_id), 0)),
110 avg(nvl(vsize(vendor_site_id), 0)),
111 avg(nvl(vsize(currency_code), 0)),
112 avg(nvl(vsize(comments), 0)),
113 avg(nvl(vsize(note_to_receiver), 0)),
114 avg(nvl(vsize(note_to_vendor), 0)),
115 avg(nvl(vsize(approved_flag), 0)),
116 avg(nvl(vsize(user_hold_flag), 0)),
117 avg(nvl(vsize(revision_num), 0)),
118 avg(nvl(vsize(segment1), 0)),
119 avg(nvl(vsize(rate), 0)),
120 avg(nvl(vsize(confirming_order_flag), 0)),
121 avg(nvl(vsize(edi_processed_flag), 0))
122 from PO_HEADERS_ALL
123 where last_update_date between
124 p_from_date and p_to_date;
125
126 --------
127
128 CURSOR c_2 IS
129 SELECT avg(nvl(vsize(currency_code), 0))
130 from gl_sets_of_books
131 where last_update_date between
132 p_from_date and p_to_date;
133
134 BEGIN
135
136 -- dbms_output.enable(100000);
137
138 -- all date FKs
139
140 x_ACCPT_DUE_DATE_FK := x_date;
141 x_APPROVED_DATE_FK := x_date;
142 x_END_DATE_FK := x_date;
143 x_CREATION_DATE_FK := x_date;
144 x_PRINTED_DATE_FK := x_date;
145 x_REVISED_DATE_FK := x_date;
146 x_START_DATE_FK := x_date;
147 x_TXN_CUR_DATE_FK := x_date;
148
149 x_total := 3 + x_total
150 + ceil (x_ACCPT_DUE_DATE_FK + 1)
151 + ceil (x_APPROVED_DATE_FK + 1)
152 + ceil (x_END_DATE_FK + 1)
153 + ceil (x_CREATION_DATE_FK + 1)
154 + ceil (x_PRINTED_DATE_FK + 1)
155 + ceil (x_REVISED_DATE_FK + 1)
156 + ceil (x_START_DATE_FK + 1)
157 + ceil (x_TXN_CUR_DATE_FK + 1);
158
159 -- all calculated numbers
160
161 x_amt_released_t := x_float;
162 x_amt_released_g := x_float;
163 x_amt_agreed_t := x_float;
164 x_amt_agreed_g := x_float;
165 x_amt_limit_t := x_float;
166 x_amt_limit_g := x_float;
167 x_amt_min_release_t := x_float;
168 x_amt_min_release_g := x_float;
169 x_amt_released_t := x_float;
170 x_amt_released_g := x_float;
171
172 x_num_days_create_to_app := x_float;
173 x_num_days_app_to_send := x_float;
174 x_num_days_app_send_to_accpt := x_float;
175
176 x_total := x_total
177 + ceil(x_amt_released_t + 1)
178 + ceil(x_amt_released_g + 1)
179 + ceil(x_amt_agreed_t + 1)
180 + ceil(x_amt_agreed_g + 1)
181 + ceil(x_amt_limit_t + 1)
182 + ceil(x_amt_limit_g + 1)
183 + ceil(x_amt_min_release_t + 1)
184 + ceil(x_amt_min_release_g + 1)
185 + ceil(x_amt_released_t + 1)
186 + ceil(x_amt_released_g + 1)
187 + ceil(x_num_days_create_to_app + 1)
188 + ceil(x_num_days_app_to_send + 1)
189 + ceil(x_num_days_app_send_to_accpt + 1);
190
191 -----------------------------------------------------
192
193
194 OPEN c_1;
195 FETCH c_1 INTO x_buyer_fk, x_PO_HEADER_ID, x_supplier_site_fk,
196 x_operating_unit_fk, x_ap_terms_fk, x_closed_fk,
197 x_po_type_fk, x_ship_via_fk, x_fob_fk, x_freight_terms_fk,
198 x_accpt_required_fk, x_frozen_fk, x_bill_location_fk,
199 x_ship_location_fk, x_sup_site_geog_fk, x_txn_cur_code_fk,
200 x_comments, x_receiver_note,
201 x_supplier_note, x_approved_fk, x_user_hold_fk,
202 x_revision_num, x_contract_num, x_txn_cur_rate,
203 x_confirm_order_fk, x_edi_processed_fk;
204 CLOSE c_1;
205
206 x_approver_fk := x_buyer_fk;
207 x_contract_pk := x_PO_HEADER_ID;
208 x_supplier_site_fk := x_supplier_site_fk + x_operating_unit_fk;
209 x_sup_site_geog_fk := x_sup_site_geog_fk + x_operating_unit_fk;
210
211
212 x_total := x_total
213 + NVL (ceil(x_buyer_fk + 1), 0)
214 + NVL (ceil(x_approver_fk + 1), 0)
215 + NVL (ceil(x_contract_pk + 1), 0)
216 + NVL (ceil(x_PO_HEADER_ID + 1), 0)
217 + NVL (ceil(x_supplier_site_fk + 1), 0)
218 + NVL (ceil(x_operating_unit_fk + 1), 0)
219 + NVL (ceil(x_ap_terms_fk + 1), 0)
220 + NVL (ceil(x_closed_fk + 1), 0)
221 + NVL (ceil(x_po_type_fk + 1), 0)
222 + NVL (ceil(x_ship_via_fk + 1), 0)
223 + NVL (ceil(x_fob_fk + 1), 0)
224 + NVL (ceil(x_freight_terms_fk + 1), 0)
225 + NVL (ceil(x_accpt_required_fk + 1), 0)
226 + NVL (ceil(x_frozen_fk + 1), 0)
227 + NVL (ceil(x_bill_location_fk + 1), 0)
231 + NVL (ceil(x_comments + 1), 0)
228 + NVL (ceil(x_ship_location_fk + 1), 0)
229 + NVL (ceil(x_sup_site_geog_fk + 1), 0)
230 + NVL (ceil(x_txn_cur_code_fk + 1), 0)
232 + NVL (ceil(x_receiver_note + 1), 0)
233 + NVL (ceil(x_supplier_note + 1), 0)
234 + NVL (ceil(x_approved_fk + 1), 0)
235 + NVL (ceil(x_user_hold_fk + 1), 0)
236 + NVL (ceil(x_confirm_order_fk + 1), 0)
237 + NVL (ceil(x_revision_num + 1), 0)
238 + NVL (ceil(x_contract_num + 1), 0)
239 + NVL (ceil(x_txn_cur_rate + 1), 0)
240 + NVL (ceil(x_edi_processed_fk + 1), 0);
241
242
243 --------------------------------------------------------------------
244
245 OPEN c_2;
246 FETCH c_2 INTO x_txn_cur_code_fk;
247 CLOSE c_2;
248
249 x_total := x_total + NVL (ceil(x_txn_cur_code_fk + 1), 0);
250
251 ------------------------------------------------------------------
252
253 -- dbms_output.put_line(' ');
254 -- dbms_output.put_line('The average row length for contract is: '
255 -- || to_char(x_total));
256
257 p_avg_row_len := x_total;
258
259 EXCEPTION
260 WHEN OTHERS THEN p_avg_row_len := 0;
261 END; -- procedure est_row_len.
262
263 END;