[Home] [Help]
PACKAGE BODY: APPS.PA_IC_INV_DEL
Source
1 PACKAGE BODY PA_IC_INV_DEL as
2 /* $Header: PAICDELB.pls 120.2.12010000.7 2010/01/15 00:43:47 apaul ship $ */
3 --
4 -- This procedure will delete the unreleased and error draft invoices
5 -- for a project
6 -- Input parameters
7 -- Parameter Type Required Description
8 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
9 --
10 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
11
12 PROCEDURE delete_invoices
13 (P_PROJECT_ID IN NUMBER,
14 p_mass_delete in varchar2 DEFAULT 'N',
15 p_unapproved_inv_only IN varchar2 DEFAULT 'N') IS /*New variable added for bug 7026205*/
16 /*new parameter p_unapproved_inv_only for bug 7172117 */
17
18 /**
19 Delete the pending and unreleased Invoices for a project
20 * Select the pending / unreleased invoices for a project
21 * If the invoice is a canceled invoice or a credit memo
22 * then the invoice should be in error
23 **/
24
25 -- This procedure will delete the Invoice and its foreign key references
26
27 -- Cursor to select the unreleased draft invoices for a project
28
29 /*Added new select variable (DI.APPROVED_DATE) in the below cursor : bug 7172117 */
30
31 CURSOR C_DEL_UNAPPROVED_INV IS
32 SELECT DI.DRAFT_INVOICE_NUM,
33 DI.AGREEMENT_ID,
34 DI.APPROVED_DATE
35 FROM PA_DRAFT_INVOICES DI
36 WHERE DI.PROJECT_ID = P_project_id
37 AND DI.RELEASED_BY_PERSON_ID IS NULL
38 AND
39 (
40 nvl(DI.CANCEL_CREDIT_MEMO_FLAG, 'N') = 'N'
41 or
42 (
43 nvl(DI.CANCEL_CREDIT_MEMO_FLAG, 'N') = 'Y'
44 AND DI.generation_error_flag = 'Y'
45 )
46 )
47 FOR UPDATE of DI.Draft_Invoice_Num
48 ORDER BY DI.Draft_Invoice_Num DESC;
49
50 l_draft_inv_num number;
51 l_amount number;
52 l_agreement_id number;
53
54 l_request_id number ;
55 l_program_application_id number;
56 l_program_id number;
57 l_user_id number;
58 l_inv_unapproved date; /*Temp variable for bug 7172117 */
59
60
61 BEGIN
62
63 IF g1_debug_mode = 'Y' THEN
64 pa_ic_inv_utils.log_message(' Entering delete_invoices procedure ');
65 END IF;
66
67 l_user_id := pa_ic_inv_utils.g_last_update_login;
68 l_program_id := pa_ic_inv_utils.g_program_id;
69 l_program_application_id := pa_ic_inv_utils.g_program_application_id;
70 l_request_id := pa_ic_inv_utils.g_request_id;
71
72 /**** logic is as follows
73
74 OPEN C_DEL_UNAPPROVED_INV ;
75 For each Draft_Invoice_num
76 Loop
77 Update summary project fundings reduce invoiced_amount by the
78 deleting invoice amount;
79 Delete from pa_distribution_warnings ;
80 Delete from pa_draft_invoice_items
81 Delete from pa_draft_invoices
82 // remove the reference to the Invoice from Invoice Details
83 End Loop;
84 *****/
85
86 open C_DEL_UNAPPROVED_INV;
87
88 loop
89
90 FETCH C_DEL_UNAPPROVED_INV
91 into l_draft_inv_num,
92 l_agreement_id,
93 l_inv_unapproved; /* Fetching the newly added parameter into new local variable. Bug 7172117 */
94
95 IF C_DEL_UNAPPROVED_INV%NOTFOUND THEN
96 EXIT;
97 END IF;
98 /* Addedd for bug 7172117 */
99 IF((p_unapproved_inv_only ='Y' and l_inv_unapproved is null) or (p_unapproved_inv_only = 'N')) then
100 pa_ic_inv_utils.update_spf (l_draft_inv_num,
101 l_agreement_id,
102 p_project_id,
103 'DELETE' );
104
105 delete from pa_distribution_warnings
106 where project_id = P_PROJECT_ID
107 and draft_invoice_num = l_draft_inv_num;
108
109 IF g1_debug_mode = 'Y' THEN
110 pa_ic_inv_utils.log_message('pa_distribution_warnings rows deleted = ' || SQL%rowcount);
111 END IF;
112
113 if p_mass_delete = 'Y' then /*Added for bug 7026205*/
114 insert_dist_warnings(p_project_id,
115 l_draft_inv_num);
116 END IF;
117
118 delete from pa_draft_invoice_items
119 where project_id = P_PROJECT_ID
120 and draft_invoice_num = l_draft_inv_num;
121
122 IF g1_debug_mode = 'Y' THEN
123 pa_ic_inv_utils.log_message('pa_draft_invoice_items rows deleted = ' || SQL%rowcount);
124 END IF;
125
126 delete from pa_draft_invoices
127 where project_id = P_PROJECT_ID
128 and draft_invoice_num = l_draft_inv_num;
129
130 IF g1_debug_mode = 'Y' THEN
131 pa_ic_inv_utils.log_message('pa_draft_invoices rows deleted = ' || SQL%rowcount);
132 END IF;
133
134 /* Added denom transfer price and denom tp_currency code for bug 2638956 */
135 /* Added acct_tp columns for bug 5276946 */
136 Update pa_expenditure_items_all EI
137 set cc_ic_processed_code = 'N',
138 denom_transfer_price = NULL,
139 denom_tp_currency_code = NULL,
140 /* Added following column updates to NULL to clear these columns when
141 deleting the invoice for bug 6132313. acct_tp_rate_date = NULL added for bug 5276946*/
142 acct_transfer_price = NULL,
143 tp_base_amount = NULL,
144 tp_ind_compiled_set_id = NULL,
145 tp_bill_rate = NULL,
146 tp_bill_markup_percentage = NULL,
147 tp_schedule_line_percentage = NULL,
148 tp_rule_percentage = NULL,
149 tp_job_id = NULL,
150 projfunc_transfer_price = NULL,
151 project_transfer_price = NULL,
152 projacct_transfer_price = NULL,
153 Acct_tp_rate_type = NULL,
154 Acct_tp_rate_date = NULL,
155 Acct_tp_exchange_rate = NULL,
156 project_tp_rate_type = NULL,
157 project_tp_rate_date = NULL,
158 project_tp_exchange_rate = NULL,
159 projfunc_tp_rate_type = NULL,
160 projfunc_tp_rate_date = NULL,
161 projfunc_tp_exchange_rate = NULL
162 where expenditure_item_id in
163 ( select expenditure_item_id
164 from PA_DRAFT_INVOICE_DETAILS
165 Where Project_id = P_project_id
166 and Draft_Invoice_Num = L_draft_inv_num
167 )
168 and (adjusted_expenditure_item_id is null /*Clause added for Bug 6899120*/
169 or (adjusted_expenditure_item_id is not null and
170 /* adjusted_expenditure_item_id in
171 ( select expenditure_item_id
172 from PA_DRAFT_INVOICE_DETAILS
173 Where Project_id = P_project_id
174 and Draft_Invoice_Num = L_draft_inv_num
175 )
176 )
177 ); 9203389 */
178 expenditure_item_id in /* 9203389 */
179 ( select PADI.expenditure_item_id
180 from PA_DRAFT_INVOICE_DETAILS PADI,
181 PA_DRAFT_INVOICE_DETAILS PADI2
182 Where PADI2.expenditure_item_id = EI.adjusted_expenditure_item_id
183 and PADI.orig_draft_invoice_num = PADI2.draft_invoice_num
184 and PADI2.project_id = P_project_id
185 and PADI.project_id = P_project_id
186 and PADI.draft_invoice_num = L_draft_inv_num
187 )
188 )
189 );
190
191 IF g1_debug_mode = 'Y' THEN
192 pa_ic_inv_utils.log_message('pa_expenditure_items_all rows updated = ' || SQL%rowcount);
193 END IF;
194
195
196 /* Added following code to update related item when deleting invoice associated
197 with the original transaction. Bug 6651747. */
198
199 Update pa_expenditure_items_all
200 set cc_ic_processed_code = 'N',
201 denom_transfer_price = NULL,
202 denom_tp_currency_code = NULl,
203 acct_transfer_price = NULL,
204 tp_base_amount = NULL,
205 tp_ind_compiled_set_id = NULL,
206 tp_bill_rate = NULL,
207 tp_bill_markup_percentage = NULL,
208 tp_schedule_line_percentage = NULL,
209 tp_rule_percentage = NULL,
210 tp_job_id = NULL,
211 projfunc_transfer_price = NULL,
212 project_transfer_price = NULL,
213 projacct_transfer_price = NULL,
214 Acct_tp_rate_type = NULL,
215 Acct_tp_rate_date = NULL,
216 Acct_tp_exchange_rate = NULL,
217 project_tp_rate_type = NULL,
218 project_tp_rate_date = NULL,
219 project_tp_exchange_rate = NULL,
220 projfunc_tp_rate_type = NULL,
221 projfunc_tp_rate_date = NULL,
222 projfunc_tp_exchange_rate = NULL
223 where source_expenditure_item_id in
224 ( select expenditure_item_id
225 from PA_DRAFT_INVOICE_DETAILS
226 Where Project_id = P_project_id
227 and Draft_Invoice_Num = L_draft_inv_num
228 );
229
230 IF g1_debug_mode = 'Y' THEN
231 pa_ic_inv_utils.log_message('Related Items: pa_expenditure_items_all rows updated = ' || SQL%rowcount);
232 END IF;
233
234 /*End of code changes for Bug 6651747 */
235
236 Update PA_DRAFT_INVOICE_DETAILS
237 Set draft_invoice_num = NULL,
238 draft_invoice_line_num = NULL,
239 last_update_date = SYSDATE,
240 last_update_login = l_user_id,
241 request_id = l_request_id,
242 Invoiced_Flag = 'N'
243 Where Project_id = P_project_id
244 and Draft_Invoice_Num = L_draft_inv_num;
245
246 IF g1_debug_mode = 'Y' THEN
247 pa_ic_inv_utils.log_message('PA_DRAFT_INVOICE_DETAILS rows updated = ' || SQL%rowcount);
248 END IF;
249
250 /* Commented for bug 1849569
251 pa_ic_inv_utils.commit_invoice;*/
252 end if;
253 end loop;
254 /* Added for bug 1849569*/
255 pa_ic_inv_utils.commit_invoice;
256 close C_DEL_UNAPPROVED_INV;
257 /* End for bug 1849569*/
258 IF g1_debug_mode = 'Y' THEN
259 pa_ic_inv_utils.log_message(' Leaving delete_invoices procedure ');
260 END IF;
261
262 EXCEPTION
263 when others then
264 raise;
265
266 END delete_invoices;
267 /*New procedure added for bug 7026205*/
268
269 PROCEDURE insert_dist_warnings
270 (P_PROJECT_ID IN NUMBER,
271 P_DRAFT_INVOICE_NUM IN NUMBER)
272
273 IS
274
275 BEGIN
276
277 INSERT INTO PA_DISTRIBUTION_WARNINGS
278 (
279 PROJECT_ID,
280 DRAFT_INVOICE_NUM,
281 LAST_UPDATE_DATE,
282 LAST_UPDATED_BY,
283 CREATION_DATE,
284 CREATED_BY,
285 REQUEST_ID,
286 PROGRAM_APPLICATION_ID,
287 PROGRAM_ID,
288 PROGRAM_UPDATE_DATE,
289 AGREEMENT_ID,
290 WARNING_MESSAGE_CODE,
291 WARNING_MESSAGE
292 )
293 (
294 Select dia.project_id,
295 DRAFT_INVOICE_NUM,
296 sysdate,
297 PA_IC_INV_UTILS.G_LAST_UPDATED_BY,
298 sysdate,
299 PA_IC_INV_UTILS.G_CREATED_BY,
300 PA_IC_INV_UTILS.G_REQUEST_ID,
301 PA_IC_INV_UTILS.G_PROGRAM_APPLICATION_ID,
302 PA_IC_INV_UTILS.G_PROGRAM_ID,
303 dia.invoice_Date,
304 dia.agreement_id,
305 dia.INV_CURRENCY_CODE,
306 (SELECT SUM(INV_AMOUNT)
307 FROM PA_DRAFT_INVOICE_ITEMS
308 WHERE PROJECT_ID = P_PROJECT_ID
309 AND DRAFT_INVOICE_NUM = P_DRAFT_INVOICE_NUM)
310 FROM PA_DRAFT_INVOICES DIA
311 WHERE PROJECT_ID = P_PROJECT_ID
312 AND DRAFT_INVOICE_NUM = P_DRAFT_INVOICE_NUM
313 );
314
315 EXCEPTION
316 when others then
317 raise;
318
319 END insert_dist_warnings;
320
321
322 end PA_IC_INV_DEL;