[Home] [Help]
PACKAGE BODY: APPS.FII_AP_AP_PAYMENT_M_C
Source
1 Package Body FII_AP_AP_PAYMENT_M_C AS
2 /* $Header: FIIAP05B.pls 120.2 2002/11/20 19:55:15 djanaswa 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_AP_PAYMENT_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 -- -------------------------------------------
22 -- Put any additional developer variables here
23 -- -------------------------------------------
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 RAISE_APPLICATION_ERROR(-20000,'Error in SETUP: ' || errbuf);
32 END IF;
33 FII_AP_AP_PAYMENT_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 FII_AP_AP_PAYMENT_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(FII_AP_AP_PAYMENT_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
38 to_char(FII_AP_AP_PAYMENT_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 edw_log.put_line(' ');
46 edw_log.put_line('Pushing data');
47
48 l_temp_date := sysdate;
49
50
51 Push_EDW_AP_APPY_PAYMENT_LSTG(FII_AP_AP_PAYMENT_M_C.g_push_date_range1, FII_AP_AP_PAYMENT_M_C.g_push_date_range2);
52 Push_EDW_AP_APPY_CHECK_LSTG(FII_AP_AP_PAYMENT_M_C.g_push_date_range1, FII_AP_AP_PAYMENT_M_C.g_push_date_range2);
53 P_EDW_AP_APPY_PYMT_MTHD_LSTG(FII_AP_AP_PAYMENT_M_C.g_push_date_range1, FII_AP_AP_PAYMENT_M_C.g_push_date_range2);
54
55
56 l_duration := sysdate - l_temp_date;
57
58 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
59 edw_log.put_line(' ');
60 -- ---------------------------------------------------------------------------
61 -- END OF Collection , Developer Customizable Section
62 -- ---------------------------------------------------------------------------
63 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,null,g_push_date_range1, 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 FII_AP_AP_PAYMENT_M_C.g_exception_msg := l_exception_msg;
71 rollback;
72 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, FII_AP_AP_PAYMENT_M_C.g_exception_msg,g_push_date_range1, g_push_date_range2);
73
74 commit;
75 End;
76
77
78 Procedure Push_EDW_AP_APPY_PAYMENT_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_AP_APPY_PAYMENT_LSTG');
84 l_date1 := p_from_date;
85 l_date2 := p_to_date;
86 Insert Into
87 EDW_AP_APPY_PAYMENT_LSTG(
88 CHECK_FK,
89 CREATION_DATE,
90 INSTANCE,
91 INVOICE_PAYMENT_ID,
92 LAST_UPDATE_DATE,
93 NAME,
94 PAYMENT,
95 PAYMENT_DP,
96 PAYMENT_PK,
97 USER_ATTRIBUTE1,
98 USER_ATTRIBUTE2,
99 USER_ATTRIBUTE3,
100 USER_ATTRIBUTE4,
101 USER_ATTRIBUTE5,
102 OPERATION_CODE,
103 COLLECTION_STATUS)
104 select NVL(CHECK_FK, 'NA_EDW'),
105 CREATION_DATE,
106 INSTANCE,
107 INVOICE_PAYMENT_ID,
108 LAST_UPDATE_DATE,
109 NAME,
110 PAYMENT,
111 PAYMENT_DP,
112 PAYMENT_PK,
113 USER_ATTRIBUTE1,
114 USER_ATTRIBUTE2,
115 USER_ATTRIBUTE3,
116 USER_ATTRIBUTE4,
117 USER_ATTRIBUTE5,
118 NULL, -- OPERATION_CODE
119 'READY'
120 from FII_AP_AP_APPY_PAYMENT_LCV
121 where last_update_date between l_date1 and l_date2;
122
123
124 l_rows_inserted := sql%rowcount;
125 g_row_count := g_row_count + l_rows_inserted ;
126 edw_log.put_line('Commiting records for EDW_AP_APPY_PAYMENT_LSTG');
127 commit;
128
129 edw_log.put_line('Completed Push_EDW_AP_APPY_PAYMENT_LSTG');
130 Exception When others then
131 raise;
132 commit;
133 END;
134
135
136 Procedure Push_EDW_AP_APPY_CHECK_LSTG(p_from_date IN date, p_to_date IN DATE) IS
137 l_date1 DATE;
138 l_date2 DATE;
139 l_rows_inserted NUMBER :=0;
140 BEGIN
141 edw_log.put_line('Starting Push_EDW_AP_APPY_CHECK_LSTG');
142 l_date1 := p_from_date;
143 l_date2 := p_to_date;
144 Insert Into
145 EDW_AP_APPY_CHECK_LSTG(
146 BANK_ACCOUNT_NAME,
147 CHECK_DP,
148 CHECK_NAME,
149 CHECK_PK,
150 CHECK_ID,
151 CREATION_DATE,
152 INSTANCE,
153 LAST_UPDATE_DATE,
154 NAME,
155 PAYMENT_METHOD_FK,
156 STATUS_LOOKUP_CODE,
157 USER_ATTRIBUTE1,
158 USER_ATTRIBUTE2,
159 USER_ATTRIBUTE3,
160 USER_ATTRIBUTE4,
161 USER_ATTRIBUTE5,
162 OPERATION_CODE,
163 COLLECTION_STATUS)
164 select BANK_ACCOUNT_NAME,
165 CHECK_DP,
166 CHECK_NAME,
167 CHECK_PK,
168 CHECK_ID,
169 CREATION_DATE,
170 INSTANCE,
171 LAST_UPDATE_DATE,
172 NAME,
173 NVL(PAYMENT_METHOD_FK, 'NA_EDW'),
174 STATUS_LOOKUP_CODE,
175 USER_ATTRIBUTE1,
176 USER_ATTRIBUTE2,
177 USER_ATTRIBUTE3,
178 USER_ATTRIBUTE4,
179 USER_ATTRIBUTE5,
180 NULL, -- OPERATION_CODE
181 'READY'
182 from FII_AP_AP_APPY_CHECK_LCV
183 where last_update_date between l_date1 and l_date2;
184
185
186 edw_log.put_line('Commiting records for EDW_AP_APPY_CHECK_LSTG');
187 commit;
188
189 edw_log.put_line('Completed Push_EDW_AP_APPY_CHECK_LSTG');
190 Exception When others then
191 raise;
192 commit;
193 END;
194
195
196 Procedure P_EDW_AP_APPY_PYMT_MTHD_LSTG(p_from_date IN date, p_to_date IN DATE) IS
197 l_date1 DATE;
198 l_date2 DATE;
199 l_rows_inserted NUMBER :=0;
200 BEGIN
201 edw_log.put_line('Starting P_EDW_AP_APPY_PYMT_MTHD_LSTG');
202 l_date1 := p_from_date;
203 l_date2 := p_to_date;
204 Insert Into
205 EDW_AP_APPY_PYMT_MTHD_LSTG(
206 ALL_FK,
207 CREATION_DATE,
208 DESCRIPTION,
209 INSTANCE,
210 LAST_UPDATE_DATE,
211 NAME,
212 PAYMENT_METHOD,
213 PAYMENT_METHOD_DP,
214 PAYMENT_METHOD_PK,
215 USER_ATTRIBUTE1,
216 USER_ATTRIBUTE2,
217 USER_ATTRIBUTE3,
218 USER_ATTRIBUTE4,
219 USER_ATTRIBUTE5,
220 OPERATION_CODE,
221 COLLECTION_STATUS)
222 select NVL(ALL_FK, 'NA_EDW'),
223 CREATION_DATE,
224 DESCRIPTION,
225 INSTANCE,
226 LAST_UPDATE_DATE,
227 NAME,
228 PAYMENT_METHOD,
229 PAYMENT_METHOD_DP,
230 PAYMENT_METHOD_PK,
231 USER_ATTRIBUTE1,
232 USER_ATTRIBUTE2,
233 USER_ATTRIBUTE3,
234 USER_ATTRIBUTE4,
235 USER_ATTRIBUTE5,
236 NULL, -- OPERATION_CODE
237 'READY'
238 from FII_AP_AP_APPY_PYMT_MTHD_LCV;
239
240
241 edw_log.put_line('Commiting records for EDW_AP_APPY_PYMT_MTHD_LSTG');
242 commit;
243
244 edw_log.put_line('Completed P_EDW_AP_APPY_PYMT_MTHD_LSTG');
245 Exception When others then
246 raise;
247 commit;
248 END;
249 End FII_AP_AP_PAYMENT_M_C;