create or replace package body mlgw_otl_validation_pkg as -- -- =============================================================== -- Copyright (C) 2006 KBACE Technologies, Inc. - Nashua, NH -- All Rights Reserved -- =============================================================== -- -- Description: TBD -- -- 1. xx -- -- Revision History: -- Date Author Change Description -- ----------- -------------- ------------------------------------ -- 01-Jul-2006 CLYNCH Initial Version. -- 25-Oct-2006 CLYNCH Added logging for crew laptop -- 01-Nov-2006 CLYNCH Added additional logging -- 02-Nov-2006 CLYNCH Check for new=Y because sometimes changes is null -- =============================================================== -- type err_tbl_typ is table of varchar2(50) index by binary_integer; err_tbl err_tbl_typ; -- -- type out_rec_typ is record( work_ticket varchar2(50), repair_order_num varchar2(50), crew_number varchar2(50), equipment_number varchar2(50), charge_code varchar2(50), requesting_area varchar2(50), wr_nbr varchar2(50), performing_area varchar2(50), start_miles varchar2(50), ending_miles varchar2(50), person_id number, legacy_earn_code varchar2(50), woc_job_class varchar2(50), element_type_id number ); type out_rec_typ_tbl is table of out_rec_typ index by binary_integer; out_rec out_rec_typ_tbl; -- l_effective_date date; -- -- ---------------------------------------------------------------- -- ---------------------- < ot_eligible > ------------------------- -- ---------------------------------------------------------------- -- function ot_eligible(p_person_id in number ,p_effective_date in date) return varchar2 is -- l_ppg varchar2(10) := 'N'; l_flsa varchar2(10) := 'N'; l_job_dff varchar2(10) := 'N'; -- begin select nvl(substr(ppg.segment3,1,1),'N') ,decode(pj.job_information3,'NEX','Y','N') ,nvl(substr(pj.attribute6,1,1),'N') into l_ppg ,l_flsa ,l_job_dff from per_all_assignments_f paf ,pay_people_groups ppg ,per_jobs pj where p_effective_date between paf.effective_start_date and paf.effective_end_date and ppg.people_group_id (+) = paf.people_group_id and pj.job_id (+) = paf.job_id and assignment_type = 'E' and primary_flag = 'Y' and person_id = p_person_id; -- if l_ppg = 'Y' or l_flsa = 'Y' or l_job_dff = 'Y' then return 'Y'; end if; -- return 'N'; exception when others then return 'N'; end ot_eligible; -- -- ---------------------------------------------------------------- -- --------------------- < shift_eligible > ----------------------- -- ---------------------------------------------------------------- -- function shift_eligible(p_person_id in number ,p_effective_date in date) return varchar2 is -- l_ppg varchar2(10) := 'N'; l_job_dff varchar2(10) := 'N'; -- begin select nvl(substr(ppg.segment4,1,1),'N') ,nvl(substr(pj.attribute5,1,1),'N') into l_ppg ,l_job_dff from per_all_assignments_f paf ,pay_people_groups ppg ,per_jobs pj where p_effective_date between paf.effective_start_date and paf.effective_end_date and ppg.people_group_id (+) = paf.people_group_id and pj.job_id (+) = paf.job_id and assignment_type = 'E' and primary_flag = 'Y' and person_id = p_person_id; -- if l_ppg = 'Y' or l_job_dff = 'Y' then return 'Y'; end if; -- return 'N'; exception when others then return 'N'; end shift_eligible; -- -- ---------------------------------------------------------------- -- -------------------- < validate_entries > ---------------------- -- ---------------------------------------------------------------- -- procedure validate_entries(p_time_building_block_id in number ,p_person_id in number ,p_effective_date in date ,p_element_type_id in varchar2) is -- errs err_tbl_typ; er number; l_earnings_type varchar2(10); l_element_name varchar2(60); -- begin errs.delete; -- begin select element_information1 ,element_name into l_earnings_type ,l_element_name from pay_element_types_f where element_type_id = p_element_type_id and p_effective_date between effective_start_date and effective_end_date; exception when others then l_earnings_type := null; l_element_name := null; end; -- if l_earnings_type = 'OT' then -- if ot_eligible(p_person_id,p_effective_date) = 'N' then errs(1) := 'Employee is Not Eligible for '||l_element_name||'.'; end if; -- elsif l_earnings_type = 'S' then -- if shift_eligible(p_person_id,p_effective_date) = 'N' then errs(1) := 'Employee is Not Eligible for '||l_element_name||'.'; end if; -- end if; -- hr_utility.trace_on(null,'KB'); -- hr_utility.set_location('Start: ',9); -- hr_utility.set_location('l_earnings_type: '||l_earnings_type,9); -- hr_utility.set_location('l_element_name: '||l_element_name,9); -- hr_utility.trace_off; -- er := errs.first; loop exit when er is null; hxc_time_entry_rules_utils_pkg.publish_message (p_name => 'MLGW' ,p_token_name => 'MSS' ,p_token_value => errs(er) ,p_time_building_block_id => p_time_building_block_id ); er := errs.next(er); end loop; end validate_entries; -- -- ---------------------------------------------------------------- -- ------------------------ < WRITE_LOG > ------------------------- -- ---------------------------------------------------------------- -- procedure write_log(p_message in varchar2) is -- l_data_file utl_file.file_type; -- begin l_data_file := utl_file.fopen ('/d12/oracle/tst5db/9.2.0/appsutil/outbound/tst5_nu08dd01', 'valotl.log', 'A'); utl_file.put_line(l_data_file,to_char(systimestamp(), 'MMDD:HH24:MI:SS.FF3') ||'|'||userenv('sessionid')||'|'||p_message); utl_file.fclose(l_data_file); -- -- if fnd_global.conc_request_id > 0 then fnd_file.put_line (fnd_file.output,p_message); end if; end write_log; -- -- ---------------------------------------------------------------- -- ----------------------- < SEND_STRING > ------------------------ -- ---------------------------------------------------------------- -- procedure send_string is x number; l_head_string varchar2(100):= ' VALOTL '; l_mss_return varchar2(1000); l_valotl_string varchar2(5000); l_performing_area_val varchar2(50); l_fixed_dist varchar2(1); l_employee_number varchar2(10); -- begin -- write_log('---> Start mlgw_otl_validation_pkg.send_string <---'); -- x := out_rec.first; loop exit when x is null; -- -- begin select substr(hou.name,2,6) ,(select decode(nvl(sum(pca.proportion),0),0,'N','Y') from pay_cost_allocations_f pca where pca.assignment_id = paf.assignment_id and l_effective_date between pca.effective_start_date and pca.effective_end_date) proportion ,lpad(ppf.employee_number,5,'0') into l_performing_area_val ,l_fixed_dist ,l_employee_number from per_all_assignments_f paf ,hr_all_organization_units hou ,per_all_people_f ppf where paf.organization_id = hou.organization_id and l_effective_date between paf.effective_start_date and paf.effective_end_date and l_effective_date between ppf.effective_start_date and ppf.effective_end_date and ppf.person_id = paf.person_id and paf.assignment_type = 'E' and paf.primary_flag = 'Y' and paf.person_id = out_rec(x).person_id group by substr(hou.name,2,6), ppf.employee_number, paf.assignment_id; exception when others then l_performing_area_val := '000000'; l_fixed_dist := 'N'; l_employee_number := '00000'; end; -- out_rec(x).performing_area := lpad(l_performing_area_val,6,'0'); -- out_rec(x).work_ticket := upper(nvl(out_rec(x).work_ticket,lpad(' ',8,' '))); out_rec(x).repair_order_num := nvl(out_rec(x).repair_order_num,lpad(0,7,'0')); out_rec(x).crew_number := nvl(out_rec(x).crew_number,lpad(0,4,'0')); out_rec(x).equipment_number := nvl(out_rec(x).equipment_number,lpad(0,4,'0')); out_rec(x).charge_code := nvl(out_rec(x).charge_code,rpad('0',4,'0')); out_rec(x).requesting_area := nvl(out_rec(x).requesting_area,lpad(0,6,'0')); out_rec(x).wr_nbr := upper(nvl(out_rec(x).wr_nbr,rpad(' ',8,' '))); out_rec(x).performing_area := nvl(out_rec(x).performing_area,lpad(0,6,'0')); out_rec(x).start_miles := nvl(out_rec(x).start_miles,lpad(0,6,'0')); out_rec(x).ending_miles := nvl(out_rec(x).ending_miles,lpad(0,6,'0')); out_rec(x).legacy_earn_code := nvl(out_rec(x).legacy_earn_code,'000'); out_rec(x).woc_job_class := nvl(out_rec(x).woc_job_class,lpad(' ',6,' ')); -- l_valotl_string := (l_head_string|| out_rec(x).work_ticket|| out_rec(x).repair_order_num|| out_rec(x).crew_number|| out_rec(x).equipment_number|| out_rec(x).charge_code|| out_rec(x).requesting_area|| out_rec(x).wr_nbr|| out_rec(x).performing_area|| out_rec(x).start_miles|| out_rec(x).ending_miles|| out_rec(x).legacy_earn_code|| l_fixed_dist|| l_employee_number|| out_rec(x).woc_job_class ); -- -- Call MSS Function to validate timecard data -- err_tbl.delete; write_log('EE#:'||l_employee_number||'|time_building_block_id:'||x||' |To MSS:'||l_valotl_string); -- -- l_mss_return := trim(mgwuser.mlgw_mss.valtimec(l_valotl_string)); -- --write_log('EE#:'||l_employee_number||'|time_building_block_id:'||x||' |From MSS:'||l_mss_return); -- -- hr_utility.set_location('-----------------------------------'||x,9); -- hr_utility.set_location('x: '||x,9); -- hr_utility.set_location('Header: '||l_head_string,9); -- hr_utility.set_location('out_rec.work_ticket: '||out_rec(x).work_ticket,9); -- hr_utility.set_location('out_rec.repair_order_num: '||out_rec(x).repair_order_num,9); -- hr_utility.set_location('out_rec.crew_number: '||out_rec(x).crew_number,9); -- hr_utility.set_location('out_rec.equipment_number: '||out_rec(x).equipment_number,9); -- hr_utility.set_location('out_rec.charge_code: '||out_rec(x).charge_code,9); -- hr_utility.set_location('out_rec.requesting_area: '||out_rec(x).requesting_area,9); -- hr_utility.set_location('out_rec.wr_nbr: '||replace(out_rec(x).wr_nbr,' ','#'),9); -- hr_utility.set_location('out_rec.performing_area: '||out_rec(x).performing_area,9); -- hr_utility.set_location('out_rec.start_miles: '||out_rec(x).start_miles,9); -- hr_utility.set_location('out_rec.ending_miles: '||out_rec(x).ending_miles,9); -- hr_utility.set_location('l_mss_return: '||l_mss_return,9); -- if replace(substr(l_mss_return,11,50),' ') is not null then err_tbl(1) := trim(substr(l_mss_return,11,50)); end if; -- if replace(substr(l_mss_return,61,50),' ') is not null then err_tbl(2) := trim(substr(l_mss_return,61,50)); end if; -- if replace(substr(l_mss_return,111,50),' ') is not null then err_tbl(3) := trim(substr(l_mss_return,111,50)); end if; -- if replace(substr(l_mss_return,161,50),' ') is not null then err_tbl(4) := trim(substr(l_mss_return,161,50)); end if; -- if replace(substr(l_mss_return,211,50),' ') is not null then err_tbl(5) := trim(substr(l_mss_return,211,50)); end if; -- -- if substr(l_mss_return,9,2) = '01' then -- write_log('EE#:'||l_employee_number||'hxc_time_entry_rules_utils_pkg.publish_message:'||err_tbl.count ); for ers in 1..5 loop -- if err_tbl.exists(ers) and err_tbl(ers) is not null then -- -- hxc_time_entry_rules_utils_pkg.publish_message (p_name => 'MLGW' ,p_token_name => 'MSS' ,p_token_value => err_tbl(ers) ,p_time_building_block_id => x ); -- end if; end loop; -- elsif substr(l_mss_return,9,2) != '00' then write_log('EE#:'||l_employee_number||'hxc_time_entry_rules_utils_pkg.publish_message:Invalid Response from MSS' ); hxc_time_entry_rules_utils_pkg.publish_message (p_name => 'MLGW' ,p_token_name => 'MSS' ,p_token_value => 'Invalid Response from MSS' ,p_time_building_block_id => x ); end if; -- -- -- Call internal validation validate_entries(p_time_building_block_id => x ,p_person_id => out_rec(x).person_id ,p_effective_date => l_effective_date ,p_element_type_id => out_rec(x).element_type_id ); -- x := out_rec.next(x); end loop; write_log('---> End mlgw_otl_validation_pkg.send_string <---'); end send_string; -- -- ---------------------------------------------------------------- -- -------------------- < VALIDATE_TIMECARD > --------------------- -- ---------------------------------------------------------------- -- /d10/oracle/uatdb/9.2.0/appsutil/outbound/uat_nu08dd01/mlgw_mss.log. -- grant execute on mlgw_mss to apps; function validate_timecard return varchar2 is -- l_timecard_info hxc_self_service_time_deposit.timecard_info; l_attribute_info hxc_self_service_time_deposit.building_block_attribute_info; x number; a number; l_timecard_id number; l_charge_code varchar2(50); l_request_area varchar2(50); l_work_request_val varchar2(50); l_messages hxc_self_service_time_deposit.message_table; l_send_string boolean; -- begin write_log('---> Start mlgw_otl_validation_pkg.validate_timecard <---'); -- hr_utility.trace_on(null,'KB'); -- hr_utility.set_location('Start: ',9); -- hr_utility.trace_off; -- -- if g_validate = 'N' then write_log('g_validate=N'); return 'X'; end if; -- begin select effective_date into l_effective_date from fnd_sessions where session_id = userenv('sessionid'); exception when others then l_effective_date := sysdate; end; write_log('l_effective_date:'||to_char(l_effective_date,'DD-MON-YYYY')); -- l_messages.delete; l_timecard_info.delete; l_attribute_info.delete; -- l_timecard_info := hxc_self_service_time_deposit.get_building_blocks; l_attribute_info := hxc_self_service_time_deposit.get_block_attributes; l_messages := hxc_self_service_time_deposit.get_messages; -- -- out_rec.delete; -- write_log('l_messages.count:'||l_messages.count||'|l_attribute_info.count:'||l_attribute_info.count||'|l_timecard_info.count:'||l_timecard_info.count); -- x := l_timecard_info.first; loop exit when x is null; -- write_log('l_timecard_info.first:'||x||'|l_timecard_info(x).changed:'||l_timecard_info(x).changed||'|l_timecard_info(x).new:'||l_timecard_info(x).new); -- hr_utility.set_location('Changed: '||l_timecard_info(x).changed,9); l_send_string := false; if nvl(l_timecard_info(x).changed,'N') = 'Y' or nvl(l_timecard_info(x).new,'N') = 'Y' then -- CL 11/2/2006 Check for new=Y because sometimes changes is null -- -- hr_utility.set_location('l_timecard_info(x).time_building_block_id: '||l_timecard_info(x).time_building_block_id,9); -- Loop through table to get the DFFs for this bbid -- a := l_attribute_info.first; loop exit when a is null; -- -- -- -- if l_attribute_info(a).bld_blk_info_type != 'SECURITY' then -- -- hr_utility.set_location('-----------------------',9); -- hr_utility.set_location('l_attribute_info(a).building_block_id'||l_attribute_info(a).building_block_id,9); -- hr_utility.set_location('l_timecard_info(x).time_building_block_id '||l_timecard_info(x).time_building_block_id ,9); -- hr_utility.set_location('l_attribute_info(a).bld_blk_info_type'||l_attribute_info(a).bld_blk_info_type ,9); -- hr_utility.set_location('l_attribute_info(a).attribute_category '||l_attribute_info(a).attribute_category ,9); -- hr_utility.set_location('l_timecard_info(x).SCOPE: '||l_timecard_info(x).SCOPE,9); -- -- end if; if l_timecard_info(x).time_building_block_id = l_attribute_info(a).building_block_id and l_attribute_info(a).attribute_category like 'ELEMENT%' and l_attribute_info(a).bld_blk_info_type = 'Dummy Element Context' then -- -- hr_utility.set_location('ELEMENT',9); -- out_rec(l_attribute_info(a).building_block_id).person_id := l_timecard_info(x).resource_id; out_rec(l_attribute_info(a).building_block_id).legacy_earn_code := '000'; out_rec(l_attribute_info(a).building_block_id).element_type_id := null; begin select distinct nvl(pel.attribute1,'000') ,element_type_id into out_rec(l_attribute_info(a).building_block_id).legacy_earn_code ,out_rec(l_attribute_info(a).building_block_id).element_type_id from pay_element_links_f pel where l_effective_date between pel.effective_start_date and pel.effective_end_date and pel.element_type_id = replace(l_attribute_info(a).attribute_category,'ELEMENT - '); exception when others then out_rec(l_attribute_info(a).building_block_id).legacy_earn_code := '000'; out_rec(l_attribute_info(a).building_block_id).element_type_id := null; end; -- if l_attribute_info(a).attribute15 not like '%/%/%' then -- Other elements use this field to hold date taken out_rec(l_attribute_info(a).building_block_id).woc_job_class := rpad(nvl(substr(l_attribute_info(a).attribute15,1,6),' '),6,' '); end if; end if; -- if l_timecard_info(x).time_building_block_id = l_attribute_info(a).building_block_id and l_attribute_info(a).attribute_category = 'MLGW Timecard Layout' and l_attribute_info(a).bld_blk_info_type = 'MLGW Timecard Layout' then -- -- hr_utility.set_location('MLGW Timecard Layout',9); -- hr_utility.set_location('l_attribute_info(a).building_block_id: '||l_attribute_info(a).building_block_id,9); -- hr_utility.set_location('l_timecard_info(x).measure: '||l_timecard_info(x).measure,9); -- hr_utility.set_location('l_timecard_info(x).TYPE: '||l_timecard_info(x).TYPE,9); -- hr_utility.set_location('l_timecard_info(x).SCOPE: '||l_timecard_info(x).SCOPE,9); -- hr_utility.set_location('l_timecard_info(x).object_version_number: '||l_timecard_info(x).object_version_number,9); -- hr_utility.set_location('l_attribute_info(a).object_version_number: '||l_attribute_info(a).object_version_number,9); -- -- out_rec(l_attribute_info(a).building_block_id).person_id := l_timecard_info(x).resource_id; out_rec(l_attribute_info(a).building_block_id).work_ticket := lpad(nvl(l_attribute_info(a).attribute2,0),8,' '); out_rec(l_attribute_info(a).building_block_id).repair_order_num := lpad(nvl(l_attribute_info(a).attribute3,0),7,'0'); out_rec(l_attribute_info(a).building_block_id).crew_number := lpad(nvl(l_attribute_info(a).attribute4,0),4,'0'); out_rec(l_attribute_info(a).building_block_id).equipment_number := lpad(nvl(l_attribute_info(a).attribute6,0),4,'0'); out_rec(l_attribute_info(a).building_block_id).start_miles := lpad(nvl(l_attribute_info(a).attribute10,0),6,'0'); out_rec(l_attribute_info(a).building_block_id).ending_miles := lpad(nvl(l_attribute_info(a).attribute11,0),6,'0'); -- -- -- hr_utility.set_location('attribute1: '||l_attribute_info(a).attribute1,9); -- hr_utility.set_location('attribute2: '||l_attribute_info(a).attribute2,9); -- hr_utility.set_location('attribute3: '||l_attribute_info(a).attribute3,9); -- hr_utility.set_location('attribute4: '||l_attribute_info(a).attribute4,9); -- hr_utility.set_location('attribute5: '||l_attribute_info(a).attribute5,9); -- hr_utility.set_location('attribute6: '||l_attribute_info(a).attribute6,9); -- hr_utility.set_location('attribute7: '||l_attribute_info(a).attribute7,9); -- hr_utility.set_location('attribute8: '||l_attribute_info(a).attribute8,9); -- hr_utility.set_location('attribute9: '||l_attribute_info(a).attribute9,9); -- hr_utility.set_location('attribute10: '||l_attribute_info(a).attribute10,9); -- hr_utility.set_location('attribute11: '||l_attribute_info(a).attribute11,9); -- hr_utility.set_location('attribute12: '||l_attribute_info(a).attribute12,9); -- hr_utility.set_location('attribute14: '||l_attribute_info(a).attribute13,9); -- hr_utility.set_location('attribute15: '||l_attribute_info(a).attribute14,9); -- hr_utility.set_location('attribute16: '||l_attribute_info(a).attribute15,9); -- hr_utility.set_location('attribute17: '||l_attribute_info(a).attribute16,9); --out_rec.wr_nbr := rpad(nvl(l_attribute_info(a).attribute13,' '),8,' '); end if; -- -- if l_timecard_info(x).time_building_block_id = l_attribute_info(a).building_block_id and l_attribute_info(a).attribute_category = 'Dummy Cost Context' and l_attribute_info(a).bld_blk_info_type = 'Dummy Cost Context' then -- -- hr_utility.set_location('Dummy Cost Context',9); -- hr_utility.set_location('l_attribute_info(a).building_block_id: '||l_attribute_info(a).building_block_id,9); -- l_work_request_val := l_attribute_info(a).attribute6; begin select substr(a.flex_value,2,6) into l_request_area from fnd_flex_values a ,fnd_flex_value_sets b where a.flex_value_set_id = b.flex_value_set_id and b.flex_value_set_name = 'MLGW_CAF_AREA' and a.flex_value_id = l_attribute_info(a).attribute5; exception when others then l_request_area := null; end; -- begin select substr(a.flex_value,2,4) into l_charge_code from fnd_flex_values a ,fnd_flex_value_sets b where a.flex_value_set_id = b.flex_value_set_id and b.flex_value_set_name = 'MLGW_CAF_CHARGE_CODE' and a.flex_value_id = l_attribute_info(a).attribute4; exception when others then l_charge_code := null; end; -- out_rec(l_attribute_info(a).building_block_id).charge_code := rpad(nvl(l_charge_code,'0'),4,'0'); out_rec(l_attribute_info(a).building_block_id).requesting_area := rpad(nvl(l_request_area,'0'),6,'0') ; out_rec(l_attribute_info(a).building_block_id).wr_nbr := rpad(nvl(l_work_request_val,' '),8,' '); -- -- hr_utility.set_location('out_rec(l_attribute_info(a).building_block_id).charge_code: '||out_rec(l_attribute_info(a).building_block_id).charge_code,9); -- hr_utility.set_location('out_rec(l_attribute_info(a).building_block_id).requesting_area: '||out_rec(l_attribute_info(a).building_block_id).requesting_area,9); -- hr_utility.set_location('out_rec(l_attribute_info(a).building_block_id).wr_nbr: '||out_rec(l_attribute_info(a).building_block_id).wr_nbr,9); end if; -- a := l_attribute_info.next(a); -- end loop; end if; -- x := l_timecard_info.next(x); -- end loop; -- write_log('out_rec.count:'||out_rec.count); -- send_string; -- --hr_utility.trace_off; write_log('---> End mlgw_otl_validation_pkg.validate_timecard <---'); return 'X'; end validate_timecard; -- end mlgw_otl_validation_pkg;