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