[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
R Babla 11-Aug-2009 115.5 8468137 Updated procedure update_company_data to select and pass the
value of SDL Number
R Babla 17-Aug-2009 115.6 8468137 Updated procedure update_company_data to correctly pass the value
of SDL Number
========================================================================*/
--
-- Global Variables
--
g_package constant varchar2(31) := 'PER_ZA_WSP_XML_GEN_PKG.';
Name : update_dom
Purpose : Update a node to XML DOM.
Arguments :
--------------------------------------------------------------------------*/
procedure update_dom( p_name varchar2
, p_text varchar2
, p_level binary_integer) is
parent_node xmldom.DOMNode;
end update_dom;
Name : update_company_data
Purpose : Updates employer data to XML DOM.
Assumes g_xml_dom is already initialized.
Arguments :
--------------------------------------------------------------------------*/
procedure update_company_data( p_legal_entity_id in number
, p_payroll_action_id in varchar2) is
l_proc varchar2(100) := g_package || 'update_company_data';
select action_information3 org_name
, action_information4 post_add_line_1 -- A(1).2 Postal Address
, action_information5 post_add_line_2
, action_information6 post_add_line_3
, action_information8 post_town_or_city
, action_information9 post_postal_code
, action_information10 post_province
, action_information11 phy_add_line_1 -- A(1).3 Physical Address
, action_information12 phy_add_line_2
, action_information13 phy_add_line_3
, action_information15 phy_town_or_city
, action_information16 phy_postal_code
, action_information17 phy_province
, action_information18 tel_no -- A(1).5 Telephone number
, action_information19 fax_no -- A(1).6 Fax number
, action_information20 email_add -- A(1).7 E-mail Address
, action_information23 sdl_no
from pay_action_information
where action_context_id = p_payroll_action_id
and action_information_category = 'ZA WSP EMPLOYER DETAILS'
and action_information2 = p_legal_entity_id
and action_context_type = 'PA';
select action_information3 sdf_name -- A(1).12 SDF Name
, action_information4 sdf_add_line_1 -- A(1).13 SDF Address
, action_information5 sdf_add_line_2
, action_information6 sdf_add_line_3
, action_information7 sdf_town_or_city
, action_information8 sdf_postal_code
, action_information9 sdf_province
, action_information10 sdf_tel -- A(1).14 SDF contact details
, action_information11 sdf_mobile
, action_information12 sdf_fax
, action_information13 sdf_email
from pay_action_information
where action_context_id = p_payroll_action_id
and action_information_category = 'ZA WSP SDF DETAILS'
and action_information2 = p_legal_entity_id
and action_context_type = 'PA';
update_dom('COMPANY', null, g_company_level);
update_dom('NAME', null, g_company_name_level);
update_dom('LINE_1', comp_contacts_rec.org_name, g_company_name_lin_level);
update_dom('POSTAL_ADDRESS', null, g_company_add_level);
update_dom('ADDRESS_LINE_1', comp_contacts_rec.post_add_line_1 , g_company_add_det_level);
update_dom('ADDRESS_LINE_2', comp_contacts_rec.post_add_line_2 , g_company_add_det_level);
update_dom('ADDRESS_LINE_3', comp_contacts_rec.post_add_line_3 , g_company_add_det_level);
update_dom('TOWN_CITY' , comp_contacts_rec.post_town_or_city, g_company_add_det_level);
update_dom('POSTAL_CODE' , comp_contacts_rec.post_postal_code , g_company_add_det_level);
update_dom('PROVINCE' , comp_contacts_rec.post_province , g_company_add_det_level);
update_dom('PHYSICAL_ADDRESS', null, g_company_add_level);
update_dom('ADDRESS_LINE_1' , comp_contacts_rec.phy_add_line_1 , g_company_add_det_level);
update_dom('ADDRESS_LINE_2' , comp_contacts_rec.phy_add_line_2 , g_company_add_det_level);
update_dom('ADDRESS_LINE_3' , comp_contacts_rec.phy_add_line_3 , g_company_add_det_level);
update_dom('TOWN_CITY' , comp_contacts_rec.phy_town_or_city, g_company_add_det_level);
update_dom('POSTAL_CODE' , comp_contacts_rec.phy_postal_code , g_company_add_det_level);
update_dom('PROVINCE' , comp_contacts_rec.phy_province , g_company_add_det_level);
update_dom('TEL' , comp_contacts_rec.tel_no , g_company_det_level);
update_dom('FAX' , comp_contacts_rec.fax_no , g_company_det_level);
update_dom('E-MAIL' ,comp_contacts_rec.email_add, g_company_det_level);
update_dom('SDL' ,comp_contacts_rec.sdl_no , g_company_det_level);
update_dom('SDF_NAME', comp_sdf_det_rec.sdf_name, g_company_det_level);
update_dom('SDF_ADDRESS', null, g_company_add_level);
update_dom('ADDRESS_LINE_1' , comp_sdf_det_rec.sdf_add_line_1 , g_company_add_det_level);
update_dom('ADDRESS_LINE_2' , comp_sdf_det_rec.sdf_add_line_2 , g_company_add_det_level);
update_dom('ADDRESS_LINE_3' , comp_sdf_det_rec.sdf_add_line_3 , g_company_add_det_level);
update_dom('TOWN_CITY' , comp_sdf_det_rec.sdf_town_or_city, g_company_add_det_level);
update_dom('POSTAL_CODE' , comp_sdf_det_rec.sdf_postal_code , g_company_add_det_level);
update_dom('PROVINCE' , comp_sdf_det_rec.sdf_province , g_company_add_det_level);
update_dom('SDF_TEL' , comp_sdf_det_rec.sdf_tel , g_company_det_level);
update_dom('SDF_MOBILE', comp_sdf_det_rec.sdf_mobile, g_company_det_level);
update_dom('SDF_FAX' , comp_sdf_det_rec.sdf_fax , g_company_det_level);
update_dom('SDF_EMAIL' , comp_sdf_det_rec.sdf_email , g_company_det_level);
end update_company_data;
Name : update_wsp_data
Purpose : Updates WSP(Training and Beneficiary) data to XML DOM.
Assumes g_xml_dom is already initialized.
Arguments :
--------------------------------------------------------------------------*/
procedure update_wsp_data( p_legal_entity_id in number
, p_payroll_action_id in varchar2) is
l_proc varchar2(100) := g_package || 'update_wsp_data';
select action_information3 sk_num -- Skills priority number (i.e serial number)
, action_information4 training_priority -- Skills priority ( Education/ Training priority)
, action_information5 lev_1
, action_information6 lev_2
, action_information7 lev_3
, action_information8 lev_4
, action_information9 lev_5
, action_information10 lev_6
, action_information11 lev_7
, action_information12 lev_8
, action_information13 unknown
, action_information14 saqa_yes
, action_information15 saqa_no
, action_information16 saqa_id
, action_information17 year
from pay_action_information
where action_context_id = p_payroll_action_id
and action_information_category = 'ZA WSP TRAINING PROGRAMS'
and action_information2 = p_legal_entity_id
and action_context_type = 'PA'
order by sk_num;
select pai.action_information6 occ_cat
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'ZA WSP PERSON DETAILS'
and pai.action_information2 = p_legal_entity_id
and pai.action_context_type = 'AAP'
group by pai.action_information6;
select distinct pai.action_information3 person_id
, pai.action_information4 race
, pai.action_information5 sex
, pai.action_information7 disability
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'ZA WSP PERSON DETAILS'
and pai.action_information2 = p_legal_entity_id
and pai.action_context_type = 'AAP'
and pai.action_information6 = p_occ_cat;
select distinct pai.action_information11 sk_pr_num
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'ZA WSP PERSON DETAILS'
and pai.action_information2 = p_legal_entity_id
and pai.action_context_type = 'AAP'
and pai.action_information6 = p_occ_cat
order by sk_pr_num;
select legislative_parameters
into l_leg_parameters
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
update_dom('WSP', null, g_wsp_level);
update_dom('WSP_START_YEAR', l_wsp_end_year - 1, g_wsp_date_level);
update_dom('WSP_END_YEAR', l_wsp_end_year, g_wsp_date_level);
update_dom('TRAINING_DETAILS', null, g_wsp_trng_level);
update_dom('SK_NUM' , wsp_trng_rec.sk_num , g_wsp_trng_det_level);
update_dom('TRAINING_PRIORITY', wsp_trng_rec.training_priority, g_wsp_trng_det_level);
update_dom('LEV_1' , wsp_trng_rec.lev_1 , g_wsp_trng_det_level);
update_dom('LEV_2' , wsp_trng_rec.lev_2 , g_wsp_trng_det_level);
update_dom('LEV_3' , wsp_trng_rec.lev_3 , g_wsp_trng_det_level);
update_dom('LEV_4' , wsp_trng_rec.lev_4 , g_wsp_trng_det_level);
update_dom('LEV_5' , wsp_trng_rec.lev_5 , g_wsp_trng_det_level);
update_dom('LEV_6' , wsp_trng_rec.lev_6 , g_wsp_trng_det_level);
update_dom('LEV_7' , wsp_trng_rec.lev_7 , g_wsp_trng_det_level);
update_dom('LEV_8' , wsp_trng_rec.lev_8 , g_wsp_trng_det_level);
update_dom('UNKNOWN' , wsp_trng_rec.unknown , g_wsp_trng_det_level);
update_dom('SAQA_YES' , wsp_trng_rec.saqa_yes , g_wsp_trng_det_level);
update_dom('SAQA_NO' , wsp_trng_rec.saqa_no , g_wsp_trng_det_level);
update_dom('SAQA_ID' , wsp_trng_rec.saqa_id , g_wsp_trng_det_level);
update_dom('BENEFICIARIRY_DETAILS', null, g_wsp_benf_level);
update_dom('OCCUPATION' , wsp_occ_rec.occ_cat, g_wsp_benf_det_level);
update_dom('PRIORITY_NUMBER' , l_sk_pr_num , g_wsp_benf_det_level);
update_dom('MALE_AFRICANS' , l_ma, g_wsp_benf_det_level);
update_dom('FEMALE_AFRICANS' , l_fa, g_wsp_benf_det_level);
update_dom('DISABILED_AFRICANS', l_da, g_wsp_benf_det_level);
update_dom('MALE_COLOUREDS' , l_mc, g_wsp_benf_det_level);
update_dom('FEMALE_COLOUREDS' , l_fc, g_wsp_benf_det_level);
update_dom('DISABILED_COLOUREDS', l_dc, g_wsp_benf_det_level);
update_dom('MALE_INDIANS' , l_mi, g_wsp_benf_det_level);
update_dom('FEMALE_INDIANS' , l_fi, g_wsp_benf_det_level);
update_dom('DISABILED_INDIANS', l_di, g_wsp_benf_det_level);
update_dom('MALE_WHITES' , l_mw, g_wsp_benf_det_level);
update_dom('FEMALE_WHITES' , l_fw, g_wsp_benf_det_level);
update_dom('DISABILED_WHITES', l_dw, g_wsp_benf_det_level);
update_dom('MALE_TOTALS' , l_mt, g_wsp_benf_det_level);
update_dom('FEMALE_TOTALS' , l_ft, g_wsp_benf_det_level);
update_dom('DISABILED_TOTALS', l_dt, g_wsp_benf_det_level);
update_dom('MALE_AFRICANS_TOT' , l_wsp_ma_sum, g_wsp_benf_level);
update_dom('FEMALE_AFRICANS_TOT' , l_wsp_fa_sum, g_wsp_benf_level);
update_dom('DISABILED_AFRICANS_TOT' , l_wsp_da_sum, g_wsp_benf_level);
update_dom('MALE_COLOUREDS_TOT' , l_wsp_mc_sum, g_wsp_benf_level);
update_dom('FEMALE_COLOUREDS_TOT' , l_wsp_fc_sum, g_wsp_benf_level);
update_dom('DISABILED_COLOUREDS_TOT', l_wsp_dc_sum, g_wsp_benf_level);
update_dom('MALE_INDIANS_TOT' , l_wsp_mi_sum, g_wsp_benf_level);
update_dom('FEMALE_INDIANS_TOT' , l_wsp_fi_sum, g_wsp_benf_level);
update_dom('DISABILED_INDIANS_TOT' , l_wsp_di_sum, g_wsp_benf_level);
update_dom('MALE_WHITES_TOT' , l_wsp_mw_sum, g_wsp_benf_level);
update_dom('FEMALE_WHITES_TOT' , l_wsp_fw_sum, g_wsp_benf_level);
update_dom('DISABILED_WHITES_TOT' , l_wsp_dw_sum, g_wsp_benf_level);
update_dom('MALE_TOTALS_TOT' , l_wsp_mt_sum, g_wsp_benf_level);
update_dom('FEMALE_TOTALS_TOT' , l_wsp_ft_sum, g_wsp_benf_level);
update_dom('DISABILED_TOTALS_TOT' , l_wsp_dt_sum, g_wsp_benf_level);
end update_wsp_data;
Name : update_atr_data
Purpose : Updates ATR(Training and Beneficiary) data to XML DOM.
Assumes g_xml_dom is already initialized.
Arguments :
--------------------------------------------------------------------------*/
procedure update_atr_data( p_legal_entity_id in number
, p_payroll_action_id in varchar2) is
l_proc varchar2(100) := g_package || 'update_atr_data';
select action_information3 sk_num -- Skills priority number (i.e serial number)
, action_information4 training_priority -- Skills priority ( Education/ Training priority)
, action_information5 lev_1
, action_information6 lev_2
, action_information7 lev_3
, action_information8 lev_4
, action_information9 lev_5
, action_information10 lev_6
, action_information11 lev_7
, action_information12 lev_8
, action_information13 unknown
, action_information14 saqa_yes
, action_information15 saqa_no
, action_information16 saqa_id
, action_information17 year
from pay_action_information
where action_context_id = p_payroll_action_id
and action_information_category = 'ZA ATR TRAINING PROGRAMS'
and action_information2 = p_legal_entity_id
and action_context_type = 'PA'
order by sk_num;
select action_information6 occ_cat
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'ZA ATR PERSON DETAILS'
and pai.action_information2 = p_legal_entity_id
and pai.action_context_type = 'AAP'
group by pai.action_information6;
select distinct pai.action_information3 person_id
, action_information4 race
, action_information5 sex
, action_information7 disability
, action_information14 status
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'ZA ATR PERSON DETAILS'
and pai.action_information2 = p_legal_entity_id
and pai.action_context_type = 'AAP'
and pai.action_information6 = p_occ_cat;
select distinct action_information11 sk_pr_num
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
where ppa.payroll_action_id = p_payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'ZA ATR PERSON DETAILS'
and pai.action_information2 = p_legal_entity_id
and pai.action_context_type = 'AAP'
and pai.action_information6 = p_occ_cat
order by sk_pr_num;
update_dom('ATR', null, g_atr_level);
update_dom('TRAINING_DETAILS', null, g_atr_trng_level);
update_dom('SK_NUM' , atr_trng_rec.sk_num , g_atr_trng_det_level);
update_dom('TRAINING_PRIORITY', atr_trng_rec.training_priority, g_atr_trng_det_level);
update_dom('LEV_1' , atr_trng_rec.lev_1 , g_atr_trng_det_level);
update_dom('LEV_2' , atr_trng_rec.lev_2 , g_atr_trng_det_level);
update_dom('LEV_3' , atr_trng_rec.lev_3 , g_atr_trng_det_level);
update_dom('LEV_4' , atr_trng_rec.lev_4 , g_atr_trng_det_level);
update_dom('LEV_5' , atr_trng_rec.lev_5 , g_atr_trng_det_level);
update_dom('LEV_6' , atr_trng_rec.lev_6 , g_atr_trng_det_level);
update_dom('LEV_7' , atr_trng_rec.lev_7 , g_atr_trng_det_level);
update_dom('LEV_8' , atr_trng_rec.lev_8 , g_atr_trng_det_level);
update_dom('UNKNOWN' , atr_trng_rec.unknown , g_atr_trng_det_level);
update_dom('SAQA_YES' , atr_trng_rec.saqa_yes , g_atr_trng_det_level);
update_dom('SAQA_NO' , atr_trng_rec.saqa_no , g_atr_trng_det_level);
update_dom('SAQA_ID' , atr_trng_rec.saqa_id , g_atr_trng_det_level);
update_dom('BENEFICIARIRY_DETAILS', null, g_atr_benf_level);
update_dom('OCCUPATION' , atr_occ_rec.occ_cat, g_atr_benf_det_level);
update_dom('PRIORITY_NUMBER' , l_sk_pr_num , g_atr_benf_det_level);
update_dom('MALE_AFRICANS' , l_ma, g_atr_benf_det_level);
update_dom('FEMALE_AFRICANS' , l_fa, g_atr_benf_det_level);
update_dom('DISABILED_AFRICANS', l_da, g_atr_benf_det_level);
update_dom('MALE_COLOUREDS' , l_mc, g_atr_benf_det_level);
update_dom('FEMALE_COLOUREDS' , l_fc, g_atr_benf_det_level);
update_dom('DISABILED_COLOUREDS', l_dc, g_atr_benf_det_level);
update_dom('MALE_INDIANS' , l_mi, g_atr_benf_det_level);
update_dom('FEMALE_INDIANS' , l_fi, g_atr_benf_det_level);
update_dom('DISABILED_INDIANS', l_di, g_atr_benf_det_level);
update_dom('MALE_WHITES' , l_mw, g_atr_benf_det_level);
update_dom('FEMALE_WHITES' , l_fw, g_atr_benf_det_level);
update_dom('DISABILED_WHITES', l_dw, g_atr_benf_det_level);
update_dom('MALE_TOTALS' , l_mt, g_atr_benf_det_level);
update_dom('FEMALE_TOTALS' , l_ft, g_atr_benf_det_level);
update_dom('DISABILED_TOTALS', l_dt, g_atr_benf_det_level);
update_dom('MALE_AFRICANS_COMP' , l_cma, g_atr_benf_det_level);
update_dom('FEMALE_AFRICANS_COMP' , l_cfa, g_atr_benf_det_level);
update_dom('DISABILED_AFRICANS_COMP', l_cda, g_atr_benf_det_level);
update_dom('MALE_COLOUREDS_COMP' , l_cmc, g_atr_benf_det_level);
update_dom('FEMALE_COLOUREDS_COMP' , l_cfc, g_atr_benf_det_level);
update_dom('DISABILED_COLOUREDS_COMP', l_cdc, g_atr_benf_det_level);
update_dom('MALE_INDIANS_COMP' , l_cmi, g_atr_benf_det_level);
update_dom('FEMALE_INDIANS_COMP' , l_cfi, g_atr_benf_det_level);
update_dom('DISABILED_INDIANS_COMP', l_cdi, g_atr_benf_det_level);
update_dom('MALE_WHITES_COMP' , l_cmw, g_atr_benf_det_level);
update_dom('FEMALE_WHITES_COMP' , l_cfw, g_atr_benf_det_level);
update_dom('DISABILED_WHITES_COMP', l_cdw, g_atr_benf_det_level);
update_dom('MALE_TOTALS_COMP' , l_cmt, g_atr_benf_det_level);
update_dom('FEMALE_TOTALS_COMP' , l_cft, g_atr_benf_det_level);
update_dom('DISABILED_TOTALS_COMP', l_cdt, g_atr_benf_det_level);
update_dom('MALE_AFRICANS_TOT' , l_ma_sum, g_atr_benf_level);
update_dom('FEMALE_AFRICANS_TOT' , l_fa_sum, g_atr_benf_level);
update_dom('DISABILED_AFRICANS_TOT' , l_da_sum, g_atr_benf_level);
update_dom('MALE_COLOUREDS_TOT' , l_mc_sum, g_atr_benf_level);
update_dom('FEMALE_COLOUREDS_TOT' , l_fc_sum, g_atr_benf_level);
update_dom('DISABILED_COLOUREDS_TOT', l_dc_sum, g_atr_benf_level);
update_dom('MALE_INDIANS_TOT' , l_mi_sum, g_atr_benf_level);
update_dom('FEMALE_INDIANS_TOT' , l_fi_sum, g_atr_benf_level);
update_dom('DISABILED_INDIANS_TOT' , l_di_sum, g_atr_benf_level);
update_dom('MALE_WHITES_TOT' , l_mw_sum, g_atr_benf_level);
update_dom('FEMALE_WHITES_TOT' , l_fw_sum, g_atr_benf_level);
update_dom('DISABILED_WHITES_TOT' , l_dw_sum, g_atr_benf_level);
update_dom('MALE_TOTALS_TOT' , l_mt_sum, g_atr_benf_level);
update_dom('FEMALE_TOTALS_TOT' , l_ft_sum, g_atr_benf_level);
update_dom('DISABILED_TOTALS_TOT' , l_dt_sum, g_atr_benf_level);
end update_atr_data;
select distinct action_information2 legal_entity_id -- Legal entity ID
from pay_action_information
where action_context_id = p_payroll_action_id
and action_information1 = p_business_group_id
and action_information2 = nvl(p_legal_entity_id, action_information2)
and action_information_category = 'ZA WSP EMPLOYER DETAILS'
and action_context_type = 'PA';
update_dom('ZA_WSP_ATR_DATA', null, g_root_level);
update_company_data(rec.legal_entity_id, p_payroll_action_id);
update_wsp_data(rec.legal_entity_id, p_payroll_action_id);
update_atr_data(rec.legal_entity_id, p_payroll_action_id);