eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.PAY_SE_TAX_CARD_REQ_PKG

Source


1 PACKAGE PAY_SE_TAX_CARD_REQ_PKG as
2 /* $Header: pysetaxr.pkh 120.1 2005/12/14 03:50:11 vetsrini noship $ */
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 		,'POSTAL_CODE=P',	  RPAD(NVL(substr(hl.Postal_code,1,5),' '),5,' ') /*PONRF*/
33 		,'POST_OFFICE=P',	  RPAD(nvl(hr_general.DECODE_LOOKUP('SE_POSTAL_CODE',HL.POSTAL_CODE),' '),15,' ')				/*ORTAF*/
34 		,'PHONE=P',		  RPAD(NVL(substr(hoi4.org_information3,1,10),' '),10,' ')												 /*TELEF*/
35           		,'TEST_RUN=P',        DECODE(pay_se_tax_card_req_pkg.get_parameter(ppa.legislative_parameters, 'TEST_RUN'),'Y','T',' ')
36 from
37      PAY_PAYROLL_ACTIONS  ppa
38     ,hr_organization_units        ou
39     ,hr_organization_information  hoi1
40     ,hr_organization_information  hoi2
41     ,hr_organization_information  hoi3
42     ,hr_organization_information  hoi4
43     ,HR_LOCATIONS_ALL HL
44 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
45 AND ou.organization_id =  PAY_SE_TAX_CARD_REQ_PKG.get_parameter(ppa.legislative_parameters, 'REQUESTING_ORG')
46 and ou.business_group_id  = ppa.business_group_id
47 AND hoi1.organization_id  = ou.organization_id
48 AND hoi1.ORG_INFORMATION_CONTEXT='CLASS'
49 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
50 AND hoi2.organization_id = ou.organization_id
51 AND hoi2.organization_id =  hoi1.organization_id
52 AND hoi2.ORG_INFORMATION_CONTEXT = 'SE_LEGAL_EMPLOYER_DETAILS'
53 AND hoi3.organization_id = ou.organization_id
54 AND hoi3.ORG_INFORMATION_CONTEXT = 'SE_ORG_CONTACT_DETAILS'
55 AND hoi3.org_information_id = (select min(hoi22.org_information_id)
56                                from
57                                hr_organization_information  hoi11
58                               ,hr_organization_information  hoi22
59                                where
60                                hoi11.organization_id = PAY_SE_TAX_CARD_REQ_PKG.get_parameter(ppa.legislative_parameters, 'REQUESTING_ORG')
61                                AND hoi11.ORG_INFORMATION_CONTEXT='CLASS'
62 	     AND hoi11.org_information1 = 'HR_LEGAL_EMPLOYER'
63                                AND hoi22.organization_id =  hoi11.organization_id
64                                AND hoi22.ORG_INFORMATION_CONTEXT = 'SE_ORG_CONTACT_DETAILS'
65                                AND hoi22.org_information1='PERSON'
66                                 )
67 AND hoi3.org_information1='PERSON'
68 AND hoi4.organization_id = ou.organization_id
69 AND hoi4.ORG_INFORMATION_CONTEXT = 'SE_ORG_CONTACT_DETAILS'
70 AND hoi4.org_information1='PHONE'
71 AND ou.Location_id  = HL.Location_id (+) ;
72 
73 
74 CURSOR CSR_SE_BODY is select  /*+ INDEX(scl, HR_SOFT_CODING_KEYFLEX_PK) */  'ORG_NUM=P', RPAD(hoi2.org_information2,12),
75        'PERSON_NUM=P', RPAD((TO_CHAR(pap.DATE_OF_BIRTH,'CC')-2)||REPLACE(PAP.NATIONAL_IDENTIFIER,'-',''),12)
76 FROM    HR_ORGANIZATION_INFORMATION hoi1
77       , HR_ORGANIZATION_INFORMATION hoi2
78       , HR_ORGANIZATION_INFORMATION hoi3
79       , HR_ORGANIZATION_INFORMATION hoi4
80    , PAY_LEGISLATION_RULES plr
81       , HR_SOFT_CODING_KEYFLEX scl
82       , PER_ALL_PEOPLE_F pap
83       , PER_ALL_ASSIGNMENTS_F paa
84       , PAY_PAYROLL_ACTIONS ppa
85 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
86 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'))
87 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
88 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
89 and hoi2.organization_id =  hoi1.organization_id
90 and hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS'
91 and hoi2.organization_id = hoi3.organization_id
92 and hoi3.ORG_INFORMATION_CONTEXT = 'SE_LOCAL_UNITS'
93 and hoi3.org_information1= hoi4.organization_id
94 and hoi4.ORG_INFORMATION_CONTEXT='CLASS'
95 and hoi4.org_information1='SE_LOCAL_UNIT'
96 and scl.id_flex_num = plr.rule_mode
97 and plr.legislation_code = 'SE'
98 and plr.rule_type = 'S'
99 and scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
100 and to_char(hoi4.organization_id) = scl.segment2
101 and paa.person_id = pap.person_id
102 and ppa.effective_date BETWEEN paa.effective_start_date and paa.effective_end_date
103 and ppa.effective_date BETWEEN pap.effective_start_date and pap.effective_end_date
104 and (paa.assignment_id,scl.segment2)
105 in
106 ( SELECT /*+ INDEX(sck, HR_SOFT_CODING_KEYFLEX_PK) */  min(paaf.assignment_id),sck.segment2
107   FROM
108 	 per_all_people_f papf
109  	,per_all_assignments_f paaf
110   	,HR_SOFT_CODING_KEYFLEX sck
111 	,HR_ORGANIZATION_INFORMATION hroi
112 	WHERE paaf.person_id = papf.person_id
113 	and paaf.BUSINESS_GROUP_ID = ppa.BUSINESS_GROUP_ID
114 	and ppa.effective_date BETWEEN paaf.effective_start_date and paaf.effective_end_date
115 	and ppa.effective_date BETWEEN papf.effective_start_date and papf.effective_end_date
116 	AND sck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
117 	and sck.id_flex_num = plr.rule_mode
118 	and sck.enabled_flag='Y'
119 	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'))
120 	AND hroi.ORG_INFORMATION_CONTEXT = 'SE_LOCAL_UNITS'
121  	AND hroi.org_information1  =  sck.segment2
122 	group by sck.segment2,paaf.person_id
123 		)
124 		order by  hoi1.organization_id;
125 
126 END PAY_SE_TAX_CARD_REQ_PKG;