[Home] [Help]
PACKAGE BODY: APPS.EDW_MRP_FORECAST_M_C
Source
1 Package Body EDW_MRP_FORECAST_M_C AS
2 G_PUSH_DATE_RANGE1 Date:=Null;
3 G_PUSH_DATE_RANGE2 Date:=Null;
4 g_row_count Number:=0;
5 g_exception_msg varchar2(2000):=Null;
6
7
8 Procedure Push(Errbuf in out NOCOPY Varchar2,
9 Retcode in out NOCOPY Varchar2,
10 p_from_date IN Varchar2,
11 p_to_date IN Varchar2) IS
12 l_dimension_name Varchar2(30) :='EDW_MRP_FORECAST_M' ;
13 l_temp_date Date:=Null;
14 l_rows_inserted Number:=0;
15 l_duration Number:=0;
16 l_exception_msg Varchar2(2000):=Null;
17 l_from_date Date:=Null;
18 l_to_date Date:=Null;
19
20 /* REM -------------------------------------------
21 REM Put any additional developer variables here
22 REM ------------------------------------------- */
23 Begin
24 Errbuf :=NULL;
25 Retcode:=0;
26 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
27 l_to_date :=to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
28 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
29 errbuf := fnd_message.get;
30 Return;
31 END IF;
32 EDW_MRP_FORECAST_M_C.g_push_date_range1 := nvl(l_from_date,
33 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
34 EDW_MRP_FORECAST_M_C.g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
35 edw_log.put_line( 'The collection range is from '||
36 to_char(EDW_MRP_FORECAST_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
37 to_char(EDW_MRP_FORECAST_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
38 edw_log.put_line(' ');
39 /* REM ---------------------------------------------------------------------
40 REM Start of Collection , Developer Customizable Section
41 REM --------------------------------------------------------------------- */
42 edw_log.put_line(' ');
43 edw_log.put_line('Pushing data');
44
45 l_temp_date := sysdate;
46
47
48 Push_EDW_MRP_FCDM_FCS_LSTG(EDW_MRP_FORECAST_M_C.g_push_date_range1, EDW_MRP_FORECAST_M_C.g_push_date_range2);
49 Push_EDW_MRP_FCDM_SET_LSTG(EDW_MRP_FORECAST_M_C.g_push_date_range1, EDW_MRP_FORECAST_M_C.g_push_date_range2);
50
51
52 l_duration := sysdate - l_temp_date;
53
54 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
55 edw_log.put_line(' ');
56 /* ---------------------------------------------------------------------------
57 REM END OF Collection , Developer Customizable Section
58 REM ---------------------------------------------------------------------------
59 */
60 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, EDW_MRP_FORECAST_M_C.g_push_date_range1, EDW_MRP_FORECAST_M_C.g_push_date_range2);
61
62 commit;
63
64 Exception When others then
65 Errbuf:=sqlerrm;
66 Retcode:=sqlcode;
67 l_exception_msg := Retcode || ':' || Errbuf;
68 EDW_MRP_FORECAST_M_C.g_exception_msg := l_exception_msg;
69 rollback;
70 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_MRP_FORECAST_M_C.g_exception_msg, EDW_MRP_FORECAST_M_C.g_push_date_range1, EDW_MRP_FORECAST_M_C.g_push_date_range2);
71
72 commit;
73 End;
74
75
76 Procedure Push_EDW_MRP_FCDM_FCS_LSTG(p_from_date IN date, p_to_date IN DATE) IS
77 l_date1 DATE;
78 l_date2 DATE;
79 l_rows_inserted NUMBER :=0;
80 BEGIN
81 edw_log.put_line('Starting Push_EDW_MRP_FCDM_FCS_LSTG');
82 l_date1 := p_from_date;
83 l_date2 := p_to_date;
84 Insert Into
85 EDW_MRP_FCDM_FCS_LSTG@EDW_APPS_TO_WH(
86 DESCRIPTION,
87 FORECAST_DP,
88 FORECAST_NAME,
89 FORECAST_PK,
90 FORECAST_SET_FK,
91 INSTANCE_CODE,
92 NAME,
93 USER_ATTRIBUTE1,
94 USER_ATTRIBUTE2,
95 USER_ATTRIBUTE3,
96 USER_ATTRIBUTE4,
97 USER_ATTRIBUTE5,
98 OPERATION_CODE,
99 COLLECTION_STATUS,
100 DISABLE_DATE)
101 select DESCRIPTION,
102 FORECAST_DP,
103 FORECAST_NAME,
104 FORECAST_PK,
105 NVL(FORECAST_SET_FK, 'NA_EDW'),
106 INSTANCE_CODE,
107 NAME,
108 USER_ATTRIBUTE1,
109 USER_ATTRIBUTE2,
110 USER_ATTRIBUTE3,
111 USER_ATTRIBUTE4,
112 USER_ATTRIBUTE5,
113 NULL, -- OPERATION_CODE
114 'READY',
115 DISABLE_DATE
116 from EDW_MRP_FCDM_FCS_LCV@APPS_TO_APPS
117 where last_update_date between l_date1 and l_date2;
118
119
120 l_rows_inserted := sql%rowcount;
121 EDW_MRP_FORECAST_M_C.g_row_count := l_rows_inserted ;
122 edw_log.put_line('Commiting records for EDW_MRP_FCDM_FCS_LSTG');
123 commit;
124
125 edw_log.put_line('Completed Push_EDW_MRP_FCDM_FCS_LSTG');
126 Exception When others then
127 raise;
128 commit;
129 END;
130
131
132 Procedure Push_EDW_MRP_FCDM_SET_LSTG(p_from_date IN date, p_to_date IN DATE) IS
133 l_date1 DATE;
134 l_date2 DATE;
135 l_rows_inserted NUMBER :=0;
136 BEGIN
137 edw_log.put_line('Starting Push_EDW_MRP_FCDM_SET_LSTG');
138 l_date1 := p_from_date;
139 l_date2 := p_to_date;
140 Insert Into
141 EDW_MRP_FCDM_SET_LSTG@EDW_APPS_TO_WH(
142 CONSUMPTION_LEVEL,
143 DESCRIPTION,
144 FORECAST_SET_DP,
145 FORECAST_SET_NAME,
146 FORECAST_SET_PK,
147 INSTANCE_CODE,
148 NAME,
149 USER_ATTRIBUTE1,
150 USER_ATTRIBUTE2,
151 USER_ATTRIBUTE3,
152 USER_ATTRIBUTE4,
153 USER_ATTRIBUTE5,
154 OPERATION_CODE,
155 COLLECTION_STATUS,
156 DISABLE_DATE)
157 select CONSUMPTION_LEVEL,
158 DESCRIPTION,
159 FORECAST_SET_DP,
160 FORECAST_SET_NAME,
161 FORECAST_SET_PK,
162 INSTANCE_CODE,
163 NAME,
164 USER_ATTRIBUTE1,
165 USER_ATTRIBUTE2,
166 USER_ATTRIBUTE3,
167 USER_ATTRIBUTE4,
168 USER_ATTRIBUTE5,
169 NULL, -- OPERATION_CODE
170 'READY',
171 DISABLE_DATE
172 from EDW_MRP_FCDM_SET_LCV@APPS_TO_APPS
173 where last_update_date between l_date1 and l_date2;
174
175
176 l_rows_inserted := sql%rowcount;
177
178 edw_log.put_line('Commiting records for EDW_MRP_FCDM_SET_LSTG');
179 commit;
180
181 edw_log.put_line('Completed Push_EDW_MRP_FCDM_SET_LSTG');
182 Exception When others then
183 raise;
184 commit;
185 END;
186 End EDW_MRP_FORECAST_M_C;