[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_COGS_FOPM_SZ
Source
1 PACKAGE BODY OPI_EDW_COGS_FOPM_SZ AS
2 /* $Header: OPIPCGZB.pls 120.1 2005/06/16 03:53:24 appldev $*/
3
4 PROCEDURE cnt_rows(p_from_date DATE,
5 p_to_date DATE,
6 p_num_rows OUT NOCOPY NUMBER) IS
7
8 BEGIN
9
10 SELECT COUNT(*) INTO p_num_rows
11 FROM (
12 SELECT SH.order_id
13 FROM
14 OP_ORDR_HDR SH,
15 OP_ORDR_DTL SD,
16 SY_ORGN_MST OM,
17 GL_PLCY_MST PM
18 WHERE SH.order_id = sd.order_id
19 AND SH.orgn_code = OM.orgn_code
20 AND OM.co_CODE = PM.co_code
21 AND SD.LINE_STATUS >= 20
22 AND GREATEST(SH.LAST_UPDATE_DATE, SD.LAST_UPDATE_DATE,PM.LAST_UPDATE_DATE)
23 between p_from_date and p_to_date);
24
25 --dbms_output.put_line ('Number of rows : '||p_num_rows);
26 EXCEPTION
27 WHEN OTHERS THEN
28 p_num_rows := 0;
29 END; -- procedure cnt_rows.
30
31
32
33 PROCEDURE est_row_len(p_from_date DATE,
34 p_to_date DATE,
35 p_avg_row_len OUT NOCOPY NUMBER) IS
36 x_date number := 7;
37 x_total number := 0;
38 x_constant number := 6;
39
40 CURSOR c_trans_dtl IS
41 SELECT avg(nvl(vsize(itp.trans_id), 0)) trx_id,
42 avg(nvl(vsize(itp.trans_um), 0)) um,
43 avg(nvl(vsize(itp.line_id), 0)) line_id,
44 avg(nvl(vsize(itp.WHSE_CODE), 0)) WHSE_code,
45 avg(nvl(vsize(itp.LOCATION), 0)) location,
46 avg(nvl(vsize(itp.TRANS_QTY), 0)) qty,
47 avg(nvl(vsize(ilm.lot_no),0)) Lot_no,
48 avg(nvl(vsize(itp.item_id),0)) item_id
49 FROM IC_TRAN_PND ITP,IC_LOTS_MST ILM
50 WHERE ITP.last_update_date between p_from_date and p_to_date
51 AND ITP.LOT_ID = ILM.LOT_ID
52 AND ITP.ITEM_ID = ILM.ITEM_ID
53 AND ITP.COMPLETED_IND =1
54 AND ITP.DOC_TYPE in ('OPSO');
55
56 CURSOR c_order_dtl IS
57 SELECT avg(nvl(vsize(ooh.order_no), 0)) order_no,
58 avg(nvl(vsize(ood.line_no), 0)) line_no,
59 avg(nvl(vsize(ood.BILLING_CURRENCY), 0)) CURRENCY,
60 avg(nvl(vsize(OOD.SHIPCUST_ID), 0)) cust_id
61 FROM op_ORDR_hdr ooh,
62 op_ordr_Dtl ood
63 WHERE GREATEST(ooh.last_update_date,ood.last_update_date) between p_from_date and p_to_date;
64
65
66 CURSOR c_instance IS
67 SELECT
68 avg(nvl(vsize(instance_code), 0))
69 FROM EDW_LOCAL_INSTANCE ;
70
71 CURSOR c_other_dtl IS
72 SELECT AVG(Nvl(Vsize(EDW_TIME_PKG.CAL_DAY_FK(Sysdate, SOB_ID) ),0)) date_fk,
73 AVG(NVL(VSIZE(SOB_ID),0)) SOB_ID,
74 AVG(NVL(VSIZE(ORG_ID),0)) ORG_ID
75 FROM gl_plcy_mst;
76
77 x_instance_fk NUMBER;
78
79 trans_dtl_rec c_trans_dtl%ROWTYPE;
80 order_dtl_rec c_order_dtl%ROWTYPE;
81 other_dtl_rec c_other_dtl%ROWTYPE;
82 BEGIN
83 OPEN c_instance;
84 FETCH c_instance INTO x_instance_fk;
85 CLOSE c_instance;
86
87 OPEN c_trans_dtl;
88 FETCH c_trans_dtl INTO trans_dtl_rec;
89 CLOSE c_trans_dtl;
90
91 OPEN c_order_dtl;
92 FETCH c_order_dtl INTO order_dtl_rec;
93 CLOSE c_order_dtl;
94
95 OPEN c_other_dtl;
96 FETCH c_other_dtl INTO other_dtl_rec;
97 CLOSE c_other_dtl;
98
99
100 x_total := 3 + x_total
101 -- COGS_PK
102 + Ceil( trans_dtl_rec.trx_id + trans_dtl_rec.line_id + x_instance_fk + 10 + 1)
103 -- INSTANCE_FK
104 + Ceil( x_instance_fk + 1)
105 -- TOP_MODEL_ITEM_FK ITEM_ORG_FK
106 + 2 * Ceil( trans_dtl_rec.item_id + other_dtl_rec.org_id + x_instance_fk + 7 +1)
107 -- OPERATING_UNIT_FK INV_ORG_FK
108 + 2* Ceil( other_dtl_rec.org_id + x_instance_fk + 1)
109 -- CUSTOMER_FK
110 + Ceil(order_dtl_rec.cust_id + x_instance_fk+ 16+ 1)
111 + Ceil( 6 * 23 +1);
112 -- All NA_EDW FKs
113 -- PROJECT_FK SALESCHANNEL
114 -- TASK_FK SALESREP
115 -- CAMPAIGN_INIT_FK CAMPAIGN_ACTL_FK
116 -- MEDCHN_INIT_FK MEDCHN_ACTL_FK
117 -- OFFER_HDR_FK OFFER_LINE_FK
118 -- TARGET_SEGMENT_INIT_FK TARGET_SEGMENT_ACTL_FK
119 -- CAMPAIGN_STATUS_ACTL_FK CAMPAIGN_STATUS_INIT_FK
120 -- ORDER_CATEGORY_FK ORDER_TYPE_FK ORDER_SOURCE_FK
121 -- 5 USER FKs
122
123 x_total := x_total
124 -- BILL_TO_LOC_FK SHIP_TO_LOC_FK
125 + 2* Ceil(other_dtl_rec.org_id + x_instance_fk + 12 + 1)
126
127 -- BASE_UOM_FK
128 + Ceil( trans_dtl_rec.um + 1)
129 -- TRX_CURRENCY_FK BASE_CURRENCY_FK
130 + Ceil( order_dtl_rec.currency + 1)
131 -- BILL_TO_SITE_FK SHIP_TO_SITE_FK
132 + 2* Ceil(other_dtl_rec.org_id + x_instance_fk + 15 +1)
133 -- MONTH_BOOKED_FK DATE_BOOKED_FK DATE_PROMISED_FK
134 -- DATE_REQUESTED_FK DATE_SCHEDULED_FK DATE_SHIPPED_FK COGS_DATE_FK
135 + 7* Ceil( other_dtl_rec.date_fk + 1)
136 -- LOCATOR_FK SHIP_INV_LOCATOR_FK
137 + Ceil(trans_dtl_rec.WHSE_CODE * 2 + trans_dtl_rec.LOCATION+ 2 * x_instance_fk + 5+ 7 +1)
138 -- SET_OF_BOOKS_FK
139 + Ceil( 3 + x_instance_fk + 1);
140
141
142 x_total := x_total
143 -- ORDER_LINE_ID
144 + Ceil(trans_dtl_rec.line_id + x_instance_fk + 1 + 1)
145 -- COGS_DATE ORDER_DATE
146 + 2 * x_date
147 -- SHIPPED_QTY_B
148 + Ceil(trans_dtl_rec.qty + 1)
149 -- COGS_B COGS_G
150 + 2 * Ceil(trans_dtl_rec.qty + 3)
151 -- LAST_UPDATE_DATE,
152 + x_date
153 -- ORDER_NUMBER
154 + Ceil(order_dtl_rec.order_no + 1)
155 -- LOT
156 + Ceil(trans_dtl_rec.lot_no + 1)
157 -- SERIAL_NUMBER
158 + Ceil(order_dtl_rec.line_no + 1);
159
160 -- dbms_output.put_line('1 x_total is ' || x_total );
161
162 p_avg_row_len := x_total;
163
164
165 END; -- procedure est_row_len.
166
167 END; -- package body OPI_EDW_COGS_FOPM_SZ