[Home] [Help]
PACKAGE BODY: APPS.EDW_OPI_PRDL_M_C
Source
1 Package Body EDW_OPI_PRDL_M_C AS
2 /* $Header: OPIPPLDB.pls 120.1 2005/06/07 02:46:16 appldev $ */
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_OPI_PRDL_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_temp_date_char Varchar2(35);
19 -- -------------------------------------------
20 -- Put any additional developer variables here
21 -- -------------------------------------------
22 Begin
23 Errbuf :=NULL;
24 Retcode:=0;
25 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
26 errbuf := fnd_message.get;
27 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
28 Return;
29 END IF;
30
31 IF (p_from_date IS NULL) THEN
32 EDW_OPI_PRDL_M_C.g_push_date_range1 := EDW_COLLECTION_UTIL.G_local_last_push_start_date -
33 EDW_COLLECTION_UTIL.g_offset;
34 ELSE
35 EDW_OPI_PRDL_M_C.g_push_date_range1 := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
36 END IF;
37
38 IF (p_to_date IS NULL) THEN
39 EDW_OPI_PRDL_M_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
40 ELSE
41 /* Bug 2333020
42 IF to_char(to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD') = to_char(sysdate,'YYYY/MM/DD') THEN
43 EDW_OPI_PRDL_M_C.g_push_date_range2 := to_date(to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS');
44 ELSE
45 l_temp_date_char := to_char(to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD');
46 EDW_OPI_PRDL_M_C.g_push_date_range2 := to_date(l_temp_date_char||' 23:59:59', 'YYYY/MM/DD HH24:MI:SS');
47 END IF;
48 */
49 EDW_OPI_PRDL_M_C.g_push_date_range2 := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
50 END IF;
51
52
53 edw_log.put_line( 'The collection range is from '||
54 to_char(EDW_OPI_PRDL_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
55 to_char(EDW_OPI_PRDL_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
56 edw_log.put_line(' ');
57
58 -- -----------------------------------------------------------------------------
59 -- Start of Collection , Developer Customizable Section
60 -- -----------------------------------------------------------------------------
61
62 edw_log.put_line(' ');
63 edw_log.put_line('Pushing data');
64
65 l_temp_date := sysdate;
66
67
68 Push_EDW_OPI_PRDL_PRDL_LSTG(EDW_OPI_PRDL_M_C.g_push_date_range1, EDW_OPI_PRDL_M_C.g_push_date_range2);
69
70
71 l_duration := sysdate - l_temp_date;
72
73 edw_log.put_line('Total rows inserted : '||g_row_count);
74 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
75 edw_log.put_line(' ');
76 -- ---------------------------------------------------------------------------
77 -- END OF Collection , Developer Customizable Section
78 -- ---------------------------------------------------------------------------
79 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,
80 EDW_OPI_PRDL_M_C.g_exception_msg,
81 EDW_OPI_PRDL_M_C.g_push_date_range1,
82 EDW_OPI_PRDL_M_C.g_push_date_range2);
83 commit;
84
85 Exception When others then
86 Errbuf:=sqlerrm;
87 Retcode:=sqlcode;
88 l_exception_msg := Retcode || ':' || Errbuf;
89 EDW_OPI_PRDL_M_C.g_exception_msg := l_exception_msg;
90 rollback;
91 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_OPI_PRDL_M_C.g_exception_msg,
92 EDW_OPI_PRDL_M_C.g_push_date_range1,
93 EDW_OPI_PRDL_M_C.g_push_date_range2);
94
95 commit;
96 End;
97
98
99 Procedure Push_EDW_OPI_PRDL_PRDL_LSTG(p_from_date IN date, p_to_date IN DATE) IS
100 l_date1 DATE;
101 l_date2 DATE;
102 l_rows_inserted NUMBER :=0;
103 BEGIN
104 edw_log.put_line('Starting Push_EDW_OPI_PRDL_PRDL_LSTG');
105 l_date1 := p_from_date;
106 l_date2 := p_to_date;
107
108 Insert Into EDW_OPI_PRDL_PRDL_LSTG@EDW_APPS_TO_WH (
109 PRDL_PK,
110 PRDL_DP,
111 ALL_FK,
112 NAME,
113 PRDL_NAME,
114 DESCRIPTION,
115 ORGN_CODE,
116 USER_ATTRIBUTE1,
117 USER_ATTRIBUTE2,
118 USER_ATTRIBUTE3,
119 USER_ATTRIBUTE4,
120 USER_ATTRIBUTE5,
121 LAST_UPDATE_DATE,
122 CREATION_DATE,
123 COLLECTION_STATUS
124 )
125 SELECT /*+ ALL_ROWS */
126 PRDL_PK,
127 PRDL_DP,
128 NVL(ALL_FK, 'NA_EDW'),
129 NAME,
130 PRDL_NAME,
131 DESCRIPTION,
132 ORGN_CODE,
133 USER_ATTRIBUTE1,
134 USER_ATTRIBUTE2,
135 USER_ATTRIBUTE3,
136 USER_ATTRIBUTE4,
137 USER_ATTRIBUTE5,
138 LAST_UPDATE_DATE,
139 CREATION_DATE,
140 'READY'
141 FROM EDW_OPI_PRDL_PRDL_LCV@APPS_TO_APPS
142 where last_update_date between l_date1 and l_date2;
143
144
145 l_rows_inserted := sql%rowcount;
146 EDW_OPI_PRDL_M_C.g_row_count := EDW_OPI_PRDL_M_C.g_row_count + l_rows_inserted ;
147 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
148 ' rows into the staging table');
149 edw_log.put_line('Commiting records for EDW_OPI_PRDL_PRDL_LSTG');
150 commit;
151
152 edw_log.put_line('Completed Push_EDW_OPI_PRDL_PRDL_LSTG');
153 Exception When others then
154 raise;
155 commit;
156 END;
157 End EDW_OPI_PRDL_M_C;