[Home] [Help]
PACKAGE BODY: APPS.EDW_POA_LN_TYPE_M_C
Source
1 Package Body EDW_POA_LN_TYPE_M_C AS
2 /* $Header: poappltb.pls 120.1 2005/06/13 13:15:32 sriswami noship $ */
3
4 g_row_count Number:= 0;
5 g_exception_message Varchar2(2000) := Null;
6
7 Procedure Push(Errbuf in out NOCOPY Varchar2,
8 Retcode in out NOCOPY Varchar2,
9 p_from_date Varchar2,
10 p_to_date Varchar2) IS
11
12 l_dimension_name Varchar2(30) := 'EDW_POA_LN_TYPE_M';
13 l_push_date_range1 Date:= Null;
14 l_push_date_range2 Date:= Null;
15
16 l_from_date date;
17 l_to_date date;
18
19 BEGIN
20 Errbuf := NULL;
21 Retcode := 0;
22
23 If (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) Then
24 errbuf := fnd_message.get;
25 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
26 End If;
27
28 l_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
29 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
30
31 l_push_date_range1 := nvl(l_from_date,
32 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
33 l_push_date_range2 := nvl(l_to_date, EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
34 edw_log.put_line( 'The collection range is from '||
35 to_char(l_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
36 to_char(l_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
37 edw_log.put_line(' ');
38
39 Edw_POA_LN_TYPE_M_C.Push_LN_TYPE(Errbuf, Retcode, L_push_date_range1,l_push_date_range2);
40
41
42 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,
43 P_PERIOD_START => l_push_date_range1,
44 P_PERIOD_END => l_push_date_range2);
45 commit;
46
47 Exception When others then
48 Errbuf := sqlerrm;
49 Retcode := sqlcode;
50 EDW_POA_LN_TYPE_M_C.g_exception_message := EDW_POA_LN_TYPE_M_C.g_exception_message||'<>
51 '||Retcode || ':' || Errbuf;
52 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_POA_LN_TYPE_M_C.g_exception_message,
53 l_push_date_range1, l_push_date_range2);
54 rollback;
55 raise;
56
57 End Push;
58
59 Procedure Push_LN_TYPE(Errbuf in out NOCOPY Varchar2,
60 Retcode in out NOCOPY Varchar2,
61 p_from_date Date,
62 p_to_date Date) IS
63 l_push_date_range1 Date := NULL;
64 l_push_date_range2 Date := NULL;
65 l_temp_date Date := NULL;
66 l_rows_inserted Number := 0;
67 l_duration Number := 0;
68 l_exception_msg Varchar2(2000) := Null;
69
70 -- -------------------------------------------
71 -- Put any additional developer variables here
72 -- -------------------------------------------
73
74 Begin
75 Errbuf :=NULL;
76 Retcode:=0;
77
78 l_push_date_range1 :=p_from_date;
79 l_push_date_range2:=p_to_date;
80
81 -- -----------------------------------------------------------------------------
82 -- Start of Collection , Developer Customizable Section
83 -- -----------------------------------------------------------------------------
84 edw_log.put_line(' ');
85 edw_log.put_line('Pushing data');
86
87 l_temp_date := sysdate;
88
89 Insert Into EDW_POA_LNTP_LN_TYPE_LSTG@EDW_APPS_TO_WH(
90 ALL_FK,
91 ALL_FK_KEY,
92 ROW_ID,
93 LINE_TYPE_DP,
94 -- DELETION_DATE,
95 DESCRIPTION,
96 INACTIVE_DATE,
97 INSTANCE,
98 NAME,
99 LINE_TYPE_PK,
100 ORDER_TYPE,
101 OUTSIDE_OP_FLAG,
102 USER_ATTRIBUTE1,
103 USER_ATTRIBUTE2,
104 USER_ATTRIBUTE3,
105 USER_ATTRIBUTE4,
106 USER_ATTRIBUTE5,
107 REQUEST_ID,
108 OPERATION_CODE,
109 ERROR_CODE,
110 COLLECTION_STATUS)
111 select
112 'ALL',
113 NULL, -- ALL_FK_KEY,
114 NULL, -- ROW_ID,
115 LINE_TYPE_PK, -- LINE_TYPE_DP,
116 -- NULL, DELETION_DATE,
117 DESCRIPTION,
118 INACTIVE_DATE,
119 INSTANCE,
120 LINE_TYPE_PK, --LINE_TYPE_NAME,
121 LINE_TYPE_PK,
122 ORDER_TYPE,
123 OUTSIDE_OP_FLAG,
124 USER_ATTRIBUTE1,
125 USER_ATTRIBUTE2,
126 USER_ATTRIBUTE3,
127 USER_ATTRIBUTE4,
128 USER_ATTRIBUTE5,
129 NULL, --REQUEST_ID,
130 NULL, --OPERATION_CODE,
131 NULL, --ERROR_CODE
132 'READY'
133 from EDW_POA_LN_TYPE_lCV@apps_to_apps
134 where last_update_date between l_push_date_range1 and l_push_date_range2
135 OR last_update_date is NULL;
136
137 l_rows_inserted := sql%rowcount;
138 l_duration := sysdate - l_temp_date;
139
140 edw_log.put_line('Inserted '||to_char(nvl(l_rows_inserted,0))||
141 ' rows into the staging table');
142 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
143 edw_log.put_line(' ');
144
145 -- ---------------------------------------------------------------------------
146 -- END OF Collection , Developer Customizable Section
147 -- ---------------------------------------------------------------------------
148 EDW_POA_LN_TYPE_M_C.g_row_count :=EDW_POA_LN_TYPE_M_C.g_row_count +l_rows_inserted;
149
150 Exception When others then
151 Errbuf := sqlerrm;
152 Retcode := sqlcode;
153 EDW_POA_LN_TYPE_M_C.g_exception_message := Retcode || ':' || Errbuf;
154 rollback;
155
156 raise;
157
158 End Push_ln_type;
159
160 End EDW_POA_LN_TYPE_M_C;