1 PACKAGE BODY gmf_subled_rep_pkg AS
2 /* $Header: GMFDSURB.pls 120.1.12010000.2 2008/11/11 16:25:38 rpatangy ship $ */
3
4 /* FUNCTION BeforeReportTrigger(P_REFERENCE_NO IN NUMBER,
5 P_LEGAL_ENTITY_ID IN NUMBER,
6 P_LEDGER_ID IN NUMBER,
7 P_COST_TYPE_ID IN NUMBER,
8 P_FISCAL_YEAR IN NUMBER,
9 P_PERIOD IN NUMBER,
10 P_START_DATE IN DATE,
11 P_END_DATE IN DATE,
12 P_ENTITY_CODE IN VARCHAR2,
13 P_EVENT_CLASS IN VARCHAR2,
14 P_EVENT_TYPE IN VARCHAR2) RETURN BOOLEAN IS */
15 FUNCTION BeforeReportTrigger RETURN BOOLEAN IS
16 l_where_clause VARCHAR2(2000);
17 l_event_type_all VARCHAR2(10);
18 BEGIN
19 -- Need to put a log message if reference no and legal entity al null
20
21 l_where_clause := '1=2';
22 /*
23 IF P_REFERENCE_NO IS NOT NULL THEN
24 l_where_clause := ' sr.reference_no = :P_REFERENCE_NO' ;
25 ELSE
26 -- If there is no reference no then user need to pass below parameter values
27 IF P_LEGAL_ENTITY_ID IS NOT NULL THEN
28 l_where_clause := ' sr.legal_entity_id = :P_LEGAL_ENTITY_ID ' ;
29 END IF; */
30 /* IF P_LEDGER_ID IS NOT NULL THEN
31 l_where_clause := l_where_clause ||' AND sr.ledger_id = :P_LEDGER_ID ' ;
32 END IF;
33 IF P_COST_TYPE_ID IS NOT NULL THEN
34 l_where_clause := l_where_clause ||' AND sr.valuation_cost_type_id = P_COST_TYPE_ID ' ;
35 END IF; */
36 /* There is no columns in extract_headers for fiscal_year and period.
37 IF p_fiscal_year IS NOT NULL THEN
38 l_where_clause := l_where_clause ||' AND ' || p_fiscal_year ;
39 END IF;
40 IF p_period IS NOT NULL THEN
41 l_where_clause := l_where_clause ||' AND ' || p_period ;
42 END IF;
43 */
44 -- END IF; -- end for reference_no
45 /*
46 -- Incase user changes the dates for report output
47 IF l_where_clause IS NOT NULL AND P_START_DATE IS NOT NULL THEN
48 l_where_clause := l_where_clause ||' AND sr.transaction_date >= ' || P_START_DATE ;
49 END IF;
50 IF l_where_clause IS NOT NULL AND P_END_DATE IS NOT NULL THEN
51 l_where_clause := l_where_clause ||' AND sr.transaction_date <= ' || P_END_DATE ;
52 END IF;
53 -- If the entity code is null means all report runs for all entity codes
54 IF l_where_clause IS NOT NULL AND P_ENTITY_CODE IS NOT NULL THEN
55 l_where_clause :=l_where_clause ||' AND sr.entity_code = '||P_ENTITY_CODE;
56 END IF;
57 IF l_where_clause IS NOT NULL AND P_EVENT_CLASS IS NOT NULL THEN
58 l_where_clause := l_where_clause ||' AND sr.event_class_code = ' || P_EVENT_CLASS ;
59 END IF;
60 IF l_where_clause IS NOT NULL AND P_EVENT_TYPE IS NOT NULL THEN
61 -- event type passed as all then no need to add condition, query for all event types
62 l_event_type_all := P_EVENT_CLASS||'_ALL';
63 IF P_EVENT_TYPE <> l_event_type_all THEN
64 l_where_clause := l_where_clause ||' AND sr.event_type_code = ' || P_EVENT_TYPE;
65 END IF;
66 END IF;
67 */
68 p_where_clause := l_where_clause;
69 -- RETURN p_where_clause;
70
71 RETURN TRUE;
72 END BeforeReportTrigger;
73
74 -- get Legal entity name
75 FUNCTION get_le_name(p_le_id IN NUMBER) RETURN VARCHAR2 IS
76 l_le_name gmf_legal_entities.legal_entity_name%TYPE :='';
77
78 BEGIN
79 SELECT legal_entity_name INTO l_le_name FROM gmf_legal_entities
80 WHERE legal_entity_id = p_le_id;
81
82 RETURN l_le_name;
83
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 RETURN l_le_name;
87 END get_le_name;
88
89 -- Get Ledger name
90 FUNCTION get_ledger_name(p_led_id IN NUMBER) RETURN VARCHAR2 IS
91 l_ledger_name gl_ledgers.name%TYPE :='';
92 BEGIN
93 SELECT name INTO l_ledger_name FROM gl_ledgers
94 WHERE ledger_id = p_led_id;
95
96 RETURN l_ledger_name;
97
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 RETURN l_ledger_name;
101 END get_ledger_name;
102
103 --get Cost type name
104 FUNCTION get_cost_type(p_ct_id IN NUMBER) RETURN VARCHAR2 IS
105 l_cost_type cm_mthd_mst.cost_mthd_code%TYPE :='';
106 BEGIN
107 SELECT cost_mthd_code INTO l_cost_type FROM cm_mthd_mst
108 WHERE cost_type_id = p_ct_id;
109
110 RETURN l_cost_type;
111
112 EXCEPTION
113 WHEN NO_DATA_FOUND THEN
114 RETURN l_cost_type;
115 END get_cost_type;
116
117 -- get the organization code
118 FUNCTION get_organization_code (p_org_id IN NUMBER, p_ref_no IN NUMBER) RETURN VARCHAR2 IS
119
120 l_org_cd mtl_parameters.organization_code%TYPE :='';
121 BEGIN
122 IF p_org_id IS NOT NULL THEN
123 SELECT organization_code INTO l_org_cd FROM mtl_parameters WHERE organization_id = p_org_id;
124 ELSIF p_ref_no IS NOT NULL THEN
125 SELECT DISTINCT organization_code INTO l_org_cd FROM mtl_parameters mp, gmf_xla_extract_headers eh
126 WHERE mp.organization_id = eh.organization_id AND eh.reference_no = p_ref_no;
127 END IF;
128
129 RETURN l_org_cd;
130
131 EXCEPTION
132 WHEN NO_DATA_FOUND THEN
133 RETURN l_org_cd;
134 WHEN TOO_MANY_ROWS THEN
135 RETURN l_org_cd;
136
137 END get_organization_code;
138
139 -- get Item number and Description
140 FUNCTION get_item_desc (p_item_id IN NUMBER, p_org_id IN NUMBER) RETURN VARCHAR2 IS
141 l_ItemDesc VARCHAR2(500) := '';
142 BEGIN
143 SELECT mif.item_number ||' - '||mif.description INTO l_ItemDesc
144 FROM mtl_item_flexfields mif
145 WHERE mif.inventory_item_id = p_item_id
146 AND mif.organization_id = p_org_id ;
147
148 RETURN l_ItemDesc;
149 EXCEPTION
150 WHEN NO_DATA_FOUND THEN
151 RETURN l_ItemDesc;
152 END get_item_desc;
153
154 -- get Organization name
155 FUNCTION get_org_name (p_org_id IN NUMBER)
156 RETURN VARCHAR2 IS
157 l_Orgname VARCHAR2(300):= '';
158
159 BEGIN
160
161 SELECT name INTO l_Orgname FROM hr_all_organization_units WHERE organization_id = p_org_id;
162
163 RETURN l_Orgname;
164
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 RETURN l_Orgname;
168 END get_org_name;
169
170 -- Get entity Code description
171 FUNCTION get_entity_code_desc(p_entity_cd IN VARCHAR2) RETURN VARCHAR2 IS
172 l_entity_code_desc xla_entity_types_vl.name%TYPE :='';
173 BEGIN
174 SELECT name INTO l_entity_code_desc FROM xla_entity_types_vl WHERE application_id =555 AND entity_code = p_entity_cd;
175
176 RETURN l_entity_code_desc;
177
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 RETURN l_entity_code_desc;
181 END get_entity_code_desc;
182
183 -- Get event class description
184 FUNCTION get_event_class_desc(p_entity_cd IN VARCHAR2, p_event_class_cd IN VARCHAR2) RETURN VARCHAR2 IS
185 l_event_class_desc xla_entity_types_vl.name%TYPE :='';
186 BEGIN
187 SELECT name INTO l_event_class_desc FROM xla_event_classes_vl
188 WHERE application_id =555 AND entity_code = p_entity_cd AND event_class_code = p_event_class_cd;
189
190 RETURN l_event_class_desc;
191
192 EXCEPTION
193 WHEN NO_DATA_FOUND THEN
194 RETURN l_event_class_desc;
195 END get_event_class_desc;
196
197 -- Get Event Type description
198 FUNCTION get_event_type_desc(p_entity_cd IN VARCHAR2, p_event_class_cd IN VARCHAR2,p_event_type_cd IN VARCHAR2 )
199 RETURN VARCHAR2 IS
200 l_event_type_desc xla_entity_types_vl.name%TYPE :='';
201
202 BEGIN
203 SELECT name INTO l_event_type_desc FROM xla_event_types_vl
204 WHERE application_id =555 AND entity_code = p_entity_cd AND event_class_code = p_event_class_cd AND event_type_code = p_event_type_cd;
205
206 RETURN l_event_type_desc;
207 EXCEPTION
208 WHEN NO_DATA_FOUND THEN
209 RETURN l_event_type_desc;
210 END get_event_type_desc;
211
212 FUNCTION get_where_clause RETURN VARCHAR2 IS
213 l_where_clause VARCHAR2(2000);
214 BEGIN
215 RETURN l_where_clause;
216 END get_where_clause;
217
218 END gmf_subled_rep_pkg;
[Home] [Help]