1 PACKAGE BODY pa_ap_integration AS
2 --$Header: PAAPINTB.pls 120.9.12010000.3 2008/11/27 06:34:54 svivaram ship $
3
4 PROCEDURE UPD_PA_DETAILS_SUPPLIER_MERGE
5 ( p_old_vendor_id IN po_vendors.vendor_id%type,
6 p_new_vendor_id IN po_vendors.vendor_id%type,
7 p_paid_inv_flag IN ap_invoices_all.PAYMENT_STATUS_FLAG%type,
8 x_stage OUT NOCOPY VARCHAR2,
9 x_status OUT NOCOPY VARCHAR2)
10
11 IS
12 Begin
13 x_stage := 'Updating Pa_implementations Table';
14 Update pa_implementations_all set Vendor_Id = p_new_vendor_id
15 Where Vendor_Id = p_old_vendor_id;
16
17 x_stage := 'Updating Pa_Expenditures_All Table';
18
19 /*Code change for 7125912 */
20 UPDATE pa_expenditures_all e
21 SET e.vendor_id = p_new_vendor_id
22 WHERE e.vendor_id = p_old_vendor_id
23 AND e.expenditure_id in (
24 SELECT ---- /*+ LEADING(ei) */
25 ei.expenditure_id
26 FROM pa_cost_distribution_lines_all c,
27 pa_expenditure_items_all ei,
28 ap_invoices_all i
29 WHERE TO_CHAR (i.invoice_id) = c.system_reference2
30 AND c.expenditure_item_id = ei.expenditure_item_id
31 -- AND ei.expenditure_id = e.expenditure_id
32 AND c.system_reference1 = TO_CHAR(p_old_vendor_id)
33 AND i.vendor_id = p_new_vendor_id
34 AND i.payment_status_flag = DECODE (NVL (p_paid_inv_flag, 'Y'), 'N', 'N', i.payment_status_flag)
35 ) ;
36 /*Code change for 7125912 END */
37 x_stage := 'Updating Pa_Expenditure_Items_All Table';
38
39 /*Changed for Bug:5864959*/
40 Update pa_expenditure_items_all ei set vendor_id = p_new_vendor_id
41 Where Vendor_Id = p_old_vendor_id
42 and exists
43 (select 1
44 from pa_cost_distribution_lines_all c,
45 ap_invoices_all i
46 where i.invoice_id = to_number(c.system_reference2)
47 and c.expenditure_item_id = ei.expenditure_item_id
48 and c.system_reference1 = p_old_vendor_id
49 and i.vendor_id = p_new_vendor_id
50 and i.PAYMENT_STATUS_FLAG =
51 decode(nvl(p_paid_inv_flag,'Y'),'N','N',i.PAYMENT_STATUS_FLAG)
52 );
53
54 x_stage := 'Updating Pa_Cost_Distribution_Lines_All Table';
55
56 If nvl(p_paid_inv_flag,'Y') = 'N' Then
57
58 /*Code change for 7125912 */
59 Declare Cursor c1 is
60 Select c.rowid row_id, c.expenditure_item_id, c.line_num
61 from pa_cost_distribution_lines_all c, ap_invoices_all i
62 where to_char(i.invoice_id) = c.system_reference2
63 /*and i.vendor_id = to_number(c.system_reference1)*/ /*Vendor_ID on Invoice is already changed...so this is not needed */
64 and c.system_reference1 = to_char(p_old_vendor_id)
65 and i.vendor_id = p_new_vendor_id
66 and i.PAYMENT_STATUS_FLAG = 'N';
67
68 /*Code change for 7125912 END */
69 Begin
70
71 x_stage := 'Updating Pa_Cost_Distribution_Lines_All Table For UNPAID Invoices';
72
73 For Rec in C1 Loop
74
75 Update pa_cost_distribution_lines_all
76 Set System_reference1 = (p_new_vendor_id)
77 Where rowid = rec.row_id;
78
79 End Loop;
80 End;
81
82 Else -- p_paid_inv_flag <> 'N'
83
84 x_stage := 'Updating Pa_Cost_Distribution_Lines_All Table For ALL Invoices';
85
86 Update Pa_Cost_Distribution_Lines_All
87 Set System_Reference1 = to_char(p_new_vendor_id)
88 Where System_Reference1 = to_char(p_old_vendor_id)
89 And system_reference1 is not null
90 And system_reference2 is not null
91 And system_reference3 is not null;
92
93 End If;
94
95 --R12 need to update vendor ID on pa_bc_packets
96 x_stage := 'Updating Pa_Bc_Packets Table';
97 Update pa_bc_packets
98 set Vendor_Id = p_new_vendor_id
99 Where Vendor_Id = p_old_vendor_id
100 And Status_Code = 'A';
101
102 --R12 need to update vendor ID on pa_bc_commitments
103 x_stage := 'Updating Pa_Bc_Commitments_All Table';
104 Update pa_bc_commitments_all
105 set Vendor_Id = p_new_vendor_id
106 Where Vendor_Id = p_old_vendor_id;
107
108 x_stage := 'Updating Pa_Project_Asset_Lines_All Table For ALL Invoices';
109
110 update pa_project_asset_lines_all set po_vendor_id = p_new_vendor_id
111 where po_vendor_id = p_old_vendor_id
112 and po_vendor_id is not null;
113
114 /* Added for bug 2649043 */
115
116 x_stage := 'Updating PA_CI_SUPPLIER_DETAILS Table For ALL Invoices';
117
118 update PA_CI_SUPPLIER_DETAILS set vendor_id = p_new_vendor_id
119 where vendor_id = p_old_vendor_id
120 and vendor_id is not null;
121
122 /* Summarization Changes */
123
124 -- FP.M Resource LIst Data Model Impact Changes, 09-JUN-04, jwhite -----------------------------
125 -- Augmented original code with additional filter
126
127 /* -- Original Code
128 Declare
129 Cursor c_resource_list is
130 Select distinct resource_list_id from pa_resource_list_members
131 where vendor_id = p_old_vendor_id and enabled_flag = 'Y';
132 */
133
134 -- FP.M Data Model Logic
135
136 Declare
137 Cursor c_resource_list is
138 Select distinct resource_list_id from pa_resource_list_members
139 where vendor_id = p_old_vendor_id
140 and enabled_flag = 'Y'
141 and nvl(migration_code,'M')= 'M';
142
143 -- End: FP.M Resource LIst Data Model Impact Changes -----------------------------
144
145
146
147 /*****
148 l_new_vendor_exists_member varchar2(1) := 'N';
149 l_new_vendor_exists_resource varchar2(1) := 'N';
150 *******Bug# 4029384*/
151
152 l_new_vendor_exists_member number := 0; /*Bug# 4029384*/
153 l_new_vendor_exists_resource number := 0; /*Bug# 4029384*/
154
155 l_new_vendor_name po_vendors.vendor_name%type;
156
157 l_expenditure_category pa_resource_list_members.expenditure_category%type;
158 l_parent_member_id pa_resource_list_members.resource_list_member_id%type;
159 l_resource_list_member_id pa_resource_list_members.resource_list_member_id%type;
160 l_track_as_labor_flag varchar2(10);
161 l_err_code Varchar2(200);
162 l_err_stage Varchar2(200);
163 l_err_stack Varchar2(2000);
164 l_resource_id pa_resources.resource_id%type;
165
166 Begin
167 x_stage := 'Start For Summarization';
168 for rec1 in c_resource_list loop
169
170 x_stage := 'New Vendor Name';
171 Select vendor_name into l_new_vendor_name from po_vendors where vendor_id = p_new_vendor_id;
172
173 Begin
174 x_stage:='See whether New vendor exists as resource in PA tables';
175
176 Select nvl(count(a.name),0) into l_new_vendor_exists_resource from pa_resource_types b, pa_resources a
177 where a.RESOURCE_TYPE_ID=b.RESOURCE_TYPE_ID and b.RESOURCE_TYPE_CODE='VENDOR'
178 And a.name = l_new_vendor_name;
179
180 Exception When no_data_found then l_new_vendor_exists_resource := 0;
181
182 End;
183
184 If l_new_vendor_exists_resource = 0 Then -- Insert New vendor as a resource
185
186 x_stage := 'New Vendor Does Not Exists ... Creating New vendor as resource';
187
188 PA_CREATE_RESOURCE.Create_Resource
189 (p_resource_name => l_new_vendor_name,
190 p_resource_type_Code => 'VENDOR',
191 p_description => l_new_vendor_name,
192 p_unit_of_measure => NULL,
193 p_rollup_quantity_flag => NULL,
194 p_track_as_labor_flag => NULL,
195 p_start_date => to_date('01/01/1950','DD/MM/YYYY'),
196 p_end_date => NULL,
197 p_person_id => NULL,
198 p_job_id => NULL,
199 p_proj_organization_id => NULL,
200 p_vendor_id => p_new_vendor_id,
201 p_expenditure_type => NULL,
202 p_event_type => NULL,
203 p_expenditure_category => NULL,
204 p_revenue_category_code => NULL,
205 p_non_labor_resource => NULL,
206 p_system_linkage => NULL,
207 p_project_role_id => NULL,
208 p_resource_id => l_resource_id,
209 p_err_code => l_err_code,
210 p_err_stage => x_stage,
211 p_err_stack => l_err_stack);
212 End If;
213
214
215 -- FP.M Resource LIst Data Model Impact Changes, 09-JUN-04, jwhite -----------------------------
216 -- Augmented original code with additional filter for migration_code
217
218
219 Begin
220
221 /* --Origianal Code
222
223 Select nvl(count(*),0) into l_new_vendor_exists_member from pa_resource_list_members
224 where resource_list_id = rec1.resource_list_id and VENDOR_ID = p_new_vendor_id;
225 */
226
227
228 -- FP.M Data Model
229
230 Select nvl(count(*),0)
231 into l_new_vendor_exists_member
232 from pa_resource_list_members
233 where resource_list_id = rec1.resource_list_id
234 and VENDOR_ID = p_new_vendor_id
235 and nvl(migration_code,'M') = 'M';
236
237
238 exception when no_data_found then l_new_vendor_exists_member := 0;
239
240 End;
241
242
243 /* --Origianal Code
244
245 update pa_resource_list_members set enabled_flag = 'N'
246 where resource_list_id = rec1.resource_list_id
247 and vendor_id = p_old_vendor_id;
248 */
249
250
251
252 -- FP.M Data Model
253
254 update pa_resource_list_members set
255 enabled_flag = 'N'
256 where resource_list_id = rec1.resource_list_id
257 and vendor_id = p_old_vendor_id
258 and nvl(migration_code,'M') = 'M';
259
260 -- End: FP.M Resource LIst Data Model Impact Changes -----------------------------
261
262
263
264 If l_new_vendor_exists_member = 0 Then -- Insert New vendor as a resource list member
265
266 x_stage:=' New Vendor Does not esists as resource member.. creating resource member';
267
268 Declare
269
270 L_RESOURCE_LIST_ID PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_ID%TYPE;
271 L_RESOURCE_ID PA_RESOURCE_LIST_MEMBERS.RESOURCE_ID%TYPE;
272 L_ORGANIZATION_ID PA_RESOURCE_LIST_MEMBERS.ORGANIZATION_ID%TYPE;
273 L_EXPENDITURE_CATEGORY PA_RESOURCE_LIST_MEMBERS.EXPENDITURE_CATEGORY%TYPE;
274 L_REVENUE_CATEGORY PA_RESOURCE_LIST_MEMBERS.REVENUE_CATEGORY%TYPE;
275 l_res_grouped PA_RESOURCE_LISTS_ALL_BG.group_resource_type_id%TYPE; /*Bug# 4029384*/
276 Begin
277
278
279 -- FP.M Resource LIst Data Model Impact Changes, 09-JUN-04, jwhite -----------------------------
280 -- Augmented original code with additional filter
281
282 /* -- Original Logic
283
284
285 SELECT
286 RESOURCE_LIST_ID, RESOURCE_ID, ORGANIZATION_ID, EXPENDITURE_CATEGORY, REVENUE_CATEGORY
287 INTO
288 L_RESOURCE_LIST_ID, L_RESOURCE_ID, L_ORGANIZATION_ID,L_EXPENDITURE_CATEGORY, L_REVENUE_CATEGORY
289 From pa_resource_list_members
290 Where RESOURCE_LIST_ID = rec1.resource_list_id
291 And resource_list_member_id = (Select parent_member_id from pa_resource_list_members
292 where RESOURCE_LIST_ID = rec1.resource_list_id
293 and vendor_id= p_old_vendor_id);
294 */
295
296
297 -- FP.M Data Model Logic
298
299 /*Bug# 4029384*/
300 select group_resource_type_id
301 into l_res_grouped
302 from pa_resource_lists_all_BG
303 where RESOURCE_LIST_ID = rec1.resource_list_id;
304
305 IF (l_res_grouped <> 0) THEN /*To check if resource list is grouped */
306
307 SELECT
308 RESOURCE_LIST_ID, RESOURCE_ID, ORGANIZATION_ID, EXPENDITURE_CATEGORY, REVENUE_CATEGORY
309 INTO
310 L_RESOURCE_LIST_ID, L_RESOURCE_ID, L_ORGANIZATION_ID,L_EXPENDITURE_CATEGORY, L_REVENUE_CATEGORY
311 From pa_resource_list_members
312 Where RESOURCE_LIST_ID = rec1.resource_list_id
313 And resource_list_member_id = (Select parent_member_id from pa_resource_list_members
314 where RESOURCE_LIST_ID = rec1.resource_list_id
315 and vendor_id= p_old_vendor_id
316 and nvl(migration_code,'M') = 'M' );
317
318 -- End: FP.M Resource LIst Data Model Impact Changes -----------------------------
319
320 ELSE /*If resource list is not grouped*/
321
322 SELECT
323 RESOURCE_LIST_ID, RESOURCE_ID, ORGANIZATION_ID, EXPENDITURE_CATEGORY, REVENUE_CATEGORY
324 INTO
325 L_RESOURCE_LIST_ID, L_RESOURCE_ID, L_ORGANIZATION_ID,L_EXPENDITURE_CATEGORY, L_REVENUE_CATEGORY
326 From pa_resource_list_members
327 Where RESOURCE_LIST_ID = rec1.resource_list_id
328 and vendor_id =p_old_vendor_id
329 and nvl(migration_code,'M') = 'M';
330
331
332 END IF; /*End of changes of Bug# 4029384*/
333
334 PA_CREATE_RESOURCE.Create_Resource_list_member
335 (p_resource_list_id => rec1.resource_list_id,
336 p_resource_name => l_new_vendor_name,
337 p_resource_type_Code => 'VENDOR',
338 p_alias => l_new_vendor_name,
339 p_sort_order => NULL,
340 p_display_flag => 'Y',
341 p_enabled_flag => 'Y',
342 p_person_id => NULL,
343 p_job_id => NULL,
344 p_proj_organization_id => L_ORGANIZATION_ID,
345 p_vendor_id => p_new_vendor_id,
346 p_expenditure_type => NULL,
347 p_event_type => NULL,
348 p_expenditure_category => l_expenditure_category,
349 p_revenue_category_code => L_REVENUE_CATEGORY,
350 p_non_labor_resource => NULL,
351 p_system_linkage => NULL,
352 p_project_role_id => NULL,
353 p_parent_member_id => l_parent_member_id,
354 p_resource_list_member_id => l_resource_list_member_id,
355 p_track_as_labor_flag => l_track_as_labor_flag,
356 p_err_code => l_err_code,
357 p_err_stage => x_stage,
358 p_err_stack => l_err_stack);
359 End;
360 End If;
361
362
363 x_stage := ' Calling Resource List change api to update summarization data';
364 /* The following code need to be called from API for resource list merger and refresh summary amounts */
365
369 NULL,
366 pa_proj_accum_main.ref_rl_accum(
367 l_err_stack,
368 l_err_code,
370 NULL,
371 rec1.resource_list_id);
372
373 End Loop;
374
375
376
377 end; /** End Summarization **/
378
379 End UPD_PA_DETAILS_SUPPLIER_MERGE;
380
381
382 FUNCTION Allow_Supplier_Merge ( p_vendor_id IN po_vendors.vendor_id%type
383 )
384 RETURN varchar2
385 IS
386 l_budget_exists Varchar2(1);
387 l_allow_merge_flg Varchar2(1); -- FP.M Change
388 BEGIN
389
390 -- FP.M Resource LIst Data Model Impact Changes, 09-JUN-04, jwhite -----------------------------
391 -- Augmented original code with additional filter
392
393 /* -- Original Logic
394
395 select 'Y' into l_budget_exists
396 from pa_resource_assignments assign, pa_resource_list_members member, pa_budget_lines budget
397 where assign.RESOURCE_LIST_MEMBER_ID=member.RESOURCE_LIST_MEMBER_ID
398 and member.vendor_id = p_vendor_id
399 and budget.resource_assignment_id = assign.resource_assignment_id
400 and rownum < 2 ;
401
402
403 */
404
405 -- FP.M Data Model Logic
406
407 select 'Y'
408 into l_budget_exists
409 from pa_resource_assignments assign
410 , pa_resource_list_members member
411 , pa_budget_lines budget
412 where assign.RESOURCE_LIST_MEMBER_ID=member.RESOURCE_LIST_MEMBER_ID
413 and member.vendor_id = p_vendor_id
414 and budget.resource_assignment_id = assign.resource_assignment_id
415 and rownum < 2
416 and nvl(member.migration_code,'M') = 'M';
417
418
419 -- End: FP.M Resource LIst Data Model Impact Changes -----------------------------
420
421
422 -- Since Budget exists for the vendor to be merged Do not allow Supplier merge
423
424 Return 'N';
425
426 -- FP.M change.
427 -- pa_resource_utils.chk_supplier_in_use function checks to see if the given supplier ID is used by any
428 -- planning resource lists or resource breakdown structures. If it is in use, it returns 'Y'; if not,
429 -- it returns 'N'. If the value returned is Y, Supplier merge is not allowed.
430
431 Exception
432 When no_data_found then
433 select decode(pa_resource_utils.chk_supplier_in_use(p_vendor_id),'Y','N','Y')
434 into l_allow_merge_flg
435 from dual;
436 Return l_allow_merge_flg;
437 END Allow_Supplier_Merge;
438
439 /***************************************************************************
440 Procedure : get_asset_addition_flag
441 Purpose : When Expense Reports are sent to AP from PA,
442 the intermediate tables ap_expense_report_headers_all
443 and ap_expense_report_lines_all are populated. A Process
444 process in AP then populates the
445 Invoice Distribution tables. As there is no way in the
446 intermediate tables, to find out if the expense report is
447 associated with a 'Capital Project', which should not be
448 interfaced from AP to FA, unlike Invoice Distribution line
449 table, where asset_addition_flag is used. This API is to
450 find out if the given project_id is a 'CAPITAL' project
451 and if so, populate the 'out' vairable to 'P', else 'U'.
452 Arguments : p_project_id IN - project id
453 x_asset_addition_flag OUT - asset addition flag
454 ****************************************************************************/
455
456
457 PROCEDURE get_asset_addition_flag
458 (p_project_id IN pa_projects_all.project_id%TYPE,
459 x_asset_addition_flag OUT NOCOPY ap_invoice_distributions_all.assets_addition_flag%TYPE)
460 IS
461
462 l_project_type_class_code pa_project_types_all.project_type_class_code%TYPE;
463
464 BEGIN
465
466 /* For Given Project Id, Get the Project_Type_Class_Code depending on the Project_Type */
467 SELECT ptype.project_type_class_code
468 INTO l_project_type_class_code
469 FROM pa_project_types_all ptype,
470 pa_projects_all proj
471 WHERE ptype.project_type = proj.project_type
472 --R12 AND NVL(ptype.org_id, -99) = NVL(proj.org_id, -99)
473 AND ptype.org_id = proj.org_id
474 AND proj.project_id = p_project_id;
475
476 /* IF Project is CAPITAL then set asset_addition_flag to 'P' else 'U' */
477
478 IF (l_project_type_class_code = 'CAPITAL') THEN
479
480 x_asset_addition_flag := 'P';
481
482 ELSE
483
484 x_asset_addition_flag := 'U';
485
486 END IF;
487
488 EXCEPTION
489
490 WHEN OTHERS THEN
491 RAISE;
492
493 END get_asset_addition_flag;
494
495 /***************************************************************************
496 Function : Get_Project_Type
497 Purpose : This function will check if the project id passed to this
498 is a 'CAPITAL' Project.If it is then this will return
499 'P' otherwise 'U'
500 Arguments : p_project_id IN - project id
504 FUNCTION Get_Project_Type
501 Returns 'P' if the project is Capital otherwise 'U'
502 ****************************************************************************/
503
505 (p_project_id IN pa_projects_all.project_id%TYPE)RETURN VARCHAR2 IS
506 l_project_type VARCHAR2(1);
507
508 BEGIN
509
510 /* For Given Project Id, Get the Project_Type_Class_Code depending on the Project_Type */
511
512 SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
513 INTO l_project_type
514 FROM pa_project_types_all ptype,
515 pa_projects_all proj
516 WHERE proj.project_type = ptype.project_type
517 -- R12 AND NVL(ptype.org_id, -99) = NVL(proj.org_id, -99)
518 AND ptype.org_id = proj.org_id
519 AND proj.project_id = p_project_id ;
520
521 RETURN l_project_type;
522
523 EXCEPTION
524 WHEN OTHERS THEN
525 RAISE;
526 END Get_Project_Type;
527
528 -- ==========================================================================================================================================
529 -- Bug 5201382 R12.PJ:XB3:DEV:NEW API TO RETRIEVE THE DATES FOR PROFILE PA_AP_EI_DATE_DEFAULT
530 -- p_transaction_date : API would return transaction date when profile value was set to 'Transaction Date'
531 -- a. For Invoice transaction invoice_date should be passed as parameter
532 -- b. For PO or Receipt Matched Invoice Transactions invoice_date should be passed as parameter
533 -- c. For RCV Transactions transaction_date should be passed.
534 -- d. For payments and discounts ap dist exp_item_date should be passed.
535 -- p_gl_date : API would return transaction date when profile value was set to 'Transaction GL Date'
536 -- a. For Invoice transactions gl_date should be passed b. For payments and discounts the accounting date must be passed
537 -- c. for RCV transactions accounting date should be passed.
538 -- p_po_exp_item_date : API would return the purchase order expenditure item date for po matched cases when profile value was set to
539 -- 'PO Expenditure Item Date/Transaction Date'. This is used for PO matched cases. It may be NULL when
540 -- p_po_distribution_id was passed to the API.
541 -- p_po_distribution_id: The parameter value is used to determine the purchase order expenditure item date for po matched cases when profile
542 -- value was set to 'PO Expenditure Item Date/Transaction Date'. when p_po_exp_item_date was passed then
543 -- p_po_distribution_id is not used to derive the expenditure item date.
544 -- p_creation_date : API would return this date when profile value was set to 'Transaction System Date'
545 -- p_calling_program : a. when called during the PO Match case : PO-MATCH b. When called from Invoice form : APXINWKB
546 -- c. When called from supplier cost xface for discounts : DISCOUNT d. When called from supplier cost xface for Payment: PAYMENT
547 -- e. When called from supplier cost xface for Receipts : RECEIPT
548 -- ==========================================================================================================================================
549 FUNCTION Get_si_cost_exp_item_date ( p_transaction_date IN pa_expenditure_items_all.expenditure_item_date%TYPE,
550 p_gl_date IN pa_cost_distribution_lines_all.gl_date%TYPE,
551 p_po_exp_item_date IN pa_expenditure_items_all.expenditure_item_date%TYPE,
552 p_creation_date IN pa_expenditure_items_all.creation_date%TYPE,
553 p_po_distribution_id IN pa_expenditure_items_all.document_distribution_id%TYPE,
554 p_calling_program IN varchar2 )
555 RETURN date is
556 l_return_date date ;
557 l_pa_exp_date_default varchar2(50) ;
558 l_pa_debug_flag varchar2(1) ;
559
560 cursor c_po_date is
561 select expenditure_item_date
562 from po_distributions_all
563 where po_distribution_id = p_po_distribution_id ;
564
565 BEGIN
566 l_pa_debug_flag := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
567 l_pa_exp_date_default := FND_PROFILE.VALUE('PA_AP_EI_DATE_DEFAULT');
568
569 IF l_pa_debug_flag = 'Y' THEN
570 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
571 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PA_AP_INTEGRATION', 'Default Exp item date profile:'||l_pa_exp_date_default) ;
572 END IF ;
573 END IF ;
574
575 CASE l_pa_exp_date_default
576 WHEN 'Transaction Date' THEN
577 l_return_date := p_transaction_date ;
578 WHEN 'Transaction GL Date' THEN
579 l_return_date := p_gl_date ;
580 WHEN 'Transaction System Date' THEN
581 l_return_date := p_creation_date ;
582 -- Bug: 5262492 (R12.PJ:XB5:QA:APL: PROJECT EI DATE NULL FOR PO/REC MATCHED INVOICE LINE/DISTRIBU
583 WHEN 'Source Document Exp Item Date' THEN
584 IF p_po_exp_item_date is not NULL then
585 l_return_date := p_po_exp_item_date ;
586 ELSE
587 IF l_pa_debug_flag = 'Y' THEN
588 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
589 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PA_AP_INTEGRATION', 'PO expenditure item date is NULL') ;
590 END IF ;
591 END IF ;
592
593 IF p_po_distribution_id is not NULL then
594 open c_po_date ;
595 fetch c_po_date into l_return_date ;
596 close c_po_date ;
597 IF l_pa_debug_flag = 'Y' THEN
598 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
599 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PA_AP_INTEGRATION',
600 'Determining the date based on the PO distribution IDL') ;
601 END IF ;
602 END IF ;
603 ELSE
604 -- Bug : 4940969
605 -- In the case of unmatched invoice the Invoice date must get @ defaulted as the EI date.
606 l_return_date := p_transaction_date ;
607
608 END IF ;
609 END IF ;
610 ELSE
611 l_return_date := NULL ;
612
613 END CASE;
614
615 IF l_pa_debug_flag = 'Y' THEN
616 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
617 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PA_AP_INTEGRATION', 'Date returned :'||to_char(l_return_date, 'DD-MON-YYYY')) ;
618 END IF ;
619 END IF ;
620
621 return l_return_date ;
622
623
624 End Get_si_cost_exp_item_date ;
625
626
627 FUNCTION Get_si_default_exp_org RETURN varchar2 is
628 l_default_exp_org HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE; /* Bug 5555041 */
629 l_organization_id HR_ALL_ORGANIZATION_UNITS_TL.ORGANIZATION_ID%TYPE; /* Bug 5555041 */
630 l_default_exp_org1 HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE; /* Bug 7575377 */
631
632 /* Bug 5555041 - Start */
633 CURSOR c_get_org_id(p_org_name HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE) IS
634 SELECT organization_id
635 FROM hr_all_organization_units_tl
636 WHERE name = p_org_name;
637
638 CURSOR c_get_org_name(p_organization_id HR_ALL_ORGANIZATION_UNITS_TL.ORGANIZATION_ID%TYPE) IS
639 SELECT name
640 FROM per_organization_units
641 WHERE organization_id = p_organization_id;
642 /* Bug 5555041 - End */
643 BEGIN
644 l_default_exp_org := FND_PROFILE.VALUE('PA_DEFAULT_EXP_ORG');
645
646 /* Bug 5555041 - Start */
647 OPEN c_get_org_id(l_default_exp_org);
648 FETCH c_get_org_id INTO l_organization_id;
649 CLOSE c_get_org_id;
650
651 OPEN c_get_org_name(l_organization_id);
652 FETCH c_get_org_name INTO l_default_exp_org1; /* Modified for bug 7575377 */
653 CLOSE c_get_org_name;
654 /* Bug 5555041 - End */
655
656 return l_default_exp_org1 ; /* Modified for bug 7575377 */
657 END Get_si_default_exp_org ;
658
659 END pa_ap_integration;
[Home] [Help]