1 PACKAGE BODY PA_IC_INV_UTILS AS
2 /* $Header: PAICUTLB.pls 120.3.12010000.3 2008/09/25 11:28:02 nkapling ship $ */
3 -- Package specification for utilities to be used in Intercompany
4 -- Invoice generation process
5 --
6 -- This procedure will initialize the global variables
7 -- Input paramaters
8 -- Parameter Type Required Description
9 -- P_LAST_UPDATE_LOGIN NUMBER Yes Standard Who column
10 -- P_REQUEST_ID NUMBER Yes
11 -- P_PROGRAM_APPLICATION_ID NUMBER Yes
12 -- P_PROGRAM_ID NUMBER Yes
13 -- P_LAST_UPDATED_BY NUMBER Yes
14 -- P_CREATED_BY NUMBER Yes
15 -- P_DEBUG_MODE VARCHAR2 Yes Debug mode
16 -- P_SOB NUMBER Yes Set of books id
17 -- P_ORG NUMBER Yes Org Id
18 -- P_FUNC_CURR VARCHAR2 Yes Functional currency code
19 --
20 --
21 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
22
23 PROCEDURE Init (
24 P_LAST_UPDATE_LOGIN NUMBER,
25 P_REQUEST_ID NUMBER,
26 P_PROGRAM_APPLICATION_ID NUMBER,
27 P_PROGRAM_ID NUMBER,
28 P_LAST_UPDATED_BY NUMBER,
29 P_CREATED_BY NUMBER,
30 P_DEBUG_MODE VARCHAR2,
31 P_SOB NUMBER,
32 P_ORG NUMBER,
33 P_FUNC_CURR VARCHAR2
34 ) IS
35
36
37 /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will obsoleted, replace with
38 new table gl_alc_ledger_rships_v and corresponding columns
39 Also remove the date validation, If we add application id and relationship_enabled_flag check then no need to
40 check the date. */
41
42 /* cursor c_reporting_sob(p_sob_id in number,p_org_id in number) is
43 select reporting_set_of_books_id ,
44 reporting_currency_code
45 from gl_mc_reporting_options
46 where primary_set_of_books_id = p_sob_id
47 and org_id = p_org_id
48 and application_id = 275
49 and nvl(enabled_flag,'N')='Y'
50 and TRUNC(sysdate) between
51 TRUNC(start_date) and TRUNC(nvl(end_date,sysdate)); */ /* BUG# 3118592 */
52
53
54 cursor c_reporting_sob(p_sob_id in number,p_org_id in number) is
55 select ledger_id reporting_set_of_books_id ,
56 currency_code reporting_currency_code
57 from gl_alc_ledger_rships_v
58 where source_ledger_id = p_sob_id
59 and (org_id = -99 OR org_id = p_org_id)
60 and application_id = 275
61 and relationship_enabled_flag ='Y';
62
63
64 I integer ;
65
66 BEGIN
67 G_LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN;
68 G_REQUEST_ID := P_REQUEST_ID;
69 G_PROGRAM_APPLICATION_ID := P_PROGRAM_APPLICATION_ID;
70 G_PROGRAM_ID := P_PROGRAM_ID;
71 G_LAST_UPDATED_BY := P_LAST_UPDATED_BY;
72 G_CREATED_BY := P_CREATED_BY;
73 G_DEBUG_MODE := P_DEBUG_MODE;
74
75 pa_debug.init_err_stack ('Intercompany Invoice');
76 pa_debug.set_process(
77 x_process => 'PLSQL',
78 x_debug_mode => p_debug_mode);
79
80 pa_debug.G_Err_Stage := 'Initializing IC Invoice';
81
82 IF g1_debug_mode = 'Y' THEN
83 pa_ic_inv_utils.log_message('Init: ' || pa_debug.G_Err_Stage);
84 END IF;
85
86 -- Initialize global variables for MRC
87
88 PA_MC_INVOICE_DETAIL_PKG.G_FUNC_CURR := P_FUNC_CURR;
89 PA_MC_INVOICE_DETAIL_PKG.G_SOB := P_SOB;
90 PA_MC_INVOICE_DETAIL_PKG.G_ORG_ID := P_ORG;
91
92 I := 1;
93
94 PA_MC_INVOICE_DETAIL_PKG.G_No_of_SOB := I - 1;
95
96 END Init;
97 -- This procedure will return the next draft invoice number to be used
98 -- for creating a new invoice header.
99 --
100 -- Input parameters
101 -- Parameter Type Required Description
102 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
103 -- P_REQUEST_ID NUMBER Yes The current request id
104 --
105 -- Output Parameters
106 -- Parameter Type Description
107 -- X_NEW_INVOICE_NUM NUMBER Invoice number to be used for the new Invoice
108 --
109 PROCEDURE Get_Next_Draft_Inv_Num
110 ( P_project_id IN NUMBER,
111 P_request_id IN NUMBER,
112 X_new_invoice_num OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
113
114 CURSOR C_NEW_INVOICE_NUM IS
115 SELECT nvl(MAX(draft_invoice_num),0) + 1
116 FROM pa_draft_invoices DI
117 WHERE DI.project_id = P_project_id;
118
119 CURSOR C_ERR_INVOICE_NUM IS
120 SELECT DI.draft_invoice_num
121 FROM pa_draft_invoices DI
122 WHERE DI.Project_ID = P_project_id
123 AND DI.draft_invoice_num
124 =
125 ( select min(draft_invoice_num)
126 from pa_draft_invoices
127 where project_id = P_project_id
128 AND request_id = P_request_id
129 AND generation_error_flag = 'Y'
130 );
131
132 l_new_invoice_num number;
133 l_err_invoice_num number;
134 l_user_id number;
135
136 BEGIN
137 /* Get the next draft invoice number for the provider project
138 * This procedure will return the next draft invoice number to be used for
139 creating a new invoice header.
140 * In case multiple invoices are generated as maybe in case of bill by
141 project option the invoices with generation error should have the
142 highest number.
143 * In order to ensure this when the new maximum invoice number is generated
144 the procedure should check if any Invoices are created with generation error
145 for the project and in the current request .
146 If multiple error invoices exist then pick up the one with the minimum number ,
147 this invoice should be renumbered with the new invoice number.
148 * All the associated invoice lines and invoice details should also be
149 renumbered.
150 * Distribution warnings if any should also be renumbered.
151 * Finally the procedure will return the old invoice number of the
152 error invoice to be used by the new Invoice.
153 */
154
155 -- Get the highest invoice number for the project
156
157 pa_debug.g_err_stage := ' In Get_Next_Draft_Inv_Num ';
158 IF g1_debug_mode = 'Y' THEN
159 pa_ic_inv_utils.log_message('Get_Next_Draft_Inv_Num: ' || pa_debug.g_err_stage);
160 pa_ic_inv_utils.log_message('Get_Next_Draft_Inv_Num: ' || 'opening cursor c_new_invoice_num');
161 END IF;
162
163 Open c_new_invoice_num;
164
165 Fetch c_new_invoice_num into l_new_invoice_num;
166
167 IF g1_debug_mode = 'Y' THEN
168 pa_ic_inv_utils.log_message('Get_Next_Draft_Inv_Num: ' || 'new_invoice_num = '||l_new_invoice_num);
169 END IF;
170
171 Close c_new_invoice_num;
172
173
174 -- Check if other invoices with generation error have been created in the
175 -- current request
176 -- If exists then renumber the error invoice with the new invoice number
177 -- and return the error invoice number
178 -- else
179 -- return the highest invoice number for the project
180 --
181
182 l_user_id := pa_ic_inv_utils.g_last_update_login;
183
184 Open c_err_invoice_num;
185
186 Fetch c_err_invoice_num into l_err_invoice_num;
187
188
189 IF c_err_invoice_num%FOUND THEN
190
191 IF g1_debug_mode = 'Y' THEN
192 pa_ic_inv_utils.log_message('Get_Next_Draft_Inv_Num: ' || 'error invoice_num = '||l_err_invoice_num);
193 END IF;
194
195 Update pa_draft_invoices
196 set draft_invoice_num = l_new_invoice_num,
197 last_update_date = SYSDATE,
198 last_update_login = l_user_id
199 where project_id = P_Project_Id
200 and draft_invoice_num = l_err_invoice_num;
201
202 Update pa_draft_invoice_items
203 set draft_invoice_num = l_new_invoice_num,
204 last_update_date = SYSDATE,
205 last_update_login = l_user_id
206 where project_id = P_Project_Id
207 and draft_invoice_num = l_err_invoice_num;
208
209 Update pa_draft_invoice_details
210 set draft_invoice_num = l_new_invoice_num,
211 last_update_date = SYSDATE,
212 last_update_login = l_user_id
213 where project_id = P_Project_Id
214 and draft_invoice_num = l_err_invoice_num;
215
216 Update pa_distribution_warnings
217 set draft_invoice_num = l_new_invoice_num,
218 last_update_date = SYSDATE,
219 last_update_login = l_user_id
220 where project_id = P_Project_Id
221 and draft_invoice_num = l_err_invoice_num;
222
223 X_NEW_INVOICE_NUM := l_err_invoice_num;
224
225 ELSE
226
227 X_NEW_INVOICE_NUM := l_new_invoice_num;
228
229 END IF;
230
231 Close c_err_invoice_num; /* bug 3865056 */
232
233 IF g1_debug_mode = 'Y' THEN
234 pa_ic_inv_utils.log_message('Get_Next_Draft_Inv_Num: ' || 'new invoice_num = '||l_new_invoice_num);
235 END IF;
236
237 EXCEPTION
238 when OTHERS then
239 IF c_err_invoice_num%ISOPEN THEN
240 close c_err_invoice_num;
241 END IF;
242 /* ATG Changes */
243 X_new_invoice_num := null;
244
245 raise;
246
247 END Get_Next_Draft_Inv_Num;
248 -- This procedure will commit the invoice transaction
249 --
250 -- There are no parameters to this procedure
251 --
252 PROCEDURE Commit_Invoice AS
253 BEGIN
254 COMMIT;
255 END Commit_Invoice;
256
257 -- This procedure will update the summary project fundings with
258 -- the invoiced amounts
259 --
260 -- Input parameters
261 -- Parameter Type Required Description
262 -- P_DRAFT_INVOICE_NUM NUMBER Yes The draft invoice number
263 -- P_AGREEMENT_ID NUMBER Yes Identifier of the Agreement
264 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
265 --
266
267 PROCEDURE Update_SPF
268 ( P_DRAFT_INVOICE_NUM IN NUMBER ,
269 P_AGREEMENT_ID IN NUMBER,
270 P_PROJECT_ID IN NUMBER,
271 P_INVOICE_MODE IN VARCHAR2) AS
272
273 l_total_billed_amount NUMBER := 0;
274
275 l_request_id number ;
276 l_program_application_id number;
277 l_program_id number;
278 l_user_id number;
279
280 BEGIN
281 l_user_id := pa_ic_inv_utils.g_last_update_login;
282 l_program_id := pa_ic_inv_utils.g_program_id;
283 l_program_application_id := pa_ic_inv_utils.g_program_application_id;
284 l_request_id := pa_ic_inv_utils.g_request_id;
285
286 pa_debug.g_err_stage := ' In Update_SPF ';
287 IF g1_debug_mode = 'Y' THEN
288 pa_ic_inv_utils.log_message('Update_SPF: ' || pa_debug.g_err_stage);
289 END IF;
290
291 SELECT SUM(amount)
292 INTO l_total_billed_amount
293 FROM pa_draft_invoice_items
294 WHERE draft_invoice_num = p_draft_invoice_num
295 AND project_id = p_project_id ;
296
297 Update pa_summary_project_fundings
298 set total_accrued_amount = NVL(total_accrued_amount,0) +
299 decode(P_INVOICE_MODE,'DELETE',(-1)*l_total_billed_amount,
300 l_total_billed_amount),
301 total_billed_amount = NVL(total_billed_amount,0) +
302 decode(P_INVOICE_MODE,'DELETE',(-1)*l_total_billed_amount,
303 l_total_billed_amount),
304 last_update_date = SYSDATE,
305 last_update_login = l_user_id,
306 request_id = l_request_id,
307 program_application_id = l_program_application_id,
308 program_id = l_program_id
309 where project_id = P_project_id
310 and agreement_id = P_agreement_id;
311
312 IF g1_debug_mode = 'Y' THEN
313 pa_ic_inv_utils.log_message ('Updated SPF rows = '||SQL%rowcount);
314 pa_ic_inv_utils.log_message ('Update_SPF: ' || 'Updating SPF with amount = ' ||
315 l_total_billed_amount);
316 END IF;
317
318
319 EXCEPTION
320 when OTHERS then
321 raise;
322 END Update_SPF;
323
324 --
325 -- This procedure will mark the generation error on the draft invoice
326 -- and insert the distribution warnings
327 --
328 -- Input parameters
329 -- Parameter Type Required Description
330 -- P_DRAFT_INVOICE_NUM NUMBER Yes The draft invoice number
331 -- P_REJN_LOOKUP_TYPE VARCHAR Yes The lookup type to be used to
332 -- get the rejection reason
333 -- P_REJN_LOOKUP_CODE VARCHAR Yes The lookup type to be used to
334 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
335 -- get the rejection code
336
337 PROCEDURE Mark_Inv_Error
338 ( P_DRAFT_INVOICE_NUM IN NUMBER,
339 P_REJN_LOOKUP_TYPE IN VARCHAR,
340 P_REJN_LOOKUP_CODE IN VARCHAR,
341 P_PROJECT_ID IN NUMBER) AS
342
343 l_request_id number ;
344 l_program_application_id number;
345 l_program_id number;
346 l_user_id number;
347 l_cnt number:=0; /* Added for bug 7034356*/
348 l_rejection_reason varchar2(80);
349
350 BEGIN
351
352 l_user_id := pa_ic_inv_utils.g_last_update_login;
353 l_program_id := pa_ic_inv_utils.g_program_id;
354 l_program_application_id := pa_ic_inv_utils.g_program_application_id;
355 l_request_id := pa_ic_inv_utils.g_request_id;
356
357 /* Fix for bug 7034356 starts here */
358 IF (P_REJN_LOOKUP_CODE = 'NO_INV_LINES') THEN
359 SELECT COUNT(*)
360 INTO l_cnt
361 FROM pa_distribution_warnings
362 WHERE project_id = p_project_id
363 AND draft_invoice_num = P_DRAFT_INVOICE_NUM;
364 END IF;
365
366 IF (P_REJN_LOOKUP_CODE = 'NO_INV_LINES' and l_cnt = 0 ) THEN
367 pa_ic_inv_utils.log_message('Deleting invoice');
368 /*pa_ic_inv_del.delete_invoices(p_project_id);*/
369 /*Fix for Bug 7433201*/
370 delete pa_draft_invoices_all
371 where project_id = p_project_id
372 AND draft_invoice_num = P_DRAFT_INVOICE_NUM;
373 /*End of fix for Bug 7433201*/
374 pa_ic_inv_utils.log_message('Done Deleting invoice');
375 ELSE
376 /* Fix for bug 7034356 ends here */
377 pa_debug.g_err_stage := ' In Mark Inv Error ';
378 IF g1_debug_mode = 'Y' THEN
379 pa_ic_inv_utils.log_message('Mark_Inv_Error: ' || pa_debug.g_err_stage);
380 END IF;
381
382 begin
383 select meaning
384 into l_rejection_reason
385 from pa_lookups
386 where lookup_type = P_REJN_LOOKUP_TYPE
387 and lookup_code = P_REJN_LOOKUP_CODE;
388
389 exception
390 when NO_DATA_FOUND then
391 l_rejection_reason := P_REJN_LOOKUP_TYPE ||'-'|| P_REJN_LOOKUP_CODE;
392 when OTHERS then
393 raise;
394 end;
395
396 Update pa_draft_invoices
397 set generation_error_flag = 'Y',
398 last_update_date = SYSDATE,
399 last_update_login = l_user_id,
400 request_id = l_request_id,
401 transfer_rejection_reason = l_rejection_reason
402 where project_id = P_PROJECT_ID
403 and draft_invoice_num = P_DRAFT_INVOICE_NUM;
404
405 IF g1_debug_mode = 'Y' THEN
406 pa_ic_inv_utils.log_message('Rows updated in DI = '||SQL%rowcount);
407 END IF;
408
409 INSERT INTO PA_DISTRIBUTION_WARNINGS
410 (
411 DRAFT_INVOICE_NUM, PROJECT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
412 CREATION_DATE, CREATED_BY, REQUEST_ID, PROGRAM_APPLICATION_ID,
413 PROGRAM_ID, PROGRAM_UPDATE_DATE, WARNING_MESSAGE
414 )
415 VALUES
416 (
417 P_draft_invoice_num, P_project_id, SYSDATE, l_user_id,
418 SYSDATE, l_user_id, l_request_id, l_program_application_id,
419 l_program_id, SYSDATE, l_rejection_reason
420 );
421
422 IF g1_debug_mode = 'Y' THEN
423 pa_ic_inv_utils.log_message('Rows Inserted in pa_distribution_warnings = '||SQL%rowcount);
424 pa_debug.g_err_stage := ' Done Mark Inv Error ';
425 pa_ic_inv_utils.log_message('Mark_Inv_Error: ' || pa_debug.g_err_stage);
426 END IF;
427 END IF; /* Added for bug 7034356*/
428 EXCEPTION
429 when OTHERS then
430 raise;
431 END Mark_Inv_Error;
432 --
433 -- This procedure will mark the expenditure items billed on an invoice as
434 -- billed.
435 --
436 -- Input parameters
437 -- Parameter Type Required Description
438 -- P_DRAFT_INVOICE_NUM NUMBER Yes The draft invoice number
439 -- P_REQUEST_ID NUMBER Yes The current request id
440 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
441 --
442 PROCEDURE Mark_EI_as_Billed
443 ( P_DRAFT_INVOICE_NUM IN NUMBER ,
444 P_REQUEST_ID IN NUMBER ,
445 P_PROJECT_ID IN NUMBER) AS
446
447 l_request_id number ;
448 l_program_application_id number;
449 l_program_id number;
450 l_user_id number;
451
452 CURSOR c_inv_detail IS
453 select expenditure_item_id expenditure_item_id,
454 denom_currency_code denom_tp_currency_code,
455 denom_bill_amount denom_transfer_price,
456 acct_rate_type acct_tp_rate_type,
457 acct_rate_date acct_tp_rate_date,
458 acct_exchange_rate acct_tp_exchange_rate,
459 bill_amount acct_transfer_price,
460 markup_calc_base_code cc_markup_base_code,
461 base_amount tp_base_amount,
462 ind_compiled_set_id tp_ind_compiled_set_id,
463 bill_rate tp_bill_rate,
464 bill_markup_percentage tp_bill_markup_percentage,
465 schedule_line_percentage tp_schedule_line_percentage
466 from pa_draft_invoice_details did
467 where did.project_id = P_PROJECT_ID
468 and did.request_id = P_REQUEST_ID
469 and did.draft_invoice_num = P_DRAFT_INVOICE_NUM
470 and did.line_num = ( select max(did1.line_num)
471 from pa_draft_invoice_details did1
472 where did1.expenditure_item_id =
473 did.expenditure_item_id)
474 ;
475
476 /*Code Changes for Bug No.2984871 start */
477 l_rowcount number :=0;
478 /*Code Changes for Bug No.2984871 end */
479
480 BEGIN
481 l_user_id := pa_ic_inv_utils.g_last_update_login;
482 l_program_id := pa_ic_inv_utils.g_program_id;
483 l_program_application_id := pa_ic_inv_utils.g_program_application_id;
484 l_request_id := pa_ic_inv_utils.g_request_id;
485
486 pa_debug.g_err_stage := ' In mark_ei_as_billed ';
487 IF g1_debug_mode = 'Y' THEN
488 pa_ic_inv_utils.log_message('Mark_EI_as_Billed: ' || pa_debug.g_err_stage);
489 END IF;
490
491 FOR c_rec in c_inv_detail
492 loop
493 -- Changed the update for the new ei locking strategy
494 -- update only if intermediate value of ei.cc_ic_processed_code = 'B' ,
495 -- i.e. succesfully billed
496 --
497 Update pa_expenditure_items_all ei
498 set ei.cc_ic_processed_code = decode(ei.cc_cross_charge_code,'I','Y','X')
499 ,ei.last_update_date = SYSDATE
500 ,ei.last_update_login = l_user_id
501 ,ei.request_id = l_request_id
502 where ei.expenditure_item_id = c_rec.expenditure_item_id
503 and ei.cc_ic_processed_code = 'B';
504
505 /*Code Changes for Bug No.2984871 start */
506 l_rowcount:=sql%rowcount;
507 /*Code Changes for Bug No.2984871 end */
508
509 IF g1_debug_mode = 'Y' THEN
510 pa_ic_inv_utils.log_message('Mark_EI_as_Billed: ' || 'Updating EI = '||c_rec.expenditure_item_id);
511
512 /* Commented for Bug 2984871
513 pa_ic_inv_utils.log_message('Updated rows = '||SQL%rowcount);*/
514
515 /*Code Changes for Bug No.2984871 start */
516 pa_ic_inv_utils.log_message('Updated rows = '||l_rowcount);
517 /*Code Changes for Bug No.2984871 end */
518 END IF;
519 end loop;
520
521 IF g1_debug_mode = 'Y' THEN
522 pa_ic_inv_utils.log_message('Mark_EI_as_Billed: ' || 'Done Updating EI as billed');
523 END IF;
524
525 EXCEPTION
526 when OTHERS then
527 raise;
528 END Mark_EI_as_Billed;
529 --
530 -- This function will set and acquire the user lock
531 --
532 -- Input parameters
533 -- Parameter Type Required Description
534 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
535 --
536 -- Return Values
537 -- 0 Success
538 -- Other Unable to acquire lock
539 --
540 --
541 FUNCTION Set_User_Lock
542 (P_PROJECT_ID IN NUMBER) RETURN NUMBER IS
543 lock_status number;
544 lock_name VARCHAR2(50);
545 BEGIN
546 lock_name := 'IC-'||P_PROJECT_ID;
547
548 lock_status := pa_debug.acquire_user_lock(lock_name,6,FALSE,0);
549
550 return(lock_status);
551
552 END Set_User_Lock;
553 --
554 -- This procedure will release user lock
555 --
556 -- Input parameters
557 -- Parameter Type Required Description
558 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
559 --
560 -- Return Values
561 -- 0 Success
562 -- Other Unable to acquire lock
563 --
564 FUNCTION Release_User_Lock
565 (P_PROJECT_ID IN NUMBER) RETURN NUMBER IS
566 lock_status number;
567 lock_name VARCHAR2(50);
568 BEGIN
569 lock_name := 'IC-'||P_PROJECT_ID;
570
571 lock_status := pa_debug.release_user_lock(lock_name);
572
573 return(lock_status);
574 END Release_User_Lock;
575 --
576 -- This Function will return 'Y' if unreleased invoices exist for a project
577 --
578 -- Input parameters
579 -- Parameter Type Required Description
580 -- P_BILL_BY_PROJECT VARCHAR Yes The draft invoice number
581 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
582 --
583 FUNCTION CHECK_IF_UNRELEASED_INVOICE
584 ( P_BILL_BY_PROJECT IN VARCHAR ,
585 P_PROJECT_ID IN NUMBER) RETURN VARCHAR AS
586
587 /***
588 Function will return 'Y' if unreleased invoices exist for a project
589 ***/
590
591
592 CURSOR C_UNREL_INV_BY_PROJECT IS
593 SELECT 'x'
594 FROM PA_DRAFT_INVOICE_DETAILS DID
595 WHERE DID.PROJECT_ID = P_PROJECT_ID
596 AND DID.INVOICED_FLAG = 'N'
597 AND NOT EXISTS
598 ( SELECT 'X'
599 FROM PA_DRAFT_INVOICES DI
600 WHERE DI.PROJECT_ID = P_PROJECT_ID
601 AND DI.RELEASED_BY_PERSON_ID IS NULL
602 AND DI.CC_PROJECT_ID = DID.CC_PROJECT_ID )
603 ;
604
605 CURSOR C_UNREL_INV IS
606 SELECT 'X'
607 FROM PA_DRAFT_INVOICES DI
608 WHERE DI.PROJECT_ID = P_PROJECT_ID
609 AND DI.RELEASED_BY_PERSON_ID IS NULL
610 ;
611
612 l_temp VARCHAR2(1);
613
614 BEGIN
615
616 /* If bill by project option is set then
617 * Identify cross charged project with uninvoiced details
618 * For each cross charged project check if unreleased invoice exists ,
619 if no unreleased invoice exists for a cross charge project then
620 the intercompany billing project needs to be picked up.
621 */
622
623 if p_bill_by_project = 'P' then
624
625 open c_unrel_inv_by_project ;
626
627 fetch c_unrel_inv_by_project into l_temp;
628
629 if c_unrel_inv_by_project%notfound then
630 close c_unrel_inv_by_project ; /* bug 3865056 */
631 RETURN ('N');
632 end if;
633
634 close c_unrel_inv_by_project ; /* bug 3865056 */
635 else
636
637 -- Else check if unreleased invoice exists for the provider project.
638
639 open c_unrel_inv;
640
641 fetch c_unrel_inv into l_temp;
642
643 if c_unrel_inv%FOUND then
644 close c_unrel_inv; /* bug 3865056 */
645 RETURN ('Y') ;
646 end if;
647
648 close c_unrel_inv; /* bug 3865056 */
649
650 end if;
651
652 RETURN('N');
653
654 exception
655 when others then
656 raise;
657 end check_if_unreleased_invoice ;
658 --
659 -- This procedure will update the draft invoice to trigger MRC
660 --
661 -- Input parameters
662 -- Parameter Type Required Description
663 -- P_DRAFT_INVOICE_NUM NUMBER Yes The draft invoice number
664 -- P_REQUEST_ID NUMBER Yes The current request id
665 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
666 --
667 PROCEDURE Update_DI_for_MRC
668 ( P_DRAFT_INVOICE_NUM IN NUMBER ,
669 P_REQUEST_ID IN NUMBER ,
670 P_PROJECT_ID IN NUMBER) AS
671 begin
672
673 -- Update pa_draft_invoices with creation date so that the
674 -- trigger on this table is fired and creates MRC rows
675 pa_debug.g_err_stage := ' In Update_DI_for_MRC ';
676 IF g1_debug_mode = 'Y' THEN
677 pa_ic_inv_utils.log_message('Update_DI_for_MRC: ' || pa_debug.g_err_stage);
678 END IF;
679
680 update pa_draft_invoices
681 set creation_date = creation_date
682 where project_id = P_PROJECT_ID
683 and request_id = P_request_id
684 and draft_invoice_num = P_draft_invoice_num;
685
686 IF g1_debug_mode = 'Y' THEN
687 pa_ic_inv_utils.log_message('Rows Updated = '||SQL%rowcount);
688 pa_ic_inv_utils.log_message('Done Update_DI_for_MRC');
689 END IF;
690
691 end Update_DI_for_MRC;
692
693 --
694 -- This procedure brings credit hold from site level profile
695 --
696 FUNCTION get_credit_hold ( P_SITE_USE_ID IN NUMBER)
697 RETURN VARCHAR2
698 IS
699 L_CREDIT_HOLD VARCHAR2(2);
700 BEGIN
701 SELECT CREDIT_HOLD
702 INTO L_CREDIT_HOLD
703 /* FROM AR_CUSTOMER_PROFILES Commented for TCA changes */
704 FROM HZ_CUSTOMER_PROFILES
705 WHERE SITE_USE_ID = P_SITE_USE_ID ;
706
707 return(L_CREDIT_HOLD);
708 EXCEPTION
709 WHEN NO_DATA_FOUND
710 THEN
711 RETURN(NULL);
712 END get_credit_hold;
713
714 -- This procedure returns active site id
715 --
716 PROCEDURE get_active_site_id ( P_ADDRESS_ID IN NUMBER,
717 P_SITE_USE_CODE IN VARCHAR2,
718 P_SITE_USE_ID OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
719 P_SITE_STATUS OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
720 IS
721 BEGIN
722 SELECT STATUS,
723 SITE_USE_ID
724 INTO P_SITE_STATUS,
725 P_SITE_USE_ID
726 /* FROM RA_SITE_USES Commented for TCA changes. */
727 FROM HZ_CUST_SITE_USES
728 WHERE CUST_ACCT_SITE_ID = P_ADDRESS_ID /* The column address_id has been replaced with CUST_ACCT_SITE_ID for tca change */
729 AND SITE_USE_CODE = P_SITE_USE_CODE
730 AND STATUS = 'A';
731
732 EXCEPTION
733 WHEN NO_DATA_FOUND
734 THEN
735 P_SITE_STATUS := 'I';
736 P_SITE_USE_ID := -99;
737 END get_active_site_id;
738
739
740 -- This procedure will return active bill_to_site_use_id and
741 -- ship_to_site_id
742 -- Input parameters
743 -- Parameter Type Required
744 -- P_BILL_TO_ADDRESS_ID NUMBER Yes
745 -- P_SHIP_TO_ADDRESS_ID NUMBER Yes
746 -- X_BILL_TO_SITE_USE_ID NUMBER
747 -- X_SHIP_TO_SITE_USE_ID NUMBER
748 -- X_BILL_TO_SITE_STATUS VARCHAR2
749 -- X_SHIP_TO_SITE_STATUS VARCHAR2
750 --
751 PROCEDURE Get_active_sites
752 ( P_BILL_TO_ADDRESS_ID IN PA_PLSQL_DATATYPES.IdTabTyp ,
753 P_SHIP_TO_ADDRESS_ID IN PA_PLSQL_DATATYPES.IdTabTyp ,
754 P_NO_OF_RECORDS IN NUMBER,
755 P_CUST_CREDIT_HOLD IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp, --File.Sql.39 bug 4440895
756 X_BILL_TO_SITE_USE_ID OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp, --File.Sql.39 bug 4440895
757 X_SHIP_TO_SITE_USE_ID OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp, --File.Sql.39 bug 4440895
758 X_BILL_TO_SITE_STATUS OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp, --File.Sql.39 bug 4440895
759 X_SHIP_TO_SITE_STATUS OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp) --File.Sql.39 bug 4440895
760 IS
761 I number;
762 BEGIN
763
764 FOR I in 1..P_NO_OF_RECORDS
765 LOOP
766 /* Get active Bill sites */
767 get_active_site_id ( P_ADDRESS_ID => P_BILL_TO_ADDRESS_ID(I),
768 P_SITE_USE_CODE => 'BILL_TO',
769 P_SITE_USE_ID => X_BILL_TO_SITE_USE_ID(I),
770 P_SITE_STATUS => X_BILL_TO_SITE_STATUS(I) );
771
772 /* Get active ship sites */
773 get_active_site_id ( P_ADDRESS_ID => P_SHIP_TO_ADDRESS_ID(I),
774 P_SITE_USE_CODE => 'SHIP_TO',
775 P_SITE_USE_ID => X_SHIP_TO_SITE_USE_ID(I),
776 P_SITE_STATUS => X_SHIP_TO_SITE_STATUS(I) );
777
778 /* Set Credit Hold for the customer */
779 /* Changed the P_BILL_TO_ADDRESS_ID(I) to X_BILL_TO_SITE_USE_ID(I)
780 in the following call of get_credit_hold for bug 2653488 */
781 P_CUST_CREDIT_HOLD(I) := nvl(get_credit_hold(X_BILL_TO_SITE_USE_ID(I)),
782 P_CUST_CREDIT_HOLD(I));
783
784 END LOOP;
785
786 END Get_active_sites;
787
788 PROCEDURE log_message (p_log_msg IN VARCHAR2) IS
789 BEGIN
790 pa_debug.write_file ('LOG',to_char(sysdate, 'DD-MON-YYYY HH:MI:SS ')||p_log_msg);
791 NULL;
792 END log_message;
793
794 end PA_IC_INV_UTILS;
[Home] [Help]