eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGI_CIS_IGIPMTHP_PKG

Source


1 PACKAGE BODY igi_cis_igipmthp_pkg AS
2 -- $Header: igipmthpb.pls 120.4.12020000.2 2012/09/18 20:32:59 sasukuma ship $
3 -- $Header: igipmthpb.pls 120.4.12020000.2 2012/09/18 20:32:59 sasukuma ship $
4 
5   --==========================================================================
6   ----Logging Declarations
7   --==========================================================================
8   C_STATE_LEVEL CONSTANT NUMBER       :=  FND_LOG.LEVEL_STATEMENT;
9   C_PROC_LEVEL  CONSTANT  NUMBER     :=  FND_LOG.LEVEL_PROCEDURE;
10   C_EVENT_LEVEL CONSTANT NUMBER       :=  FND_LOG.LEVEL_EVENT;
11   C_EXCEP_LEVEL CONSTANT NUMBER       :=  FND_LOG.LEVEL_EXCEPTION;
12   C_ERROR_LEVEL CONSTANT NUMBER       :=  FND_LOG.LEVEL_ERROR;
13   C_UNEXP_LEVEL CONSTANT NUMBER       :=  FND_LOG.LEVEL_UNEXPECTED;
14   g_log_level   CONSTANT NUMBER      := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
15   g_path_name   CONSTANT VARCHAR2(100)  := 'igi.plsql.igipmthpb.igi_cis_igipmthp_pkg';
16 
17   PROCEDURE log
18   (
19     p_level             IN NUMBER,
20     p_procedure_name    IN VARCHAR2,
21     p_debug_info        IN VARCHAR2
22   )
23   IS
24 
25   BEGIN
26     IF (p_level >= g_log_level ) THEN
27       FND_LOG.STRING(p_level, p_procedure_name, p_debug_info);
28     END IF;
29   END log;
30 
31   PROCEDURE init
32   IS
33     l_procedure_name       VARCHAR2(100) :='.init';
34   BEGIN
35     log(C_STATE_LEVEL, l_procedure_name, 'Package Information');
36     log(C_STATE_LEVEL, l_procedure_name, '$Header: igipmthpb.pls 120.4.12020000.2 2012/09/18 20:32:59 sasukuma ship $');
37   END;
38 
39   FUNCTION BeforeReport RETURN BOOLEAN IS
40      l_procedure_name         VARCHAR2(100):='.BeforeReport';
41      SINGLE_QUOTE constant varchar2(3) := '''';
42      TWO_SINGLE_QUOTE constant varchar2(6) := '''''';
43   BEGIN
44     l_procedure_name := g_path_name || l_procedure_name;
45     log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
46     log(C_STATE_LEVEL, l_procedure_name, 'p_supplier_from='||p_supplier_from);
47     log(C_STATE_LEVEL, l_procedure_name, 'p_supplier_to='||p_supplier_to);
48     log(C_STATE_LEVEL, l_procedure_name, 'p_period='||p_period);
49     log(C_STATE_LEVEL, l_procedure_name, 'p_mode='||p_mode);
50     log(C_STATE_LEVEL, l_procedure_name, 'p_sort_by='||p_sort_by);
51     log(C_STATE_LEVEL, l_procedure_name, 'p_report_lev='||p_report_lev);
52     log(C_STATE_LEVEL, l_procedure_name, 'p_amt_type='||p_amt_type);
53       pwhereclause := ' ';
54       tableclause := ' ';
55 
56       --bug 6124461 AP_SUPPLIERS table has been included in the query
57       IF (p_supplier_from IS NOT NULL) THEN
58          tableclause := 'AP_SUPPLIERS po_ven,';
59          pwhereclause  := pwhereclause || ' and lines.vendor_id = po_ven.vendor_id and po_ven.vendor_name >=  ''' || replace(p_supplier_from, SINGLE_QUOTE, TWO_SINGLE_QUOTE) || '''';
60       END IF;
61 
62       IF (p_supplier_to IS NOT NULL) THEN
63          IF (p_supplier_from IS NOT NULL) THEN
64             pwhereclause  := pwhereclause || ' and po_ven.vendor_name <= ''' || replace(p_supplier_to, SINGLE_QUOTE, TWO_SINGLE_QUOTE) || '''';
65           ELSE
66             tableclause := 'AP_SUPPLIERS po_ven,';
67             pwhereclause  := pwhereclause || ' and lines.vendor_id = po_ven.vendor_id and po_ven.vendor_name <= ''' || replace(p_supplier_to, SINGLE_QUOTE, TWO_SINGLE_QUOTE) || '''';
68           END IF;
69       END IF;
70       --end bug 6124461
71       --Bug 5933093 start
72       IF (p_period IS NOT NULL ) THEN
73          pwhereclause := pwhereclause || ' AND hdr.period_name = ''' || p_period || '''';
74          IF p_mode = 'P' THEN
75             pwhereclause := pwhereclause || ' AND hdr.header_id = (select max(header_id) from IGI_CIS_MTH_RET_HDR_T where period_name = ''' || p_period || '''';
76             pwhereclause := pwhereclause || ' AND request_status_code = ''P'')';
77          ELSE
78             pwhereclause := pwhereclause || ' AND hdr.header_id = (select max(header_id) from IGI_CIS_MTH_RET_HDR_H where period_name = ''' || p_period || '''';
79             pwhereclause := pwhereclause || ' AND request_status_code = ''C'')';
80          END IF;
81       END IF ;
82       --Bug 5933093 end
83 
84       IF p_mode = 'P' THEN
85          pwhereclause := pwhereclause || ' AND request_status_code = ''P''';
86          tableclause  := tableclause||' IGI_CIS_MTH_RET_HDR_T hdr, IGI_CIS_MTH_RET_LINES_T lines, IGI_CIS_MTH_RET_PAY_T pay ';
87       ELSE
88          pwhereclause := pwhereclause || ' AND request_status_code = ''C''';
89          tableclause  := tableclause||' IGI_CIS_MTH_RET_HDR_H hdr, IGI_CIS_MTH_RET_LINES_H lines, IGI_CIS_MTH_RET_PAY_H pay ';
90       END IF ;
91 
92       IF (p_sort_by IS NOT NULL ) THEN
93          IF p_sort_by = 'VENDOR_NAME' Then
94             orderbyclause := ' ORDER BY ' || p_sort_by;
95          ELSE
96             orderbyclause := ' ORDER BY ' || p_sort_by || ', lines.vendor_name';
97          END IF;
98       ELSE
99          orderbyclause := ' ORDER BY lines.vendor_name';
100       END IF;
101       IF p_report_lev = 'S' then
102         partselect := ' '''' invoice_num, ';
103         partgroupby := ' ';
104       Elsif p_report_lev ='D' then
105         partselect := 'nvl((select invoice_num from ap_invoices where pay.invoice_id = invoice_id),'''') invoice_num, ';
106         partgroupby := ',pay.invoice_id ';
107       End if;
111          pwhereclause := pwhereclause ||' and ((nvl(lines.total_payments,0) + nvl(lines.total_deductions,0)) >=0 and
108 
109       --ER6137652 Start
110       IF nvl(p_amt_type,'P') = 'P' THEN
112                            nvl(lines.material_cost,0) >= 0 and
113                            nvl(lines.labour_cost,0) >= 0 and
114                            nvl(lines.total_deductions,0) >= 0) ';
115       ELSIF nvl(p_amt_type,'P') = 'N' THEN
116          pwhereclause := pwhereclause || ' and ((nvl(lines.total_payments,0) + nvl(lines.total_deductions,0)) < 0 or
117                            nvl(lines.material_cost,0) < 0 or
118                            nvl(lines.labour_cost,0) < 0 or
119                            nvl(lines.total_deductions,0) < 0) ';
120       END IF;
121       --ER6137652 End
122 
123       -- for debugging
124       FND_FILE.PUT_LINE(FND_FILE.LOG,'p_mode to trigger: ' || p_mode);
125       FND_FILE.PUT_LINE(FND_FILE.LOG,'where clause:' || pwhereclause);
126       FND_FILE.PUT_LINE(FND_FILE.LOG,'orderby clause:' || orderbyclause);
127       FND_FILE.PUT_LINE(FND_FILE.LOG,'partselect :' || partselect);
128       FND_FILE.PUT_LINE(FND_FILE.LOG,'partgroupby:' || partgroupby);
129       FND_FILE.PUT_LINE(FND_FILE.LOG,'amount type:' || p_amt_type);
130 
131     log(C_STATE_LEVEL, l_procedure_name, 'pwhereclause='||pwhereclause);
132     log(C_STATE_LEVEL, l_procedure_name, 'orderbyclause='||orderbyclause);
133     log(C_STATE_LEVEL, l_procedure_name, 'partselect='||partselect);
134     log(C_STATE_LEVEL, l_procedure_name, 'partgroupby='||partgroupby);
135     log(C_STATE_LEVEL, l_procedure_name, 'END');
136       RETURN(TRUE);
137   END BeforeReport;
138 
139   FUNCTION AfterReport RETURN BOOLEAN IS
140     l_procedure_name         VARCHAR2(100):='.AfterReport';
141     l_header_id number := 0;
142   BEGIN
143     l_procedure_name := g_path_name || l_procedure_name;
144     log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
145     log(C_STATE_LEVEL, l_procedure_name, 'p_mode='||p_mode);
146     log(C_STATE_LEVEL, l_procedure_name, 'p_del_preview='||p_del_preview);
147     IF p_mode = 'P' and p_del_preview = 'Y' THEN
148       Select max(header_id) --Bug 5933093
149       into l_header_id
150       from igi_cis_mth_ret_hdr_t
151       where period_name = p_period
152       and request_status_code = 'P';
153       log(C_STATE_LEVEL, l_procedure_name, 'l_header_id='||l_header_id);
154       delete from igi_cis_mth_ret_hdr_t where header_id = l_header_id;
155       log(C_STATE_LEVEL, l_procedure_name, 'Deleted '||SQL%ROWCOUNT||' rows from igi_cis_mth_ret_hdr_t');
156       delete from igi_cis_mth_ret_lines_t where header_id = l_header_id;
157       log(C_STATE_LEVEL, l_procedure_name, 'Deleted '||SQL%ROWCOUNT||' rows from igi_cis_mth_ret_lines_t');
158       delete from igi_cis_mth_ret_pay_t where header_id = l_header_id;
159       log(C_STATE_LEVEL, l_procedure_name, 'Deleted '||SQL%ROWCOUNT||' rows from igi_cis_mth_ret_pay_t');
160       commit;
161     END IF;
162     log(C_STATE_LEVEL, l_procedure_name, 'END');
163     RETURN(TRUE);
164   END AfterReport;
165 
166   FUNCTION get_p_supplier_from RETURN VARCHAR2 IS
167     l_procedure_name         VARCHAR2(100):='.get_p_supplier_from';
168   BEGIN
169         RETURN (p_supplier_from);
170   END get_p_supplier_from;
171 
172 
173   FUNCTION get_p_supplier_to RETURN VARCHAR2 IS
174     l_procedure_name         VARCHAR2(100):='.get_p_supplier_to';
175   BEGIN
176         RETURN (p_supplier_to);
177   END get_p_supplier_to;
178 
179   FUNCTION get_p_rep_mode RETURN VARCHAR2 IS
180     l_procedure_name         VARCHAR2(100):='.get_p_rep_mode';
181   BEGIN
182         RETURN(p_report_lev);
183   END get_p_rep_mode;
184 
185   FUNCTION get_period_start_date RETURN VARCHAR2 IS
186     l_procedure_name         VARCHAR2(100):='.get_period_start_date';
187       l_period_type ap_other_periods.period_type%TYPE;
188       l_start_date    ap_other_periods.start_date%TYPE;
189   BEGIN
190     l_procedure_name := g_path_name || l_procedure_name;
191     log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
192      l_period_type := fnd_profile.value('IGI_CIS2007_CALENDAR');
193 
194      SELECT start_date
195        INTO l_start_date
196        FROM ap_other_periods
197       WHERE period_type = l_period_type
198         AND period_name = p_period;
199 
200     log(C_STATE_LEVEL, l_procedure_name, 'END');
201      RETURN(l_start_date);
202   EXCEPTION
203       WHEN no_data_found THEN
204     log(C_STATE_LEVEL, l_procedure_name, 'EXCEPTION:'||SQLERRM);
205         RETURN(NULL);
206   END get_period_start_date;
207 
208 
209   FUNCTION get_period_end_date RETURN VARCHAR2 IS
210     l_procedure_name         VARCHAR2(100):='.get_period_end_date';
211             l_period_type ap_other_periods.period_type%TYPE;
212             l_end_date    ap_other_periods.start_date%TYPE;
213   BEGIN
214     l_procedure_name := g_path_name || l_procedure_name;
215     log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
216      l_period_type := fnd_profile.value('IGI_CIS2007_CALENDAR');
217 
218      SELECT end_date
219        INTO l_end_date
220        FROM ap_other_periods
221       WHERE period_type = l_period_type
222         AND period_name = p_period;
223 
224     log(C_STATE_LEVEL, l_procedure_name, 'END');
225       RETURN (l_end_date);
226   EXCEPTION
227      WHEN no_data_found THEN
228     log(C_STATE_LEVEL, l_procedure_name, 'EXCEPTION:'||SQLERRM);
229        RETURN(NULL);
230   END get_period_end_date;
231 
232 
233   FUNCTION get_print_type RETURN VARCHAR2 IS
234     l_procedure_name         VARCHAR2(100):='.get_print_type';
235           l_print_type igi_lookups.meaning%TYPE := null;
236   BEGIN
237     l_procedure_name := g_path_name || l_procedure_name;
241        FROM igi_lookups
238     log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
239      SELECT meaning
240        INTO l_print_type
242       WHERE lookup_type = 'IGI_CIS2007_PRINT_TYPES'
243         AND lookup_code = p_print_type;
244 
245     log(C_STATE_LEVEL, l_procedure_name, 'END');
246      RETURN (l_print_type);
247   EXCEPTION
248      WHEN no_data_found THEN
249     log(C_STATE_LEVEL, l_procedure_name, 'EXCEPTION:'||SQLERRM);
250         RETURN(NULL);
251   END get_print_type;
252 
253 
254   FUNCTION get_org_name RETURN VARCHAR2 IS
255     l_procedure_name         VARCHAR2(100):='.get_org_name';
256       l_org_id  hr_operating_units.organization_id%TYPE   := NULL ;
257       l_org_name hr_operating_units.name%TYPE := NULL ;
258   BEGIN
259     l_procedure_name := g_path_name || l_procedure_name;
260     log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
261   --   l_org_id := fnd_profile.value('ORG_ID');
262 
263      l_org_id:=  mo_global.get_current_org_id;
264      if(l_org_id is null) then
265       l_org_id := fnd_profile.value('ORG_ID');
266      end if;
267 
268      SELECT name
269        INTO l_org_name
270        FROM hr_operating_units
271       WHERE organization_id = l_org_id;
272 
273     log(C_STATE_LEVEL, l_procedure_name, 'END');
274      RETURN(l_org_name);
275   EXCEPTION
276      WHEN no_data_found THEN
277     log(C_STATE_LEVEL, l_procedure_name, 'EXCEPTION:'||SQLERRM);
278         RETURN(NULL);
279   END get_org_name;
280 
281   FUNCTION get_p_sort_by RETURN VARCHAR2 IS
282     l_procedure_name         VARCHAR2(100):='.get_p_sort_by';
283       l_sort_by igi_lookups.meaning%TYPE := NULL ;
284   BEGIN
285     l_procedure_name := g_path_name || l_procedure_name;
286     log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
287 
288      SELECT meaning
289        INTO l_sort_by
290        FROM igi_lookups
291       WHERE lookup_type='IGI_CIS2007_MTHR_SORT_COLS'
292         AND lookup_code=p_sort_by;
293 
294     log(C_STATE_LEVEL, l_procedure_name, 'END');
295      RETURN(l_sort_by);
296   EXCEPTION
297      WHEN no_data_found THEN
298     log(C_STATE_LEVEL, l_procedure_name, 'EXCEPTION:'||SQLERRM);
299         RETURN(NULL);
300   END get_p_sort_by;
301 
302 
303   FUNCTION get_p_report_title RETURN VARCHAR2 IS
304     l_procedure_name         VARCHAR2(100):='.get_p_report_title';
305       l_report_title igi_lookups.meaning%TYPE := NULL;
306   BEGIN
307     l_procedure_name := g_path_name || l_procedure_name;
308     log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
309       SELECT meaning
310         INTO l_report_title
311         FROM igi_lookups
312        WHERE lookup_type = 'IGI_CIS2007_NEW_REPORTS'
313          AND lookup_code = decode(p_mode||p_report_lev,'PS','IGIPMTPS',
314                                                      'PD','IGIPMTPD',
315                                                      'FD','IGIPMTRD',
316                                                      'FS','IGIPMTRS');
317     log(C_STATE_LEVEL, l_procedure_name, 'END');
318      RETURN(l_report_title);
319   EXCEPTION
320       WHEN no_data_found THEN
321     log(C_STATE_LEVEL, l_procedure_name, 'EXCEPTION:'||SQLERRM);
322          RETURN(NULL);
323   END get_p_report_title;
324 
325   FUNCTION get_tax_status(p_awt_group_code IN VARCHAR2) RETURN VARCHAR2 IS
326     l_procedure_name         VARCHAR2(100):='.get_tax_status';
327       l_net_group VARCHAR2(10) := NULL;
328       l_unmatch_group VARCHAR2(10) := NULL;
329       l_gross_group VARCHAR2(10) := NULL;
330       l_tax_status VARCHAR2(10) := NULL;
331   BEGIN
332     l_procedure_name := g_path_name || l_procedure_name;
333     log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
334     l_net_group := fnd_profile.VALUE('IGI_CIS2007_NET_WTH_GROUP');
335     l_unmatch_group := fnd_profile.VALUE('IGI_CIS2007_UNMATCHED_WTH_GROUP');
336     l_gross_group  := fnd_profile.VALUE('IGI_CIS2007_GROSS_WTH_GROUP');
337 
338     SELECT meaning
339        INTO l_tax_status
340        FROM igi_lookups
341        WHERE lookup_type = 'IGI_CIS2007_TAX_STATUS'
342        AND lookup_code = decode(nvl(p_awt_group_code,' '),l_gross_group,'G',
343                                                  l_net_group,'N',
344                                                  l_unmatch_group,'U');
345     /*if p_awt_group_code = l_gross_group then
346  *       l_tax_status := 'Gross';
347  *           Elsif p_awt_group_code = l_net_group then
348  *                 l_tax_status := 'Net';
349  *                     Elsif p_awt_group_code = l_unmatch_group then
350  *                           l_tax_status := 'Unmatched';
351  *                               End if; */
352     log(C_STATE_LEVEL, l_procedure_name, 'END');
353     RETURN(l_tax_status);
354   EXCEPTION
355     WHEN OTHERS THEN
356     log(C_STATE_LEVEL, l_procedure_name, 'EXCEPTION:'||SQLERRM);
357       RETURN(NULL);
358   END get_tax_status;
359 
360   --Function Added for ER6137652
361   FUNCTION get_p_amt_type RETURN VARCHAR2 IS
362     l_procedure_name         VARCHAR2(100):='.get_p_amt_type';
363       l_amt_type igi_lookups.meaning%TYPE := NULL;
364   BEGIN
365     l_procedure_name := g_path_name || l_procedure_name;
366     log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
367       SELECT meaning
368         INTO l_amt_type
369         FROM igi_lookups
370        WHERE lookup_type = 'IGI_CIS2007_MTH_RET_AMT_TYPE'
371          AND lookup_code = nvl(p_amt_type,'P');
372     log(C_STATE_LEVEL, l_procedure_name, 'END');
373      RETURN(l_amt_type);
374   EXCEPTION
375       WHEN no_data_found THEN
376     log(C_STATE_LEVEL, l_procedure_name, 'EXCEPTION:'||SQLERRM);
380 init;
377          RETURN(NULL);
378   END get_p_amt_type;
379 BEGIN
381 END igi_cis_igipmthp_pkg ;