[Home] [Help]
PACKAGE BODY: APPS.CS_SR_INTERFACE_CP
Source
1 PACKAGE BODY CS_SR_INTERFACE_CP AS
2 /* $Header: cspapgb.pls 120.0.12020000.13 2013/06/05 10:21:52 bkanimoz noship $ */
3
4
5
6 --------------------------------------------------------------------------------
7 -- Procedure Name : INTERFACE_COST_DETAILS
8 --
9 -- Parameters (other than standard ones)
10 --
11 -- IN OUT
12 -- errbuf : This parameter is not used but is a standard
13 -- parameter for concurrent program procedures.
14 -- The function fnd_concurrent.set_completion_status
15 -- is called instead.
16 -- errcode : This parameter is not used but is a standard
17 -- parameter for concurrent program procedures.
18 -- The function fnd_concurrent.set_completion_status
19 -- is called instead.
20 --
21 -- IN
22 -- p_incident_id : Indicates that all the cost records for this SR needs
23 -- to be interfaced to projects
24 -- p_incident_status_id : Indicates that all the cost records with this status id needs
25 -- to be interfaced to projects
26 -- p_incident_status_id : Indicates that all the cost records with this type id needs
27 -- to be interfaced to projects
28 -- p_creation_from_date : Indicates the lower end of the range of dates
29 -- that need to be compared with CREATION_DATE of
30 -- the COST record to pick it up for interface
31 -- p_creation_to_date : Indicates the higher end of the range of dates
32 -- that need to be compared with CREATION_DATE of
33 -- the COST record to pick it up for interface
34 -- p_project _id : Indicates that all the cost records for this Project Id needs
35 -- to be interfaced to projects
36 -- p_project_task_id : Indicates that all the cost records for this Project Task Id needs
37 -- to be interfaced to projects
38 -- p_transaction_type_id : Indicates that all the cost records for this Transaction type Id(Service Activity)
39 -- needs to be interfaced to projects
40 -- p_billing_type : Indicates that all the cost records for this Billing Type(M,E or L) needs
41 -- to be interfaced to projects
42 -- p_source_code : Indicates that all the cost records for this Souce Code(SR,DR or FS) needs
43 -- to be interfaced to projects
44 -- p_source_number : Indicates that all the cost records for this Souce Number (If the source code is SR then
45 -- this is the Incident Nunmber , if it is FS then this is the debrief number and for DR it is the repair number
46 -- is the repair number) needs to be interfaced to projects
47 -- to be interfaced to projects
48 -- p_org_id : Indicates that all the cost records for this org_id (operating unit) needs
49 -- to be interfaced to projects
50 -- p_inv_org_id : Indicates that all the cost records for this inv_org_id (Inventory Organization) needs
51 -- to be interfaced to projects
52 -- p_expenditure_org_id : Indicates that all the cost records for this expenditure_org_id (Expenditure Organization) needs
53 -- to be interfaced to projects
54
55 -- HISTORY
56 --
57 ----------------+------------+--------------------------------------------------
58 -- DATE | UPDATED BY | Change Description
59 ----------------+------------+--------------------------------------------------
60 -- 06-mar-2013 | bkanimoz | Created
64 PROCEDURE interface_cost_details
61 -- | |
62 ----------------+------------+--------------------------------------------------
63
65 ( errbuf IN OUT NOCOPY VARCHAR2
66 , errcode IN OUT NOCOPY INTEGER
67 , p_creation_from_date IN VARCHAR2
68 , p_creation_to_date IN VARCHAR2
69 , p_project_id IN NUMBER
70 , p_project_task_id IN NUMBER
71 , p_transaction_type_id IN NUMBER
72 , p_billing_type IN VARCHAR2
73 , p_incident_id IN NUMBER
74 , p_incident_status_id IN NUMBER
75 , p_source_code IN VARCHAR2
76 , p_source_number IN NUMBER
77 , p_org_id IN NUMBER
78 , p_inv_org_id IN NUMBER
79 , p_expenditure_org_id IN NUMBER
80 --, p_api_version_number IN NUMBER
81 --, p_init_msg_list IN VARCHAR2
82 --, p_commit IN VARCHAR2
83 --, p_validation_level IN NUMBER
84 ) IS
85
86 l_text VARCHAR2(4000);
87 l_transaction_source VARCHAR2(30);
88 l_batch_name VARCHAR2(240);
89 l_transaction_date DATE;
90 l_employee_num VARCHAR2(30);
91 l_organization_name VARCHAR2(240);
92 l_expenditure_item_date DATE;
93 l_project_num VARCHAR2(25);
94 l_task_num VARCHAR2(25);
95 l_expenditure_type VARCHAR2(30);
96 l_quantity NUMBER;
97 l_project_rate_flag VARCHAR2(1);
98 l_raw_cost NUMBER;
99 l_extended_cost NUMBER;
100 l_transaction_status_code VARCHAR2(1);
101 l_cost_id NUMBER;
102 l_orig_ref VARCHAR2(30);
103 l_UNMAT_NEG_TXN_FLAG VARCHAR2(1);
104 l_system_linkage VARCHAR2(30);
105 l_created_by NUMBER(15);
106 l_creation_date DATE;
107 l_last_updated_by NUMBER(15);
108 l_last_update_date DATE;
109 l_denom_currency_code VARCHAR2(15);
110 l_billable_flag VARCHAR2(1);
111 l_project_id NUMBER;
112 l_task_id NUMBER;
113 l_cost_source_code varchar2(10);
114 l_cost_source_id NUMBER;
115 l_person_id NUMBER(15);
116 l_organization_id NUMBER(15);
117 l_item_id NUMBER;
118 l_uom VARCHAR2(30);
119 l_currency VARCHAR2(15);
120 l_source_id NUMBER;
121 l_source_code VARCHAR2(5);
122 l_exp_ending_date DATE;
123 l_estimate_Detail_id NUMBER;
124 l_project_currency VARCHAR2(10);
125 l_conv_rate number;
126 l_costed_flag VARCHAR2(1);
127 l_denom_raw_cost NUMBER;
128 l_charge_exp_org_id NUMBER;
129 l_business_group_id NUMBER;
130 l_business_group_name VARCHAR2(240);
131 p_conv_type_code varchar2(100);
132 l_exp_org_id NUMBER;
133 l_project_number varchar2(100);
134 l_project_task_number varchar2(100);
135 L_LOG_MODULE CONSTANT VARCHAR2(255):= 'cs.plsql.' || 'CS_SR_INTERFACE_CP.INTERFACE_COST_DETAILS' || '.';
136 l_cost_org_id number;
137 l_success_count NUMBER :=0;
138 l_failure_count NUMBER :=0;
139 l_count NUMBER :=0;
140 l_ret BOOLEAN;
141 l_report_caption VARCHAR2(4000);
142 l_interface_warning EXCEPTION;
143 l_interface_error EXCEPTION;
144 p_msg_data VARCHAR2(1000);
145 j NUMBER := 1;
146 m NUMBER := 1;
147 l_err_cost_id NUMBER ;
148 l_succ_cost_id NUMBER;
149 p_msg_application VARCHAR2(50);
150 p_msg_type VARCHAR2(1);
151 -- p_msg_data VARCHAR2(4000);
152 p_msg_token1 VARCHAR2(25);
153 p_msg_token2 VARCHAR2(25);
154 p_msg_token3 VARCHAR2(25);
155 p_msg_count NUMBER;
156 l_incident_number VARCHAR2(64);
157
158
159 type output_rec is record(cost_id number , incident_number varchar2(50),reject_reason varchar2(1000));
160
161 type asso_array is table of output_rec index by binary_integer;
162
163 l_err_cst_rec asso_array;
164 l_succ_cst_rec asso_array;
165
166
167
168
169 CURSOR get_project_attr is
170 SELECT ctb.transaction_source ,
171 ctb.expenditure_type ,
172 ctb.system_linkage_function ,
173 NVL(ctb.use_project_rate_flag,'N') ,
174 csd.cost_id ,
175 csd.transaction_date ,
176 csd.quantity ,
177 csd.extended_cost ,
178 csd.currency_code ,
179 csd.inventory_item_id ,
180 csd.unit_of_measure_code ,
181 ced.project_id ,
182 ced.project_task_id,
183 ced.estimate_detail_id,
184 ced.source_code,
185 ced.source_id,
186 ced.expenditure_org_id,
187 (SELECT segment1 from pa_projects_all pp where pp.project_id =ced.project_id ) ProjectNumber,
188 (SELECT task_number from pa_tasks pt where pt.task_id =ced.project_task_id ) ProjectTaskNumber,
189 csd.org_id,
190 cia.incident_number
191 FROM cs_cost_details csd,
192 cs_estimate_details ced,
193 cs_txn_billing_types ctb,
194 cs_incidents_all_b cia
198 AND nvl(ctb.interface_to_pa_flag,'N')='Y'
195 WHERE cia.incident_id = ced.incident_id
196 AND ced.estimate_detail_id = csd.estimate_detail_id
197 AND csd.txn_billing_type_id = ctb.txn_billing_type_id
199 -- and cia.incident_number ='68584' --remove this later
200 AND csd.transaction_date BETWEEN NVL(p_creation_from_date,csd.transaction_date-1) AND NVL(p_creation_to_date,csd.transaction_date+1)
201 AND ced.project_id = NVL(p_project_id,ced.project_id)
202 AND ced.project_task_id = NVL(p_project_task_id,ced.project_task_id)
203 AND csd.transaction_type_id = NVL(p_transaction_type_id, csd.transaction_type_id)
204 AND cia.incident_status_id = NVL(p_incident_status_id, cia.incident_status_id)
205 AND cia.incident_id = NVL(p_incident_id, cia.incident_id)
206 AND csd.source_code = NVL(p_source_code,csd.source_code)
207 AND csd.source_id = NVL(p_source_number,csd.source_id)
208 AND csd.org_id = NVL(p_org_id , csd.org_id)
209 AND csd.inventory_org_id = NVL(p_inv_org_id , csd.inventory_org_id)
210 AND ced.expenditure_org_id = NVL(p_expenditure_org_id,ced.expenditure_org_id)
211 AND ctb.billing_type = NVL(p_billing_type , ctb.billing_type) --bug 16806860
212 --and csd.cost_id= 39302;
213 AND ced.order_header_id is not null --can interface the cost line of only those charges which are submitted to OM
214 AND nvl(csd.interfaced_to_pa_flag,'N') in ('E','N');
215
216
217 BEGIN
218 /* --fnd_file.put_line(FND_FILE.OUTPUT,'exception :'||sqlerrm);
219 fnd_file.put_line(FND_FILE.log,'p_creation_from_date :'||p_creation_from_date);
220 fnd_file.put_line(FND_FILE.log,'p_creation_to_date :'||p_creation_to_date);
221 fnd_file.put_line(FND_FILE.log,'p_project_id :'||p_project_id);
222 fnd_file.put_line(FND_FILE.log,'p_project_task_id :'||p_project_task_id);
223 fnd_file.put_line(FND_FILE.log,'p_transaction_type_id :'||p_transaction_type_id);
224 fnd_file.put_line(FND_FILE.log,'p_billing_type :'||p_billing_type);
225 fnd_file.put_line(FND_FILE.log,'p_incident_id :'||p_incident_id);
226 fnd_file.put_line(FND_FILE.log,'p_incident_status_id :'||p_incident_status_id);
227 fnd_file.put_line(FND_FILE.log,'p_source_code :'||p_source_code);
228 fnd_file.put_line(FND_FILE.log,'p_source_number :'||p_source_number);
229 fnd_file.put_line(FND_FILE.log,'p_org_id :'||p_org_id);
230 fnd_file.put_line(FND_FILE.log,'p_inv_org_id :'||p_inv_org_id);*/
231 FND_MSG_PUB.initialize;
232
233 /*l_err_cst_rec := l_cst_arry();
234 l_succ_cst_rec := l_cst_arry();
235 l_err_rec := l_err_arry();*/
236
237
238 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
239 THEN
240 FND_LOG.String
241 (
242 FND_LOG.level_procedure
243 , L_LOG_MODULE || 'start_time'
244 , 'The start time is ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
245 );
246
247
248 FND_LOG.String
249 (
250 FND_LOG.level_procedure
251 , L_LOG_MODULE || 'start'
252 , 'Inside ' || 'Interface Cost Details ' || ', called with parameters below:'
253 );
254
255 FND_LOG.String
256 (
257 FND_LOG.level_procedure
258 , L_LOG_MODULE || 'param 1'
259 , 'errbuf:' || errbuf
260 );
261 FND_LOG.String
262 (
263 FND_LOG.level_procedure
264 , L_LOG_MODULE || 'param 2'
265 , 'errcode:' || errcode
266 );
267
268
269 FND_LOG.String
270 (
271 FND_LOG.level_procedure
272 , L_LOG_MODULE || 'param 3'
273 , 'p_creation_from_date: ' || p_creation_from_date
274 );
275 FND_LOG.String
276 (
277 FND_LOG.level_procedure
278 , L_LOG_MODULE || 'param 4'
279 , 'p_creation_to_date: ' || p_creation_to_date
280 );
281
282 FND_LOG.String
283 (
284 FND_LOG.level_procedure
285 , L_LOG_MODULE || 'param 5'
286 , 'p_project_id ' || p_project_id
287 );
288
289
290 FND_LOG.String
291 (
292 FND_LOG.level_procedure
293 , L_LOG_MODULE || 'param 6'
294 , 'p_project_task_id ' || p_project_task_id
295 );
296
297
298 FND_LOG.String
299 (
300 FND_LOG.level_procedure
301 , L_LOG_MODULE || 'param 7'
302 , 'p_transaction_type_id ' || p_transaction_type_id
303 );
304
305 FND_LOG.String
306 (
307 FND_LOG.level_procedure
308 , L_LOG_MODULE || 'param 8'
309 , 'p_billing_type ' || p_billing_type
310 );
311
312 FND_LOG.String
313 (
314 FND_LOG.level_procedure
315 , L_LOG_MODULE || 'param 9'
316 , 'p_incident_id ' || p_incident_id
317 );
318
319 FND_LOG.String
320 (
321 FND_LOG.level_procedure
322 , L_LOG_MODULE || 'param 10'
323 , 'p_incident_status_id ' || p_incident_status_id
324 );
325
326 FND_LOG.String
327 (
328 FND_LOG.level_procedure
329 , L_LOG_MODULE || 'param 11'
330 , 'p_source_code ' || p_source_code
331 );
332
333 FND_LOG.String
334 (
335 FND_LOG.level_procedure
336 , L_LOG_MODULE || 'param 12'
337 , 'p_source_number ' || p_source_number
338 );
339
340 FND_LOG.String
341 (
342 FND_LOG.level_procedure
343 , L_LOG_MODULE || 'param 13'
344 , 'p_org_id ' || p_org_id
345 );
346
347 FND_LOG.String
348 (
349 FND_LOG.level_procedure
350 , L_LOG_MODULE || 'param 14'
351 , 'p_inv_org_id ' || p_inv_org_id
352 );
353
354
355 End if;
356
357
358
362
359
360
361 /*
363 begin
364
365 -- dbms_application_info.set_client_info('458');
366 mo_global.init('PA');
367 fnd_global.apps_initialize(1318,20638,170);
368 mo_global.set_policy_context('S','458');
369 end;
370
371
372
373 o PA_EXPENDITURE_GROUPS_ALL
374
375 o PA_EXPENDITURES_ALL
376
377 o PA_EXPENDITURE_ITEMS_ALL
378
379 o PA_COST_DISTRIBUTION_LINES_ALL
380
381 o PA_EXPENDITURE_COMMENTS
382
383 */
384
385
386
387
388 Open get_project_attr;
389 LOOP
390
391 BEGIN
392
393 Fetch get_project_attr
394 INTO l_transaction_source,
395 l_expenditure_type,
396 l_system_linkage,
397 l_project_rate_flag,
398 l_cost_id,
399 l_transaction_date,
400 l_quantity,
401 l_extended_cost,
402 l_currency,
403 l_item_id,
404 l_uom,
405 l_project_id,
406 l_task_id,
407 l_estimate_detail_id,
408 l_source_code,
409 l_source_id,
410 l_exp_org_id,
411 l_project_number,
412 l_project_task_number,
413 l_cost_org_id,
414 l_incident_number;
415
416 l_count := l_count +1;
417
418
419 Exit when get_project_attr%NOTFOUND;
420
421 /*
422 EBS Projects provides an interface table PA_TRANSACTION_INTERFACE_ALL as a staging location for project transactions before they can be
423 physically loaded into EBS Projects. Every row in the interface table constitutes an expenditure line in Projects.
424 During the import process, EBS Projects will validate the data before they are imported.
425 The validation to be performed varies, depending on the expenditure type class chosen for the row.
426
427 */
428
429
430 /*Parameter #1
431 TRANSACTION_SOURCE
432 Transaction Source is derived from the project settings region on the SAC setup UI
433 transaction Source is defined for the SAC+Billing Type combination
434 */
435
436 l_transaction_source := l_transaction_source;
437
438 /* Parameter #2
439 BATCH_NAME
440 Service should use the Batch Name 'SERVICE'.This will allow users to quickly differentiate the lines pushed by service
441 */
442
443 l_batch_name := 'SERVICE';
444
445
446 /* Parameter #3
447 EXPENDITURE_ENDING_DATE
448 This is the last day of the expenditure week for this transaction.Function pa_utils.newgetweekending(p_date in date)
449 takes in the transaction date and returns the expenditure ending date for that week
450 */
451
452
453 l_exp_ending_date := pa_utils.newgetweekending(l_transaction_date);
454
455 /* Parameter #4
456 EMPLOYEE NUMBER
457 This is the employee number who incurred the expense
458 */
459
460 l_employee_num := NULL;
461
462
463 Begin
464
465 If l_source_code ='SR' THEN -- pass the created_by in the cs_estimate_details table to fetch the employee number
466
467 SELECT pap.employee_number
468 INTO l_employee_num
469 FROM cs_estimate_details ced,
470 fnd_user fu ,
471 per_all_people_f pap
472 WHERE ced.created_by = fu.user_id
473 AND pap.person_id = fu.employee_id
474 AND ced.estimate_detail_id = l_estimate_detail_id
475 AND to_date(ced.creation_date) between nvl(pap.effective_start_date,sysdate) and nvl(pap.effective_end_date,sysdate);
476
477 Else -- for a debrief line we need to get the technicians name
478
479 SELECT pap.employee_number
480 INTO l_employee_num
481 FROM csf_debrief_lines cdl,
482 csf_debrief_headers cdh,
483 jtf_task_assignments jta,
484 jtf_rs_resource_extns jrr,
485 fnd_user fu ,
486 PER_ALL_PEOPLE_F pap
487 WHERE cdh.task_assignment_id = jta.task_assignment_id
488 AND cdl.debrief_header_id = cdh.debrief_header_id
489 AND cdl.debrief_line_id = l_source_id
490 AND jrr.resource_id = jta.resource_id
491 AND pap.person_id = fu.employee_id
492 AND fu.user_id = jrr.user_id ;
493
494 END IF;
495
496 Exception
497
498 WHEN TOO_MANY_ROWS THEN
499
500 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
501 THEN
502 FND_LOG.String
503 (
504 FND_LOG.level_unexpected
505 , L_LOG_MODULE || 'TOO_MANY_ROWS'
506 , 'Not able to fetch Employeee Number '
507 );
508 END IF ;
509
510 p_msg_data :='Not able to fetch Employeee Number ';
511
512 FND_MESSAGE.Set_Name('CS', p_msg_data);
513 -- --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
514 FND_MSG_PUB.Add;
515
516
517
518 RAISE l_interface_error;
519
520 WHEN NO_DATA_FOUND THEN
521 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
522 THEN
523 FND_LOG.String
524 (
525 FND_LOG.level_unexpected
526 , L_LOG_MODULE || 'NO_DATA_FOUND'
527 , 'Not able to fetch Employeee Number '
528 );
529 END IF ;
530 p_msg_data :='Not able to fetch Employeee Number ';
531
532 FND_MESSAGE.Set_Name('CS', p_msg_data);
533 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
537
534 FND_MSG_PUB.Add;
535
536 RAISE l_interface_error;
538 --FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_PURGSET_INV');
539 -- FND_MSG_PUB.ADD;
540
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542
543 WHEN OTHERS then
544
545 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
546 THEN
547 FND_LOG.String
548 (
549 FND_LOG.level_unexpected
550 , L_LOG_MODULE || 'OTHERS'
551 , 'Not able to fetch Employeee Number '
552 );
553 END IF ;
554
555 p_msg_data :='Not able to fetch Employeee Number ';
556
557 FND_MESSAGE.Set_Name('CS', p_msg_data);
558 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
559 FND_MSG_PUB.Add;
560 RAISE l_interface_error;
561
562 End;
563
564
565
566
567
568 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
569 THEN
570 FND_LOG.String
571 (
572 FND_LOG.level_procedure
573 , L_LOG_MODULE || 'After Fetching Employee Number'
574 , 'Employee Number :'||l_employee_num
575 );
576
577 End if;
578
579 /* Parameter #5
580 ORGANIZATION_NAME
581 This is the org that incurred the expense.
582 Leave this blank
583 */
584
585
586
587 BEGIN
588 SELECT name
589 INTO l_organization_name
590 FROM hr_all_organization_units --need to clarify whether to use hr_organization_units
591 WHERE organization_id = (select expenditure_org_id from cs_estimate_details where estimate_detail_id =l_estimate_detail_id );
592
593 Exception
594 WHEN TOO_MANY_ROWS THEN
595 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
596 THEN
597 FND_LOG.String
598 (
599 FND_LOG.level_unexpected
600 , L_LOG_MODULE || 'TOO_MANY_ROWS'
601 , 'Not able to fetch the organization name '
602 );
603 END IF ;
604 p_msg_data :='Not able to fetch organization name ';
605 FND_MESSAGE.Set_Name('CS', p_msg_data);
606 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
607 FND_MSG_PUB.Add;
608
609 RAISE l_interface_error;
610
611 WHEN NO_DATA_FOUND THEN
612 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
613 THEN
614 FND_LOG.String
615 (
616 FND_LOG.level_unexpected
617 , L_LOG_MODULE || 'NO_DATA_FOUND'
618 , 'Not able to fetch the organization name '
619 );
620 END IF ;
621
622 --FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_PURGSET_INV');
623 -- FND_MSG_PUB.ADD;
624 p_msg_data :='Not able to fetch organization name ';
625 FND_MESSAGE.Set_Name('CS', p_msg_data);
626 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
627 FND_MSG_PUB.Add;
628
629 RAISE l_interface_error;
630
631 WHEN OTHERS then
632 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
633 THEN
634 FND_LOG.String
635 (
636 FND_LOG.level_unexpected
637 , L_LOG_MODULE || 'OTHERS'
638 , 'Not able to fetch the organization name '
639 );
640 END IF ;
641 p_msg_data :='Not able to fetch organization name ';
642 FND_MESSAGE.Set_Name('CS', p_msg_data);
643 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
644 FND_MSG_PUB.Add;
645 RAISE l_interface_error;
646
647 End;
648
649
650
651 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
652 THEN
653 FND_LOG.String
654 (
655 FND_LOG.level_procedure
656 , L_LOG_MODULE || 'After Fetching Expenditure Organization Name'
657 , 'Organization Name :'||l_organization_name
658 );
659
660 End if;
661
662 /* Parameter #6
663 EXPENDITURE_ITEM_DATE
664 Date on which the transaction occured
665 Transaction Date of the cost record
666 */
667
668 l_expenditure_item_date := l_transaction_date;
669
670
671
672
673 /* Parameter #7
674 PROJECT_NUMBER
675 Project for which the expense is incurred
676 Leave Blank (will populate project_id instead)
677 */
678
679 Begin
680
681 SELECT segment1
682 INTO l_project_num
683 FROM pa_projects_all
684 WHERE project_id = (select project_id from cs_estimate_details where estimate_detail_id =l_estimate_detail_id );
685
686 Exception
687 WHEN TOO_MANY_ROWS THEN
688 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
689 THEN
690 FND_LOG.String
691 (
692 FND_LOG.level_unexpected
693 , L_LOG_MODULE || 'TOO_MANY_ROWS'
694 , 'Not able to fetch the Project Number '
695 );
696 END IF ;
697 p_msg_data :='Not able to fetch Project Number ';
698 FND_MESSAGE.Set_Name('CS', p_msg_data);
699 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
700 FND_MSG_PUB.Add;
701 RAISE l_interface_error;
702
703 WHEN NO_DATA_FOUND THEN
704 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
705 THEN
706 FND_LOG.String
707 (
708 FND_LOG.level_unexpected
712 END IF ;
709 , L_LOG_MODULE || 'NO_DATA_FOUND'
710 , 'Not able to fetch the Project Number '
711 );
713
714 --FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_PURGSET_INV');
715 -- FND_MSG_PUB.ADD;
716
717 p_msg_data :='Not able to fetch Project Number ';
718 FND_MESSAGE.Set_Name('CS', p_msg_data);
719 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
720 FND_MSG_PUB.Add;
721
722 RAISE l_interface_error;
723 WHEN OTHERS then
724 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
725 THEN
726 FND_LOG.String
727 (
728 FND_LOG.level_unexpected
729 , L_LOG_MODULE || 'OTHERS'
730 , 'Not able to fetch the Project Number '
731 );
732 END IF ;
733 p_msg_data :='Not able to fetch Project Number ';
734 FND_MESSAGE.Set_Name('CS', p_msg_data);
735 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
736 FND_MSG_PUB.Add;
737 RAISE l_interface_error;
738
739 End;
740
741
742
743
744 /* Parameter #8
745 TASK_NUMBER
746 Task for which the expense is incurred
747 Leave Blank (will populate task_id instead)
748 */
749 Begin
750
751 SELECT task_number
752 INTO l_task_num
753 FROM pa_tasks where task_id = (select project_task_id from cs_estimate_details where estimate_detail_id =l_estimate_detail_id );
754 Exception
755 WHEN TOO_MANY_ROWS THEN
756 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
757 THEN
758 FND_LOG.String
759 (
760 FND_LOG.level_unexpected
761 , L_LOG_MODULE || 'TOO_MANY_ROWS'
762 , 'Not able to fetch the Project Task Number '
763 );
764 END IF ;
765 p_msg_data :='Not able to fetch Project Task Number ';
766 FND_MESSAGE.Set_Name('CS', p_msg_data);
767 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
768 FND_MSG_PUB.Add;
769
770
771
772 RAISE l_interface_error;
773
774 WHEN NO_DATA_FOUND THEN
775 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
776 THEN
777 FND_LOG.String
778 (
779 FND_LOG.level_unexpected
780 , L_LOG_MODULE || 'NO_DATA_FOUND'
781 , 'Not able to fetch the Project Task Number '
782 );
783 END IF ;
784
785 --FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_PURGSET_INV');
786 -- FND_MSG_PUB.ADD;
787 p_msg_data :='Not able to fetch Project Task Number ';
788 FND_MESSAGE.Set_Name('CS', p_msg_data);
789 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
790 FND_MSG_PUB.Add;
791
792
793 RAISE l_interface_error;
794 WHEN OTHERS then
795 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
796 THEN
797 FND_LOG.String
798 (
799 FND_LOG.level_unexpected
800 , L_LOG_MODULE || 'OTHERS'
801 , 'Not able to fetch the Project Task Number '
802 );
803 END IF ;
804 p_msg_data :='Not able to fetch Project Task Number ';
805 FND_MESSAGE.Set_Name('CS', p_msg_data);
806 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
807 FND_MSG_PUB.Add;
808
809
810
811 RAISE l_interface_error;
812
813 End;
814
815
816 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
817 THEN
818 FND_LOG.String
819 (
820 FND_LOG.level_procedure
821 , L_LOG_MODULE || 'After Fetching Project Number and Task Number'
822 , 'Project Number :'||l_project_num ||'Project Task Number :'||l_task_num
823 );
824
825
826 End if;
827
828 /* Parameter #9
829 EXEPNDITURE_TYPE
830 Derived from the project setups on the SAC UI
831 */
832 l_expenditure_type := l_expenditure_type;
833
834
835 /* Parameter #10 ,
836 NON_LABOR_RESOURCE
837 N/A
838 */
839
840
841 /* Parameter #11 ,
842 NON_LABOR_RESOURCE_ORG_NAME
843 N/A
844 */
845
846
847 /* Parameter #12 ,
848 QUANTITY
849 Quantity from the cost record
850 */
851 l_quantity := l_quantity;
852
853
854 /* Parameter #13 ,
855 RAW_COST
856 This is the extended cost (unit cost * Quantity) of the cost record
857 This extended cost has to be converted to the functional currency of the Expenditure Org
858 */
859
860 If l_project_rate_flag = 'Y' then
861 l_raw_cost := NULL;
862 Else
863 --l_raw_cost := l_extended_cost;
864 Begin
865
866 select projfunc_currency_code
867 INTO l_project_currency
868 FROM PA_PROJECTS_ALL
869 WHERE project_id = l_project_id;
870
871 Exception
872 WHEN TOO_MANY_ROWS THEN
873 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
874 THEN
875 FND_LOG.String
876 (
877 FND_LOG.level_unexpected
878 , L_LOG_MODULE || 'TOO_MANY_ROWS'
879 , 'Not able to fetch the Project Currency Code '
880 );
881 END IF ;
882 p_msg_data :='Not able to fetch Project Currency Code ';
883
884 RAISE l_interface_error;
885
886 WHEN NO_DATA_FOUND THEN
887 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
888 THEN
889 FND_LOG.String
890 (
891 FND_LOG.level_unexpected
892 , L_LOG_MODULE || 'NO_DATA_FOUND'
896
893 , 'Not able to fetch the Project Currency Code '
894 );
895 END IF ;
897 --FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_PURGSET_INV');
898 -- FND_MSG_PUB.ADD;
899 p_msg_data :='Not able to fetch Project Currency Code ';
900 FND_MESSAGE.Set_Name('CS', p_msg_data);
901 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
902 FND_MSG_PUB.Add;
903
904
905 RAISE l_interface_error;
906 WHEN OTHERS then
907 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
908 THEN
909 FND_LOG.String
910 (
911 FND_LOG.level_unexpected
912 , L_LOG_MODULE || 'OTHERS'
913 , 'Not able to fetch the Project Currency Code '
914 );
915 END IF ;
916 p_msg_data :='Not able to fetch Project Currency Code ';
917 FND_MESSAGE.Set_Name('CS', p_msg_data);
918 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
919 FND_MSG_PUB.Add;
920
921 RAISE l_interface_error;
922 End;
923
924
925 l_conv_rate := GL_CURRENCY_API.get_rate_sql(l_currency
926 ,l_project_currency,
927 l_transaction_date,
928 p_conv_type_code);
929
930 l_raw_cost := l_extended_cost * l_conv_rate;
931
932 --pending currency conversion
933 End If;
934
935
936
937
938 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
939 THEN
940 FND_LOG.String
941 (
942 FND_LOG.level_procedure
943 , L_LOG_MODULE || 'After Fetching Raw Cost'
944 , 'Raw Cost :'||l_raw_cost
945 );
946
947 End if;
948
949 /* Parameter #14 ,
950 EXPENDITURE_COMMENT
951 N/A
952 */
953
954
955 /* Parameter #15 ,
956 TRANSACTION_STATUS_CODE
957 Service will set this to "P" meaning Pending
958 */
959 l_transaction_status_code :='P';
960
961
962 /* Parameter #16 ,
963 TRANSACTION_REJECTION_CODE
964 N/A
965 */
966
967
968 /* Parameter #17 ,
969 EXPENDITURE_ID
970 N/A
971 */
972
973
974 /* Parameter #18 ,
975 ORIG_TRANSCTION_REFERENCE
976 Pass the Cost record Id to this
977 */
978
979 --if l_cost_id <> 39262 then
980 l_orig_ref := l_cost_id;
981 --end if;
982
983
984
985 /* Parameter #19 ,
986 ATTRIBUTE_CATEGORY
987 N/A
988 */
989
990 /* Parameter #20 ,
991 ATTRIBUTE1_ATTRIBUTE10
992 N/A
993 */
994
995 /* Parameter #21 ,
996 RAW_COST_RATE
997 N/A
998 Raw cost rate does not need to be provided since total raw cost is provided.
999 */
1000
1001 /* Parameter #22 ,
1002 INTERFACE_ID
1003 N/A
1004 */
1005
1006 /* Parameter #23 ,
1007 UNMATCHED_NEGATIVE_TXN_FLAG
1008 If the cost record has a negative amount the value of this field will be Y
1009
1010 If this column is set to Y, Transaction Import will bypass the matching validation logic
1011 that is usually executed for adjustments (negative transactions).
1012 If this column is set to N, Oracle Projects finds the matching item and populates
1013 */
1014
1015 If sign(l_extended_cost) = -1 Then
1016 l_UNMAT_NEG_TXN_FLAG := 'Y';
1017 End if;
1018
1019
1020
1021 /* Parameter #24 ,
1022 EXPENDITURE_ITEM_ID
1023 N/A
1024 */
1025
1026
1027 /* Parameter #25 ,
1028 ORG_ID
1029 If this cost originated from manual charges, then use the operating unit of the project expenditure org id stamped on the charge line.
1030 If this cost originated from Debrief, then use the operating unit of the project expenditure org id stamped on the debrief line.
1031 */
1032 --pending clarification
1033
1034
1035
1036
1037 /* Parameter #26 - #31
1038 DR_CODE_COMBINATION_ID , CR_CODE_COMBINATION_ID,CDL_SYSTEM_REFERENCE1 - CDL_SYSTEM_REFERENCE5 ,GL_DATE,BURDENED_COST,
1039 BURDENED_COST_RATE
1040
1041 N/A
1042
1043 (burdened cost is when 1)An expenditure type class of Burden Transaction or
1044 2) A transaction source with the Cost Burdened option enabled ,
1045 Burden transactions have quantities and raw costs equal to zero.
1046 */
1047
1048
1049 /*Parameter #32
1050 SYSTEM_LINKAGE
1051 Obtained from the Project setups
1052
1053 */
1054
1055 l_system_linkage := l_system_linkage;
1056
1057 /*Parameter #33
1058 TXN_INTERFACE_ID
1059 N/A
1060 */
1061
1062 /*Parameter #34
1063 USER_TRANSACTION_SOURCE
1064 N/A - this is required only when TRANSACTION_SOURCE is not populated
1065 Service will always use the seeded transaction source
1066 */
1067
1068 /*Parameter #35 standard WHO column
1069 CREATED BY
1070 */
1071 l_created_by := FND_GLOBAL.USER_ID;
1072
1073 /*Parameter #36
1074 CREATION DATE
1075 */
1076 l_creation_date := SYSDATE;
1077
1078 /*Parameter #37
1079 LAST UPDATED BY
1080 */
1081 l_last_updated_by := FND_GLOBAL.USER_ID;
1082
1083
1084 /*Parameter #38
1085 LAST UPDATE DATE
1086 */
1087 l_last_update_date := SYSDATE;
1088
1089
1090
1091
1092 /*Parameter #39,#40,#41
1093 RECIPT_CURRENCY_AMOUNT,RECIPT_CURRENCY_CODE,RECIPT_EXCHANGE_RATE
1094 N/A Service does not handle Receipts
1095 */
1096
1097
1098
1099 /*Parameter #42
1100 DENOM_CURRENCY_CODE
1101 Currency code for the transaction currency
1102 Pass Currency on the cost record
1106
1103 */
1104
1105 l_denom_currency_code := l_currency;
1107
1108 /*Parameter #43
1109 DENOM_RAW_COST
1110 Raw cost expressed in transaction currency
1111 */
1112
1113
1114
1115 -- confirm -- pending clarification
1116 BEGIN
1117
1118 select nvl(costed_flag ,'N')
1119 into l_costed_flag
1120 from pa_transaction_sources
1121 where SYSTEM_LINKAGE_FUNCTION = l_system_linkage
1122 and transaction_source = l_transaction_source;
1123
1124 Exception
1125 WHEN OTHERS then
1126 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1127 THEN
1128 FND_LOG.String
1129 (
1130 FND_LOG.level_unexpected
1131 , L_LOG_MODULE || 'OTHERS'
1132 , 'Not able to fetch the Costed Flag '
1133 );
1134 END IF ;
1135
1136 End;
1137
1138 If l_project_rate_flag = 'Y' then -- and l_costed_flag ='Y' then
1139 l_denom_raw_cost := null;
1140 Else
1141 if l_costed_flag ='Y' then
1142 l_denom_raw_cost := l_extended_cost ;--pending currency conversion and import raw cost amount check
1143 end if;
1144 End if;
1145
1146
1147
1148
1149
1150
1151
1152 /*Parameter #44
1153 DENOM_BURDENED_COST
1154 N/A
1155 */
1156
1157 /*Parameter #45, #46,#47,#48,#49,#50
1158 Accounted Transactions - N/A as service will always push the cost unaccounted
1159 */
1160
1161 /*parameter # 51 - 59
1162 N/A
1163 */
1164
1165 /*Parameter #60
1166 BILLABLE_FLAG
1167 Always pass N as service will not bill the customers thru Projects
1168 */
1169
1170 l_billable_flag := 'N';
1171
1172
1173 /*Parameter #61 - 70
1174 N/A
1175 */
1176
1177
1178 /*Parameter #71
1179 PROJECT_ID
1180 If the costs originated from manual charge lines, then use the project id stamped on the charge line. If the costs originated from debrief, then use the project id stamped on the debrief line.
1181 For Depot Repair cost records, Depot Repair must populate the charge line with the project id when creating the charge line.
1182
1183 */
1184
1185 l_project_id := l_project_id;
1186 --pending cursor query
1187
1188
1189
1190 /*Parameter #72
1191 TASK_ID
1192 If the costs originated from manual charge lines, then use the task id stamped on the charge line. If the costs originated from debrief, then use the task id stamped on the debrief line.
1193 For Depot Repair cost records, Depot Repair must populate the charge line with the task id when creating the charge line.
1194
1195 */
1196
1197 l_task_id := l_task_id;
1198 --pending cursor query
1199
1200
1201 /*Parameter #73
1202 PERSON_ID
1203
1204 For service ,we will provide this value for labor and expense lines- either internal employee or contingent workers
1205 The person_id is obtained by looking at the source Code on the cost record. For FS lines, the resource id can be found from there, and then trace back to the fnd user and then the person_id
1206 For charge line, use the created by user id and then find the fnd_user to find the person_id.
1207 */
1208
1209 --Person Id is populated only when the ExpType Class is not equal to SUPPLIER INVOICES
1210
1211 If l_system_linkage <> 'VI' then --supplier invoice
1212
1213 Begin
1214
1215 If l_cost_source_code ='SD' then
1216
1217 SELECT fu.person_party_id
1218 -- jrr.user_id,
1219 -- fu.user_name
1220 INTO l_person_id
1221 FROM csf_debrief_lines cdl,
1222 csf_debrief_headers cdh,
1223 jtf_task_assignments jta,
1224 jtf_rs_resource_extns jrr,
1225 fnd_user fu
1226 WHERE cdh.task_assignment_id = jta.task_assignment_id
1227 AND cdl.debrief_header_id = cdh.debrief_header_id
1228 AND cdl.debrief_line_id = l_estimate_Detail_id -- this needs to be changed
1229 AND jrr.resource_id = jta.resource_id
1230 AND fu.user_id = jrr.user_id ;
1231 Else
1232 /*SELECT fu.person_party_id
1233 INTO l_person_id
1234 FROM cs_estimate_details ced,
1235 fnd_user fu
1236 WHERE ced.created_by = fu.user_id
1237 AND ced.estimate_detail_id = l_estimate_Detail_id;-- this needs to be changed*/
1238
1239
1240 SELECT pap.person_id
1241 INTO l_person_id
1242 FROM cs_estimate_details ced,
1243 fnd_user fu ,
1244 per_all_people_f pap
1245 WHERE ced.created_by = fu.user_id
1246 AND pap.party_id = fu.person_party_id
1247 AND ced.estimate_detail_id = l_estimate_Detail_id-- this needs to be changed
1248 AND ced.creation_date between nvl(pap.effective_start_date,sysdate) and nvl(pap.effective_end_date,sysdate);
1249
1250
1251
1252
1253
1254 End if;
1255 Exception
1256 WHEN TOO_MANY_ROWS THEN
1257 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1258 THEN
1259 FND_LOG.String
1260 (
1261 FND_LOG.level_unexpected
1262 , L_LOG_MODULE || 'TOO_MANY_ROWS'
1263 , 'Not able to fetch the Person Id '
1264 );
1265 END IF ;
1266 p_msg_data :='Not able to fetch the Person Id';
1267 FND_MESSAGE.Set_Name('CS', p_msg_data);
1268 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
1269 FND_MSG_PUB.Add;
1270
1271
1272 RAISE l_interface_error;
1273
1274 WHEN NO_DATA_FOUND THEN
1275 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1276 THEN
1277 FND_LOG.String
1278 (
1279 FND_LOG.level_unexpected
1283 END IF ;
1280 , L_LOG_MODULE || 'NO_DATA_FOUND'
1281 , 'Not able to fetch the Person Id '
1282 );
1284 p_msg_data :='Not able to fetch the Person Id';
1285 FND_MESSAGE.Set_Name('CS', p_msg_data);
1286 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
1287 FND_MSG_PUB.Add;
1288
1289 --FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_PURGSET_INV');
1290 -- FND_MSG_PUB.ADD;
1291
1292 RAISE l_interface_error;
1293 WHEN OTHERS then
1294 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1295 THEN
1296 FND_LOG.String
1297 (
1298 FND_LOG.level_unexpected
1299 , L_LOG_MODULE || 'OTHERS'
1300 , 'Not able to fetch the Person Id '
1301 );
1302 END IF ;
1303 p_msg_data :='Not able to fetch the Person Id';
1304 FND_MESSAGE.Set_Name('CS', p_msg_data);
1305 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
1306 FND_MSG_PUB.Add;
1307
1308 RAISE l_interface_error;
1309 End;
1310
1311
1312 Else
1313 l_person_id := NULL; --For VI vendor If will be populated
1314
1315 End if;
1316
1317
1318
1319
1320 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1321 THEN
1322 FND_LOG.String
1323 (
1324 FND_LOG.level_procedure
1325 , L_LOG_MODULE || 'After Fetching Person Id'
1326 , 'Person Id:'||l_person_id
1327 );
1328
1329 End if;
1330
1331 /*Parameter #74
1332 ORGANIZATION_ID
1333 If the costs originated from Charges, then use the project expenditure org id stamped on the charge line. If the costs originated from Debrief, then use the project expenditure org id stamped on the debrief line.
1334
1335 For Depot Repair cost records, Depot Repair must populate the charge line with the expenditure org id when creating the charge line.
1336 */
1337
1338 If l_cost_source_code ='SR' then
1339 l_organization_id := l_exp_org_id; -- this needs to be changed
1340 Else --SD
1341 null;
1342 --pending query from FS
1343 End if;
1344
1345 /*Parameter #75
1346 NON_LABOR_RESOURCE_ORG_ID
1347 N/A
1348 */
1349
1350
1351 /*Parameter #76
1352 VENDOR ID
1353 N/A
1354 */
1355
1356 /*#80 PERSON_BUSINESS_GROUP_ID */
1357
1358 Begin
1359
1360 SELECT pap. business_group_id
1361 into l_business_group_id
1362 FROM cs_estimate_details ced,
1363 fnd_user fu ,
1364 PER_ALL_PEOPLE_F pap
1365 WHERE ced.created_by = fu.user_id
1366 AND pap.person_id = fu.employee_id
1367 AND ced.estimate_detail_id = l_estimate_detail_id and rownum=1;
1368
1369
1370 Select name into
1371 l_business_group_name
1372 from hr_all_organization_units
1373 where organization_id =l_business_group_id and rownum=1;
1374
1375
1376 Exception
1377 WHEN TOO_MANY_ROWS THEN
1378 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1379 THEN
1380 FND_LOG.String
1381 (
1382 FND_LOG.level_unexpected
1383 , L_LOG_MODULE || 'TOO_MANY_ROWS'
1384 , 'Not able to fetch the business_group_id '
1385 );
1386 END IF ;
1387 p_msg_data :='Not able to fetch the business_group_id';
1388 FND_MESSAGE.Set_Name('CS', p_msg_data);
1389 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
1390 FND_MSG_PUB.Add;
1391
1392 RAISE l_interface_error;
1393
1394 WHEN NO_DATA_FOUND THEN
1395 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1396 THEN
1397 FND_LOG.String
1398 (
1399 FND_LOG.level_unexpected
1400 , L_LOG_MODULE || 'NO_DATA_FOUND'
1401 , 'Not able to fetch the business_group_id '
1402 );
1403 END IF ;
1404
1405 --FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_PURGSET_INV');
1406 -- FND_MSG_PUB.ADD;
1407 p_msg_data :='Not able to fetch the business_group_id';
1408 FND_MESSAGE.Set_Name('CS', p_msg_data);
1409 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
1410 FND_MSG_PUB.Add;
1411
1412 RAISE l_interface_error;
1413 WHEN OTHERS then
1414 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1415 THEN
1416 FND_LOG.String
1417 (
1418 FND_LOG.level_unexpected
1419 , L_LOG_MODULE || 'OTHERS'
1420 , 'Not able to fetch the business_group_id '
1421 );
1422 END IF ;
1423 p_msg_data :='Not able to fetch the business_group_id';
1424 FND_MESSAGE.Set_Name('CS', p_msg_data);
1425 --FND_MESSAGE.SET_TOKEN('API_NAME', p_msg_data);
1426 FND_MSG_PUB.Add;
1427
1428 RAISE l_interface_error;
1429 End;
1430
1431
1432
1433 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1434 THEN
1435 FND_LOG.String
1436 (
1437 FND_LOG.level_procedure
1438 , L_LOG_MODULE || 'After Fetching Business Group Id'
1439 , 'l_business_group_id:'||l_business_group_id
1440 );
1441
1442 End if;
1443
1444 /*Parameter #77 - and #82 except 80
1445
1446 N/A
1447 */
1448
1449
1450
1451 /*Parameter 81 and #83
1452
1453 ITEM_ID
1454 */
1455
1456 l_item_id := l_item_id;
1457 l_uom := l_uom;
1458
1459
1460
1461 /*Parameter 84 and #97
1462 N/A
1463 */
1464
1465
1466 Validate_transaction( p_project_id => l_project_id
1467 , p_task_id => l_task_id
1471 , p_person_id => l_person_id
1468 , p_ei_date => l_exp_ending_date
1469 , p_expenditure_type => l_expenditure_type
1470 , p_non_labor_resource => ''
1472 , p_quantity => l_quantity
1473 , p_denom_currency_code => l_denom_currency_code
1474 , p_acct_currency_code => l_project_currency
1475 , p_denom_raw_cost => l_denom_raw_cost
1476 , p_acct_raw_cost => l_raw_cost
1477 , p_acct_rate_type => null
1478 , p_acct_rate_date => null
1479 , p_acct_exchange_rate => null
1480 , p_transfer_ei => null
1481 , p_incurred_by_org_id => l_cost_org_id
1482 , p_nl_resource_org_id => ''
1483 , p_transaction_source =>l_transaction_source
1484 , p_calling_module => 'SERVICE'
1485 , p_vendor_id => null
1486 , p_entered_by_user_id => l_created_by
1487 , p_attribute_category => ''
1488 , p_attribute1 => ''
1489 , p_attribute2 => ''
1490 , p_attribute3 => ''
1491 , p_attribute4 => ''
1492 , p_attribute5 => ''
1493 , p_attribute6 => ''
1494 , p_attribute7 => ''
1495 , p_attribute8 => ''
1496 , p_attribute9 => ''
1497 , p_attribute10 => ''
1498 , p_attribute11 => ''
1499 , p_attribute12 => ''
1500 , p_attribute13 => ''
1501 , p_attribute14 => ''
1502 , p_attribute15 => ''
1503 , p_msg_application => p_msg_application
1504 , p_msg_type => p_msg_type
1505 , p_msg_token1 => p_msg_token1
1506 , p_msg_token2 => p_msg_token2
1507 , p_msg_token3 => p_msg_token3
1508 , p_msg_count => p_msg_count
1509 , p_msg_data => p_msg_data
1510 , p_billable_flag => l_billable_flag);
1511
1512 --fnd_file.put_line(FND_FILE.log,'Aftre calling Validate_transaction :'||p_msg_data);
1513
1514
1515 If p_msg_type = 'E' and p_msg_data is not null then
1516
1517
1518 --FND_MSG_PUB.initialize;
1519 FND_MESSAGE.SET_NAME(p_msg_application,p_msg_data);
1520 ----FND_MESSAGE.SET_TOKEN('pa_transactions_pub.Validate_transaction', p_msg_token1);
1521 FND_MSG_PUB.add;
1522
1523 RAISE l_interface_error;
1524 End if;
1525
1526
1527
1528 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1529 THEN
1530 FND_LOG.String
1531 (
1532 FND_LOG.level_procedure
1533 , L_LOG_MODULE || 'Before inserting into PA_TRANSACTION_INTERFACE_ALL table'
1534 , 'Begins'
1535 );
1536
1537 End if;
1538
1539
1540
1541 Insert into PA_TRANSACTION_INTERFACE_ALL
1542 (
1543 transaction_source, --1
1544 batch_name , --2
1545 expenditure_ending_date, --3
1546 employee_number, --4
1547 organization_name, --5
1548 expenditure_item_date, --6
1549 project_number , --7
1550 task_number, --8
1551 expenditure_type, --9
1552 non_labor_resource, --10
1553 non_labor_resource_org_name,--11
1554 quantity, --12
1555 raw_cost, --13
1556 expenditure_comment, --14
1557 transaction_status_code , --15
1558 transaction_rejection_code, --16
1559 expenditure_id, --17
1560 orig_transaction_reference, --18
1561 attribute_category , --19
1562 attribute1, --20
1563 attribute2, --20
1564 attribute3, --20
1565 attribute4, --20
1566 attribute5, --20
1567 attribute6, --20
1568 attribute7, --20
1569 attribute8, --20
1570 attribute9, --20
1571 attribute10, --20
1572 raw_cost_rate, --21
1573 interface_id, --22
1574 unmatched_negative_txn_flag, --23
1575 expenditure_item_id, --24
1576 org_id, --25
1577 dr_code_combination_id , --26
1578 cr_code_combination_id , --27
1579 cdl_system_reference1, --28
1580 cdl_system_reference2, --28
1581 cdl_system_reference3, --28
1582 cdl_system_reference4, --28
1583 cdl_system_reference5, --28
1584 gl_date, --29
1585 burdened_cost, --30
1586 burdened_cost_rate, --31
1587 system_linkage, --32
1588 txn_interface_id, --33
1589 user_transaction_source, --34
1590 created_by, --35
1591 creation_date, --36
1592 last_updated_by, --37
1593 last_update_date, --38
1594 receipt_currency_amount, --39
1595 receipt_currency_code, --40
1596 receipt_exchange_rate, --41
1597 denom_currency_code, --42
1598 denom_raw_cost, --43
1599 denom_burdened_cost, --44
1600 acct_rate_date, --45
1601 acct_rate_type, --46
1602 acct_exchange_rate, --47
1603 acct_raw_cost, --48
1604 acct_burdened_cost, --49
1605 acct_exchange_rounding_limit,--50
1606 project_currency_code, --51
1607 project_rate_date, --52
1608 project_rate_type, --53
1609 project_exchange_rate, --54
1610 orig_exp_txn_reference1, --55
1611 orig_exp_txn_reference2 , --56
1612 orig_exp_txn_reference3, --57
1613 orig_user_exp_txn_reference,--58
1614 vendor_number , --59
1615 override_to_organization_name,--60
1616 reversed_orig_txn_reference, --61
1617 billable_flag, --62
1618 person_business_group_name, --63
1619 projfunc_currency_code, --64
1620 projfunc_cost_rate_type, --65
1621 projfunc_cost_rate_date, --66
1625 assignment_name, --70
1622 projfunc_cost_exchange_rate, --67
1623 project_raw_cost, --68
1624 project_burdened_cost, --69
1626 work_type_name, --71
1627 accrual_flag, --72
1628 project_id, --73
1629 task_id, --74
1630 person_id, --75
1631 organization_id, --76
1632 non_labor_resource_org_id, --77
1633 vendor_id, --78
1634 override_to_organization_id, --79
1635 assignment_id, --80
1636 work_type_id, --81
1637 person_business_group_id, --82
1638 inventory_item_id, --83
1639 wip_resource_id, --84
1640 unit_of_measure, --85
1641 po_number, --86
1642 po_header_id , --87
1643 po_line_num, --88
1644 po_line_id, --89
1645 person_type, --90
1646 po_price_type, --91
1647 adjusted_expenditure_item_id, --92
1648 fc_document_type, --93
1649 document_type, --94
1650 document_distribution_type, --95
1651 si_assets_addition_flag, --96
1652 sc_xfer_code, --97
1653 adjusted_txn_interface_id, --98
1654 net_zero_adjustment_flag --99
1655
1656
1657 )
1658 Values
1659 (
1660 l_transaction_source, --1 comes from cs_projects.transaction_source
1661 l_batch_name, --2 harcoded
1662 l_exp_ending_date , --3 Function newgetweekending(p_date in date) return date
1663 l_employee_num, --4 N/A - populate the person_id instead of this
1664 l_organization_name, --5 N/A - This is the name of the expenditure_org_id
1665 l_expenditure_item_date, --6 cs_cost_details.transaction_date
1666 l_project_number, --7 N/A - populate the project_id instead of this
1667 l_project_task_number, --8 N/A - populate the task_id instead of this
1668 l_expenditure_type , --9 comes from cs_projects.expenditure_type
1669 null, --10 N/A for service
1670 null, --11 N/A for service
1671 l_quantity, --12 cs_cost_details.quantity
1672 l_raw_cost , --13 cs_cost_details.extended_cost (cost should be in the OUs functional currency)
1673 null, --14 N/A for service
1674 l_transaction_status_code, --15 harcoded
1675 null, --16 Projects will assign this
1676 null, --17 Projects will assign this
1677 l_orig_ref, --18 cs_cost_details.cost_id
1678 null, --19 N/A for service Attribute 1--10
1679 null, --20 N/A for service
1680 null, --20 N/A for service
1681 null, --20 N/A for service
1682 null, --20 N/A for service
1683 null, --20 N/A for service
1684 null, --20 N/A for service
1685 null, --20 N/A for service
1686 null, --20 N/A for service
1687 null, --20 N/A for service
1688 null, --20 N/A for service
1689 null, --21 N/A - we have provided total cost , so this is not applciable
1690 null, --22 Projects will assign this
1691 l_UNMAT_NEG_TXN_FLAG, --23
1692 null, --24 Projects will assign this
1693 l_cost_org_id, --25 cs_cost_details.Operating Unit on the cost record
1694 null, --26 N/A for service , costs will go as unaccounted , this column is the ID of the GL debit account.
1695 null, --27 N/A for service , costs will go as unaccounted , this column is the ID of the GL credit account.
1696 null, --28 N/A for service ,the reference to the record in the external system if it has already been accounted for and interfaced to Oracle General Ledger
1697 null, --28
1698 null, --28
1699 null, --28
1700 null, --28
1701 null, --29 N/A for service
1702 null, --30 N/A for service
1703 null, --31 N/A for service
1704 l_system_linkage, --32 cs_projects.function(expenditure type class_
1705 null, --33 Projects will assign this (UNIQUE indentifier for this table)
1706 null, --34 N/A
1707 FND_GLOBAL.USER_ID, --35
1708 sysdate, --36
1709 FND_GLOBAL.USER_ID, --37
1710 sysdate, --38
1711 null, --39 N/A for service , as service does not handle receipts
1712 null, --40 N/A for service , as service does not handle receipts
1713 null, --41 N/A for service , as service does not handle receipts
1714 l_denom_currency_code, --42 cs_cost_details.currency_code
1715 l_raw_cost , --43 Service will calculate the qty x unit cost in the denom_currency_code and put the value in this column.
1716 null, --44 NA for service
1717 null, --45 NA for service
1718 null, --46 NA for service
1719 null, --47 NA for service
1720 null, --48 NA for service
1721 null, --49 NA for service
1722 null, --50 NA for service
1723 null, --51 Project will assign this
1724 null, --52 NA for service
1725 null, --53 NA for service
1726 null, --54 NA for service
1727 null, --55 NA for service
1728 null, --56 NA for service
1729 null, --57 NA for service
1730 null, --58 NA for service
1731 null, --59 NA for service
1732 null, --60 NA for service
1733 null, --61 NA for service
1737 null, --65 NA for service
1734 'N' , --62 hard coded service wil not bill customers thru projects
1735 l_business_group_name, --63 NA for service
1736 null, --64 NA for service
1738 null, --66 NA for service
1739 null, --67 NA for service
1740 null, --68 NA for service
1741 null, --69 NA for service
1742 null, --70 NA for service
1743 null, --71 NA for service
1744 null, --72 NA for service
1745 null, --73 cs_incidents_all_b.project_id
1746 null, --74 cs_incidents_all_b.project_id
1747 null, --75 person_id mapped to the USER differs for SR and SD - should nto populate for SUPPLIER invoices
1748 null, --76 expenditure_org_id
1749 null, --77 NA for service
1750 null, --78 Incase of SUPPLIER Invoices , person_id mapped to the USER differs for SR and SD (same as 73)
1751 null, --79 NA for service
1752 null, --80 NA for service
1753 null, --81 NA for service
1754 null, --82 NA for service
1755 l_item_id, --83 cs_cost_details.inventory_item_id
1756 null, --84 NA for service
1757 l_uom, --85 cs_cost_details.unit_of_measure_code
1758 null, --86 NA for service
1759 null, --87 NA for service
1760 null, --88 NA for service
1761 null, --89 NA for service
1762 null, --90 NA for service
1763 null, --91 NA for service
1764 null, --92 NA for service
1765 null, --93 NA for service
1766 null, --94 NA for service
1767 null, --95 NA for service
1768 null, --96 NA for service
1769 null, --97 NA for service
1770 null, --98 NA for service
1771 null --99 NA for service
1772
1773 );
1774
1775
1776
1777 If sql%rowcount=1 then -- if record is successfully inserted
1778
1779
1780 l_success_count := l_success_count+1;
1781
1782 Update cs_cost_details set interfaced_to_pa_flag = 'T'
1783 where cost_id = l_cost_id;
1784
1785
1786 -- l_succ_cst_rec.EXTEND(1);
1787 l_succ_cst_rec(m).cost_id :=l_cost_id ;
1788 l_succ_cst_rec(m).incident_number :=l_incident_number;
1789
1790
1791 m:=m+1;
1792
1793
1794 commit;
1795
1796
1797
1798 end if;
1799
1800
1801
1802 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1803 THEN
1804 FND_LOG.String
1805 (
1806 FND_LOG.level_procedure
1807 , L_LOG_MODULE || 'After inserting into PA_TRANSACTION_INTERFACE_ALL table'
1808 , 'ends'
1809 );
1810
1811 End if;
1812
1813
1814 EXCEPTION
1815
1816
1817
1818 When l_interface_error then
1819
1820
1821 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1822 THEN
1823 FND_LOG.String
1824 (
1825 FND_LOG.level_procedure
1826 , L_LOG_MODULE || 'Inside exception'
1827 ,'sqlerrm:'||sqlerrm
1828 );
1829
1830 End if;
1831
1832
1833 -- p_msg_data:=FND_MESSAGE.GET;
1834
1835
1836 FND_MSG_PUB.Count_And_Get(
1837 p_count => p_msg_count, --out
1838 p_data => p_msg_data,--out
1839 p_encoded => FND_API.G_FALSE) ;--in
1840 FND_MSG_PUB.Delete_Msg;
1841
1842 fnd_file.put_line(FND_FILE.log,'FND_MSG_PUB.Count_And_Get : '||p_msg_data);
1843
1844
1845
1846 l_failure_count := l_failure_count+1;
1847
1848
1849 Update cs_cost_details set interfaced_to_pa_flag = 'N'
1850 where cost_id = l_cost_id;
1851
1852 -- l_err_cst_rec.EXTEND(1);
1853 l_err_cst_rec(j).cost_id :=l_cost_id ;
1854 l_err_cst_rec(j).incident_number :=l_incident_number ;
1855
1856
1857 -- l_err_rec.EXTEND(1) ;
1858 l_err_cst_rec(j).reject_reason := substr(p_msg_data, 1, 900) ;
1859
1860 j:=j+1;
1861
1862 WHEN OTHERS THEN
1863
1864
1865 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1866 THEN
1867 FND_LOG.String
1868 (
1869 FND_LOG.level_procedure
1870 , L_LOG_MODULE || 'Inside exception'
1871 ,'sqlerrm:'||sqlerrm
1872 );
1873
1874 End if;
1875
1876 l_failure_count := l_failure_count+1;
1877
1878
1879 Update cs_cost_details set interfaced_to_pa_flag = 'N'
1880 where cost_id = l_cost_id;
1881
1882 l_err_cst_rec(j).cost_id :=l_cost_id ;
1883 l_err_cst_rec(j).incident_number :=l_incident_number ;
1884
1885
1886 -- l_err_rec.EXTEND(1) ;
1887 l_err_cst_rec(j).reject_reason := substr(p_msg_data, 1, 900) ;
1888
1889 j:=j+1;
1890
1891 p_msg_data := sqlerrm;
1892
1893
1894
1895 END;
1896
1897
1898
1899
1900 END LOOP;
1901
1902 CLOSE get_project_attr;
1903
1904
1905
1906
1910 p_msg_data := 'The concurrent program did not find any cost records to be interfaced to projects';
1907 If (l_count-1)= 0 then
1908
1909
1911 raise l_interface_warning;
1912
1913
1914 End if;
1915
1916
1917
1918
1919 fnd_file.put_line
1920 (
1921 FND_FILE.OUTPUT
1922 , '<html><body>'
1923 );
1924
1925 fnd_file.put_line
1926 (
1927 FND_FILE.OUTPUT
1928 , '<h3>' || 'Results of the Cost Interface Program'
1929 || '</h3><table border cellspacing=0 cellpadding=5 width=40%>'
1930 );
1931
1932
1933 fnd_file.put_line
1934 (
1935 FND_FILE.OUTPUT
1936 , '<tr><td><b>' || 'Number of Cost Records considered for Projects Interface ' || '</b></td><td><b>'
1937 || (l_count-1)|| '</b></td></tr>'
1938 );
1939
1940
1941 fnd_file.put_line
1942 (
1943 FND_FILE.OUTPUT
1944 , '<tr><td><b>' || 'Number of Cost Records successfully inserted into the Projects Interface Table' || '</b></td><td><b>'
1945 || l_success_count|| '</b></td></tr>'
1946 );
1947
1948
1949
1950
1951
1952 fnd_file.put_line
1953 (
1954 FND_FILE.OUTPUT
1955 , '<tr><td><b>' || 'Number of Cost Records failed to be inserted into the Projects Interface Table' || '</b></td><td><b>'
1956 || l_failure_count|| '</b></td></tr>'
1957 );
1958
1959
1960
1961
1962 If l_err_cst_rec.count > 0 then
1963
1964
1965 fnd_file.put_line
1966 (
1967 FND_FILE.OUTPUT
1968 , '<table border ="1" cellspacing=0 cellpadding=5 width=50%>'
1969 ||'<caption align="top" ><font color=red><b>'||'Details of the Cost records that failed Insertion'||'</font></b></caption>'
1970 );
1971
1972
1973 fnd_file.put_line
1974 (
1975 FND_FILE.OUTPUT,'<br><br><br><br>'
1976 );
1977
1978
1979 fnd_file.put_line
1980 (
1981 FND_FILE.OUTPUT
1982 , '<tr>'
1983 ||'<th><b>'||'Cost Id'||'</b></th>'
1984 ||'<th><b>'||'Service Request Number'||'</b></th>'
1985 ||'<th><b>'||'Reject Reason'||'</b></th>'
1986 ||'</tr>'
1987
1988 );
1989 --fnd_file.put_line(FND_FILE.log,'getting l_err_cst_rec.count : '||l_err_cst_rec.count);
1990
1991 For i in 1..l_err_cst_rec.count
1992 Loop
1993
1994 /* select cost_id into l_err_cost_id
1995 from cs_cost_details
1996 where cost_id = l_err_cst_rec(i) ;*/
1997
1998 fnd_file.put_line
1999 (
2000 FND_FILE.OUTPUT
2001 , '<tr>'
2002 );
2003
2004 l_text := '<td> ' || l_err_cst_rec(i).cost_id
2005 || '</td><td>' || l_err_cst_rec(i).incident_number
2006 || '</td><td>' || l_err_cst_rec(i).reject_reason
2007 || '</td>';
2008
2009
2010 fnd_file.put_line
2011 (
2012 FND_FILE.OUTPUT
2013 , '<b>'||l_text||'</b>'
2014 );
2015
2016
2017 fnd_file.put_line (
2018 FND_FILE.OUTPUT
2019 , '</tr>'
2020 );
2021
2022 End loop;
2023
2024 End if;
2025
2026
2027
2028
2029
2030 If l_succ_cst_rec.count > 0 then
2031
2032
2033 fnd_file.put_line
2034 (
2035 FND_FILE.OUTPUT
2036 , '<table border="1" cellspacing=0 cellpadding=5 width=50%>'
2037 ||'<caption align="top" ><font color=green><b>'||'Details of the cost records that succeeded Insertion '||'</font></b></caption>'
2038 );
2039
2040
2041 fnd_file.put_line
2042 (
2043 FND_FILE.OUTPUT,'<br><br><br><br>'
2044 );
2045
2046
2047 /* fnd_file.put_line
2048 (
2049 FND_FILE.OUTPUT
2050 , '<tr>'
2051 ||'<th><b>'||'Cost Id'||'</b></th>'
2052 ||'</tr>'
2053
2054 );*/
2055
2056
2057
2058 fnd_file.put_line
2059 (
2060 FND_FILE.OUTPUT
2061 , '<tr>'
2062 ||'<th><b>'||'Cost Id'||'</b></th>'
2063 ||'<th><b>'||'Service Request Number'||'</b></th>'
2064 ||'</tr>'
2065
2066 );
2067
2068
2069 For i in 1..l_succ_cst_rec.count
2070 Loop
2071
2072 /*select cost_id
2073 into l_succ_cost_id
2074 from cs_cost_details
2075 where cost_id = l_succ_cst_rec(i) ;*/
2076
2077
2078
2079 fnd_file.put_line
2080 (
2081 FND_FILE.OUTPUT
2082 , '<tr>'
2083 );
2084
2085 /* l_text := '<td> <b> ' || l_succ_cst_rec(i)
2086 || '</b></td>'; */
2087
2088 l_text := '<td> ' || l_succ_cst_rec(i).cost_id
2089 || '</td><td>' || l_succ_cst_rec(i).incident_number
2090 || '</td>';
2091
2092 fnd_file.put_line
2093 (
2094 FND_FILE.OUTPUT
2095 , '<b>'||l_text||'</b>'
2096 );
2097
2098
2099 fnd_file.put_line (
2100 FND_FILE.OUTPUT
2101 , '</tr>'
2102 );
2103
2104
2105
2106 End loop;
2107
2108 End if;
2109
2110 fnd_file.put_line
2111 (
2112 FND_FILE.OUTPUT
2116
2113 , '</table></body></html>' );
2114
2115
2117
2118
2119 Exception
2120 When l_interface_warning then
2121
2122 l_ret := fnd_concurrent.set_completion_status
2123 (
2124 'WARNING'
2125 , SUBSTR(p_msg_data, 1, 240)
2126 );
2127
2128 fnd_file.put_line(fnd_file.log,p_msg_data);
2129
2130 WHEN l_interface_error then
2131
2132
2133 FND_MSG_PUB.Count_And_Get(
2134 p_count => p_msg_count,
2135 p_data => p_msg_data,
2136 p_encoded => FND_API.G_FALSE) ;
2137
2138
2139 l_failure_count := l_failure_count+1;
2140
2141
2142
2143 Update cs_cost_details set interfaced_to_pa_flag = 'N'
2144 where cost_id = l_cost_id;
2145
2146 l_err_cst_rec(j).cost_id :=l_cost_id ;
2147 l_err_cst_rec(j).incident_number :=l_incident_number ;
2148
2149
2150 -- l_err_rec.EXTEND(1) ;
2151 l_err_cst_rec(j).reject_reason := substr(p_msg_data, 1, 900) ;
2152
2153 j:=j+1;
2154
2155
2156 p_msg_data := sqlerrm;
2157
2158
2159 END interface_cost_details;
2160
2161
2162 PROCEDURE Validate_transaction(
2163 p_project_id IN NUMBER
2164 , p_task_id IN NUMBER
2165 , p_ei_date IN DATE
2166 , p_expenditure_type IN VARCHAR2
2167 , p_non_labor_resource IN VARCHAR2
2168 , p_person_id IN NUMBER
2169 , p_quantity IN NUMBER DEFAULT NULL
2170 , p_denom_currency_code IN VARCHAR2 DEFAULT NULL
2171 , p_acct_currency_code IN VARCHAR2 DEFAULT NULL
2172 , p_denom_raw_cost IN NUMBER DEFAULT NULL
2173 , p_acct_raw_cost IN NUMBER DEFAULT NULL
2174 , p_acct_rate_type IN VARCHAR2 DEFAULT NULL
2175 , p_acct_rate_date IN DATE DEFAULT NULL
2176 , p_acct_exchange_rate IN NUMBER DEFAULT NULL
2177 , p_transfer_ei IN NUMBER DEFAULT NULL
2178 , p_incurred_by_org_id IN NUMBER DEFAULT NULL
2179 , p_nl_resource_org_id IN NUMBER DEFAULT NULL
2180 , p_transaction_source IN VARCHAR2 DEFAULT NULL
2181 , p_calling_module IN VARCHAR2 DEFAULT NULL
2182 , p_vendor_id IN NUMBER DEFAULT NULL
2183 , p_entered_by_user_id IN NUMBER DEFAULT NULL
2184 , p_attribute_category IN VARCHAR2 DEFAULT NULL
2185 , p_attribute1 IN VARCHAR2 DEFAULT NULL
2186 , p_attribute2 IN VARCHAR2 DEFAULT NULL
2187 , p_attribute3 IN VARCHAR2 DEFAULT NULL
2188 , p_attribute4 IN VARCHAR2 DEFAULT NULL
2189 , p_attribute5 IN VARCHAR2 DEFAULT NULL
2190 , p_attribute6 IN VARCHAR2 DEFAULT NULL
2191 , p_attribute7 IN VARCHAR2 DEFAULT NULL
2192 , p_attribute8 IN VARCHAR2 DEFAULT NULL
2193 , p_attribute9 IN VARCHAR2 DEFAULT NULL
2194 , p_attribute10 IN VARCHAR2 DEFAULT NULL
2195 , p_attribute11 IN VARCHAR2 DEFAULT NULL
2196 , p_attribute12 IN VARCHAR2 DEFAULT NULL
2197 , p_attribute13 IN VARCHAR2 DEFAULT NULL
2198 , p_attribute14 IN VARCHAR2 DEFAULT NULL
2199 , p_attribute15 IN VARCHAR2 DEFAULT NULL
2200 , p_msg_application IN OUT NOCOPY VARCHAR2
2201 , p_msg_type OUT NOCOPY VARCHAR2
2202 , p_msg_token1 OUT NOCOPY VARCHAR2
2203 , p_msg_token2 OUT NOCOPY VARCHAR2
2204 , p_msg_token3 OUT NOCOPY VARCHAR2
2205 , p_msg_count OUT NOCOPY NUMBER
2206 , p_msg_data OUT NOCOPY VARCHAR2
2207 , p_billable_flag OUT NOCOPY VARCHAR2
2208
2209 )IS
2210
2211 BEGIN
2212 --null;
2213
2214
2215 PA_TRANSACTIONS_PUB.Validate_transaction
2216 ( x_project_id => p_project_id
2217 , x_task_id => p_task_id
2218 , x_ei_date => p_ei_date
2219 , x_expenditure_type => p_expenditure_type
2220 , x_non_labor_resource => p_non_labor_resource
2221 , x_person_id => p_person_id
2222 , x_quantity => p_quantity
2223 , x_denom_currency_code => p_denom_currency_code
2224 , x_acct_currency_code => p_acct_currency_code
2225 , x_denom_raw_cost => p_denom_raw_cost
2226 , x_acct_raw_cost => p_acct_raw_cost
2227 , x_acct_rate_type => p_acct_rate_type
2228 , x_acct_rate_date => p_acct_rate_date
2229 , x_acct_exchange_rate => p_acct_exchange_rate
2230 , x_transfer_ei => p_transfer_ei
2231 , x_incurred_by_org_id => p_incurred_by_org_id
2232 , x_nl_resource_org_id => p_nl_resource_org_id
2233 , x_transaction_source => p_transaction_source
2234 , x_calling_module => p_calling_module
2235 , x_vendor_id => p_vendor_id
2236 , x_entered_by_user_id => p_entered_by_user_id
2237 , x_attribute_category => p_attribute_category
2238 , x_attribute1 => p_attribute1
2239 , x_attribute2 => p_attribute2
2240 , x_attribute3 => p_attribute3
2241 , x_attribute4 => p_attribute4
2242 , x_attribute5 => p_attribute5
2243 , x_attribute6 => p_attribute6
2247 , x_attribute10 => p_attribute10
2244 , x_attribute7 => p_attribute7
2245 , x_attribute8 => p_attribute8
2246 , x_attribute9 => p_attribute9
2248 , x_attribute11 => p_attribute11
2249 , x_attribute12 => p_attribute12
2250 , x_attribute13 => p_attribute13
2251 , x_attribute14 => p_attribute14
2252 , x_attribute15 => p_attribute15
2253 , x_msg_application => p_msg_application
2254 , x_msg_type => p_msg_type
2255 , x_msg_token1 => p_msg_token1
2256 , x_msg_token2 => p_msg_token2
2257 , x_msg_token3 => p_msg_token3
2258 , x_msg_count => p_msg_count
2259 , x_msg_data => p_msg_data
2260 , x_billable_flag => p_billable_flag );
2261
2262
2263
2264 END Validate_transaction;
2265
2266
2267
2268
2269 PROCEDURE update_interface_status(P_transaction_source IN VARCHAR2,
2270 P_batch IN VARCHAR2,
2271 P_user_id IN NUMBER,
2272 P_xface_id IN NUMBER) IS
2273
2274 l_cost_id NUMBER;
2275 l_status_code VARCHAR2(10);
2276
2277
2278 CURSOR get_interface_status IS
2279 SELECT transaction_status_code ,orig_transaction_reference
2280 FROM pa_transaction_interface_all
2281 WHERE transaction_source = P_transaction_source
2282 AND batch_name = P_batch
2283 AND created_by = P_user_id
2284 AND txn_interface_id = P_xface_id;
2285
2286
2287 BEGIN
2288
2289 Open get_interface_status;
2290 Fetch get_interface_status into l_status_code ,l_cost_id;
2291
2292
2293 UPDATE cs_cost_details
2294 set interfaced_to_pa_flag = decode(l_status_code,'I','Y',
2295 'R','E')
2296 where cost_id = l_cost_id;
2297
2298 If l_status_code ='I' then
2299
2300 UPDATE pa_transaction_interface_all
2301 set transaction_status_code ='A'
2302 where orig_transaction_reference = to_char(l_cost_id);
2303
2304 End if;
2305
2306 Commit;
2307
2308 Close get_interface_status;
2309
2310 END update_interface_status;
2311
2312
2313
2314
2315 END CS_SR_INTERFACE_CP;
2316