[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert the values into temporary table and delete the values from
temporary table once the data is printed.
*/
PROCEDURE print_report_address(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER)
IS
l6_wait BOOLEAN;
select userenv('sessionid')
into l_session_id
from dual;
select number_of_copies,
printer,
print_style,
save_output_flag
into copies_buffer,
printer_buffer,
style_buffer,
save_result
from fnd_concurrent_requests
where request_id = to_number(req_id);
DELETE
FROM pay_us_rpt_totals
WHERE attribute20 = 'INDIANA_YEAR_BEGIN_ADDRESS'
AND organization_id = l_session_id;
select number_of_copies,
printer,
print_style,
save_output_flag
into copies_buffer,
printer_buffer,
style_buffer,
save_result
from fnd_concurrent_requests
where request_id = to_number(req_id);
PROCEDURE get_insert_values ( p_proc_name VARCHAR2,
p_BUSINESS_GROUP_ID VARCHAR2,
p_person_id VARCHAR2,
p_curr_year VARCHAR2,
p_gre_name IN OUT NOCOPY VARCHAR2,
p_full_name IN OUT NOCOPY VARCHAR2,
p_employee_number IN OUT NOCOPY VARCHAR2)
IS
/* cursor to get the person's assignment detail */
CURSOR csr_person_details
IS
SELECT ppf.full_name,
ppf.employee_number
FROM
per_all_people_f ppf
WHERE
ppf.person_id = p_person_id
AND to_date('01-JAN-'||p_curr_year,'DD/MM/YYYY')
BETWEEN (ppf.effective_start_date + 1)
AND ppf.effective_end_date;
SELECT hou.name
FROM hr_all_organization_units hou
WHERE hou.organization_id = hou.business_group_id
and hou.business_group_id = p_business_group_id ;
l_proc_name varchar2(50) := 'get_insert_value';
hr_utility.trace('In proc : get_insert_values');
END get_insert_values ;
select userenv('sessionid')
into l_session_id
from dual;
INSERT
INTO pay_us_rpt_totals
(
organization_id,
gre_name,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute20
)
VALUES (
l_session_id,
p_tax_unit_name,
p_emp_full_name,
p_employee_number,
to_char(p_effective_start_date,'mm/dd/yyyy'),
p_town_or_city ,
p_region_1 ,
p_region_2 ,
p_postal_code ,
p_add_information17,
p_add_information18,
p_add_information19,
p_add_information20,
p_error,
'INDIANA_YEAR_BEGIN_ADDRESS');
procedure update_address(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_business_group IN VARCHAR2,
p_curr_year IN VARCHAR2) is
l_ret_code number;
SELECT *
FROM per_addresses pa
WHERE
pa.add_information17 = 'IN'
and pa.date_from < to_date('01-JAN-'||p_curr_year,'DD/MM/YYYY')
and pa.date_to is null
and pa.primary_flag = 'Y'
and pa.business_group_id = p_business_group;
update PER_ADDRESSES
set date_to = l_last_day
where address_id = l_add_rec.address_id;
hr_utility.trace('Updated Addresses : ' || to_char(l_add_rec.address_id));
insert into PER_ADDRESSES
(ADDRESS_ID,
BUSINESS_GROUP_ID,
PERSON_ID,
DATE_FROM,
PRIMARY_FLAG,
STYLE,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_TYPE,
COMMENTS,
COUNTRY,
DATE_TO,
POSTAL_CODE,
REGION_1,
REGION_2,
REGION_3,
TELEPHONE_NUMBER_1,
TELEPHONE_NUMBER_2,
TELEPHONE_NUMBER_3,
TOWN_OR_CITY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ADDR_ATTRIBUTE_CATEGORY,
ADDR_ATTRIBUTE1,
ADDR_ATTRIBUTE2,
ADDR_ATTRIBUTE3,
ADDR_ATTRIBUTE4,
ADDR_ATTRIBUTE5,
ADDR_ATTRIBUTE6,
ADDR_ATTRIBUTE7,
ADDR_ATTRIBUTE8,
ADDR_ATTRIBUTE9,
ADDR_ATTRIBUTE10,
ADDR_ATTRIBUTE11,
ADDR_ATTRIBUTE12,
ADDR_ATTRIBUTE13,
ADDR_ATTRIBUTE14,
ADDR_ATTRIBUTE15,
ADDR_ATTRIBUTE16,
ADDR_ATTRIBUTE17,
ADDR_ATTRIBUTE18,
ADDR_ATTRIBUTE19,
ADDR_ATTRIBUTE20,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
OBJECT_VERSION_NUMBER,
ADD_INFORMATION17,
ADD_INFORMATION18,
ADD_INFORMATION19,
ADD_INFORMATION20,
ADD_INFORMATION13,
ADD_INFORMATION14,
ADD_INFORMATION15,
ADD_INFORMATION16
)
values
(per_addresses_s.NEXTVAL,
l_add_rec.BUSINESS_GROUP_ID,
l_add_rec.PERSON_ID,
to_date('01-JAN-'||p_curr_year,'DD/MM/YYYY'),
--to_date('01-JAN-2001', 'DD-MON-YYYY'),
l_add_rec.PRIMARY_FLAG,
l_add_rec.STYLE,
l_add_rec.ADDRESS_LINE1,
l_add_rec.ADDRESS_LINE2,
l_add_rec.ADDRESS_LINE3,
l_add_rec.ADDRESS_TYPE,
l_add_rec.COMMENTS,
l_add_rec.COUNTRY,
NULL,
l_add_rec.POSTAL_CODE,
l_add_rec.REGION_1,
l_add_rec.REGION_2,
l_add_rec.REGION_3,
l_add_rec.TELEPHONE_NUMBER_1,
l_add_rec.TELEPHONE_NUMBER_2,
l_add_rec.TELEPHONE_NUMBER_3,
l_add_rec.TOWN_OR_CITY,
l_add_rec.REQUEST_ID,
l_add_rec.PROGRAM_APPLICATION_ID,
l_add_rec.PROGRAM_ID,
l_add_rec.PROGRAM_UPDATE_DATE,
l_add_rec.ADDR_ATTRIBUTE_CATEGORY,
l_add_rec.ADDR_ATTRIBUTE1,
l_add_rec.ADDR_ATTRIBUTE2,
l_add_rec.ADDR_ATTRIBUTE3,
l_add_rec.ADDR_ATTRIBUTE4,
l_add_rec.ADDR_ATTRIBUTE5,
l_add_rec.ADDR_ATTRIBUTE6,
l_add_rec.ADDR_ATTRIBUTE7,
l_add_rec.ADDR_ATTRIBUTE8,
l_add_rec.ADDR_ATTRIBUTE9,
l_add_rec.ADDR_ATTRIBUTE10,
l_add_rec.ADDR_ATTRIBUTE11,
l_add_rec.ADDR_ATTRIBUTE12,
l_add_rec.ADDR_ATTRIBUTE13,
l_add_rec.ADDR_ATTRIBUTE14,
l_add_rec.ADDR_ATTRIBUTE15,
l_add_rec.ADDR_ATTRIBUTE16,
l_add_rec.ADDR_ATTRIBUTE17,
l_add_rec.ADDR_ATTRIBUTE18,
l_add_rec.ADDR_ATTRIBUTE19,
l_add_rec.ADDR_ATTRIBUTE20,
l_add_rec.LAST_UPDATE_DATE,
l_add_rec.LAST_UPDATED_BY,
l_add_rec.LAST_UPDATE_LOGIN,
l_add_rec.CREATED_BY,
l_add_rec.CREATION_DATE,
l_add_rec.OBJECT_VERSION_NUMBER,
NULL,
NULL,
NULL,
NULL,
l_add_rec.ADD_INFORMATION13,
l_add_rec.ADD_INFORMATION14,
l_add_rec.ADD_INFORMATION15,
l_add_rec.ADD_INFORMATION16
) ;
get_insert_values(
'INDIANA_YEAR_BEGIN_ADDRESS',
l_add_rec.BUSINESS_GROUP_ID,
l_add_rec.PERSON_ID,
p_curr_year,
l_gre_name,
l_full_name,
l_employee_number
);