[Home] [Help]
PACKAGE BODY: APPS.PA_PAXRWDOH_XMLP_PKG
Source
1 PACKAGE BODY PA_PAXRWDOH_XMLP_PKG AS
2 /* $Header: PAXRWDOHB.pls 120.0 2008/01/02 11:56:30 krreddy noship $ */
3
4 FUNCTION get_company_name RETURN BOOLEAN IS
5 l_name gl_sets_of_books.name%TYPE;
6 BEGIN
7 SELECT gl.name
8 INTO l_name
9 FROM gl_sets_of_books gl,pa_implementations pi
10 WHERE gl.set_of_books_id = pi.set_of_books_id;
11
12 c_company_name_header := l_name;
13
14 RETURN (TRUE);
15
16 EXCEPTION
17
18 WHEN OTHERS THEN
19 RETURN (FALSE);
20
21 END;
22
23 function BeforeReport return boolean is
24
25 begin
26 declare
27 init_error exception;
28 Org_Name hr_organization_units.name%TYPE;
29 ndf Varchar2(80);
30 begin
31
32 /*srw.user_exit('FND SRWINIT');*/null;
33
34 if START_ORG_ID is not NULL then
35 select substr(name,1,60) into Org_Name from hr_organization_units
36 where organization_id = START_ORG_ID;
37 end if;
38 C_Org_Name := Org_Name;
39 if ( get_company_name <> TRUE ) then
40 raise init_error;
41 end if;
42
43 select meaning into ndf from pa_lookups where
44 lookup_code = 'NO_DATA_FOUND' and
45 lookup_type = 'MESSAGE';
46 c_no_data_found := ndf;
47 end;
48
49
50
51 if P_hierarchy_type = 'PA_REPORTING_ORG' then
52 select meaning into p_select_column
53 From pa_lookups
54 where lookup_code = 'PA_REPORTING_ORG' and lookup_type='ALL_HIERARCHY_CLASS';
55 P_structure_id := 'and pi.organization_structure_id = pos1.organization_structure_id';
56 P_version_id := 'and pi.org_structure_version_id = posv.org_structure_version_id';
57 P_where_clause := 'and p.org_structure_version_id = pi.org_structure_version_id';
58 p_from_clause := 'PER_ORG_STRUCTURE_VERSIONS posv';
59 p_where_clause1 := 'and p.organization_id_parent= org.organization_id';
60 P_Decode_column := 'pi.org_structure_version_id';
61 P_select_column1 := 'pi.org_structure_version_id';
62 P_parent_org_id := 'pi.start_organization_id';
63 p_burden := 'and 1=1';
64 if start_org_id is null then
65 select start_organization_id into start_org_id from pa_implementations;
66 end if;
67
68 elsif P_hierarchy_type = 'PA_EXPENDITURE_ORG' then
69 select meaning into p_select_column
70 From pa_lookups
71 where lookup_code = 'PA_EXPENDITURE_ORG' and lookup_type='ALL_HIERARCHY_CLASS';
72 P_structure_id := 'and pi.exp_org_structure_id = pos1.organization_structure_id';
73 P_version_id := 'and pi.exp_org_structure_version_id = posv.org_structure_version_id';
74 P_where_clause := 'and p.org_structure_version_id = pi.exp_org_structure_version_id';
75 p_from_clause := 'PER_ORG_STRUCTURE_VERSIONS posv';
76 p_where_clause1 := 'and p.organization_id_parent= org.organization_id ';
77 P_Decode_column := 'pi.Exp_org_structure_version_id';
78 P_select_column1 := 'pi.exp_org_structure_version_id';
79 P_parent_org_id := 'pi.exp_start_org_id';
80 p_burden := 'and 1=1';
81 if start_org_id is null then
82 select exp_start_org_id into start_org_id from pa_implementations;
83 end if;
84
85
86 elsif P_hierarchy_type = 'PA_PROJECT_ORG' then
87 select meaning into p_select_column
88 From pa_lookups
89 where lookup_code = 'PA_PROJECT_ORG' and lookup_type='ALL_HIERARCHY_CLASS';
90 P_structure_id := 'and pi.proj_org_structure_id = pos1.organization_structure_id';
91 P_version_id := 'and pi.proj_org_structure_version_id = posv.org_structure_version_id';
92
93 P_where_clause := 'and p.org_structure_version_id = pi.proj_org_structure_version_id';
94 p_from_clause := 'PER_ORG_STRUCTURE_VERSIONS posv';
95 p_where_clause1 := 'and p.organization_id_parent= org.organization_id';
96 P_Decode_column := 'pi.proj_org_structure_version_id';
97 P_select_column1 := 'pi.proj_org_structure_version_id';
98 P_parent_org_id := 'pi.proj_start_org_id';
99 p_burden := 'and 1=1';
100 if start_org_id is null then
101 select proj_start_org_id into start_org_id from pa_implementations;
102 end if;
103
104 elsif p_Hierarchy_Type = 'PA_BURDENING_ORG' then
105 P_where_clause := 'and p.org_structure_version_id =to_number(hr2.org_information2)';
106 p_where_clause1 := 'and pi.business_group_id = hr2.organization_id';
107 P_structure_id := 'and p.org_structure_version_id = posv.org_structure_version_id';
108 P_version_id := 'and pos1.organization_structure_id = posv.organization_structure_id';
109 P_org_hr := 'and p.organization_id_parent= org.organization_id';
110 p_from_clause := 'hr_organization_information hr2,PER_ORG_STRUCTURE_VERSIONS posv';
111 P_Decode_column := 'to_number(hr2.org_information2)';
112 P_select_column1 := 'p.organization_id_parent';
113 p_parent_org_id :='p.organization_id_parent';
114 p_burden:= 'and hr2.org_information_context ' ||'='||''''||'Project Burdening Hierarchy'||'''';
115 select meaning into p_select_column
116 From pa_lookups
117 where lookup_code = 'PA_BURDENING_ORG' and lookup_type='ALL_HIERARCHY_CLASS';
118
119
120 if start_org_id is null then
121
122 select distinct organization_id_parent into start_org_id
123 from per_org_structure_elements a
124 ,pa_implementations b
125 ,hr_organization_information c
126 where organization_id_parent not in
127 ( select d.ORGANIZATION_ID_CHILD from per_org_structure_elements d
128 where d.org_structure_version_id = to_number(c.org_information2)
129 )
130 and a.org_structure_version_id = to_number(c.org_information2)
131 and b.business_group_id = c.organization_id
132 and c.org_information_context = 'Project Burdening Hierarchy' ;
133
134 end if;
135 end if;
136
137 return (TRUE);
138 EXCEPTION when NO_DATA_FOUND then
139 null;
140 return(TRUE);
141 end;
142
143 function AfterReport return boolean is
144 begin
145 /*srw.user_exit('FND SRWEXIT') ;*/null;
146
147 return (TRUE);
148 end;
149
150 --Functions to refer Oracle report placeholders--
151
152 Function C_Company_Name_Header_p return varchar2 is
153 Begin
154 return C_Company_Name_Header;
155 END;
156 Function C_Org_Name_p return varchar2 is
157 Begin
158 return C_Org_Name;
159 END;
160 Function C_NO_DATA_FOUND_p return varchar2 is
161 Begin
162 return C_NO_DATA_FOUND;
163 END;
164 END PA_PAXRWDOH_XMLP_PKG ;
165