[Home] [Help]
PACKAGE BODY: APPS.EDW_MRP_DM_CLS_M_C
Source
1 Package Body EDW_MRP_DM_CLS_M_C AS
2 /* $Header: ISCSCD0B.pls 115.7 2002/12/19 01:31:46 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_MRP_DM_CLS_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 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
30 errbuf := fnd_message.get;
31 Return;
32 END IF;
33 EDW_MRP_DM_CLS_M_C.g_push_date_range1 := nvl(l_from_date,
34 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
35 EDW_MRP_DM_CLS_M_C.g_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(EDW_MRP_DM_CLS_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
38 to_char(EDW_MRP_DM_CLS_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
39 edw_log.put_line(' ');
40 /*
41 ----------------------------------------------------------------------------
42 Start of Collection , Developer Customizable Section
43 --------------------------------------------------------------------------
44 */
45
46 edw_log.put_line(' ');
47 edw_log.put_line('Pushing data');
48
49 l_temp_date := sysdate;
50
51
52 Push_EDW_MRP_DCDM_DM_LSTG(EDW_MRP_DM_CLS_M_C.g_push_date_range1, EDW_MRP_DM_CLS_M_C.g_push_date_range2);
53
54
55 l_duration := sysdate - l_temp_date;
56
57 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
58 edw_log.put_line(' ');
59 /*
60 REM ---------------------------------------------------------------------------
61 REM END OF Collection , Developer Customizable Section
62 REM ---------------------------------------------------------------------------*/
63 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, EDW_MRP_DM_CLS_M_C.g_push_date_range1, EDW_MRP_DM_CLS_M_C.g_push_date_range2);
64 commit;
65
66 Exception When others then
67 Errbuf:=sqlerrm;
68 Retcode:=sqlcode;
69 l_exception_msg := Retcode || ':' || Errbuf;
70 EDW_MRP_DM_CLS_M_C.g_exception_msg := l_exception_msg;
71 rollback;
72 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_MRP_DM_CLS_M_C.g_exception_msg, EDW_MRP_DM_CLS_M_C.g_push_date_range1, EDW_MRP_DM_CLS_M_C.g_push_date_range2);
73
74 commit;
75 End;
76
77
78 Procedure Push_EDW_MRP_DCDM_DM_LSTG(p_from_date IN date, p_to_date IN DATE) IS
79 l_date1 DATE;
80 l_date2 DATE;
81 l_rows_inserted NUMBER :=0;
82 BEGIN
83 edw_log.put_line('Starting Push_EDW_MRP_DCDM_DM_LSTG');
84 l_date1 := p_from_date;
85 l_date2 := p_to_date;
86 Insert Into
87 EDW_MRP_DCDM_DM_LSTG@EDW_APPS_TO_WH(
88 ALL_FK,
89 DEMAND_CLASS,
90 DEMAND_CLASS_DP,
91 DEMAND_CLASS_PK,
92 DESCRIPTION,
93 INSTANCE_CODE,
94 NAME,
95 USER_ATTRIBUTE1,
96 USER_ATTRIBUTE2,
97 USER_ATTRIBUTE3,
98 USER_ATTRIBUTE4,
99 USER_ATTRIBUTE5,
100 OPERATION_CODE,
101 COLLECTION_STATUS)
102 select NVL(ALL_FK, 'NA_EDW'),
103 DEMAND_CLASS,
104 DEMAND_CLASS_DP,
105 DEMAND_CLASS_PK,
106 DESCRIPTION,
107 INSTANCE_CODE,
108 NAME,
109 USER_ATTRIBUTE1,
110 USER_ATTRIBUTE2,
111 USER_ATTRIBUTE3,
112 USER_ATTRIBUTE4,
113 USER_ATTRIBUTE5,
114 NULL, -- OPERATION_CODE
115 'READY'
116 from EDW_MRP_DCDM_DM_LCV@APPS_TO_APPS
117 where last_update_date between l_date1 and l_date2;
118
119
120 l_rows_inserted := sql%rowcount;
121 EDW_MRP_DM_CLS_M_C.g_row_count := EDW_MRP_DM_CLS_M_C.g_row_count + l_rows_inserted ;
122 edw_log.put_line('Commiting records for EDW_MRP_DCDM_DM_LSTG');
123 commit;
124
125 edw_log.put_line('Completed Push_EDW_MRP_DCDM_DM_LSTG');
126 Exception When others then
127 raise;
128 commit;
129 END;
130 End EDW_MRP_DM_CLS_M_C;