[Home] [Help]
PACKAGE BODY: APPS.IGI_CIS_IGIPMTHP_PKG
Source
1 PACKAGE BODY igi_cis_igipmthp_pkg AS
2 -- $Header: igipmthpb.pls 120.0.12000000.2 2007/07/11 12:34:28 vensubra noship $
3 -- $Header: igipmthpb.pls 120.0.12000000.2 2007/07/11 12:34:28 vensubra noship $
4 FUNCTION BeforeReport RETURN BOOLEAN IS
5 SINGLE_QUOTE constant varchar2(3) := '''';
6 TWO_SINGLE_QUOTE constant varchar2(6) := '''''';
7 BEGIN
8 pwhereclause := ' ';
9 tableclause := ' ';
10
11 --bug 6124461 AP_SUPPLIERS table has been included in the query
12 IF (p_supplier_from IS NOT NULL) THEN
13 tableclause := 'AP_SUPPLIERS po_ven,';
14 pwhereclause := pwhereclause || ' and lines.vendor_id = po_ven.vendor_id and po_ven.vendor_name >= ''' || replace(p_supplier_from, SINGLE_QUOTE, TWO_SINGLE_QUOTE) || '''';
15 END IF;
16
17 IF (p_supplier_to IS NOT NULL) THEN
18 IF (p_supplier_from IS NOT NULL) THEN
19 pwhereclause := pwhereclause || ' and po_ven.vendor_name <= ''' || replace(p_supplier_to, SINGLE_QUOTE, TWO_SINGLE_QUOTE) || '''';
20 ELSE
21 tableclause := 'AP_SUPPLIERS po_ven,';
22 pwhereclause := pwhereclause || ' and lines.vendor_id = po_ven.vendor_id and po_ven.vendor_name <= ''' || replace(p_supplier_to, SINGLE_QUOTE, TWO_SINGLE_QUOTE) || '''';
23 END IF;
24 END IF;
25 --end bug 6124461
26 --Bug 5933093 start
27 IF (p_period IS NOT NULL ) THEN
28 pwhereclause := pwhereclause || ' AND hdr.period_name = ''' || p_period || '''';
29 IF p_mode = 'P' THEN
30 pwhereclause := pwhereclause || ' AND hdr.header_id = (select min(header_id) from IGI_CIS_MTH_RET_HDR_T where period_name = ''' || p_period || '''';
31 pwhereclause := pwhereclause || ' AND request_status_code = ''P'')';
32 ELSE
33 pwhereclause := pwhereclause || ' AND hdr.header_id = (select min(header_id) from IGI_CIS_MTH_RET_HDR_H where period_name = ''' || p_period || '''';
34 pwhereclause := pwhereclause || ' AND request_status_code = ''C'')';
35 END IF;
36 END IF ;
37 --Bug 5933093 end
38
39 IF p_mode = 'P' THEN
40 pwhereclause := pwhereclause || ' AND request_status_code = ''P''';
41 tableclause := tableclause||' IGI_CIS_MTH_RET_HDR_T hdr, IGI_CIS_MTH_RET_LINES_T lines, IGI_CIS_MTH_RET_PAY_T pay ';
42 ELSE
43 pwhereclause := pwhereclause || ' AND request_status_code = ''C''';
44 tableclause := tableclause||' IGI_CIS_MTH_RET_HDR_H hdr, IGI_CIS_MTH_RET_LINES_H lines, IGI_CIS_MTH_RET_PAY_H pay ';
45 END IF ;
46
47 IF (p_sort_by IS NOT NULL ) THEN
48 IF p_sort_by = 'VENDOR_NAME' Then
49 orderbyclause := ' ORDER BY ' || p_sort_by;
50 ELSE
51 orderbyclause := ' ORDER BY ' || p_sort_by || ', lines.vendor_name' ;
52 END IF;
53 ELSE
54 orderbyclause := ' ORDER BY lines.vendor_name';
55 END IF;
56 IF p_report_lev = 'S' then
57 partselect := ' '''' invoice_num, ';
58 partgroupby := ' ';
59 Elsif p_report_lev ='D' then
60 partselect := 'nvl((select invoice_num from ap_invoices where pay.invoice_id = invoice_id),'''') invoice_num, ';
61 partgroupby := ',pay.invoice_id ';
62 End if;
63
64 --ER6137652 Start
65 IF nvl(p_amt_type,'P') = 'P' THEN
66 pwhereclause := pwhereclause ||' and ((nvl(lines.total_payments,0) + nvl(lines.total_deductions,0)) >=0 and
67 nvl(lines.material_cost,0) >= 0 and
68 nvl(lines.labour_cost,0) >= 0 and
69 nvl(lines.total_deductions,0) >= 0) ';
70 ELSIF nvl(p_amt_type,'P') = 'N' THEN
71 pwhereclause := pwhereclause || ' and ((nvl(lines.total_payments,0) + nvl(lines.total_deductions,0)) < 0 or
72 nvl(lines.material_cost,0) < 0 or
73 nvl(lines.labour_cost,0) < 0 or
74 nvl(lines.total_deductions,0) < 0) ';
75 END IF;
76 --ER6137652 End
77
78 -- for debugging
79 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_mode to trigger: ' || p_mode);
80 FND_FILE.PUT_LINE(FND_FILE.LOG,'where clause:' || pwhereclause);
81 FND_FILE.PUT_LINE(FND_FILE.LOG,'orderby clause:' || orderbyclause);
82 FND_FILE.PUT_LINE(FND_FILE.LOG,'partselect :' || partselect);
83 FND_FILE.PUT_LINE(FND_FILE.LOG,'partgroupby:' || partgroupby);
84 FND_FILE.PUT_LINE(FND_FILE.LOG,'amount type:' || p_amt_type);
85
86 RETURN(TRUE);
87 END BeforeReport;
88
89 FUNCTION AfterReport RETURN BOOLEAN IS
90 l_header_id number := 0;
91 BEGIN
92 IF p_mode = 'P' and p_del_preview = 'Y' THEN
93 Select min(header_id) --Bug 5933093
94 into l_header_id
95 from igi_cis_mth_ret_hdr_t
96 where period_name = p_period
97 and request_status_code = 'P';
98 delete from igi_cis_mth_ret_hdr_t where header_id = l_header_id;
99 delete from igi_cis_mth_ret_lines_t where header_id = l_header_id;
100 delete from igi_cis_mth_ret_pay_t where header_id = l_header_id;
101 commit;
102 END IF;
103 RETURN(TRUE);
104 END AfterReport;
105
106 FUNCTION get_p_supplier_from RETURN VARCHAR2 IS
107 BEGIN
108 RETURN (p_supplier_from);
109 END get_p_supplier_from;
110
111
112 FUNCTION get_p_supplier_to RETURN VARCHAR2 IS
113 BEGIN
114 RETURN (p_supplier_to);
115 END get_p_supplier_to;
116
117 FUNCTION get_p_rep_mode RETURN VARCHAR2 IS
118 BEGIN
119 RETURN(p_report_lev);
120 END get_p_rep_mode;
121
122 FUNCTION get_period_start_date RETURN VARCHAR2 IS
123 l_period_type ap_other_periods.period_type%TYPE;
124 l_start_date ap_other_periods.start_date%TYPE;
125 BEGIN
126 l_period_type := fnd_profile.value('IGI_CIS2007_CALENDAR');
127
128 SELECT start_date
129 INTO l_start_date
130 FROM ap_other_periods
131 WHERE period_type = l_period_type
132 AND period_name = p_period;
133
134 RETURN(l_start_date);
135 EXCEPTION
136 WHEN no_data_found THEN
137 RETURN(NULL);
138 END get_period_start_date;
139
140
141 FUNCTION get_period_end_date RETURN VARCHAR2 IS
142 l_period_type ap_other_periods.period_type%TYPE;
143 l_end_date ap_other_periods.start_date%TYPE;
144 BEGIN
145 l_period_type := fnd_profile.value('IGI_CIS2007_CALENDAR');
146
147 SELECT end_date
148 INTO l_end_date
149 FROM ap_other_periods
150 WHERE period_type = l_period_type
151 AND period_name = p_period;
152
153 RETURN (l_end_date);
154 EXCEPTION
155 WHEN no_data_found THEN
156 RETURN(NULL);
157 END get_period_end_date;
158
159
160 FUNCTION get_print_type RETURN VARCHAR2 IS
161 l_print_type igi_lookups.meaning%TYPE := null;
162 BEGIN
163 SELECT meaning
164 INTO l_print_type
165 FROM igi_lookups
166 WHERE lookup_type = 'IGI_CIS2007_PRINT_TYPES'
167 AND lookup_code = p_print_type;
168
169 RETURN (l_print_type);
170 EXCEPTION
171 WHEN no_data_found THEN
172 RETURN(NULL);
173 END get_print_type;
174
175
176 FUNCTION get_org_name RETURN VARCHAR2 IS
177 l_org_id hr_operating_units.organization_id%TYPE := NULL ;
178 l_org_name hr_operating_units.name%TYPE := NULL ;
179 BEGIN
180 l_org_id := fnd_profile.value('ORG_ID');
181
182 SELECT name
183 INTO l_org_name
184 FROM hr_operating_units
185 WHERE organization_id = l_org_id;
186
187 RETURN(l_org_name);
188 EXCEPTION
189 WHEN no_data_found THEN
190 RETURN(NULL);
191 END get_org_name;
192
193 FUNCTION get_p_sort_by RETURN VARCHAR2 IS
194 l_sort_by igi_lookups.meaning%TYPE := NULL ;
195 BEGIN
196
197 SELECT meaning
198 INTO l_sort_by
199 FROM igi_lookups
200 WHERE lookup_type='IGI_CIS2007_MTHR_SORT_COLS'
201 AND lookup_code=p_sort_by;
202
203 RETURN(l_sort_by);
204 EXCEPTION
205 WHEN no_data_found THEN
206 RETURN(NULL);
207 END get_p_sort_by;
208
209
210 FUNCTION get_p_report_title RETURN VARCHAR2 IS
211 l_report_title igi_lookups.meaning%TYPE := NULL;
212 BEGIN
213 SELECT meaning
214 INTO l_report_title
215 FROM igi_lookups
216 WHERE lookup_type = 'IGI_CIS2007_NEW_REPORTS'
217 AND lookup_code = decode(p_mode||p_report_lev,'PS','IGIPMTPS',
218 'PD','IGIPMTPD',
219 'FD','IGIPMTRD',
220 'FS','IGIPMTRS');
221 RETURN(l_report_title);
222 EXCEPTION
223 WHEN no_data_found THEN
224 RETURN(NULL);
225 END get_p_report_title;
226
227 FUNCTION get_tax_status(p_awt_group_code IN VARCHAR2) RETURN VARCHAR2 IS
228 l_net_group VARCHAR2(10) := NULL;
229 l_unmatch_group VARCHAR2(10) := NULL;
230 l_gross_group VARCHAR2(10) := NULL;
231 l_tax_status VARCHAR2(10) := NULL;
232 BEGIN
233 l_net_group := fnd_profile.VALUE('IGI_CIS2007_NET_WTH_GROUP');
234 l_unmatch_group := fnd_profile.VALUE('IGI_CIS2007_UNMATCHED_WTH_GROUP');
235 l_gross_group := fnd_profile.VALUE('IGI_CIS2007_GROSS_WTH_GROUP');
236
237 SELECT meaning
238 INTO l_tax_status
239 FROM igi_lookups
240 WHERE lookup_type = 'IGI_CIS2007_TAX_STATUS'
241 AND lookup_code = decode(nvl(p_awt_group_code,' '),l_gross_group,'G',
242 l_net_group,'N',
243 l_unmatch_group,'U');
244 /*if p_awt_group_code = l_gross_group then
245 l_tax_status := 'Gross';
246 Elsif p_awt_group_code = l_net_group then
247 l_tax_status := 'Net';
248 Elsif p_awt_group_code = l_unmatch_group then
249 l_tax_status := 'Unmatched';
250 End if; */
251 RETURN(l_tax_status);
252 EXCEPTION
253 WHEN OTHERS THEN
254 RETURN(NULL);
255 END get_tax_status;
256
257 --Function Added for ER6137652
258 FUNCTION get_p_amt_type RETURN VARCHAR2 IS
259 l_amt_type igi_lookups.meaning%TYPE := NULL;
260 BEGIN
261 SELECT meaning
262 INTO l_amt_type
263 FROM igi_lookups
264 WHERE lookup_type = 'IGI_CIS2007_MTH_RET_AMT_TYPE'
265 AND lookup_code = nvl(p_amt_type,'P');
266 RETURN(l_amt_type);
267 EXCEPTION
268 WHEN no_data_found THEN
269 RETURN(NULL);
270 END get_p_amt_type;
271
272 END igi_cis_igipmthp_pkg ;