[Home] [Help]
PACKAGE BODY: APPS.FII_AR_SIC_CODE_M_C
Source
1 Package Body FII_AR_SIC_CODE_M_C AS
2 /* $Header: FIIAR02B.pls 120.1 2004/03/01 05:42:11 sgautam ship $ */
3 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('EDW_DEBUG'), 'N');
4 G_PUSH_DATE_RANGE1 Date:=Null;
5 G_PUSH_DATE_RANGE2 Date:=Null;
6 g_row_count Number:=0;
7 g_exception_msg varchar2(2000):=Null;
8
9
10 Procedure Push(Errbuf in out NOCOPY Varchar2,
11 Retcode in out NOCOPY Varchar2,
12 p_from_date IN Varchar2,
13 p_to_date IN Varchar2) IS
14 l_dimension_name Varchar2(30) :='EDW_SIC_CODE_M' ;
15 l_temp_date Date:=Null;
16 l_rows_inserted Number:=0;
17 l_duration Number:=0;
18 l_exception_msg Varchar2(2000):=Null;
19 l_from_date Date:=Null;
20 l_to_date Date:=Null;
21
22 -- -------------------------------------------
23 -- Put any additional developer variables here
24 -- -------------------------------------------
25 Begin
26 Errbuf :=NULL;
27 Retcode:=0;
28 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
29 l_to_date :=to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
30 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
31 errbuf := fnd_message.get;
32 RAISE_APPLICATION_ERROR(-20000,'Error in SETUP: ' || errbuf);
33 END IF;
34 FII_AR_SIC_CODE_M_C.g_push_date_range1 := nvl(l_from_date,
35 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
36 FII_AR_SIC_CODE_M_C.g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
37 if g_debug_flag = 'Y' then
38 edw_log.put_line( 'The collection range is from '||
39 to_char(FII_AR_SIC_CODE_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
40 to_char(FII_AR_SIC_CODE_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
41 edw_log.put_line(' ');
42 end if;
43
44 -- -----------------------------------------------------------------------------
45 -- Start of Collection , Developer Customizable Section
46 -- -----------------------------------------------------------------------------
47 if g_debug_flag = 'Y' then
48 edw_log.put_line(' ');
49 edw_log.put_line('Pushing data');
50 end if;
51
52 l_temp_date := sysdate;
53
54
55 Push_EDW_SICM_SIC_LSTG(FII_AR_SIC_CODE_M_C.g_push_date_range1, FII_AR_SIC_CODE_M_C.g_push_date_range2);
56
57
58 l_duration := sysdate - l_temp_date;
59
60 if g_debug_flag = 'Y' then
61 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
62 edw_log.put_line(' ');
63 end if;
64 -- ---------------------------------------------------------------------------
65 -- END OF Collection , Developer Customizable Section
66 -- ---------------------------------------------------------------------------
67 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,null,g_push_date_range1, g_push_date_range2);
68 commit;
69
70 Exception When others then
71 Errbuf:=sqlerrm;
72 Retcode:=sqlcode;
73 l_exception_msg := Retcode || ':' || Errbuf;
74 FII_AR_SIC_CODE_M_C.g_exception_msg := l_exception_msg;
75 rollback;
76 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, FII_AR_SIC_CODE_M_C.g_exception_msg,g_push_date_range1, g_push_date_range2);
77
78 commit;
79 End;
80
81
82 Procedure Push_EDW_SICM_SIC_LSTG(p_from_date IN date, p_to_date IN DATE) IS
83 l_date1 DATE;
84 l_date2 DATE;
85 l_rows_inserted NUMBER :=0;
86 BEGIN
87 if g_debug_flag = 'Y' then
88 edw_log.put_line('Starting Push_EDW_SICM_SIC_LSTG');
89 end if;
90 l_date1 := p_from_date;
91 l_date2 := p_to_date;
92 Insert Into
93 EDW_SICM_SIC_LSTG(
94 ALL_FK,
95 INSTANCE,
96 NAME,
97 SIC_CODE,
98 SIC_CODE_DP,
99 SIC_CODE_PK,
100 USER_ATTRIBUTE1,
101 USER_ATTRIBUTE2,
102 USER_ATTRIBUTE3,
103 USER_ATTRIBUTE4,
104 USER_ATTRIBUTE5,
105 DESCRIPTION,
106 UPDATE_FACT_FLAG,
107 OPERATION_CODE,
108 COLLECTION_STATUS)
109 select NVL(ALL_FK, 'NA_EDW'),
110 INSTANCE,
111 NAME,
112 SIC_CODE,
113 SIC_CODE_DP,
114 SIC_CODE_PK,
115 USER_ATTRIBUTE1,
116 USER_ATTRIBUTE2,
117 USER_ATTRIBUTE3,
118 USER_ATTRIBUTE4,
119 USER_ATTRIBUTE5,
120 NULL, --DESCRIPTION,
121 NULL, --UPDATE_FACT_FLAG,
122 NULL, -- OPERATION_CODE
123 'READY'
124 from FII_AR_SICM_SIC_LCV
125 where last_update_date between l_date1 and l_date2;
126
127
128 l_rows_inserted := sql%rowcount;
129 g_row_count := g_row_count + l_rows_inserted ;
130 if g_debug_flag = 'Y' then
131 edw_log.put_line('Commiting records for EDW_SICM_SIC_LSTG');
132 end if;
133 commit;
134
135
136 if g_debug_flag = 'Y' then
137 edw_log.put_line('Completed Push_EDW_SICM_SIC_LSTG');
138 end if;
139 Exception When others then
140 raise;
141 commit;
142 END;
143 End FII_AR_SIC_CODE_M_C;