[Home] [Help]
PACKAGE: APPS.PAY_SE_TAX_CARD_REQ_PKG
Source
1 PACKAGE PAY_SE_TAX_CARD_REQ_PKG AUTHID CURRENT_USER as
2 /* $Header: pysetaxr.pkh 120.1.12010000.3 2009/10/13 10:32:33 vijranga ship $ */
3
4 level_cnt NUMBER;
5
6 FUNCTION get_parameter (
7 p_parameter_string in varchar2
8 ,p_token in varchar2
9 ,p_segment_number in number default null) RETURN varchar2;
10
11 PROCEDURE range_code(
12 p_payroll_action_id IN NUMBER,
13 p_sqlstr OUT NOCOPY VARCHAR2);
14
15
16 PROCEDURE assignment_action_code(
17 pactid IN NUMBER,
18 stperson IN NUMBER,
19 endperson IN NUMBER,
20 chunk IN NUMBER);
21
22 /********************************************************
23 * Cursor to fetch Body record information *
24 ********************************************************/
25 CURSOR CSR_SE_HEAD
26 IS select 'ORG_NUM=P', RPAD(hoi2.org_information2,12) /*ORGNRF*/
27 ,'REQUEST_DATE=P', to_char(fnd_date.CANONICAL_TO_DATE(PAY_SE_TAX_CARD_REQ_PKG.get_parameter(ppa.legislative_parameters, 'EFFECTIVE_DATE')), 'YYYYMMDD') /*DATUMF*/
28 ,'TIME_STAMP=P', to_char(fnd_date.CANONICAL_TO_DATE(PAY_SE_TAX_CARD_REQ_PKG.get_parameter(ppa.legislative_parameters, 'EFFECTIVE_DATE')),'HH24MISS') /*KLOCKF*/
29 ,'EMPLOYER_NAME=P', RPAD(NVL(substr(ou.name,1,30),' '),30,' ') /*NAMNF*/
30 ,'CONTACT_PERSON=P', RPAD(NVL(substr(hoi3.org_information3,1,30),' '),30,' ') /*AVDF */
31 ,'ADDRESS1=P', RPAD(NVL(substr(HL.ADDRESS_LINE_1||decode(HL.ADDRESS_LINE_1,NULL,'',',')|| HL.ADDRESS_LINE_2|| decode(HL.ADDRESS_LINE_2,NULL,'',',') ||HL.ADDRESS_LINE_3,1,30),' '),30,' ') /*ADRF*/
32 -- Bug#8849455 fix Added space between 3 and 4 digits in postal code
33 ,'POSTAL_CODE=P', RPAD(NVL(substr(hl.Postal_code,1,3)||' '||substr(hl.Postal_code,4,2),' '),7,' ') /*PONRF*/
34 ,'POST_OFFICE=P', RPAD(nvl(substr(hr_general.DECODE_LOOKUP('SE_POSTAL_CODE',HL.POSTAL_CODE),7,16),' '),15,' ') /*ORTAF*/
35 ,'PHONE=P', RPAD(NVL(substr(hoi4.org_information3,1,10),' '),10,' ') /*TELEF*/
36 ,'TEST_RUN=P', DECODE(pay_se_tax_card_req_pkg.get_parameter(ppa.legislative_parameters, 'TEST_RUN'),'Y','T',' ')
37 from
38 PAY_PAYROLL_ACTIONS ppa
39 ,hr_organization_units ou
40 ,hr_organization_information hoi1
41 ,hr_organization_information hoi2
42 ,hr_organization_information hoi3
43 ,hr_organization_information hoi4
44 ,HR_LOCATIONS_ALL HL
45 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
46 AND ou.organization_id = PAY_SE_TAX_CARD_REQ_PKG.get_parameter(ppa.legislative_parameters, 'REQUESTING_ORG')
47 and ou.business_group_id = ppa.business_group_id
48 AND hoi1.organization_id = ou.organization_id
49 AND hoi1.ORG_INFORMATION_CONTEXT='CLASS'
50 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
51 AND hoi2.organization_id = ou.organization_id
52 AND hoi2.organization_id = hoi1.organization_id
53 AND hoi2.ORG_INFORMATION_CONTEXT = 'SE_LEGAL_EMPLOYER_DETAILS'
54 AND hoi3.organization_id = ou.organization_id
55 AND hoi3.ORG_INFORMATION_CONTEXT = 'SE_ORG_CONTACT_DETAILS'
56 AND hoi3.org_information_id = (select min(hoi22.org_information_id)
57 from
58 hr_organization_information hoi11
59 ,hr_organization_information hoi22
60 where
61 hoi11.organization_id = PAY_SE_TAX_CARD_REQ_PKG.get_parameter(ppa.legislative_parameters, 'REQUESTING_ORG')
62 AND hoi11.ORG_INFORMATION_CONTEXT='CLASS'
63 AND hoi11.org_information1 = 'HR_LEGAL_EMPLOYER'
64 AND hoi22.organization_id = hoi11.organization_id
65 AND hoi22.ORG_INFORMATION_CONTEXT = 'SE_ORG_CONTACT_DETAILS'
66 AND hoi22.org_information1='PERSON'
67 )
68 AND hoi3.org_information1='PERSON'
69 AND hoi4.organization_id = ou.organization_id
70 AND hoi4.ORG_INFORMATION_CONTEXT = 'SE_ORG_CONTACT_DETAILS'
71 AND hoi4.org_information1='PHONE'
72 AND ou.Location_id = HL.Location_id (+) ;
73
74
75 CURSOR CSR_SE_BODY is select /*+ INDEX(scl, HR_SOFT_CODING_KEYFLEX_PK) */ 'ORG_NUM=P', RPAD(hoi2.org_information2,12),
76 'PERSON_NUM=P', RPAD((TO_CHAR(pap.DATE_OF_BIRTH,'CC')-2)||REPLACE(PAP.NATIONAL_IDENTIFIER,'-',''),12)
77 FROM HR_ORGANIZATION_INFORMATION hoi1
78 , HR_ORGANIZATION_INFORMATION hoi2
79 , HR_ORGANIZATION_INFORMATION hoi3
80 , HR_ORGANIZATION_INFORMATION hoi4
81 , PAY_LEGISLATION_RULES plr
82 , HR_SOFT_CODING_KEYFLEX scl
83 , PER_ALL_PEOPLE_F pap
84 , PER_ALL_ASSIGNMENTS_F paa
85 , PAY_PAYROLL_ACTIONS ppa
86 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
87 and hoi1.organization_id = decode(pay_se_tax_card_req_pkg.get_parameter(ppa.legislative_parameters, 'REQUEST_FOR'),'ALL_ORG',hoi1.organization_id,pay_se_tax_card_req_pkg.get_parameter(ppa.legislative_parameters, 'REQUESTING_ORG'))
88 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
89 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
90 and hoi2.organization_id = hoi1.organization_id
91 and hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS'
92 and hoi2.organization_id = hoi3.organization_id
93 and hoi3.ORG_INFORMATION_CONTEXT = 'SE_LOCAL_UNITS'
94 and hoi3.org_information1= hoi4.organization_id
95 and hoi4.ORG_INFORMATION_CONTEXT='CLASS'
96 and hoi4.org_information1='SE_LOCAL_UNIT'
97 and scl.id_flex_num = plr.rule_mode
98 and plr.legislation_code = 'SE'
99 and plr.rule_type = 'S'
100 and scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
101 and to_char(hoi4.organization_id) = scl.segment2
102 and paa.person_id = pap.person_id
103 and ppa.effective_date BETWEEN paa.effective_start_date and paa.effective_end_date
104 and ppa.effective_date BETWEEN pap.effective_start_date and pap.effective_end_date
105 and (paa.assignment_id,scl.segment2)
106 in
107 ( SELECT /*+ INDEX(sck, HR_SOFT_CODING_KEYFLEX_PK) */ min(paaf.assignment_id),sck.segment2
108 FROM
109 per_all_people_f papf
110 ,per_all_assignments_f paaf
111 ,HR_SOFT_CODING_KEYFLEX sck
112 ,HR_ORGANIZATION_INFORMATION hroi
113 WHERE paaf.person_id = papf.person_id
114 and paaf.BUSINESS_GROUP_ID = ppa.BUSINESS_GROUP_ID
115 and ppa.effective_date BETWEEN paaf.effective_start_date and paaf.effective_end_date
116 and ppa.effective_date BETWEEN papf.effective_start_date and papf.effective_end_date
117 AND sck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
118 and sck.id_flex_num = plr.rule_mode
119 and sck.enabled_flag='Y'
120 AND hroi.organization_id =decode(pay_se_tax_card_req_pkg.get_parameter(ppa.legislative_parameters, 'REQUEST_FOR'),'ALL_ORG',hoi1.organization_id,pay_se_tax_card_req_pkg.get_parameter(ppa.legislative_parameters, 'REQUESTING_ORG'))
121 AND hroi.ORG_INFORMATION_CONTEXT = 'SE_LOCAL_UNITS'
122 AND hroi.org_information1 = sck.segment2
123 group by sck.segment2,paaf.person_id
124 )
125 order by hoi1.organization_id;
126
127 END PAY_SE_TAX_CARD_REQ_PKG;