[Home] [Help]
PACKAGE BODY: APPS.EDW_LOOKUP_M_C
Source
1 Package Body EDW_LOOKUP_M_C AS
2 /* $Header: poapplkb.pls 120.1 2005/06/13 13:14:05 sriswami noship $ */
3
4 g_row_count Number :=0;
5 g_exception_message varchar2(2000) :=NULL;
6
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
12 l_dimension_name Varchar2(30) :='EDW_LOOKUP_M';
13 l_push_date_range1 Date:= Null;
14 l_push_date_range2 Date:= Null;
15
16 l_from_date date;
17 l_to_date date;
18
19
20 BEGIN
21 Errbuf := NULL;
22 Retcode := 0;
23
24 If (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) Then
25 errbuf := fnd_message.get;
26 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
27 End If;
28
29 fnd_date.initialize('YYYY/MM/DD', 'YYYY/MM/DD HH24:MI:SS');
30 l_from_date := fnd_date.displayDT_to_date(p_from_date);
31 l_to_date := fnd_date.displayDT_to_date(p_to_date);
32
33 l_push_date_range1 := nvl(l_from_date,
34 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
35 l_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(l_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
38 to_char(l_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
39 edw_log.put_line(' ');
40
41 Edw_Lookup_M_C.Push_Edw_Lookups(Errbuf, Retcode, L_push_date_range1,l_push_date_range2);
42
43 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,
44 P_PERIOD_START => l_push_date_range1,
45 P_PERIOD_END => l_push_date_range2);
46 commit;
47
48 Exception When others then
49 Errbuf := sqlerrm;
50 Retcode := sqlcode;
51 EDW_LOOKUP_M_C.g_exception_message := EDW_LOOKUP_M_C.g_exception_message||'<>'||Retcode || ':' || Errbuf;
52 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_LOOKUP_M_C.g_exception_message,
53 l_push_date_range1, l_push_date_range2);
54 rollback;
55 raise;
56
57 End Push;
58
59
60 Procedure Push_Edw_Lookups(Errbuf in out NOCOPY Varchar2,
61 Retcode in out NOCOPY Varchar2,
62 p_from_date in Date,
63 p_to_date in Date) IS
64
65 l_push_date_range1 Date := NULL;
66 l_push_date_range2 Date := NULL;
67 l_temp_date Date := NULL;
68 l_rows_inserted Number := 0;
69 l_duration Number := 0;
70 l_exception_msg Varchar2(2000) := Null;
71
72 -- -------------------------------------------
73 -- Put any additional developer variables here
74 -- -------------------------------------------
75
76 Begin
77
78 Errbuf :=NULL;
79 Retcode:=0;
80
81 l_push_date_range1 := p_from_date;
82 l_push_date_range2 := p_to_date;
83 -- -----------------------------------------------------------------------------
84 -- Start of Collection , Developer Customizable Section
85 -- -----------------------------------------------------------------------------
86 edw_log.put_line(' ');
87 edw_log.put_line('Pushing data');
88
89 l_temp_date := sysdate;
90
91 Insert Into EDW_LKUP_LOOKUP_CODE_LSTG(
92 ALL_FK,
93 ALL_FK_KEY,
94 ROW_ID,
95 DESCRIPTION,
96 LOOKUP_CODE_DP,
97 NAME,
98 END_DATE_ACTIVE,
99 INSTANCE,
100 LAST_UPDATE_DATE,
101 LOOKUP_CODE,
102 LOOKUP_CODE_PK,
103 LOOKUP_TYPE,
104 START_DATE_ACTIVE,
105 TABLE_CODE,
106 USER_ATTRIBUTE1,
107 USER_ATTRIBUTE2,
108 USER_ATTRIBUTE3,
109 USER_ATTRIBUTE4,
110 USER_ATTRIBUTE5,
111 REQUEST_ID,
112 OPERATION_CODE,
113 ERROR_CODE,
114 COLLECTION_STATUS)
115 select
116 'ALL', -- ALL_FK
117 NULL, -- ALL_FK_KEY
118 NULL, -- ROW_ID
119 DESCRIPTION,
120 lookup_codes_dp, -- DISPLAYED_NAME,
121 NAME, -- NAME
122 NULL,
123 INSTANCE, -- INSTANCE
124 max(LAST_UPDATE_DATE), -- LAST_UPDATE_DATE
125 LOOKUP_CODE,
126 LOOKUP_CODE_PK, -- LOOKUP_CODE_PK
127 LOOKUP_TYPE,
128 NULL,
129 TABLE_CODE,
130 USER_ATTRIBUTE1,
131 USER_ATTRIBUTE2,
132 USER_ATTRIBUTE3,
133 USER_ATTRIBUTE4,
134 USER_ATTRIBUTE5,
135 NULL, -- REQUEST_ID
136 NULL, -- OPERATION_CODE
137 NULL, -- ERROR_CODE
138 'READY'
139 from EDW_LOOKUP_CODES_LCV
140 where last_update_date between l_push_date_range1 and
141 l_push_date_range2
142 OR last_update_date is NULL
143 group by description, lookup_codes_dp,name, instance,
144 user_attribute1, user_attribute2,
145 user_attribute3, user_attribute4, user_attribute5,
146 lookup_code, lookup_code_pk, lookup_type, table_code;
147
148 l_rows_inserted := sql%rowcount;
149 l_duration := sysdate - l_temp_date;
150
151 edw_log.put_line('Inserted '||to_char(nvl(l_rows_inserted,0))||
152 ' rows into the staging table');
153 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
154 edw_log.put_line(' ');
155
156 -- ---------------------------------------------------------------------------
157 -- END OF Collection , Developer Customizable Section
158 -- ---------------------------------------------------------------------------
159 EDW_LOOKUP_M_C.g_row_count := EDW_LOOKUP_M_C.g_row_count + l_rows_inserted;
160
161 Exception When others then
162 Errbuf := sqlerrm;
163 Retcode := sqlcode;
164
165 EDW_LOOKUP_M_C.g_exception_message := Retcode || ':' || Errbuf;
166 rollback;
167
168 raise;
169
170
171 End Push_Edw_Lookups;
172
173
174 End EDW_LOOKUP_M_C;