1 PACKAGE BODY PQP_VEHICLE_MILEAGE AS
2 /* $Header: pqbladwr.pkb 115.5 2003/06/27 10:22:11 jcpereir noship $*/
3
4 PROCEDURE INITIALIZE_BALANCES(errbuf OUT NOCOPY VARCHAR2
5 ,retcode OUT NOCOPY NUMBER
6 ,p_business_group_id IN NUMBER
7 )
8 AS
9 -- Get the Car and Mileage Elements which have run result entries between 6-Apr-2003
10 -- and 5-Apr-2004
11 CURSOR c_ele_exst (cp_business_group_id NUMBER)
12 IS
13 SELECT element_name
14 ,substr(pete.EEI_INFORMATION1,0,1) ownership
15 from pay_element_types_f pet,
16 pay_element_type_extra_info pete
17 where exists (SELECT 'X'
18 from pay_run_results prr
19 ,pay_assignment_actions paa
20 ,pay_payroll_actions ppa
21 WHERE prr.element_type_id=pet.element_type_id
22 AND prr.assignment_action_id=paa.assignment_action_id
23 AND paa.payroll_action_id=ppa.payroll_action_id
24 AND ppa.effective_date BETWEEN TO_DATE('04/06/2003','MM/DD/YYYY')
25 AND TO_DATE('04/05/2004','MM/DD/YYYY')
26 )
27 AND pete.element_type_id=pet.element_type_id
28 AND pete.information_type='PQP_VEHICLE_MILEAGE_INFO'
29 AND pete.EEI_INFORMATION_CATEGORY='PQP_VEHICLE_MILEAGE_INFO'
30 AND pete.EEI_INFORMATION1 in ('C','P','PM','PP','CM','CP')
31 AND pet.business_group_id =cp_business_group_id;
32
33 -- Check if given Element has a link or not
34 CURSOR c_get_link (cp_business_group_id NUMBER
35 ,cp_element_name VARCHAR2
36 ,cp_ownership VARCHAR2
37 )
38 IS
39 SELECT element_name
40 FROM pay_element_types_f pet
41 WHERE NOT EXISTS (SELECT 'X'
42 FROM pay_element_links_f pel
43 WHERE pel.element_type_id=pet.element_type_id
44 AND pel.business_group_id=pet.business_group_id
45 )
46 AND ( pet.element_name like cp_element_name||'%Addl Ele%'
47 OR (pet.element_name like cp_element_name||'%Mileage Res2%'
48 AND cp_ownership = 'C'))
49 AND pet.business_group_id=cp_business_group_id ;
50
51 -- Get Error Info from per_all_assignments_f
52 CURSOR c_err_log
53 IS
54 select purt.location_name||' Needs to be linked to Assignment '
55 ||paaf.assignment_number||' of Employee '||papf.full_name ERROR_LOG
56 from pay_us_rpt_totals purt
57 ,per_all_assignments_f paaf
58 ,per_all_people_f papf
59 where purt.state_name = 'CARMILEAGE_UPGRADE'
60 and purt.tax_unit_id = 250
61 and paaf.assignment_id = purt.location_id
62 and fnd_date.canonical_to_date(purt.organization_name) between paaf.effective_start_date
63 and paaf.effective_end_date
64 and paaf.person_id = papf.person_id
65 and fnd_date.canonical_to_date(purt.organization_name) between papf.effective_start_date
66 and papf.effective_end_date;
67
68
69 l_ele_exst c_ele_exst%ROWTYPE;
70 l_get_link c_get_link%ROWTYPE;
71 l_element_count NUMBER;
72 l_link_flag NUMBER:=0;
73 l_err_log VARCHAR2(100);
74 BEGIN
75 OPEN c_ele_exst (p_business_group_id);
76 LOOP
77 FETCH c_ele_exst INTO l_ele_exst;
78 EXIT WHEn c_ele_exst%NOTFOUND;
79 OPEN c_get_link (p_business_group_id
80 ,l_ele_exst.element_name
81 ,l_ele_exst.ownership);
82 LOOP
83 FETCH c_get_link INTO l_get_link;
84 EXIT WHEN c_get_link%NOTFOUND;
85 l_link_flag := 1;
86 --write into Log the list of Elements for which links don't exist.
87 fnd_file.put_line(fnd_file.log,'Link does not exist for ' || l_get_link.element_name );
88 hr_utility.set_location('Link does not exist for ' || l_get_link.element_name,10);
89 END LOOP;
90 CLOSE c_get_link;
91
92 END LOOP;
93 CLOSE c_ele_exst;
94
95 hr_utility.set_location('Missing Links ' || l_link_flag,10);
96 -- We need to check if there are any missing links
97 IF l_link_flag > 0 THEN
98 fnd_file.put_line(fnd_file.log,'Please Create the above Element Links and Retry.');
99 ELSE
100 --Call the Procedure to initialize the balances
101 pqp_ini_bal.Initialize_Balances(p_business_group_id =>p_business_group_id);
102 -- Write Error Messages to the Log
103 OPEN c_err_log;
104 LOOP
105 FETCH c_err_log into l_err_log;
106 EXIT WHEN c_err_log%NOTFOUND;
107 fnd_file.put_line(fnd_file.log,l_err_log);
108 END LOOP;
109 CLOSE c_err_log;
110 END IF;
111 END initialize_balances;
112 ---------------------------------------------------End------------------------------------------------------------
113 END;
114
[Home] [Help]