[Home] [Help]
PACKAGE BODY: APPS.IGI_CIS2007_IGIPMTHP_PKG
Source
1 package body IGI_CIS2007_IGIPMTHP_PKG AS
2 -- $Header: IGIPMTHB.pls 120.2 2011/10/14 05:50:10 dramired ship $
3 -- Private type declarations
4 procedure populate_history (p_old_header_id number, p_request_status_code varchar2)
5 is
6 l_new_header_id number;
7 begin
8 select IGI_CIS_MTH_RET_HDR_T_S.nextval into l_new_header_id from dual;
9 insert into igi_cis_mth_ret_hdr_h
10 (HEADER_ID,
11 ORG_ID,
12 CIS_SENDER_ID,
13 TAX_OFFICE_NUMBER,
14 PAYE_REFERENCE,
15 REQUEST_ID,
16 REQUEST_STATUS_CODE,
17 PROGRAM_APPLICATION_ID,
18 PROGRAM_ID,
19 PROGRAM_LOGIN_ID,
20 UNIQUE_TAX_REFERENCE_NUM,
21 ACCOUNTS_OFFICE_REFERENCE,
22 PERIOD_NAME,
23 PERIOD_ENDING_DATE,
24 NIL_RETURN_FLAG,
25 EMPLOYMENT_STATUS_FLAG,
26 SUBCONT_VERIFY_FLAG,
27 INFORMATION_CORRECT_FLAG,
28 INACTIVITY_INDICATOR,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_LOGIN,
32 CREATION_DATE,
33 CREATED_BY)
34 select l_new_header_id,
35 ORG_ID,
36 CIS_SENDER_ID,
37 TAX_OFFICE_NUMBER,
38 PAYE_REFERENCE,
39 REQUEST_ID,
40 p_request_status_code,
41 PROGRAM_APPLICATION_ID,
42 PROGRAM_ID,
43 PROGRAM_LOGIN_ID,
44 UNIQUE_TAX_REFERENCE_NUM,
45 ACCOUNTS_OFFICE_REFERENCE,
46 PERIOD_NAME,
47 PERIOD_ENDING_DATE,
48 NIL_RETURN_FLAG,
49 EMPLOYMENT_STATUS_FLAG,
50 SUBCONT_VERIFY_FLAG,
51 INFORMATION_CORRECT_FLAG,
52 INACTIVITY_INDICATOR,
53 LAST_UPDATE_DATE,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_LOGIN,
56 CREATION_DATE,
57 CREATED_BY
58 from igi_cis_mth_ret_hdr_h
59 where HEADER_ID = p_old_header_id ;
60
61 insert into igi_cis_mth_ret_lines_h
62 (HEADER_ID,
63 ORG_ID,
64 VENDOR_ID,
65 VENDOR_NAME,
66 VENDOR_TYPE_LOOKUP_CODE,
67 FIRST_NAME,
68 SECOND_NAME,
69 LAST_NAME,
70 SALUTATION,
71 TRADING_NAME,
72 UNMATCHED_TAX_FLAG,
73 UNIQUE_TAX_REFERENCE_NUM,
74 COMPANY_REGISTRATION_NUMBER,
75 NATIONAL_INSURANCE_NUMBER,
76 VERIFICATION_NUMBER,
77 TOTAL_PAYMENTS,
78 LABOUR_COST,
79 MATERIAL_COST,
80 TOTAL_DEDUCTIONS,
81 CIS_TAX,
82 DISCOUNT_AMOUNT,
83 LAST_UPDATE_DATE,
84 LAST_UPDATED_BY,
85 LAST_UPDATE_LOGIN,
86 CREATION_DATE,
87 CREATED_BY)
88 select l_new_header_id,
89 ORG_ID,
90 VENDOR_ID,
91 VENDOR_NAME,
92 VENDOR_TYPE_LOOKUP_CODE,
93 FIRST_NAME,
94 SECOND_NAME,
95 LAST_NAME,
96 SALUTATION,
97 TRADING_NAME,
98 UNMATCHED_TAX_FLAG,
99 UNIQUE_TAX_REFERENCE_NUM,
100 COMPANY_REGISTRATION_NUMBER,
101 NATIONAL_INSURANCE_NUMBER,
102 VERIFICATION_NUMBER,
103 TOTAL_PAYMENTS,
104 LABOUR_COST,
105 MATERIAL_COST,
106 TOTAL_DEDUCTIONS,
107 CIS_TAX,
108 DISCOUNT_AMOUNT,
109 LAST_UPDATE_DATE,
110 LAST_UPDATED_BY,
111 LAST_UPDATE_LOGIN,
112 CREATION_DATE,
113 CREATED_BY
114 from igi_cis_mth_ret_lines_h
115 where HEADER_ID = p_old_header_id ;
116
117 insert into igi_cis_mth_ret_pay_h
118 (HEADER_ID,
119 ORG_ID,
120 VENDOR_ID,
121 CHILD_VENDOR_ID,
122 INVOICE_ID,
123 INVOICE_PAYMENT_ID,
124 AMOUNT,
125 LAST_UPDATE_DATE,
126 LAST_UPDATED_BY,
127 LAST_UPDATE_LOGIN,
128 CREATION_DATE,
129 CREATED_BY,
130 LABOUR_COST,
131 MATERIAL_COST,
132 TOTAL_DEDUCTIONS,
133 CIS_TAX,
134 DISCOUNT_AMOUNT)
135 Select l_new_header_id,
136 ORG_ID,
137 VENDOR_ID,
138 CHILD_VENDOR_ID,
139 INVOICE_ID,
140 INVOICE_PAYMENT_ID,
141 AMOUNT,
142 LAST_UPDATE_DATE,
143 LAST_UPDATED_BY,
144 LAST_UPDATE_LOGIN,
145 CREATION_DATE,
146 CREATED_BY,
147 LABOUR_COST,
148 MATERIAL_COST,
149 TOTAL_DEDUCTIONS,
150 CIS_TAX,
151 DISCOUNT_AMOUNT
152 from igi_cis_mth_ret_pay_h
153 where HEADER_ID = p_old_header_id ;
154 commit;
155 end;
156
157 PROCEDURE pr_audit_update(p_in_header_id IN NUMBER,p_in_completion_code IN VARCHAR2)
158 IS
159 l_cnt_header_id number;
160 l_temp_count number :=0;
161 l_hist_count number :=0;
162 BEGIN
163
164 --Following block added for bug # 6074547
165 begin
166 select count(header_id) into l_temp_count
167 from igi_cis_mth_ret_hdr_t where header_id = p_in_header_id;
168
169 select count(header_id) into l_hist_count
170 from igi_cis_mth_ret_hdr_h where header_id = p_in_header_id;
171
172 if l_temp_count = 0 and l_hist_count > 0 then
173 populate_history(p_in_header_id,p_in_completion_code);
174 return;
175 end if;
176 end;
177
178 update igi_cis_mth_ret_hdr_t
179 set REQUEST_STATUS_CODE = p_in_completion_code,
180 PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID(),
181 PROGRAM_ID = FND_GLOBAL.CONC_PROGRAM_ID(),
182 PROGRAM_LOGIN_ID = FND_GLOBAL.CONC_LOGIN_ID()
183 where HEADER_ID = p_in_header_id;
184 BEGIN
185 select count(header_id)
186 into l_cnt_header_id
187 from igi_cis_mth_ret_hdr_h
188 where header_id = p_in_header_id
189 group by header_id;
190 EXCEPTION
191 when no_data_found then
192 l_cnt_header_id := 0;
193 END;
194 IF (l_cnt_header_id = 0) THEN
195 insert into igi_cis_mth_ret_hdr_h
196 (HEADER_ID,
197 ORG_ID,
198 CIS_SENDER_ID,
199 TAX_OFFICE_NUMBER,
200 PAYE_REFERENCE,
201 REQUEST_ID,
202 REQUEST_STATUS_CODE,
203 PROGRAM_APPLICATION_ID,
204 PROGRAM_ID,
205 PROGRAM_LOGIN_ID,
206 UNIQUE_TAX_REFERENCE_NUM,
207 ACCOUNTS_OFFICE_REFERENCE,
208 PERIOD_NAME,
209 PERIOD_ENDING_DATE,
210 NIL_RETURN_FLAG,
211 EMPLOYMENT_STATUS_FLAG,
212 SUBCONT_VERIFY_FLAG,
213 INFORMATION_CORRECT_FLAG,
214 INACTIVITY_INDICATOR,
215 LAST_UPDATE_DATE,
216 LAST_UPDATED_BY,
217 LAST_UPDATE_LOGIN,
218 CREATION_DATE,
219 CREATED_BY)
220 select HEADER_ID,
221 ORG_ID,
222 CIS_SENDER_ID,
223 TAX_OFFICE_NUMBER,
224 PAYE_REFERENCE,
225 REQUEST_ID,
226 REQUEST_STATUS_CODE,
227 PROGRAM_APPLICATION_ID,
228 PROGRAM_ID,
229 PROGRAM_LOGIN_ID,
230 UNIQUE_TAX_REFERENCE_NUM,
231 ACCOUNTS_OFFICE_REFERENCE,
232 PERIOD_NAME,
233 PERIOD_ENDING_DATE,
234 NIL_RETURN_FLAG,
235 EMPLOYMENT_STATUS_FLAG,
236 SUBCONT_VERIFY_FLAG,
237 INFORMATION_CORRECT_FLAG,
238 INACTIVITY_INDICATOR,
239 LAST_UPDATE_DATE,
240 LAST_UPDATED_BY,
241 LAST_UPDATE_LOGIN,
242 CREATION_DATE,
243 CREATED_BY
244 from igi_cis_mth_ret_hdr_t
245 where HEADER_ID = p_in_header_id ;
246 insert into igi_cis_mth_ret_lines_h
247 (HEADER_ID,
248 ORG_ID,
249 VENDOR_ID,
250 VENDOR_NAME,
251 VENDOR_TYPE_LOOKUP_CODE,
252 FIRST_NAME,
253 SECOND_NAME,
254 LAST_NAME,
255 SALUTATION,
256 TRADING_NAME,
257 UNMATCHED_TAX_FLAG,
258 UNIQUE_TAX_REFERENCE_NUM,
259 COMPANY_REGISTRATION_NUMBER,
260 NATIONAL_INSURANCE_NUMBER,
261 VERIFICATION_NUMBER,
262 TOTAL_PAYMENTS,
263 LABOUR_COST,
264 MATERIAL_COST,
265 TOTAL_DEDUCTIONS,
266 CIS_TAX,
267 DISCOUNT_AMOUNT,
268 LAST_UPDATE_DATE,
269 LAST_UPDATED_BY,
270 LAST_UPDATE_LOGIN,
271 CREATION_DATE,
272 CREATED_BY)
273 select HEADER_ID,
274 ORG_ID,
275 VENDOR_ID,
276 VENDOR_NAME,
277 VENDOR_TYPE_LOOKUP_CODE,
278 FIRST_NAME,
279 SECOND_NAME,
280 LAST_NAME,
281 SALUTATION,
282 TRADING_NAME,
283 UNMATCHED_TAX_FLAG,
284 UNIQUE_TAX_REFERENCE_NUM,
285 COMPANY_REGISTRATION_NUMBER,
286 NATIONAL_INSURANCE_NUMBER,
287 VERIFICATION_NUMBER,
288 TOTAL_PAYMENTS,
289 LABOUR_COST,
290 MATERIAL_COST,
291 TOTAL_DEDUCTIONS,
292 CIS_TAX,
293 DISCOUNT_AMOUNT,
294 LAST_UPDATE_DATE,
295 LAST_UPDATED_BY,
296 LAST_UPDATE_LOGIN,
297 CREATION_DATE,
298 CREATED_BY
299 from igi_cis_mth_ret_lines_t
300 where HEADER_ID = p_in_header_id ;
301 insert into igi_cis_mth_ret_pay_h
302 (HEADER_ID,
303 ORG_ID,
304 VENDOR_ID,
305 CHILD_VENDOR_ID,
306 INVOICE_ID,
307 INVOICE_PAYMENT_ID,
308 AMOUNT,
309 LAST_UPDATE_DATE,
310 LAST_UPDATED_BY,
311 LAST_UPDATE_LOGIN,
312 CREATION_DATE,
313 CREATED_BY,
314 LABOUR_COST,
315 MATERIAL_COST,
316 TOTAL_DEDUCTIONS,
317 CIS_TAX,
318 DISCOUNT_AMOUNT)
319 Select HEADER_ID,
320 ORG_ID,
321 VENDOR_ID,
322 CHILD_VENDOR_ID,
323 INVOICE_ID,
324 INVOICE_PAYMENT_ID,
325 AMOUNT,
326 LAST_UPDATE_DATE,
327 LAST_UPDATED_BY,
328 LAST_UPDATE_LOGIN,
329 CREATION_DATE,
330 CREATED_BY,
331 LABOUR_COST,
332 MATERIAL_COST,
333 TOTAL_DEDUCTIONS,
334 CIS_TAX,
335 DISCOUNT_AMOUNT
336 from igi_cis_mth_ret_pay_t
337 where HEADER_ID = p_in_header_id ;
338 delete from igi_cis_mth_ret_hdr_t where header_id = p_in_header_id;
339 delete from igi_cis_mth_ret_lines_t where header_id = p_in_header_id;
340 delete from igi_cis_mth_ret_pay_t where header_id = p_in_header_id;
341 commit;
342 ELSE
343 null;
344 END IF;
345 EXCEPTION
346 when others then
347 --raise_application_error('-20301','Error in insertion of audit table ');
348 null;
349 end pr_audit_update ;
350 end IGI_CIS2007_IGIPMTHP_PKG;