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 BODY: APPS.GHG_ORGANIZATION_MAPPINGS_PKG

Source


1 PACKAGE BODY GHG_ORGANIZATION_MAPPINGS_PKG AS
2 /*$Header: ghgorgmb.pls 120.3 2011/12/30 22:56:27 lvenkatr noship $ */
3 
4 FUNCTION find_flexfield_mapping (x_distribution_line_number NUMBER,
5                                  x_code_combination_id      NUMBER,
6                                  x_chart_of_accounts_id     NUMBER,
7                                  x_gl_date DATE,
8 				 x_calling_mode VARCHAR2 DEFAULT 'ONLINE') RETURN NUMBER IS
9 
10 v_debug_info         VARCHAR2(200);
11 v_map_segment1_flag  VARCHAR2(1);
12 v_map_segment2_flag  VARCHAR2(1);
13 v_map_segment3_flag  VARCHAR2(1);
14 v_map_segment4_flag  VARCHAR2(1);
15 v_map_segment5_flag  VARCHAR2(1);
16 v_map_segment6_flag  VARCHAR2(1);
17 v_map_segment7_flag  VARCHAR2(1);
18 v_map_segment8_flag  VARCHAR2(1);
19 v_map_segment9_flag  VARCHAR2(1);
20 v_map_segment10_flag VARCHAR2(1);
21 v_map_segment11_flag VARCHAR2(1);
22 v_map_segment12_flag VARCHAR2(1);
23 v_map_segment13_flag VARCHAR2(1);
24 v_map_segment14_flag VARCHAR2(1);
25 v_map_segment15_flag VARCHAR2(1);
26 v_map_segment16_flag VARCHAR2(1);
27 v_map_segment17_flag VARCHAR2(1);
28 v_map_segment18_flag VARCHAR2(1);
29 v_map_segment19_flag VARCHAR2(1);
30 v_map_segment20_flag VARCHAR2(1);
31 v_map_segment21_flag VARCHAR2(1);
32 v_map_segment22_flag VARCHAR2(1);
33 v_map_segment23_flag VARCHAR2(1);
34 v_map_segment24_flag VARCHAR2(1);
35 v_map_segment25_flag VARCHAR2(1);
36 v_map_segment26_flag VARCHAR2(1);
37 v_map_segment27_flag VARCHAR2(1);
38 v_map_segment28_flag VARCHAR2(1);
39 v_map_segment29_flag VARCHAR2(1);
40 v_map_segment30_flag VARCHAR2(1);
41 v_sqltext            VARCHAR2(2000) := NULL;
42 v_flexfield          VARCHAR2(240) := NULL;
43 v_ghg_organization_id        NUMBER(15);
44 
45 CURSOR get_mapping_segments (i_coa_id NUMBER) IS
46 SELECT application_column_name
47 FROM   fnd_id_flex_segments_vl
48 WHERE  id_flex_num = i_coa_id
49 AND    id_flex_code = 'GL#'
50 ORDER BY segment_num;
51 
52 BEGIN
53 
54   v_debug_info := 'Retrieving organization account mapping for code_combination_id ' || x_code_combination_id || '(distribution line ' || x_distribution_line_number ||')' || '(Gl Date ' || to_char(x_gl_date, 'DD-MON-YYYY') || ')';
55 
56   SELECT gcc.concatenated_segments
57   INTO   v_flexfield
58   FROM   gl_code_combinations_kfv gcc
59   WHERE  gcc.code_combination_id = x_code_combination_id;
60 
61   SELECT map_segment1_flag,
62          map_segment2_flag,
63          map_segment3_flag,
64          map_segment4_flag,
65          map_segment5_flag,
66          map_segment6_flag,
67          map_segment7_flag,
72          map_segment12_flag,
68          map_segment8_flag,
69          map_segment9_flag,
70          map_segment10_flag,
71          map_segment11_flag,
73          map_segment13_flag,
74          map_segment14_flag,
75          map_segment15_flag,
76          map_segment16_flag,
77          map_segment17_flag,
78          map_segment18_flag,
79          map_segment19_flag,
80          map_segment20_flag,
81          map_segment21_flag,
82          map_segment22_flag,
83          map_segment23_flag,
84          map_segment24_flag,
85          map_segment25_flag,
86          map_segment26_flag,
87          map_segment27_flag,
88          map_segment28_flag,
89          map_segment29_flag,
90          map_segment30_flag
91   INTO   v_map_segment1_flag,
92          v_map_segment2_flag,
93          v_map_segment3_flag,
94          v_map_segment4_flag,
95          v_map_segment5_flag,
96          v_map_segment6_flag,
97          v_map_segment7_flag,
98          v_map_segment8_flag,
99          v_map_segment9_flag,
100          v_map_segment10_flag,
101          v_map_segment11_flag,
102          v_map_segment12_flag,
103          v_map_segment13_flag,
104          v_map_segment14_flag,
105          v_map_segment15_flag,
106          v_map_segment16_flag,
107          v_map_segment17_flag,
108          v_map_segment18_flag,
109          v_map_segment19_flag,
110          v_map_segment20_flag,
111          v_map_segment21_flag,
112          v_map_segment22_flag,
113          v_map_segment23_flag,
114          v_map_segment24_flag,
115          v_map_segment25_flag,
116          v_map_segment26_flag,
117          v_map_segment27_flag,
118          v_map_segment28_flag,
119          v_map_segment29_flag,
120          v_map_segment30_flag
121   FROM   GHG_SEGMENT_MAPPINGS;
122 
123   v_sqltext := 'SELECT xnfam.ghg_organization_id FROM gl_code_combinations gcc, GHG_ORG_ACCOUNT_MAPPINGS_V xnfam ';
124   v_sqltext := v_sqltext || 'WHERE gcc.code_combination_id = ' || x_code_combination_id || ' AND gcc.chart_of_accounts_id = ' || x_chart_of_accounts_id ;
125   v_sqltext := v_sqltext || ' AND ' || 'to_date(''' || to_char(x_gl_date, 'DD-MON-YYYY') || ''', ''DD-MON-YYYY'')  BETWEEN NVL(xnfam.start_date, sysdate - 1) AND NVL(xnfam.end_date, sysdate + 1) ';
126 
127   FOR map_rec IN get_mapping_segments(x_chart_of_accounts_id) LOOP
128 
129     IF map_rec.application_column_name = 'SEGMENT1' AND v_map_segment1_flag = 'Y' THEN
130       v_sqltext := v_sqltext || 'AND gcc.segment1 BETWEEN xnfam.segment1_low AND xnfam.segment1_high ';
131     ELSIF map_rec.application_column_name = 'SEGMENT2' AND v_map_segment2_flag = 'Y' THEN
132       v_sqltext := v_sqltext || 'AND gcc.segment2 BETWEEN xnfam.segment2_low AND xnfam.segment2_high ';
133     ELSIF map_rec.application_column_name = 'SEGMENT3' AND v_map_segment3_flag = 'Y' THEN
134       v_sqltext := v_sqltext || 'AND gcc.segment3 BETWEEN xnfam.segment3_low AND xnfam.segment3_high ';
135     ELSIF map_rec.application_column_name = 'SEGMENT4' AND v_map_segment4_flag = 'Y' THEN
136       v_sqltext := v_sqltext || 'AND gcc.segment4 BETWEEN xnfam.segment4_low AND xnfam.segment4_high ';
137     ELSIF map_rec.application_column_name = 'SEGMENT5' AND v_map_segment5_flag = 'Y' THEN
138       v_sqltext := v_sqltext || 'AND gcc.segment5 BETWEEN xnfam.segment5_low AND xnfam.segment5_high ';
139     ELSIF map_rec.application_column_name = 'SEGMENT6' AND v_map_segment6_flag = 'Y' THEN
140       v_sqltext := v_sqltext || 'AND gcc.segment6 BETWEEN xnfam.segment6_low AND xnfam.segment6_high ';
141     ELSIF map_rec.application_column_name = 'SEGMENT7' AND v_map_segment7_flag = 'Y' THEN
142       v_sqltext := v_sqltext || 'AND gcc.segment7 BETWEEN xnfam.segment7_low AND xnfam.segment7_high ';
143     ELSIF map_rec.application_column_name = 'SEGMENT8' AND v_map_segment8_flag = 'Y' THEN
144       v_sqltext := v_sqltext || 'AND gcc.segment8 BETWEEN xnfam.segment8_low AND xnfam.segment8_high ';
145     ELSIF map_rec.application_column_name = 'SEGMENT9' AND v_map_segment9_flag = 'Y' THEN
146       v_sqltext := v_sqltext || 'AND gcc.segment9 BETWEEN xnfam.segment9_low AND xnfam.segment9_high ';
147     ELSIF map_rec.application_column_name = 'SEGMENT10' AND v_map_segment10_flag = 'Y' THEN
148       v_sqltext := v_sqltext || 'AND gcc.segment10 BETWEEN xnfam.segment10_low AND xnfam.segment10_high ';
149      ELSIF map_rec.application_column_name = 'SEGMENT11' AND v_map_segment11_flag = 'Y' THEN
150       v_sqltext := v_sqltext || 'AND gcc.segment11 BETWEEN xnfam.segment11_low AND xnfam.segment11_high ';
151     ELSIF map_rec.application_column_name = 'SEGMENT12' AND v_map_segment12_flag = 'Y' THEN
152       v_sqltext := v_sqltext || 'AND gcc.segment12 BETWEEN xnfam.segment12_low AND xnfam.segment12_high ';
153     ELSIF map_rec.application_column_name = 'SEGMENT13' AND v_map_segment13_flag = 'Y' THEN
154       v_sqltext := v_sqltext || 'AND gcc.segment13 BETWEEN xnfam.segment13_low AND xnfam.segment13_high ';
155     ELSIF map_rec.application_column_name = 'SEGMENT14' AND v_map_segment14_flag = 'Y' THEN
156       v_sqltext := v_sqltext || 'AND gcc.segment14 BETWEEN xnfam.segment14_low AND xnfam.segment14_high ';
157     ELSIF map_rec.application_column_name = 'SEGMENT15' AND v_map_segment15_flag = 'Y' THEN
158       v_sqltext := v_sqltext || 'AND gcc.segment15 BETWEEN xnfam.segment15_low AND xnfam.segment15_high ';
159     ELSIF map_rec.application_column_name = 'SEGMENT16' AND v_map_segment16_flag = 'Y' THEN
160       v_sqltext := v_sqltext || 'AND gcc.segment16 BETWEEN xnfam.segment16_low AND xnfam.segment16_high ';
161     ELSIF map_rec.application_column_name = 'SEGMENT17' AND v_map_segment17_flag = 'Y' THEN
162       v_sqltext := v_sqltext || 'AND gcc.segment17 BETWEEN xnfam.segment17_low AND xnfam.segment17_high ';
166       v_sqltext := v_sqltext || 'AND gcc.segment19 BETWEEN xnfam.segment19_low AND xnfam.segment19_high ';
163     ELSIF map_rec.application_column_name = 'SEGMENT18' AND v_map_segment18_flag = 'Y' THEN
164       v_sqltext := v_sqltext || 'AND gcc.segment18 BETWEEN xnfam.segment18_low AND xnfam.segment18_high ';
165     ELSIF map_rec.application_column_name = 'SEGMENT19' AND v_map_segment19_flag = 'Y' THEN
167     ELSIF map_rec.application_column_name = 'SEGMENT20' AND v_map_segment20_flag = 'Y' THEN
168       v_sqltext := v_sqltext || 'AND gcc.segment20 BETWEEN xnfam.segment20_low AND xnfam.segment20_high ';
169     ELSIF map_rec.application_column_name = 'SEGMENT21' AND v_map_segment21_flag = 'Y' THEN
170       v_sqltext := v_sqltext || 'AND gcc.segment21 BETWEEN xnfam.segment21_low AND xnfam.segment21_high ';
171     ELSIF map_rec.application_column_name = 'SEGMENT22' AND v_map_segment22_flag = 'Y' THEN
172       v_sqltext := v_sqltext || 'AND gcc.segment22 BETWEEN xnfam.segment22_low AND xnfam.segment22_high ';
173     ELSIF map_rec.application_column_name = 'SEGMENT23' AND v_map_segment23_flag = 'Y' THEN
174       v_sqltext := v_sqltext || 'AND gcc.segment23 BETWEEN xnfam.segment23_low AND xnfam.segment23_high ';
175     ELSIF map_rec.application_column_name = 'SEGMENT24' AND v_map_segment24_flag = 'Y' THEN
176       v_sqltext := v_sqltext || 'AND gcc.segment24 BETWEEN xnfam.segment24_low AND xnfam.segment24_high ';
177     ELSIF map_rec.application_column_name = 'SEGMENT25' AND v_map_segment25_flag = 'Y' THEN
178       v_sqltext := v_sqltext || 'AND gcc.segment25 BETWEEN xnfam.segment25_low AND xnfam.segment25_high ';
179     ELSIF map_rec.application_column_name = 'SEGMENT26' AND v_map_segment26_flag = 'Y' THEN
180       v_sqltext := v_sqltext || 'AND gcc.segment26 BETWEEN xnfam.segment26_low AND xnfam.segment26_high ';
181     ELSIF map_rec.application_column_name = 'SEGMENT27' AND v_map_segment27_flag = 'Y' THEN
182       v_sqltext := v_sqltext || 'AND gcc.segment27 BETWEEN xnfam.segment27_low AND xnfam.segment27_high ';
183     ELSIF map_rec.application_column_name = 'SEGMENT28' AND v_map_segment28_flag = 'Y' THEN
184       v_sqltext := v_sqltext || 'AND gcc.segment28 BETWEEN xnfam.segment28_low AND xnfam.segment28_high ';
185     ELSIF map_rec.application_column_name = 'SEGMENT29' AND v_map_segment29_flag = 'Y' THEN
186       v_sqltext := v_sqltext || 'AND gcc.segment29 BETWEEN xnfam.segment29_low AND xnfam.segment29_high ';
187     ELSIF map_rec.application_column_name = 'SEGMENT30' AND v_map_segment30_flag = 'Y' THEN
188       v_sqltext := v_sqltext || 'AND gcc.segment30 BETWEEN xnfam.segment30_low AND xnfam.segment30_high ';
189     END IF;
190 
191   END LOOP;
192 
193   EXECUTE IMMEDIATE v_sqltext INTO v_ghg_organization_id;
194 
195   RETURN v_ghg_organization_id;
196 
197   EXCEPTION
198      WHEN NO_DATA_FOUND THEN
199        IF x_calling_mode = 'BATCH' THEN
200          RETURN -1;
201        ELSE
202          FND_MESSAGE.SET_NAME('GHG', 'GHG_CANNOT_FIND_FLEX_MAPPING');
203 
204          FND_MESSAGE.SET_TOKEN('DETAILS', v_flexfield);
205          FND_MESSAGE.SET_TOKEN('DIST_LINE_NUM', x_distribution_line_number);
206          APP_EXCEPTION.RAISE_EXCEPTION;
207       END IF;
208      WHEN OTHERS THEN
209        IF x_calling_mode = 'BATCH' THEN
210          RETURN -1;
211        ELSE
212          IF (SQLCODE <> -20001) THEN
213            FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
214            FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
215            FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
216          END IF;
217          APP_EXCEPTION.RAISE_EXCEPTION;
218        END IF;
219 
220 END;
221 
222 FUNCTION find_project_mapping (x_distribution_line_number NUMBER,
223                                x_project_number           VARCHAR2,
224                                x_task_number              VARCHAR2,
225 			       x_gl_date DATE) RETURN NUMBER IS
226 
227 v_debug_info  VARCHAR2(100);
228 v_ghg_organization_id NUMBER(15);
229 
230 BEGIN
231 
232   v_debug_info := 'Retrieving organization project mapping for project/task ' || x_project_number || '/' || x_task_number || '(distribution line ' || x_distribution_line_number ||')';
233 
234   SELECT ghg_organization_id
235   INTO   v_ghg_organization_id
236   FROM   GHG_ORG_PROJECT_MAPPINGS_V xnpm
237   WHERE  x_project_number BETWEEN xnpm.from_project_number AND xnpm.to_project_number
238   AND    x_task_number BETWEEN xnpm.from_task_number AND xnpm.to_task_number
239   AND    x_gl_date BETWEEN NVL(xnpm.start_date, sysdate - 1)
240                  AND     NVL(xnpm.end_date, sysdate + 1);
241 
242   RETURN v_ghg_organization_id;
243 
244   EXCEPTION
245      WHEN NO_DATA_FOUND THEN
246        FND_MESSAGE.SET_NAME('GHG', 'GHG_CANNOT_FIND_PRJ_MAPPING');
247 
248        FND_MESSAGE.SET_TOKEN('DETAILS', x_project_number || '/' || x_task_number);
249        FND_MESSAGE.SET_TOKEN('DIST_LINE_NUM', x_distribution_line_number);
250        APP_EXCEPTION.RAISE_EXCEPTION;
251      WHEN OTHERS THEN
252          IF (SQLCODE <> -20001) THEN
253            FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
254            FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
255            FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
256          END IF;
257        APP_EXCEPTION.RAISE_EXCEPTION;
258 
259 END;
260 
261 END GHG_ORGANIZATION_MAPPINGS_PKG;