1 package PAY_GB_ENROLL_INFO_GENE AUTHID CURRENT_USER as
2 /* $Header: pygbpaeig.pkh 120.0.12020000.4 2013/02/18 04:44:55 rsadhana noship $ */
3 PROCEDURE generate
4 (
5 ERRBUG OUT NOCOPY VARCHAR2
6 ,RETCODE OUT NOCOPY VARCHAR2
7 ,P_EMPLOYER IN number
8 ,P_PAYROLL IN number
9 ,P_PERIOD IN number
10 ,P_LETTER IN VARCHAR2
11 --,p_xml OUT NOCOPY CLOB
12 ,P_DEBUG_FLAG IN varchar2
13 ,P_MODE IN varchar2
14 ,P_ASSIGNMENT_SET number
15 ,P_ISSUE_DATE VARCHAR2
16 );
17
18 procedure generate_pdf(
19
20 P_EMPLOYER IN number
21 ,P_PAYROLL IN number
22 ,P_PERIOD IN number
23 ,P_TEMPLATE_NAME IN VARCHAR2
24 --,P_LETTER IN VARCHAR2
25 ,P_ASSIGNMENT_SET IN number
26 ,P_LETTER IN VARCHAR2
27 ,P_ISSUE_DATE IN VARCHAR2
28 ,p_xml OUT NOCOPY CLOB
29 );
30
31 cursor csr_address(p_person_id number, p_effective_date date) is
32 select upper(substr(trim(addr.address_line1),1,35)) addr1,
33 upper(substr(trim(addr.address_line2),1,35)) addr2,
34 upper(substr(trim(addr.address_line3),1,35)) addr3,
35 substr(addr.postal_code,1,10) post_code,
36 upper(substr(trim(addr.town_or_city),1,35)) addr4,
37 upper(substr(trim(addr.country),1,35)) country
38 from per_addresses addr
39 where addr.person_id = p_person_id
40 and ( addr.primary_flag = 'Y'
41 or addr.primary_flag is null)
42 and p_effective_date between nvl(addr.date_from,fnd_date.canonical_to_date('0001/01/01 00:00:00'))
43 and nvl(addr.date_to, fnd_date.canonical_to_date('4712/12/31 00:00:00'));
44
45 end;