[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_UOM_CONV_F_C
Source
1 Package Body OPI_EDW_UOM_CONV_F_C AS
2 /* $Header: OPIUOMCB.pls 120.1 2005/06/07 02:54:59 appldev $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6 g_exception_msg varchar2(2000):=Null;
7 Procedure Push(Errbuf out NOCOPY Varchar2,
8 Retcode out NOCOPY Varchar2,
9 p_from_date IN Varchar2,
10 p_to_date IN Varchar2) IS
11 l_fact_name Varchar2(30) :='OPI_EDW_UOM_CONV_FSTG' ;
12 l_date1 Date:=Null;
13 l_date2 Date:=Null;
14 l_temp_date Date:=Null;
15 l_temp_date_char Varchar2(2000) := Null ;
16 l_rows_inserted Number:=0;
17 l_duration Number:=0;
18 l_exception_msg Varchar2(2000):=Null;
19
20 -- -------------------------------------------
21 -- Put any additional developer variables here
22 -- -------------------------------------------
23 Begin
24 Errbuf :=NULL;
25 Retcode:=0;
26 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name)) THEN
27 errbuf := fnd_message.get;
28 Return;
29 END IF;
30 /*
31 g_push_date_range1 := nvl(p_from_date,
32 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
33 g_push_date_range2 := nvl(p_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
34 */
35 IF (p_from_date IS NULL) THEN
36 OPI_EDW_UOM_CONV_F_C.g_push_date_range1 := EDW_COLLECTION_UTIL.G_local_last_push_start_date -
37 EDW_COLLECTION_UTIL.g_offset;
38 ELSE
39 OPI_EDW_UOM_CONV_F_C.g_push_date_range1 := to_date(p_from_date,
40 'YYYY/MM/DD HH24:MI:SS');
41 END IF;
42
43 IF (p_to_date IS NULL) THEN
44 OPI_EDW_UOM_CONV_F_C.g_push_date_range2 :=
45 EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
46 ELSE
47 IF to_char(to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD') =
48 to_char(sysdate,'YYYY/MM/DD') THEN
49 OPI_EDW_UOM_CONV_F_C.g_push_date_range2 := to_date(to_char(sysdate,
50 'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS');
51 ELSE
52 l_temp_date_char := to_char(to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS'),
53 'YYYY/MM/DD');
54 OPI_EDW_UOM_CONV_F_C.g_push_date_range2 := to_date(l_temp_date_char||
55 ' 23:59:59', 'YYYY/MM/DD HH24:MI:SS');
56 END IF;
57 END IF ;
58
59
60
61 l_date1 := g_push_date_range1;
62 l_date2 := g_push_date_range2;
63 edw_log.put_line( 'The collection range is from '||
64 to_char(l_date1,'MM/DD/YYYY HH24:MI:SS')||' to '||
65 to_char(l_date2,'MM/DD/YYYY HH24:MI:SS'));
66 edw_log.put_line(' ');
67
68 -- -----------------------------------------------------------------------------
69 -- Start of Collection , Developer Customizable Section
70 -- -----------------------------------------------------------------------------
71
72 edw_log.put_line(' ');
73 edw_log.put_line('Pushing data');
74
75 l_temp_date := sysdate;
76 Insert Into OPI_EDW_UOM_CONV_FSTG@EDW_APPS_TO_WH(
77 BASE_UOM,
78 CONVERSION_RATE,
79 EDW_BASE_UOM_FK,
80 EDW_CONVERSION_RATE,
81 EDW_UOM_FK,
82 INSTANCE_FK,
83 INVENTORY_ITEM_ID,
84 UOM,
85 UOM_CONV_PK,
86 USER_ATTRIBUTE1,
87 USER_ATTRIBUTE10,
88 USER_ATTRIBUTE11,
89 USER_ATTRIBUTE12,
90 USER_ATTRIBUTE13,
91 USER_ATTRIBUTE14,
92 USER_ATTRIBUTE15,
93 USER_ATTRIBUTE2,
94 USER_ATTRIBUTE3,
95 USER_ATTRIBUTE4,
96 USER_ATTRIBUTE5,
97 USER_ATTRIBUTE6,
98 USER_ATTRIBUTE7,
99 USER_ATTRIBUTE8,
100 USER_ATTRIBUTE9,
101 USER_FK1,
102 USER_FK2,
103 USER_FK3,
104 USER_FK4,
105 USER_FK5,
106 USER_MEASURE1,
107 USER_MEASURE2,
108 USER_MEASURE3,
109 USER_MEASURE4,
110 USER_MEASURE5,
111 OPERATION_CODE,
112 COLLECTION_STATUS,
113 CLASS_CONVERSION_FLAG)
114 select
115 BASE_UOM,
116 CONVERSION_RATE,
117 NVL(EDW_BASE_UOM_FK,'NA_EDW'),
118 EDW_CONVERSION_RATE,
119 NVL(EDW_UOM_FK,'NA_EDW'),
120 NVL(INSTANCE_FK,'NA_EDW'),
121 INVENTORY_ITEM_ID,
122 UOM,
123 UOM_CONV_PK,
124 USER_ATTRIBUTE1,
125 USER_ATTRIBUTE10,
126 USER_ATTRIBUTE11,
127 USER_ATTRIBUTE12,
128 USER_ATTRIBUTE13,
129 USER_ATTRIBUTE14,
130 USER_ATTRIBUTE15,
131 USER_ATTRIBUTE2,
132 USER_ATTRIBUTE3,
133 USER_ATTRIBUTE4,
134 USER_ATTRIBUTE5,
135 USER_ATTRIBUTE6,
136 USER_ATTRIBUTE7,
137 USER_ATTRIBUTE8,
138 USER_ATTRIBUTE9,
139 NVL(USER_FK1,'NA_EDW'),
140 NVL(USER_FK2,'NA_EDW'),
141 NVL(USER_FK3,'NA_EDW'),
142 NVL(USER_FK4,'NA_EDW'),
143 NVL(USER_FK5,'NA_EDW'),
144 USER_MEASURE1,
145 USER_MEASURE2,
146 USER_MEASURE3,
147 USER_MEASURE4,
148 USER_MEASURE5,
149 NULL, -- OPERATION_CODE
150 'NOT-COLLECTIBLE',
151 CLASS_CONVERSION_FLAG
152 from OPI_EDW_UOM_CONV_FCV@apps_to_apps
153 where last_update_date between l_date1 and l_date2;
154 l_rows_inserted := sql%rowcount;
155 l_duration := sysdate - l_temp_date;
156
157 edw_log.put_line('Inserted'||to_char(nvl(sql%rowcount,0))||
158 ' rows into the staging table');
159 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
160 edw_log.put_line(' ');
161
162 -- ---------------------------------------------------------------------------
163 -- END OF Collection , Developer Customizable Section
164 -- ---------------------------------------------------------------------------
165 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, l_exception_msg,
166 OPI_EDW_UOM_CONV_F_C.g_push_date_range1,
167 OPI_EDW_UOM_CONV_F_C.g_push_date_range2);
168
169 Exception When others then
170 Errbuf:=sqlerrm;
171 Retcode:=sqlcode;
172 l_exception_msg := Retcode || ':' || Errbuf;
173 rollback;
174 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
175 OPI_EDW_UOM_CONV_F_C.g_push_date_range1,
176 OPI_EDW_UOM_CONV_F_C.g_push_date_range2);
177 raise;
178
179 End;
180 End OPI_EDW_UOM_CONV_F_C;