[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
should be reported in Box38. Inserted
blank space between characters for
proper alignment in the field in PDF.
14-Sep-2009 sapalani 115.3 8899845 Business Number has to be reported
only in the first slip. Also made
changes to report only 2 characters
for box38 in error report.
27-Nov-2009 sapalani 115.4 9156528 Concatenated the address lines into
one string with line breaks
inbetween. This is reported under
XML tag .
09-Sep-2010 rgottipa 115.5 9984889 Modified get_asg_xml
to create xml tags for 2010 template
17-Sep-2010 rgottipa 115.8 9984889 increased size of varray because PDF request
is failing if empolyee is having more
other info elements
20-Sep-2010 rgottipa 115.9 9984889 Format for Other inforamtion data is
changed in creating XML
21-Sep-2010 rgottipa 115.10 9984889 l_k variable is passed as parameter
for convert_2_xml function.
27-Oct-2010 nkjaladi 115.11 5046003 Modified package to support the
T4A amend PDF report.
12-Nov-2010 rgottipa 115.12 10174115 l_first_nm length is changed from
12 to 15 characters in get_asg_xml()
22-Nov-2010 nkjaladi 115.13 10312756 Modified cursor c_t4a_othinfo_amts
in procedure store_other_information
to exclude balance
CAEOY_T4A_OTHER_INFO_AMOUNT036_PER_GRE_YTD
as CAEOY_T4A_EMPLOYEE_REGISTRATION_NO is
displayed in code 36
06-Dec-2010 rgottipa 115.17 10162688 XML structure modified such that
employee data displayed between tags
03-Jan-2011 nkjaladi 115.18 10162688 Modified package to support the
T4A Cancel PDF report.
10-Jan-2011 sneelapa 115.19 10624469 Modified package to create new XML
tag blank_type if EMPLOYEE is having
even number of data pages.
12-Jan-2011 sneelapa 115.20 10624469 Modified package to create new XML
tag blank_type if EMPLOYEE is having
even number of data pages.
15-Feb-2011 sneelapa 115.21 11775104 Modified get_asg_xml procedure
to create box61 tag only when request
is submitted with type as "Employer".
03-Nov-2011 sgotlasw 115.22 12943928 Code has been modified whether to diplay address
or GRE of employer on T4A PDF, T4A AMENDED, T4A
CANCELLED based on the input parameter from year 2011.
06-Jun-2012 rgottipa 115.23 Created the new function 'get_final_xml' which
is used to call from online.
17-Jan-2013 rgottipa 115.25 16055709 For EMPLOYEER option, blank page is not needed.
*/
FUNCTION append_to_lob(p_text in varchar)
RETURN BLOB IS
text_size NUMBER;
select decode(fdi.user_name, 'CAEOY_EMPLOYEE_NUMBER', '014',
'CAEOY_T4A_EMPLOYEE_REGISTRATION_NO', '036', substr(fdi.user_name,28,3)) code,
fai.value value
from ff_database_items fdi,
ff_archive_items fai
where fai.user_entity_id=fdi.user_entity_id
and fai.context1= p_assignment_action_id
and (((fdi.user_name LIKE 'CAEOY_T4A_OTHER_INFO_AMOUNT%_PER_GRE_YTD')
and (fdi.user_name <> 'CAEOY_T4A_OTHER_INFO_AMOUNT036_PER_GRE_YTD')) -- #10312756
or fdi.user_name in ('CAEOY_EMPLOYEE_NUMBER', 'CAEOY_T4A_EMPLOYEE_REGISTRATION_NO'))
-- and fai.value <> '0' by rgottipa for bug 6456662
order by code;
select decode(fdi.user_name, 'CAEOY_EMPLOYEE_NUMBER', '014', substr(fdi.user_name,28,3)) code,
fai.value value
from ff_database_items fdi,
ff_archive_items fai
where fai.user_entity_id=fdi.user_entity_id
and fai.context1= p_assignment_action_id
and ((fdi.user_name LIKE 'CAEOY_T4A_OTHER_INFO_AMOUNT%_PER_GRE_YTD')
or fdi.user_name in ('CAEOY_EMPLOYEE_NUMBER'))
order by code;
select 'Y'
into l_code36_exists
from ff_database_items fdi,
ff_archive_items fai
where fai.user_entity_id=fdi.user_entity_id
and fai.context1= p_assignment_action_id
and fdi.user_name ='CAEOY_T4A_EMPLOYEE_REGISTRATION_NO';
g_other_info_list.delete;
select
meaning
from
hr_lookups
where
lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
lookup_code = p_lookup_code;
select paa.payroll_action_id
into l_pa_id
from pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id;
select paa1.assignment_action_id, -- archiver asg action
ppa1.payroll_action_id, -- archiver pact
pay_ca_t4_reg.get_parameter('PRINT',ppa.legislative_parameters) -- Added for Bug 12943928
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
and fnd_number.canonical_to_number(substr(paa.serial_number,1,14)) = paa1.assignment_action_id
and paa1.payroll_action_id = ppa1.payroll_action_id
--and ppa1.report_type = 'T4A'
and ( ((ppa.report_type = 'PAYCAT4ACLPDF')
and ppa1.report_type in ('CAEOY_T4A_AMEND_PP','T4A'))
or
(ppa1.report_type = decode(ppa.report_type,'PAYCAT4AAMPDF','CAEOY_T4A_AMEND_PP','T4A')) --#5046003 included PAYCAT4AAMPDF
) --# 10359119 Added the or condition.
and ppa1.action_type = 'X'
and ppa1.action_status = 'C'
and ppa1.effective_date = ppa.effective_date;
select
code,
value
from ( select decode(l_box38,'13',hl.lookup_code,
SUBSTR(fdi.user_name,11,5)||': '||
SUBSTR(pay_ca_t4a_reg.get_label('PAY_CA_T4A_FOOTNOTES',
hl.lookup_code, l_lang),1,46)) code,
to_number(fai.value) value
from hr_lookups HL,
ff_database_items fdi,
ff_archive_items fai
where fai.user_entity_id=fdi.user_entity_id
and fai.context1= p_assignment_action_id
and fdi.user_name like 'CAEOY_T4A_BOX%_%_AMT_PER_GRE_YTD'
and fai.value <> '0'
and hl.lookup_type = 'PAY_CA_T4A_FOOTNOTES'
and decode(HL.LOOKUP_CODE,'10(BOX24)','10A',hl.lookup_code) =
SUBSTR(FDI.USER_NAME, 17, instr(fdi.user_name,'AMT') - 18 )
union all
select
decode(l_box38,'13',hl.lookup_code,
pay_ca_t4a_reg.get_label('PAY_CA_T4A_NONBOX_FOOTNOTES',
hl.lookup_code, l_lang)),
to_number(pai.action_information5)
from pay_action_information pai,
hr_lookups hl
where pai.action_context_id = p_assignment_action_id
and hl.lookup_type = 'PAY_CA_T4A_NONBOX_FOOTNOTES'
and hl.lookup_code = pai.action_information4
and pai.action_information6 = 'T4A')
where rownum < 5
order by code;
select
meaning
from
hr_lookups
where
lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
lookup_code = p_lookup_code;
select decode(correspondence_language,NULL,'US',correspondence_language)
from per_all_people_f
where person_id = to_number(pay_ca_archive_utils.get_archive_value(
l_aa_id, 'CAEOY_PERSON_ID'))
and p_effective_date between effective_start_date and effective_end_date;
select name
from hr_all_organization_units hou,
pay_assignment_actions paa
where paa.assignment_action_id = l_aa_id
and paa.tax_unit_id = hou.organization_id;
select paa1.assignment_action_id, -- archiver asg action
ppa1.payroll_action_id -- archiver pact
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
and substr(paa.serial_number,29,1) = 'Y'
--and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
and fnd_number.canonical_to_number(substr(paa.serial_number,1,14)) = paa1.assignment_action_id
and paa1.payroll_action_id = ppa1.payroll_action_id
--and ppa1.report_type = 'T4A'
and ( ((ppa.report_type = 'PAYCAT4ACLPDF')
and ppa1.report_type in ('CAEOY_T4A_AMEND_PP','T4A'))
or
(ppa1.report_type = decode(ppa.report_type,'PAYCAT4AAMPDF','CAEOY_T4A_AMEND_PP','T4A')) --#5046003 included PAYCAT4AAMPDF
) --# 10359119 Added the or condition.
and ppa1.action_type = 'X'
and ppa1.action_status = 'C'
and ppa1.effective_date = ppa.effective_date;