[Home] [Help]
PACKAGE BODY: APPS.EDW_OPI_OPRN_M_C
Source
1 Package Body EDW_OPI_OPRN_M_C AS
2 /* $Header: OPIOPRDB.pls 120.1 2005/06/07 02:26:54 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 in out NOCOPY Varchar2,
8 Retcode in out NOCOPY Varchar2,
9 p_from_date IN VARCHAR2,
10 p_to_date IN VARCHAR2) IS
11 l_dimension_name Varchar2(30) :='EDW_OPI_OPRN_M' ;
12 l_temp_date Date:=Null;
13 l_rows_inserted Number:=0;
14 l_duration Number:=0;
15 l_exception_msg Varchar2(2000):=Null;
16 l_temp_date_char Varchar2(35);
17 -- -------------------------------------------
18 -- Put any additional developer variables here
19 -- -------------------------------------------
20 Begin
21 Errbuf :=NULL;
22 Retcode:=0;
23 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
24 errbuf := fnd_message.get;
25 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
26 Return;
27 END IF;
28 IF (p_from_date IS NULL) THEN
29 EDW_OPI_OPRN_M_C.g_push_date_range1 := EDW_COLLECTION_UTIL.G_local_last_push_start_date -
30 EDW_COLLECTION_UTIL.g_offset;
31 ELSE
32 EDW_OPI_OPRN_M_C.g_push_date_range1 := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
33 END IF;
34 IF (p_to_date IS NULL) THEN
35 EDW_OPI_OPRN_M_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
36 ELSE
37 EDW_OPI_OPRN_M_C.g_push_date_range2 := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
38 END IF;
39 edw_log.put_line( 'The collection range is from '||
40 to_char(EDW_OPI_OPRN_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
41 to_char(EDW_OPI_OPRN_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
42 edw_log.put_line(' ');
43 -- -----------------------------------------------------------------------------
44 -- Start of Collection , Developer Customizable Section
45 -- -----------------------------------------------------------------------------
46 edw_log.put_line(' ');
47 edw_log.put_line('Pushing data');
48 l_temp_date := sysdate;
49 Push_EDW_OPI_OPRN_OPRN_LSTG(EDW_OPI_OPRN_M_C.g_push_date_range1, EDW_OPI_OPRN_M_C.g_push_date_range2);
50 Push_EDW_OPI_OPRN_OPRC_LSTG(EDW_OPI_OPRN_M_C.g_push_date_range1, EDW_OPI_OPRN_M_C.g_push_date_range2);
51 l_duration := sysdate - l_temp_date;
52 edw_log.put_line('Total rows inserted : '||g_row_count);
53 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
54 edw_log.put_line(' ');
55 -- ---------------------------------------------------------------------------
56 -- END OF Collection , Developer Customizable Section
57 -- ---------------------------------------------------------------------------
58 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,EDW_OPI_OPRN_M_C.g_exception_msg,
59 EDW_OPI_OPRN_M_C.g_push_date_range1,
60 EDW_OPI_OPRN_M_C.g_push_date_range2);
61 commit;
62 Exception When others then
63 Errbuf:=sqlerrm;
64 Retcode:=sqlcode;
65 l_exception_msg := Retcode || ':' || Errbuf;
66 EDW_OPI_OPRN_M_C.g_exception_msg := l_exception_msg;
67 rollback;
68 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_OPI_OPRN_M_C.g_exception_msg,
69 EDW_OPI_OPRN_M_C.g_push_date_range1,
70 EDW_OPI_OPRN_M_C.g_push_date_range2);
71 commit;
72 End;
73
74 Procedure Push_EDW_OPI_OPRN_OPRN_LSTG(p_from_date IN date, p_to_date IN DATE) IS
75 l_date1 DATE;
76 l_date2 DATE;
77 l_rows_inserted NUMBER :=0;
78 BEGIN
79 edw_log.put_line('Starting Push_EDW_OPI_OPRN_OPRN_LSTG');
80 l_date1 := p_from_date;
81 l_date2 := p_to_date;
82 Insert Into
83 EDW_OPI_OPRN_OPRN_LSTG@EDW_APPS_TO_WH(
84 OPRN_PK,
85 OPRC_FK,
86 OPRN_DP,
87 NAME,
88 OPRN_NAME,
89 DESCRIPTION,
90 ORGN_CODE,
91 DEPARTMENT,
92 PROCESS_QTY_UOM,
93 USER_ATTRIBUTE1,
94 USER_ATTRIBUTE2,
95 USER_ATTRIBUTE3,
96 USER_ATTRIBUTE4,
97 USER_ATTRIBUTE5,
98 LAST_UPDATE_DATE,
99 CREATION_DATE,
100 OPERATION_CODE,
101 COLLECTION_STATUS)
102 select
103 OPRN_PK,
104 OPRC_FK,
105 OPRN_DP,
106 NAME,
107 OPRN_NAME,
108 DESCRIPTION,
109 ORGN_CODE,
110 DEPARTMENT,
111 PROCESS_QTY_UOM,
112 USER_ATTRIBUTE1,
113 USER_ATTRIBUTE2,
114 USER_ATTRIBUTE3,
115 USER_ATTRIBUTE4,
116 USER_ATTRIBUTE5,
117 LAST_UPDATE_DATE,
118 CREATION_DATE,
119 NULL OPERATION_CODE,
120 'READY'
121 from EDW_OPI_OPRN_OPRN_LCV@APPS_TO_APPS
122 where last_update_date between l_date1 and l_date2;
123 l_rows_inserted := sql%rowcount;
124 EDW_OPI_OPRN_M_C.g_row_count := EDW_OPI_OPRN_M_C.g_row_count + l_rows_inserted ;
125 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
126 ' rows into the staging table');
127 edw_log.put_line('Commiting records into EDW_OPI_OPRN_OPRN_LSTG');
128 commit;
129 edw_log.put_line('Completed Push_EDW_OPI_OPRN_OPRN_LSTG');
130 Exception When others then
131 raise;
132 commit;
133 END;
134
135 Procedure Push_EDW_OPI_OPRN_OPRC_LSTG(p_from_date IN date, p_to_date IN DATE) IS
136 l_date1 DATE;
137 l_date2 DATE;
138 l_rows_inserted NUMBER :=0;
139 BEGIN
140 edw_log.put_line('Starting Push_EDW_OPI_OPRN_OPRC_LSTG');
141 l_date1 := p_from_date;
142 l_date2 := p_to_date;
143 Insert Into
144 EDW_OPI_OPRN_OPRC_LSTG@EDW_APPS_TO_WH(
145 ALL_FK,
146 OPRC_PK,
147 OPRC_DP,
148 NAME,
149 DESCRIPTION,
150 USER_ATTRIBUTE1,
151 USER_ATTRIBUTE2,
152 USER_ATTRIBUTE3,
153 USER_ATTRIBUTE4,
154 USER_ATTRIBUTE5,
155 LAST_UPDATE_DATE,
156 CREATION_DATE,
157 OPERATION_CODE,
158 COLLECTION_STATUS)
159 select
160 ALL_FK,
161 OPRC_PK,
162 OPRC_DP,
163 NAME,
164 DESCRIPTION,
165 USER_ATTRIBUTE1,
166 USER_ATTRIBUTE2,
167 USER_ATTRIBUTE3,
168 USER_ATTRIBUTE4,
169 USER_ATTRIBUTE5,
170 LAST_UPDATE_DATE,
171 CREATION_DATE,
172 NULL OPERATION_CODE,
173 'READY'
174 from EDW_OPI_OPRN_OPRC_LCV@APPS_TO_APPS
175 where last_update_date between l_date1 and l_date2;
176 l_rows_inserted := sql%rowcount;
177 EDW_OPI_OPRN_M_C.g_row_count := EDW_OPI_OPRN_M_C.g_row_count + l_rows_inserted ;
178 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
179 ' rows into the staging table');
180 edw_log.put_line('Commiting records into EDW_OPI_OPRN_OPRC_LSTG');
181 commit;
182 edw_log.put_line('Completed Push_EDW_OPI_OPRN_OPRC_LSTG');
183 Exception When others then
184 raise;
185 commit;
186 END;
187
188 End EDW_OPI_OPRN_M_C;