PACKAGE: APPS.PAY_DK_TAX_CARD_REQUEST_PKG

Source

1 PACKAGE PAY_DK_TAX_CARD_REQUEST_PKG AUTHID CURRENT_USER as
2 /* $Header: pydktcrq.pkh 120.0 2005/05/29 04:21:34 appldev noship $ */
3 
4 level_cnt NUMBER;
5 
6 
7 PROCEDURE range_cursor(
8                 p_payroll_action_id     IN  NUMBER,
9                 p_sqlstr                OUT NOCOPY VARCHAR2);
10 
11 
12 PROCEDURE assignment_action_code(
13                           pactid    IN NUMBER,
14                           stperson  IN NUMBER,
15                           endperson IN NUMBER,
16                           chunk     IN NUMBER);
17 
18 FUNCTION get_parameter(
19                  p_parameter_string  IN VARCHAR2
20                 ,p_token             IN VARCHAR2
21                 ,p_segment_number    IN NUMBER DEFAULT NULL )RETURN VARCHAR2;
22 
23 
24 
25 CURSOR get_org_details IS
26 SELECT 'ORGANIZATION_NAME=P',hou1.name
27       ,'TRANSFER_CVRNO=P',hoi2.org_information1
28       ,'ORGANIZATION_ADDR=P', loc.ADDRESS_LINE_1||' '||loc.ADDRESS_LINE_2||' '||loc.ADDRESS_LINE_3
29 FROM    HR_ORGANIZATION_UNITS hou1
30       , HR_ORGANIZATION_INFORMATION hoi1
31       , HR_ORGANIZATION_INFORMATION hoi2
32       , HR_LOCATIONS loc
33       , PAY_PAYROLL_ACTIONS ppa
34 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
35 and hou1.business_group_id = ppa.BUSINESS_GROUP_ID
36 and hou1.organization_id = nvl(PAY_DK_TAX_CARD_REQUEST_PKG.get_parameter(legislative_parameters,'LEGAL_EMPLOYER_NAME',null),hou1.organization_id)
37 and hou1.location_id = loc.LOCATION_ID(+)
38 and hoi1.organization_id = hou1.organization_id
39 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
40 and hoi1.org_information1 = nvl2(PAY_DK_TAX_CARD_REQUEST_PKG.get_parameter(legislative_parameters,'LEGAL_EMPLOYER_NAME',null),'HR_LEGAL_EMPLOYER','DK_SERVICE_PROVIDER')
41 and hoi1.ORG_INFORMATION2 ='Y'
42 and hoi2.ORG_INFORMATION_CONTEXT= nvl2(PAY_DK_TAX_CARD_REQUEST_PKG.get_parameter(legislative_parameters,'LEGAL_EMPLOYER_NAME',null)
43                                       ,'DK_LEGAL_ENTITY_DETAILS','DK_SERVICE_PROVIDER_DETAILS')
44 and hoi2.organization_id =  hoi1.organization_id
45 and ppa.EFFECTIVE_DATE BETWEEN hou1.DATE_FROM and nvl(hou1.DATE_TO, ppa.EFFECTIVE_DATE);
46 
47 
48 CURSOR get_employee_details IS
49 SELECT distinct('CPR_NO=P'), to_char(PAP.NATIONAL_IDENTIFIER), 'EMP_NO=P',to_char(PAP.EMPLOYEE_NUMBER)	, 'LEGAL_EMPLR_CVRNO=P', hoi2.ORG_INFORMATION1
50 FROM per_all_assignments_f PAA
51    , per_all_people_f PAP
52    , hr_soft_coding_keyflex SCL
53    , pay_payroll_actions PPA
54    , HR_ORGANIZATION_UNITS hou
55    , HR_ORGANIZATION_INFORMATION hoi1
56    , HR_ORGANIZATION_INFORMATION hoi2
57 WHERE PPA.payroll_action_id =  pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
58 and PAA.business_group_id = PPA.business_group_id
59 AND PAP.per_information_category ='DK'
60 AND PAA.PERSON_ID = PAP.PERSON_ID
61 AND PAA.soft_coding_keyflex_id = SCL.soft_coding_keyflex_id
62 AND PPA.EFFECTIVE_DATE  between  PAA.EFFECTIVE_START_DATE  and PAA.EFFECTIVE_END_DATE
63 AND PPA.EFFECTIVE_DATE  between  PAP.EFFECTIVE_START_DATE  and PAP.EFFECTIVE_END_DATE
64 and hou.business_group_id =  PPA.business_group_id
65 and hoi1.organization_id = hou.organization_id
66 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
67 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
68 and hoi1.ORG_INFORMATION2 = 'Y'
69 and hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS'
70 and hoi2.organization_id =  hoi1.organization_id
71 and nvl(hoi2.org_information1,0)= nvl2(PAY_DK_TAX_CARD_REQUEST_PKG.get_parameter(legislative_parameters,'LEGAL_EMPLOYER_NAME',null),pay_magtape_generic.get_parameter_value('TRANSFER_CVRNO'),nvl(hoi2.org_information1,0) )
72 and ppa.EFFECTIVE_DATE BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, ppa.EFFECTIVE_DATE)
73 AND SCL.ENABLED_FLAG = 'Y'
74 AND SCL.SEGMENT1 =to_char(hou.ORGANIZATION_ID);
75 
76 END PAY_DK_TAX_CARD_REQUEST_PKG;