[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.6 2008/10/21 06:16:10 dlella 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
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 );
178
179 IF g1_debug_mode = 'Y' THEN
180 pa_ic_inv_utils.log_message('pa_expenditure_items_all rows updated = ' || SQL%rowcount);
181 END IF;
182
183
184 /* Added following code to update related item when deleting invoice associated
185 with the original transaction. Bug 6651747. */
186
187 Update pa_expenditure_items_all
188 set cc_ic_processed_code = 'N',
189 denom_transfer_price = NULL,
190 denom_tp_currency_code = NULl,
191 acct_transfer_price = NULL,
192 tp_base_amount = NULL,
193 tp_ind_compiled_set_id = NULL,
194 tp_bill_rate = NULL,
195 tp_bill_markup_percentage = NULL,
196 tp_schedule_line_percentage = NULL,
197 tp_rule_percentage = NULL,
198 tp_job_id = NULL,
199 projfunc_transfer_price = NULL,
200 project_transfer_price = NULL,
201 projacct_transfer_price = NULL,
202 Acct_tp_rate_type = NULL,
203 Acct_tp_rate_date = NULL,
204 Acct_tp_exchange_rate = NULL,
205 project_tp_rate_type = NULL,
206 project_tp_rate_date = NULL,
207 project_tp_exchange_rate = NULL,
208 projfunc_tp_rate_type = NULL,
209 projfunc_tp_rate_date = NULL,
210 projfunc_tp_exchange_rate = NULL
211 where source_expenditure_item_id in
212 ( select expenditure_item_id
213 from PA_DRAFT_INVOICE_DETAILS
214 Where Project_id = P_project_id
215 and Draft_Invoice_Num = L_draft_inv_num
216 );
217
218 IF g1_debug_mode = 'Y' THEN
219 pa_ic_inv_utils.log_message('Related Items: pa_expenditure_items_all rows updated = ' || SQL%rowcount);
220 END IF;
221
222 /*End of code changes for Bug 6651747 */
223
224 Update PA_DRAFT_INVOICE_DETAILS
225 Set draft_invoice_num = NULL,
226 draft_invoice_line_num = NULL,
227 last_update_date = SYSDATE,
228 last_update_login = l_user_id,
229 request_id = l_request_id,
230 Invoiced_Flag = 'N'
231 Where Project_id = P_project_id
232 and Draft_Invoice_Num = L_draft_inv_num;
233
234 IF g1_debug_mode = 'Y' THEN
235 pa_ic_inv_utils.log_message('PA_DRAFT_INVOICE_DETAILS rows updated = ' || SQL%rowcount);
236 END IF;
237
238 /* Commented for bug 1849569
239 pa_ic_inv_utils.commit_invoice;*/
240 end if;
241 end loop;
242 /* Added for bug 1849569*/
243 pa_ic_inv_utils.commit_invoice;
244 close C_DEL_UNAPPROVED_INV;
245 /* End for bug 1849569*/
246 IF g1_debug_mode = 'Y' THEN
247 pa_ic_inv_utils.log_message(' Leaving delete_invoices procedure ');
248 END IF;
249
250 EXCEPTION
251 when others then
252 raise;
253
254 END delete_invoices;
255 /*New procedure added for bug 7026205*/
256
257 PROCEDURE insert_dist_warnings
258 (P_PROJECT_ID IN NUMBER,
259 P_DRAFT_INVOICE_NUM IN NUMBER)
260
261 IS
262
263 BEGIN
264
265 INSERT INTO PA_DISTRIBUTION_WARNINGS
266 (
267 PROJECT_ID,
268 DRAFT_INVOICE_NUM,
269 LAST_UPDATE_DATE,
270 LAST_UPDATED_BY,
271 CREATION_DATE,
272 CREATED_BY,
273 REQUEST_ID,
274 PROGRAM_APPLICATION_ID,
275 PROGRAM_ID,
276 PROGRAM_UPDATE_DATE,
277 AGREEMENT_ID,
278 WARNING_MESSAGE_CODE,
279 WARNING_MESSAGE
280 )
281 (
282 Select dia.project_id,
283 DRAFT_INVOICE_NUM,
284 sysdate,
285 PA_IC_INV_UTILS.G_LAST_UPDATED_BY,
286 sysdate,
287 PA_IC_INV_UTILS.G_CREATED_BY,
288 PA_IC_INV_UTILS.G_REQUEST_ID,
289 PA_IC_INV_UTILS.G_PROGRAM_APPLICATION_ID,
290 PA_IC_INV_UTILS.G_PROGRAM_ID,
291 dia.invoice_Date,
292 dia.agreement_id,
293 dia.INV_CURRENCY_CODE,
294 (SELECT SUM(INV_AMOUNT)
295 FROM PA_DRAFT_INVOICE_ITEMS
296 WHERE PROJECT_ID = P_PROJECT_ID
297 AND DRAFT_INVOICE_NUM = P_DRAFT_INVOICE_NUM)
298 FROM PA_DRAFT_INVOICES DIA
299 WHERE PROJECT_ID = P_PROJECT_ID
300 AND DRAFT_INVOICE_NUM = P_DRAFT_INVOICE_NUM
301 );
302
303 EXCEPTION
304 when others then
305 raise;
306
307 END insert_dist_warnings;
308
309
310 end PA_IC_INV_DEL;