[Home] [Help]
PACKAGE BODY: APPS.PER_RI_CREATE_HIER_ELEMENT
Source
1 Package Body per_ri_create_hier_element As
2 /* $Header: perrihierele.pkb 120.1 2006/04/13 03:24:38 pkagrawa noship $ */
3 Function get_line_status(p_view Varchar2,p_dp_batch_line_id Number)
4 Return Varchar2 Is
5
6 Type csr_dp_line_status_type Is Ref Cursor;
7 csr_dp_line_status csr_dp_line_status_type;
8
9 l_status Varchar2(2);
10 l_sql_stmt Varchar2(200);
11 Begin
12
13 l_sql_stmt := 'Select line_status From '||p_view||' Where batch_line_id = :1';
14
15 Open csr_dp_line_status For l_sql_stmt using p_dp_batch_line_id;
16 Fetch csr_dp_line_status Into l_status;
17 Close csr_dp_line_status;
18
19 return l_status;
20
21 End get_line_status;
22
23
24 Procedure insert_batch_lines(P_BATCH_ID in number
25 ,P_DATA_PUMP_BATCH_LINE_ID in number default null
26 ,P_DATA_PUMP_BUSINESS_GRP_NAME in varchar2 default null
27 ,P_USER_SEQUENCE in number default null
28 ,P_LINK_VALUE in number default null
29 ,P_EFFECTIVE_DATE in date
30 ,P_DATE_FROM in date
31 ,P_VIEW_ALL_ORGS in varchar2
32 ,P_END_OF_TIME in date
33 ,P_HR_INSTALLED in varchar2
34 ,P_PA_INSTALLED in varchar2
35 ,P_POS_CONTROL_ENABLED_FLAG in varchar2
36 ,P_WARNING_RAISED in varchar2
37 ,P_PARENT_ORGANIZATION_NAME in varchar2
38 ,P_LANGUAGE_CODE in varchar2
39 ,P_ORG_STR_VERSION_USER_KEY in varchar2
40 ,P_CHILD_ORGANIZATION_NAME in varchar2
41 ,P_SECURITY_PROFILE_NAME in varchar2) as
42
43 l_org_structure_user_key Varchar2(240);
44 l_temp Varchar2(240);
45 l_org_structure_version_id number;
46 l_bg_id number;
47 l_temp_id number;
48
49
50 l_dp_batch_line_id_org_str Number;
51
52 Cursor csr_get_org_str_user_key(c_batch_line_id in Number) Is
53 Select p_org_str_version_user_key
54 From hrdpv_create_hierarchy_element
55 Where batch_line_id = c_batch_line_id;
56
57 Cursor csr_get_org_str_version_id (c_bg_id in number) Is
58 select posv.organization_structure_id
59 from per_organization_structures pos,per_org_structure_versions posv
60 where pos.organization_structure_id = posv.organization_structure_id
61 and (posv.business_group_id +0 = c_bg_id
62 or posv.business_group_id is null)
63 and name = p_org_str_version_user_key;
64
65 Cursor csr_bg_id (c_bg_name in varchar2) Is
66 -- select business_group_id from per_business_groups
67 -- where name = c_bg_name;
68
69 select hr_api.return_business_group_id(c_bg_name) as business_group_id
70 from dual;
71
72
73 Cursor csr_org_str_user_key(c_org_structure_user_key in varchar2 ) Is
74 Select unique_key_id
75 From hr_pump_batch_line_user_keys
76 Where user_key_value = l_org_structure_user_key;
77
78
79
80 begin
81
82 hr_utility.set_location('Entering per_ri_create_hier_element.insert_batch_lines', 10);
83
84 If p_data_pump_batch_line_id Is Not Null Then
85
86 --get batch line ids
87 hr_utility.set_location('Correct Errors Scenario', 20);
88 Open csr_get_org_str_user_key(p_data_pump_batch_line_id);
89 Fetch csr_get_org_str_user_key Into l_org_structure_user_key;
90 Close csr_get_org_str_user_key;
91
92
93 Else
94
95 hr_utility.set_location('Normal Scenario', 20);
96
97 l_org_structure_user_key := p_org_str_version_user_key;
98
99 End If;
100
101 If l_dp_batch_line_id_org_str Is Null Or get_line_status('HRDPV_CREATE_HIERARCHY_ELEMENT',l_dp_batch_line_id_org_str) <> 'C' Then
102
103 Begin
104 Open csr_bg_id(p_data_pump_business_grp_name);
105 Fetch csr_bg_id Into l_bg_id;
106 Close csr_bg_id;
107 Exception
108 When Others Then
109 l_bg_id := Null;
110 End;
111
112 hr_utility.set_location('Business Group id ' || to_char(l_bg_id), 30);
113
114 Open csr_get_org_str_version_id(l_bg_id);
115 Fetch csr_get_org_str_version_id Into l_org_structure_version_id;
116 Close csr_get_org_str_version_id;
117
118 hr_utility.set_location('Org Structure Version id ' || to_char(l_org_structure_version_id) || ' Org Structure Version User Key = ' ||l_org_structure_user_key , 40);
119
120
121 Open csr_org_str_user_key(l_org_structure_user_key);
122 Fetch csr_org_str_user_key Into l_temp_id;
123 IF csr_org_str_user_key%NOTFOUND then
124 hr_utility.set_location('Inserted the User Key '||l_org_structure_user_key , 50);
125 hr_pump_utils.add_user_key(l_org_structure_user_key,l_org_structure_version_id);
126 end if;
127 close csr_org_str_user_key;
128
129 hrdpp_create_hierarchy_element.insert_batch_lines(P_BATCH_ID => p_batch_id
130 ,P_DATA_PUMP_BATCH_LINE_ID => p_data_pump_batch_line_id
131 ,P_DATA_PUMP_BUSINESS_GRP_NAME => p_data_pump_business_grp_name
132 ,P_USER_SEQUENCE => p_user_sequence
133 ,P_LINK_VALUE => p_link_value
134 ,P_EFFECTIVE_DATE => p_effective_date
135 ,P_DATE_FROM => p_date_from
136 ,P_VIEW_ALL_ORGS => p_view_all_orgs
137 ,P_END_OF_TIME => p_end_of_time
138 ,P_HR_INSTALLED => p_hr_installed
139 ,P_PA_INSTALLED => p_pa_installed
140 ,P_POS_CONTROL_ENABLED_FLAG => p_pos_control_enabled_flag
141 ,P_WARNING_RAISED => p_warning_raised
142 ,P_PARENT_ORGANIZATION_NAME => p_parent_organization_name
143 ,P_LANGUAGE_CODE => p_language_code
144 ,P_ORG_STR_VERSION_USER_KEY => l_org_structure_user_key
145 ,P_CHILD_ORGANIZATION_NAME => p_child_organization_name
146 ,P_SECURITY_PROFILE_NAME => p_security_profile_name );
147
148 End If;
149
150 End insert_batch_lines;
151
152
153 End per_ri_create_hier_element;
154