[Home] [Help]
PACKAGE BODY: APPS.EDW_OPI_LOT_M_C
Source
1 Package Body EDW_OPI_LOT_M_C AS
2 /* $Header: OPIPLTDB.pls 120.2 2005/06/07 03:44:23 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_LOT_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_LOT_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_LOT_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_LOT_M_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
40 ELSE
41 EDW_OPI_LOT_M_C.g_push_date_range2 := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
42 END IF;
43
44
45 edw_log.put_line( 'The collection range is from '||
46 to_char(EDW_OPI_LOT_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
47 to_char(EDW_OPI_LOT_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
48 edw_log.put_line(' ');
49
50 -- -----------------------------------------------------------------------------
51 -- Start of Collection , Developer Customizable Section
52 -- -----------------------------------------------------------------------------
53
54 edw_log.put_line(' ');
55 edw_log.put_line('Pushing data');
56
57 l_temp_date := sysdate;
58
59
60 Push_EDW_OPI_LOTD_LOT_LSTG(EDW_OPI_LOT_M_C.g_push_date_range1, EDW_OPI_LOT_M_C.g_push_date_range2);
61
62
63 l_duration := sysdate - l_temp_date;
64
65 edw_log.put_line('Total rows inserted : '||g_row_count);
66 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
67 edw_log.put_line(' ');
68 -- ---------------------------------------------------------------------------
69 -- END OF Collection , Developer Customizable Section
70 -- ---------------------------------------------------------------------------
71 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,EDW_OPI_LOT_M_C.g_exception_msg,
72 EDW_OPI_LOT_M_C.g_push_date_range1,
73 EDW_OPI_LOT_M_C.g_push_date_range2);
74 commit;
75
76 Exception When others then
77 Errbuf:=sqlerrm;
78 Retcode:=sqlcode;
79 l_exception_msg := Retcode || ':' || Errbuf;
80 EDW_OPI_LOT_M_C.g_exception_msg := l_exception_msg;
81 rollback;
82 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_OPI_LOT_M_C.g_exception_msg,
83 EDW_OPI_LOT_M_C.g_push_date_range1,
84 EDW_OPI_LOT_M_C.g_push_date_range2);
85 commit;
86 End;
87
88
89 Procedure Push_EDW_OPI_LOTD_LOT_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 edw_log.put_line('Starting Push_EDW_OPI_LOTD_LOT_LSTG');
95 l_date1 := p_from_date;
96 l_date2 := p_to_date;
97 Insert Into
98 EDW_OPI_LOTD_LOT_LSTG@EDW_APPS_TO_WH(
99 EDW_LOT_PK,
100 EDW_LOT_DP,
101 ORGANIZATION_ID,
102 INVENTORY_ITEM_ID,
103 NAME,
104 LOT_NAME,
105 ITEM_REVISION,
106 NETTABLE_FLAG,
107 EXPIRATION_DATE,
108 DESCRIPTION,
109 USER_ATTRIBUTE1,
110 USER_ATTRIBUTE2,
111 USER_ATTRIBUTE3,
112 USER_ATTRIBUTE4,
113 USER_ATTRIBUTE5,
114 ALL_FK,
115 LAST_UPDATE_DATE,
116 CREATION_DATE,
117 LOT,
118 PARENT_LOT,
119 OPERATION_CODE,
120 COLLECTION_STATUS)
121 select EDW_LOT_PK,
122 EDW_LOT_DP,
123 ORGANIZATION_ID,
124 INVENTORY_ITEM_ID,
125 NAME,
126 LOT_NAME,
127 ITEM_REVISION,
128 NETTABLE_FLAG,
129 EXPIRATION_DATE,
130 DESCRIPTION,
131 USER_ATTRIBUTE1,
132 USER_ATTRIBUTE2,
133 USER_ATTRIBUTE3,
134 USER_ATTRIBUTE4,
135 USER_ATTRIBUTE5,
136 NVL(ALL_FK, 'NA_EDW'),
137 LAST_UPDATE_DATE,
138 CREATION_DATE,
139 LOT,
140 PARENT_LOT,
141 NULL, -- OPERATION_CODE
142 'READY'
143 from EDW_OPI_LOTD_LOT_LCV@APPS_TO_APPS
144 where last_update_date between l_date1 and l_date2;
145
146
147 l_rows_inserted := sql%rowcount;
148 EDW_OPI_LOT_M_C.g_row_count := EDW_OPI_LOT_M_C.g_row_count + l_rows_inserted ;
149 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
150 ' rows into the staging table');
151 edw_log.put_line('Commiting records for EDW_OPI_LOTD_LOT_LSTG');
152 commit;
153
154 edw_log.put_line('Completed Push_EDW_OPI_LOTD_LOT_LSTG');
155 Exception When others then
156 raise;
157 commit;
158 END;
159 End EDW_OPI_LOT_M_C;