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