[Home] [Help]
PACKAGE BODY: APPS.IGI_CIS2007_IGIPMTHP_PKG
Source
1 package body IGI_CIS2007_IGIPMTHP_PKG AS
2 -- $Header: IGIPMTHB.pls 120.0.12000000.2 2007/07/16 12:35:51 vensubra noship $
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 DISCOUNT_AMOUNT,
82 LAST_UPDATE_DATE,
83 LAST_UPDATED_BY,
84 LAST_UPDATE_LOGIN,
85 CREATION_DATE,
86 CREATED_BY)
87 select l_new_header_id,
88 ORG_ID,
89 VENDOR_ID,
90 VENDOR_NAME,
91 VENDOR_TYPE_LOOKUP_CODE,
92 FIRST_NAME,
93 SECOND_NAME,
94 LAST_NAME,
95 SALUTATION,
96 TRADING_NAME,
97 UNMATCHED_TAX_FLAG,
98 UNIQUE_TAX_REFERENCE_NUM,
99 COMPANY_REGISTRATION_NUMBER,
100 NATIONAL_INSURANCE_NUMBER,
101 VERIFICATION_NUMBER,
102 TOTAL_PAYMENTS,
103 LABOUR_COST,
104 MATERIAL_COST,
105 TOTAL_DEDUCTIONS,
106 DISCOUNT_AMOUNT,
107 LAST_UPDATE_DATE,
108 LAST_UPDATED_BY,
109 LAST_UPDATE_LOGIN,
110 CREATION_DATE,
111 CREATED_BY
112 from igi_cis_mth_ret_lines_h
113 where HEADER_ID = p_old_header_id ;
114
115 insert into igi_cis_mth_ret_pay_h
116 (HEADER_ID,
117 ORG_ID,
118 VENDOR_ID,
119 CHILD_VENDOR_ID,
120 INVOICE_ID,
121 INVOICE_PAYMENT_ID,
122 AMOUNT,
123 LAST_UPDATE_DATE,
124 LAST_UPDATED_BY,
125 LAST_UPDATE_LOGIN,
126 CREATION_DATE,
127 CREATED_BY,
128 LABOUR_COST,
129 MATERIAL_COST,
130 TOTAL_DEDUCTIONS,
131 DISCOUNT_AMOUNT)
132 Select l_new_header_id,
133 ORG_ID,
134 VENDOR_ID,
135 CHILD_VENDOR_ID,
136 INVOICE_ID,
137 INVOICE_PAYMENT_ID,
138 AMOUNT,
139 LAST_UPDATE_DATE,
140 LAST_UPDATED_BY,
141 LAST_UPDATE_LOGIN,
142 CREATION_DATE,
143 CREATED_BY,
144 LABOUR_COST,
145 MATERIAL_COST,
146 TOTAL_DEDUCTIONS,
147 DISCOUNT_AMOUNT
148 from igi_cis_mth_ret_pay_h
149 where HEADER_ID = p_old_header_id ;
150 commit;
151 end;
152
153 PROCEDURE pr_audit_update(p_in_header_id IN NUMBER,p_in_completion_code IN VARCHAR2)
154 IS
155 l_cnt_header_id number;
156 l_temp_count number :=0;
157 l_hist_count number :=0;
158 BEGIN
159
160 --Following block added for bug # 6074547
161 begin
162 select count(header_id) into l_temp_count
163 from igi_cis_mth_ret_hdr_t where header_id = p_in_header_id;
164
165 select count(header_id) into l_hist_count
166 from igi_cis_mth_ret_hdr_h where header_id = p_in_header_id;
167
168 if l_temp_count = 0 and l_hist_count > 0 then
169 populate_history(p_in_header_id,p_in_completion_code);
170 return;
171 end if;
172 end;
173
174 update igi_cis_mth_ret_hdr_t
175 set REQUEST_STATUS_CODE = p_in_completion_code,
176 PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID(),
177 PROGRAM_ID = FND_GLOBAL.CONC_PROGRAM_ID(),
178 PROGRAM_LOGIN_ID = FND_GLOBAL.CONC_LOGIN_ID()
179 where HEADER_ID = p_in_header_id;
180 BEGIN
181 select count(header_id)
182 into l_cnt_header_id
183 from igi_cis_mth_ret_hdr_h
184 where header_id = p_in_header_id
185 group by header_id;
186 EXCEPTION
187 when no_data_found then
188 l_cnt_header_id := 0;
189 END;
190 IF (l_cnt_header_id = 0) THEN
191 insert into igi_cis_mth_ret_hdr_h
192 (HEADER_ID,
193 ORG_ID,
194 CIS_SENDER_ID,
195 TAX_OFFICE_NUMBER,
196 PAYE_REFERENCE,
197 REQUEST_ID,
198 REQUEST_STATUS_CODE,
199 PROGRAM_APPLICATION_ID,
200 PROGRAM_ID,
201 PROGRAM_LOGIN_ID,
202 UNIQUE_TAX_REFERENCE_NUM,
203 ACCOUNTS_OFFICE_REFERENCE,
204 PERIOD_NAME,
205 PERIOD_ENDING_DATE,
206 NIL_RETURN_FLAG,
207 EMPLOYMENT_STATUS_FLAG,
208 SUBCONT_VERIFY_FLAG,
209 INFORMATION_CORRECT_FLAG,
210 INACTIVITY_INDICATOR,
211 LAST_UPDATE_DATE,
212 LAST_UPDATED_BY,
213 LAST_UPDATE_LOGIN,
214 CREATION_DATE,
215 CREATED_BY)
216 select HEADER_ID,
217 ORG_ID,
218 CIS_SENDER_ID,
219 TAX_OFFICE_NUMBER,
220 PAYE_REFERENCE,
221 REQUEST_ID,
222 REQUEST_STATUS_CODE,
223 PROGRAM_APPLICATION_ID,
224 PROGRAM_ID,
225 PROGRAM_LOGIN_ID,
226 UNIQUE_TAX_REFERENCE_NUM,
227 ACCOUNTS_OFFICE_REFERENCE,
228 PERIOD_NAME,
229 PERIOD_ENDING_DATE,
230 NIL_RETURN_FLAG,
231 EMPLOYMENT_STATUS_FLAG,
232 SUBCONT_VERIFY_FLAG,
233 INFORMATION_CORRECT_FLAG,
234 INACTIVITY_INDICATOR,
235 LAST_UPDATE_DATE,
236 LAST_UPDATED_BY,
237 LAST_UPDATE_LOGIN,
238 CREATION_DATE,
239 CREATED_BY
240 from igi_cis_mth_ret_hdr_t
241 where HEADER_ID = p_in_header_id ;
242 insert into igi_cis_mth_ret_lines_h
243 (HEADER_ID,
244 ORG_ID,
245 VENDOR_ID,
246 VENDOR_NAME,
247 VENDOR_TYPE_LOOKUP_CODE,
248 FIRST_NAME,
249 SECOND_NAME,
250 LAST_NAME,
251 SALUTATION,
252 TRADING_NAME,
253 UNMATCHED_TAX_FLAG,
254 UNIQUE_TAX_REFERENCE_NUM,
255 COMPANY_REGISTRATION_NUMBER,
256 NATIONAL_INSURANCE_NUMBER,
257 VERIFICATION_NUMBER,
258 TOTAL_PAYMENTS,
259 LABOUR_COST,
260 MATERIAL_COST,
261 TOTAL_DEDUCTIONS,
262 DISCOUNT_AMOUNT,
263 LAST_UPDATE_DATE,
264 LAST_UPDATED_BY,
265 LAST_UPDATE_LOGIN,
266 CREATION_DATE,
267 CREATED_BY)
268 select HEADER_ID,
269 ORG_ID,
270 VENDOR_ID,
271 VENDOR_NAME,
272 VENDOR_TYPE_LOOKUP_CODE,
273 FIRST_NAME,
274 SECOND_NAME,
275 LAST_NAME,
276 SALUTATION,
277 TRADING_NAME,
278 UNMATCHED_TAX_FLAG,
279 UNIQUE_TAX_REFERENCE_NUM,
280 COMPANY_REGISTRATION_NUMBER,
281 NATIONAL_INSURANCE_NUMBER,
282 VERIFICATION_NUMBER,
283 TOTAL_PAYMENTS,
284 LABOUR_COST,
285 MATERIAL_COST,
286 TOTAL_DEDUCTIONS,
287 DISCOUNT_AMOUNT,
288 LAST_UPDATE_DATE,
289 LAST_UPDATED_BY,
290 LAST_UPDATE_LOGIN,
291 CREATION_DATE,
292 CREATED_BY
293 from igi_cis_mth_ret_lines_t
294 where HEADER_ID = p_in_header_id ;
295 insert into igi_cis_mth_ret_pay_h
296 (HEADER_ID,
297 ORG_ID,
298 VENDOR_ID,
299 CHILD_VENDOR_ID,
300 INVOICE_ID,
301 INVOICE_PAYMENT_ID,
302 AMOUNT,
303 LAST_UPDATE_DATE,
304 LAST_UPDATED_BY,
305 LAST_UPDATE_LOGIN,
306 CREATION_DATE,
307 CREATED_BY,
308 LABOUR_COST,
309 MATERIAL_COST,
310 TOTAL_DEDUCTIONS,
311 DISCOUNT_AMOUNT)
312 Select HEADER_ID,
313 ORG_ID,
314 VENDOR_ID,
315 CHILD_VENDOR_ID,
316 INVOICE_ID,
317 INVOICE_PAYMENT_ID,
318 AMOUNT,
319 LAST_UPDATE_DATE,
320 LAST_UPDATED_BY,
321 LAST_UPDATE_LOGIN,
322 CREATION_DATE,
323 CREATED_BY,
324 LABOUR_COST,
325 MATERIAL_COST,
326 TOTAL_DEDUCTIONS,
327 DISCOUNT_AMOUNT
328 from igi_cis_mth_ret_pay_t
329 where HEADER_ID = p_in_header_id ;
330 delete from igi_cis_mth_ret_hdr_t where header_id = p_in_header_id;
331 delete from igi_cis_mth_ret_lines_t where header_id = p_in_header_id;
332 delete from igi_cis_mth_ret_pay_t where header_id = p_in_header_id;
333 commit;
334 ELSE
335 null;
336 END IF;
337 EXCEPTION
338 when others then
339 --raise_application_error('-20301','Error in insertion of audit table ');
340 null;
341 end pr_audit_update ;
342 end IGI_CIS2007_IGIPMTHP_PKG;