[Home] [Help]
PACKAGE BODY: APPS.EDW_OE_SLCHNL_M_C
Source
1 Package Body EDW_OE_SLCHNL_M_C AS
2 /* $Header: ISCSCD3B.pls 115.8 2002/12/19 00:52:48 scheung 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_OE_SLCHNL_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 /* REM -------------------------------------------
22 REM Put any additional developer variables here
23 REM ------------------------------------------- */
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
30 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
31 errbuf := fnd_message.get;
32 Return;
33 END IF;
34
35 EDW_OE_SLCHNL_M_C.g_push_date_range1 := nvl(l_from_date,
36 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
37 EDW_OE_SLCHNL_M_C.g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
38
39 edw_log.put_line( 'The collection range is from '||
40 to_char(EDW_OE_SLCHNL_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
41 to_char(EDW_OE_SLCHNL_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
42 edw_log.put_line(' ');
43
44 /* REM ------------------------------------------------------------------------
45 REM Start of Collection , Developer Customizable Section
46 REM ------------------------------------------------------------------------- */
47
48 edw_log.put_line(' ');
49 edw_log.put_line('Pushing data');
50
51 l_temp_date := sysdate;
52
53
54 Push_EDW_OE_SCHN_SCHN_LSTG(EDW_OE_SLCHNL_M_C.g_push_date_range1, EDW_OE_SLCHNL_M_C.g_push_date_range2);
55
56
57 l_duration := sysdate - l_temp_date;
58
59 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
60 edw_log.put_line(' ');
61 /* REM -----------------------------------------------------------------------
62 REM END OF Collection , Developer Customizable Section
63 REM ------------------------------------------------------------------------- */
64 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, EDW_OE_SLCHNL_M_C.g_push_date_range1, EDW_OE_SLCHNL_M_C.g_push_date_range2);
65 commit;
66
67 Exception When others then
68 Errbuf:=sqlerrm;
69 Retcode:=sqlcode;
70 l_exception_msg := Retcode || ':' || Errbuf;
71 EDW_OE_SLCHNL_M_C.g_exception_msg := l_exception_msg;
72 rollback;
73 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_OE_SLCHNL_M_C.g_exception_msg, EDW_OE_SLCHNL_M_C.g_push_date_range1, EDW_OE_SLCHNL_M_C.g_push_date_range2);
74
75 commit;
76 End;
77
78
79 Procedure Push_EDW_OE_SCHN_SCHN_LSTG(p_from_date IN date, p_to_date IN DATE) IS
80 l_date1 DATE;
81 l_date2 DATE;
82 l_rows_inserted NUMBER :=0;
83 BEGIN
84 edw_log.put_line('Starting Push_EDW_OE_SCHN_SCHN_LSTG');
85 l_date1 := p_from_date;
86 l_date2 := p_to_date;
87 Insert Into
88 EDW_OE_SCHN_SCHN_LSTG(
89 ACTIVE_FROM_DATE,
90 ACTIVE_TO_DATE,
91 ALL_FK,
92 CREATION_DATE,
93 ENABLED_FLAG,
94 INSTANCE_CODE,
95 LAST_UPDATE_DATE,
96 NAME,
97 SALES_CHANNEL_CODE,
98 SALES_CHANNEL_DP,
99 SALES_CHANNEL_NAME,
100 SALES_CHANNEL_PK,
101 USER_ATTRIBUTE1,
102 USER_ATTRIBUTE2,
103 USER_ATTRIBUTE3,
104 USER_ATTRIBUTE4,
105 USER_ATTRIBUTE5,
106 OPERATION_CODE,
107 COLLECTION_STATUS)
108 select ACTIVE_FROM_DATE,
109 ACTIVE_TO_DATE,
110 NVL(ALL_FK, 'NA_EDW'),
111 CREATION_DATE,
112 ENABLED_FLAG,
113 INSTANCE_CODE,
114 LAST_UPDATE_DATE,
115 NAME,
116 SALES_CHANNEL_CODE,
117 SALES_CHANNEL_DP,
118 SALES_CHANNEL_NAME,
119 SALES_CHANNEL_PK,
120 USER_ATTRIBUTE1,
121 USER_ATTRIBUTE2,
122 USER_ATTRIBUTE3,
123 USER_ATTRIBUTE4,
124 USER_ATTRIBUTE5,
125 NULL, -- OPERATION_CODE
126 'READY'
127 from EDW_OE_SCHN_SCHN_LCV
128 where last_update_date between l_date1 and l_date2;
129
130
131 l_rows_inserted := sql%rowcount;
132 EDW_OE_SLCHNL_M_C.g_row_count := EDW_OE_SLCHNL_M_C.g_row_count + l_rows_inserted ;
133 edw_log.put_line('Commiting records for EDW_OE_SCHN_SCHN_LSTG');
134 commit;
135
136 edw_log.put_line('Completed Push_EDW_OE_SCHN_SCHN_LSTG');
137 Exception When others then
138 raise;
139 commit;
140 END;
141 End EDW_OE_SLCHNL_M_C;