[Home] [Help]
PACKAGE BODY: APPS.GCS_INTERCO_DYNAMIC_PKG
Source
1 PACKAGE BODY GCS_INTERCO_DYNAMIC_PKG AS
2 /* $Header $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7 g_pkg_name VARCHAR2(100) := 'gcs.plsql.GCS_INTERCO_DYNAMIC_PKG';
8 g_fnd_user_id NUMBER := fnd_global.user_id;
9 g_fnd_login_id NUMBER := fnd_global.login_id;
10 g_no_rows NUMBER :=0;
11 g_intercompany_org_code VARCHAR2(30) := 'DIFFERENT_ORG' ;
12 g_specific_intercompany_id NUMBER:= 0;
13 g_cons_run_name VARCHAR2(80);
14 gbl_period_end_date DATE;
15 --
16 -- PUBLIC FUNCTIONS
17 --
18 FUNCTION INSR_INTERCO_LINES (p_hierarchy_id IN NUMBER,
19 p_cal_period_id IN NUMBER,
20 p_entity_id IN NUMBER,
21 p_match_rule_code VARCHAR2,
22 p_balance_type VARCHAR2,
23 p_elim_mode IN VARCHAR2,
24 P_Currency_code IN VARCHAR2,
25 p_dataset_code IN NUMBER,
26 p_lob_dim_col_name IN VARCHAR2,
27 p_cons_run_name IN VARCHAR2,
28 p_period_end_date IN DATE,
29 p_fem_ledger_id IN NUMBER)
30 RETURN BOOLEAN IS
31
32 l_api_name VARCHAR2(30) := 'INSR_INTERCO_LINES';
33
34 -- Insert all eligible elimination lines from GCS_INTERCO_ELM_TRX
35 -- GCS_ENTRY-LINES.
36 BEGIN
37
38 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
39 fnd_log.STRING (fnd_log.level_procedure,
40 g_pkg_name || '.' || l_api_name,
41 gcs_utility_pkg.g_module_enter
42 || ' '
43 || l_api_name
44 || '() '
45 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
46 );
47 END IF;
48
49
50
51 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
52 fnd_log.STRING (fnd_log.level_procedure,
53 g_pkg_name || '.' || l_api_name,
54 ' Arguments passed to Insr_Interco_Lines() '
55 ||' Hierarchy_Id: '||p_hierarchy_id
56 ||' Cal_Period_Id: '||p_cal_period_id
57 ||' Entity_Id: '||p_entity_id
58 ||' Match Rule Code: '||p_match_rule_code
59 ||' Balance_Type: '||p_balance_type
60 ||' Elim_Mode: '||p_elim_mode
61 ||' Currency_Code: '||p_currency_code
62 ||' Dataset Code:'||p_dataset_code
63 ||' LOB dim column name: '||p_lob_dim_col_name
64 ||' Consolidation Run name:'||p_cons_run_name
65 ||'Period end date: '||p_period_end_date
66 ||'Fem Ledger Id: '||p_fem_ledger_id);
67
68 END IF;
69
70 g_cons_run_name := p_cons_run_name;
71 gbl_period_end_date := p_period_end_date;
72 IF (P_ELIM_MODE = 'IE') THEN
73 IF (p_match_rule_code = 'COMPANY') THEN /* In Intercompany option */
74 g_no_rows := 0;
75 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
76 fnd_log.STRING (fnd_log.level_procedure,
77 g_pkg_name || '.' || l_api_name,
78 'Intercompany- Inserting entry lines'
79 || ' into GCS_ENTRY_LINES_GT'
80 || ' after matching by company-Receivables side'
81 );
82 END IF;
83
84
85 INSERT INTO GCS_ENTRY_LINES_GT
86 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
87 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
88 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
89 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
90 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
91 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
92 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
93 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
94 , DESCRIPTION, YTD_BALANCE_E, RECEIVABLES_ORG_ID,
95 PAYABLES_ORG_ID )
96 SELECT /*+ ORDERED FULL(GIHG) INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
97 INDEX(GIM GCS_INTERCO_MEMBERS_U1) USE_NL(GIET GIM)
98 INDEX(GCR GCS_CONS_RELATIONSHIPS_N1)
99 INDEX (FB FEM_BALANCES_P)
100 USE_NL(GCR FB)*/
101 gihg.entry_id, giet.company_cost_center_org_id
102 , giet.line_item_id
103 , giet.intercompany_id,
104 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
105 SUM(NVL(fb.ytd_debit_balance_e,0))
106 , SUM(NVL(fb.ytd_credit_balance_e,0))
107 , Max(gihg.rule_id)
108 , (SUM(NVL(fb.ytd_credit_balance_e,0))
109 - SUM(NVL(fb.ytd_debit_balance_e,0))),
110 DECODE(MAX(gim.line_item_group), 1,
111 giet.company_cost_center_org_id,
112 giet.Intercompany_id),
113 DECODE(MAX(gim.line_item_group), 2,
114 giet.company_cost_center_org_id,
115 giet.Intercompany_id)
116 FROM GCS_INTERCO_HDR_GT gihg,
117 GCS_INTERCO_ELM_TRX giet,
118 GCS_INTERCO_MEMBERS gim,
119 GCS_CONS_RELATIONSHIPS gcr,
120 FEM_BALANCES fb
121 WHERE giet.cal_period_id = p_cal_period_id
122 AND giet.hierarchy_id = p_hierarchy_id
123 AND gihg.currency_code IN (p_currency_code,'STAT')
124 AND giet.line_item_id = gim.line_item_id
125 AND (giet.src_entity_id = gihg.source_entity_id
126 AND giet.target_entity_id = gihg.target_entity_id)
127 AND gim.rule_id = gihg.rule_id
128 AND gim.line_item_group = 1
129 AND gcr.hierarchy_id = p_hierarchy_id
130 AND gcr.parent_entity_id = p_entity_id
131 AND gcr.actual_ownership_flag ='Y'
132 AND gcr.dominant_parent_flag = 'Y'
133 AND (gbl_period_end_date
134 BETWEEN NVL(start_date,TO_DATE('01/01/1950', 'MM/DD/YYYY'))
135 AND NVL(END_DATE, TO_DATE('12/31/9999', 'MM/DD/YYYY')))
136 AND gcr.child_entity_id = fb.entity_id
137 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
138 AND giet.intercompany_id = fb.intercompany_id
139 AND giet.line_item_id = fb.line_item_id
140 AND fb.currency_code = gihg.currency_code
141 AND fb.cal_period_id = giet.cal_period_id
142 AND fb.dataset_code = p_dataset_code
143 AND fb.ledger_id = P_fem_ledger_id
144 AND fb.source_system_code = 70
145
146 GROUP BY gihg.entry_id, giet.company_cost_center_org_id,
147 giet.intercompany_id,
148
149 giet.line_item_id;
150 g_no_rows := NVL(SQL%ROWCOUNT,0);
151
152 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
153 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
154 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
155 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_LINES_GT');
156
157 FND_LOG.String (fnd_log.level_procedure,
158 g_pkg_name || '.' || l_api_name,
159 'SHRD0117: '||FND_MESSAGE.get);
160 END IF;
161
162 --****************************----
163 g_no_rows := 0;
164 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
165 fnd_log.STRING (fnd_log.level_procedure,
166 g_pkg_name || '.' || l_api_name,
167 'Intercompany- Inserting entry lines'
168 || ' into GCS_ENTRY_LINES_GT'
169 || ' after matching by company - Payabales side'
170 );
171 END IF;
172
173 INSERT INTO GCS_ENTRY_LINES_GT
174 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
175 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
176 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
177 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
178 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
179 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
180 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
181 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
182 , DESCRIPTION, YTD_BALANCE_E, RECEIVABLES_ORG_ID,
183 PAYABLES_ORG_ID )
184 SELECT /*+ ORDERED FULL(GIHG) INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
185 INDEX(GIM GCS_INTERCO_MEMBERS_U1) USE_NL(GIET GIM)
186 INDEX(GCR GCS_CONS_RELATIONSHIPS_N1)
187 INDEX (FB FEM_BALANCES_P)
188 USE_NL(GCR FB)*/
189 gihg.entry_id, giet.company_cost_center_org_id
190 , giet.line_item_id
191 , giet.intercompany_id,
192 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
193 SUM(NVL(fb.ytd_debit_balance_e,0))
194 , SUM(NVL(fb.ytd_credit_balance_e,0))
195 , MAX(gihg.rule_id)
196 , (SUM(NVL(fb.ytd_credit_balance_e,0))
197 - SUM(NVL(fb.ytd_debit_balance_e,0))),
198 DECODE(MAX(gim.line_item_group), 1,
199 giet.company_cost_center_org_id,
200 giet.Intercompany_id),
201 DECODE(MAX(gim.line_item_group), 2,
202 giet.company_cost_center_org_id,
203 giet.Intercompany_id)
204 FROM GCS_INTERCO_HDR_GT gihg,
205 GCS_INTERCO_ELM_TRX giet,
206 GCS_INTERCO_MEMBERS gim,
207 GCS_CONS_RELATIONSHIPS gcr,
208 FEM_BALANCES fb
209 WHERE giet.cal_period_id = p_cal_period_id
210 AND giet.hierarchy_id = p_hierarchy_id
211 AND gihg.currency_code IN (p_currency_code,'STAT')
212 AND giet.line_item_id = gim.line_item_id
213 AND (giet.src_entity_id = gihg.target_entity_id
214 AND giet.target_entity_id = gihg.source_entity_id )
215 AND gim.rule_id = gihg.rule_id
216 AND gim.line_item_group = 2
217 AND gcr.hierarchy_id = p_hierarchy_id
218 AND gcr.parent_entity_id = p_entity_id
219 AND gcr.actual_ownership_flag ='Y'
220 AND gcr.dominant_parent_flag = 'Y'
221 AND (gbl_period_end_date
222 BETWEEN NVL(start_date,TO_DATE('01/01/1950', 'MM/DD/YYYY'))
223 AND NVL(END_DATE, TO_DATE('12/31/9999', 'MM/DD/YYYY')))
224 AND gcr.child_entity_id = fb.entity_id
225 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
226 AND giet.intercompany_id = fb.intercompany_id
227 AND giet.line_item_id = fb.line_item_id
228 AND fb.currency_code = gihg.currency_code
229 AND fb.cal_period_id = giet.cal_period_id
230 AND fb.dataset_code = p_dataset_code
231 AND fb.ledger_id = P_fem_ledger_id
232 AND fb.source_system_code = 70
233
234 GROUP BY gihg.entry_id, giet.company_cost_center_org_id,
235 giet.intercompany_id,
236
237 giet.line_item_id;
238 g_no_rows := NVL(SQL%ROWCOUNT,0);
239
240 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
241 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
242 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
243 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_LINES_GT');
244
245 FND_LOG.String (fnd_log.level_procedure,
246 g_pkg_name || '.' || l_api_name,
247 'SHRD0117: '||FND_MESSAGE.get);
248 END IF;
249
250
251
252
253 ELSIF (p_match_rule_code = 'ORGANIZATION') THEN
254 --In Intercompany option
255 g_no_rows := 0;
256 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
257 fnd_log.STRING (fnd_log.level_procedure,
258 g_pkg_name || '.' || l_api_name,
259 'Intercompany- Inserting entry lines'
260 || ' into GCS_ENTRY_LINES_GT'
261 || ' after matching by Org-Receivables side'
262 );
263 END IF;
264
265 INSERT INTO GCS_ENTRY_LINES_GT
266 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
267 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
268 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
269 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
270 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
271 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
272 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
273 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
274 , DESCRIPTION ,YTD_BALANCE_E, RECEIVABLES_ORG_ID,
275 PAYABLES_ORG_ID)
276 SELECT /*+ ORDERED FULL(GIHG) INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
277 INDEX(GIM GCS_INTERCO_MEMBERS_U1) USE_NL(GIET GIM)
278 INDEX(GCR GCS_CONS_RELATIONSHIPS_N1)
279 INDEX (FB FEM_BALANCES_P)
280 USE_NL(GCR FB)*/
281 gihg.entry_id, giet.company_cost_center_org_id
282 , giet.line_item_id
283 , giet.intercompany_id,
284 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
285 SUM(NVL(fb.ytd_debit_balance_e,0))
286 , SUM(NVL(fb.ytd_credit_balance_e,0))
287 , MAX(gihg.rule_id)
288 , (SUM(NVL(fb.ytd_credit_balance_e,0))
289 - SUM(NVL(fb.ytd_debit_balance_e,0))),
290 DECODE(MAX(gim.line_item_group), 1,
291 giet.company_cost_center_org_id,
292 giet.Intercompany_id),
293 DECODE(MAX(gim.line_item_group), 2,
294 giet.company_cost_center_org_id,
295 giet.Intercompany_id)
296 FROM GCS_INTERCO_HDR_GT gihg,
297 GCS_INTERCO_ELM_TRX giet,
298 GCS_INTERCO_MEMBERS gim,
299 GCS_CONS_RELATIONSHIPS gcr,
300 FEM_BALANCES fb
301 WHERE giet.cal_period_id = p_cal_period_id
302 AND giet.hierarchy_id = p_hierarchy_id
303 AND gihg.currency_code IN (p_currency_code,'STAT')
304 AND giet.line_item_id = gim.line_item_id
305 AND (giet.src_entity_id =
306 gihg.source_entity_id
307 AND giet.target_entity_id =
308 gihg.target_entity_id)
309 AND gim.rule_id = gihg.rule_id
310 AND gim.line_item_group = 1
311 AND gcr.hierarchy_id = p_hierarchy_id
312 AND gcr.parent_entity_id = p_entity_id
313 AND gcr.actual_ownership_flag ='Y'
314 AND gcr.dominant_parent_flag = 'Y'
315 AND (gbl_period_end_date
316 BETWEEN NVL(start_date,TO_DATE('01/01/1950', 'MM/DD/YYYY'))
317 AND NVL(END_DATE, TO_DATE('12/31/9999', 'MM/DD/YYYY')))
318 AND gcr.child_entity_id = fb.entity_id
319 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
320 AND giet.intercompany_id = fb.intercompany_id
321 AND giet.line_item_id = fb.line_item_id
322 AND fb.currency_code = gihg.currency_code
323 AND fb.cal_period_id = giet.cal_period_id
324 AND fb.dataset_code = p_dataset_code
325 AND fb.ledger_id = P_fem_ledger_id
326 AND fb.source_system_code = 70
327
328 GROUP BY gihg.entry_id, giet.company_cost_center_org_id,
329 giet.intercompany_id,
330
331 giet.line_item_id;
332
333 g_no_rows := NVL(SQL%ROWCOUNT,0);
334
335 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
336 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
337 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
338 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_LINES_GT');
339
340 FND_LOG.String (fnd_log.level_procedure,
341 g_pkg_name || '.' || l_api_name,
342 'SHRD0117: '||FND_MESSAGE.get);
343 END IF;
344
345
346 g_no_rows := 0;
347 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
348 fnd_log.STRING (fnd_log.level_procedure,
349 g_pkg_name || '.' || l_api_name,
350 'Intercompany- Inserting entry lines'
354 END IF;
351 || ' into GCS_ENTRY_LINES'
352 || ' after matching by Org-Payables side'
353 );
355
356 INSERT INTO GCS_ENTRY_LINES_GT
357 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
358 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
359 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
360 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
361 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
362 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
363 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
364 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
365 , DESCRIPTION ,YTD_BALANCE_E, RECEIVABLES_ORG_ID,
366 PAYABLES_ORG_ID)
367 SELECT /*+ ORDERED FULL(GIHG) INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
368 INDEX(GIM GCS_INTERCO_MEMBERS_U1) USE_NL(GIET GIM)
369 INDEX(GCR GCS_CONS_RELATIONSHIPS_N1)
370 INDEX (FB FEM_BALANCES_P)
371 USE_NL(GCR FB)*/
372 gihg.entry_id, giet.company_cost_center_org_id
373 , giet.line_item_id
374 , giet.intercompany_id,
375 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
376 SUM(NVL(fb.ytd_debit_balance_e,0))
377 , SUM(NVL(fb.ytd_credit_balance_e,0))
378 , MAX(gihg.rule_id)
379 , (SUM(NVL(fb.ytd_credit_balance_e,0))
380 - SUM(NVL(fb.ytd_debit_balance_e,0))),
381 DECODE(MAX(gim.line_item_group), 1,
382 giet.company_cost_center_org_id,
383 giet.Intercompany_id),
384 DECODE(MAX(gim.line_item_group), 2,
385 giet.company_cost_center_org_id,
386 giet.Intercompany_id)
387 FROM GCS_INTERCO_HDR_GT gihg,
388 GCS_INTERCO_ELM_TRX giet,
389 GCS_INTERCO_MEMBERS gim,
390 GCS_CONS_RELATIONSHIPS gcr,
391 FEM_BALANCES fb
392 WHERE giet.cal_period_id = p_cal_period_id
393 AND giet.hierarchy_id = p_hierarchy_id
394 AND gihg.currency_code IN (p_currency_code,'STAT')
395 AND giet.line_item_id = gim.line_item_id
396 AND (giet.src_entity_id =
397 gihg.target_entity_id
398 AND giet.target_entity_id =
399 gihg.source_entity_id)
400 AND gim.rule_id = gihg.rule_id
401 AND gim.line_item_group = 2
402 AND gcr.hierarchy_id = p_hierarchy_id
403 AND gcr.parent_entity_id = p_entity_id
404 AND gcr.actual_ownership_flag ='Y'
405 AND gcr.dominant_parent_flag = 'Y'
406 AND (gbl_period_end_date
407 BETWEEN NVL(start_date,TO_DATE('01/01/1950', 'MM/DD/YYYY'))
408 AND NVL(END_DATE, TO_DATE('12/31/9999', 'MM/DD/YYYY')))
409 AND gcr.child_entity_id = fb.entity_id
410 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
411 AND giet.intercompany_id = fb.intercompany_id
412 AND giet.line_item_id = fb.line_item_id
413 AND fb.currency_code = gihg.currency_code
414 AND fb.cal_period_id = giet.cal_period_id
415 AND fb.dataset_code = p_dataset_code
416 AND fb.ledger_id = P_fem_ledger_id
417 AND fb.source_system_code = 70
418
419 GROUP BY gihg.entry_id, giet.company_cost_center_org_id,
420 giet.intercompany_id,
421
422 giet.line_item_id;
423
424 g_no_rows := NVL(SQL%ROWCOUNT,0);
425
426 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
427 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
428 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
429 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_LINES_GT');
430
431 FND_LOG.String (fnd_log.level_procedure,
432 g_pkg_name || '.' || l_api_name,
433 'SHRD0117: '||FND_MESSAGE.get);
434 END IF;
435 END IF; -- End if for match by
436
437 ELSIF (P_ELIM_MODE = 'IA') THEN
438 IF (p_match_rule_code = 'COMPANY') THEN --In Intracompany option
439 g_no_rows := 0;
440 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
441 fnd_log.STRING (fnd_log.level_procedure,
442 g_pkg_name || '.' || l_api_name,
443 'Intracompany- Inserting entry lines'
444 || ' into GCS_ENTRY_LINES_GT'
445 || ' after matching by company'
446 );
447 END IF;
448
449 INSERT INTO GCS_ENTRY_LINES_GT
450 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
451 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
452 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
453 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
454 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
455 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
456 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
457 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
458 , DESCRIPTION, YTD_BALANCE_E, RECEIVABLES_ORG_ID,
459 PAYABLES_ORG_ID)
460 SELECT /*+ ORDERED FULL(GIHG)
461 INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
462 INDEX(GIM GCS_INTERCO_MEMBERS_U1)
463 INDEX (FB FEM_BALANCES_P)
464 USE_NL(GIET FB)*/
465 gihg.entry_id, giet.company_cost_center_org_id
466 , giet.line_item_id
470 , SUM(fb.ytd_credit_balance_e)
467 , giet.intercompany_id,
468 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
469 SUM(fb.ytd_debit_balance_e)
471 , MAX(gihg.rule_id)
472 , (SUM(NVL(fb.ytd_credit_balance_e,0))
473 - SUM(NVL(fb.ytd_debit_balance_e,0))),
474 DECODE(MAX(gim.line_item_group), 1,
475 giet.company_cost_center_org_id,
476 giet.Intercompany_id),
477 DECODE(MAX(gim.line_item_group), 2,
478 giet.company_cost_center_org_id,
479 giet.Intercompany_id)
480 FROM GCS_INTERCO_HDR_GT gihg,
481 GCS_INTERCO_ELM_TRX giet,
482 GCS_INTERCO_MEMBERS gim,
483 FEM_BALANCES fb
484 WHERE giet.cal_period_id = p_cal_period_id
485 AND giet.hierarchy_id = p_hierarchy_id
486 AND gihg.currency_code IN (p_currency_code,'STAT')
487 AND giet.line_item_id = gim.line_item_id
488 AND giet.src_entity_id = giet.target_entity_id
489 AND giet.src_entity_id = gihg.source_entity_id
490 AND giet.target_entity_id = gihg.target_entity_id
491 AND gim.rule_id = gihg.rule_id
492 AND fb.entity_id = p_entity_id
493 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
494 AND giet.intercompany_id = fb.intercompany_id
495 AND giet.line_item_id = fb.line_item_id
496 AND fb.currency_code = gihg.currency_code
497 AND fb.cal_period_id = giet.cal_period_id
498 AND fb.dataset_code = p_dataset_code
499 AND fb.ledger_id = P_fem_ledger_id
500 AND fb.source_system_code = 70
501
502 AND NOT EXISTS (SELECT 1
503 FROM GCS_INTERCO_ELM_TRX giet3,
504 GCS_INTERCO_MEMBERS gim2
505 WHERE giet3.hierarchy_id = p_hierarchy_id
506 AND giet3.cal_period_id = p_cal_period_id
507 AND giet3.src_entity_id = giet3.target_entity_id
508 AND giet3.src_entity_id = giet.src_entity_id
509 AND giet3.line_item_id = giet.line_item_id
510 AND giet3.src_company_id = giet.src_company_id
511 AND giet3.target_company_id = giet.target_company_id
512
513 AND gim2.line_item_id = giet3.line_item_id
514 AND gim2.rule_id = gihg.rule_id
515 AND gim2.line_item_group > gim.line_item_group)
516
517 GROUP BY gihg.entry_id, giet.company_cost_center_org_id ,
518 giet.intercompany_id,
519
520 giet.line_item_id;
521 g_no_rows := NVL(SQL%ROWCOUNT,0);
522
523 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
524 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
525 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
526 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_LINES_GT');
527
528 FND_LOG.String (fnd_log.level_procedure,
529 g_pkg_name || '.' || l_api_name,
530 'SHRD0117: '||FND_MESSAGE.get);
531 END IF;
532
533
534
535 ELSIF (p_match_rule_code = 'ORGANIZATION') THEN
536 -- In Intracompany option
537 g_no_rows := 0;
538 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
539 fnd_log.STRING (fnd_log.level_procedure,
540 g_pkg_name || '.' || l_api_name,
541 'Intracompany- Inserting entry lines'
542 || 'into GCS_ENTRY_LINES_GT'
543 || 'after matching by Org'
544 );
545 END IF;
546
547 INSERT INTO GCS_ENTRY_LINES_GT
548 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
549 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
550 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
551 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
552 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
553 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
554 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
555 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
556 , DESCRIPTION , YTD_BALANCE_E,RECEIVABLES_ORG_ID,
557 PAYABLES_ORG_ID )
558 SELECT /*+ ORDERED FULL(GIHG)
559 INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
560 INDEX(GIM GCS_INTERCO_MEMBERS_U1)
561 INDEX (FB FEM_BALANCES_P)
562 USE_NL(GIET FB)*/
563 gihg.entry_id, giet.company_cost_center_org_id
564 , giet.line_item_id
565 , giet.intercompany_id,
566 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
567 SUM(NVL(fb.ytd_debit_balance_e,0))
568 , SUM(NVL(fb.ytd_credit_balance_e,0))
569 , MAX(gihg.rule_id)
570 , (SUM(NVL(fb.ytd_credit_balance_e,0))
571 - SUM(NVL(fb.ytd_debit_balance_e,0))),
572 DECODE(MAX(gim.line_item_group), 1,
573 giet.company_cost_center_org_id,
574 giet.Intercompany_id),
575 DECODE(MAX(gim.line_item_group), 2,
576 giet.company_cost_center_org_id,
577 giet.Intercompany_id)
578 FROM GCS_INTERCO_HDR_GT gihg,
579 GCS_INTERCO_ELM_TRX giet,
580 GCS_INTERCO_MEMBERS gim,
581 FEM_BALANCES fb
582 WHERE giet.cal_period_id = p_cal_period_id
586 AND giet.src_entity_id = giet.target_entity_id
583 AND giet.hierarchy_id = p_hierarchy_id
584 AND gihg.currency_code IN (p_currency_code,'STAT')
585 AND giet.line_item_id = gim.line_item_id
587 AND giet.src_entity_id = gihg.source_entity_id
588 AND giet.target_entity_id = gihg.target_entity_id
589 AND gim.rule_id = gihg.rule_id
590 AND fb.entity_id = p_entity_id
591 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
592 AND giet.intercompany_id = fb.intercompany_id
593 AND giet.line_item_id = fb.line_item_id
594 AND fb.currency_code = gihg.currency_code
595 AND fb.cal_period_id = giet.cal_period_id
596 AND fb.dataset_code = p_dataset_code
597 AND fb.ledger_id = P_fem_ledger_id
598 AND fb.source_system_code = 70
599
600 AND NOT EXISTS (SELECT 1
601 FROM GCS_INTERCO_ELM_TRX giet3,
602 GCS_INTERCO_MEMBERS gim2
603 WHERE giet3.hierarchy_id = p_hierarchy_id
604 AND giet3.cal_period_id = p_cal_period_id
605 AND giet3.src_entity_id = giet3.target_entity_id
606 AND giet3.src_entity_id = giet.src_entity_id
607 AND giet3.line_item_id = giet.line_item_id
608 AND giet3.company_cost_center_org_id =
609 giet.company_cost_center_org_id
610 AND giet3.intercompany_id = giet.intercompany_id
611 AND gim2.line_item_id = giet3.line_item_id
612 AND gim2.rule_id = gihg.rule_id
613 AND gim2.line_item_group > gim.line_item_group)
614
615 GROUP BY gihg.entry_id, giet.company_cost_center_org_id,
616 giet.intercompany_id,
617
618
619 giet.line_item_id;
620
621 g_no_rows := NVL(SQL%ROWCOUNT,0);
622
623 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
624 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
625 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
626 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_LINES_GT');
627
628 FND_LOG.String (fnd_log.level_procedure,
629 g_pkg_name || '.' || l_api_name,
630 'SHRD0117: '||FND_MESSAGE.get);
631 END IF;
632 End If; -- End of matching rule code in intracompany
633
634 END If; -- End of elimination mode.
635 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
636 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
637 g_pkg_name || '.' || l_api_name,
638 GCS_UTILITY_PKG.g_module_success || l_api_name ||
639 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
640 END IF;
641 RETURN TRUE;
642
643 EXCEPTION
644
645 WHEN OTHERS THEN
646 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
647 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
648 g_pkg_name || '.' || l_api_name,
649 SUBSTR(SQLERRM, 1, 255));
650 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
651 g_pkg_name || '.' || l_api_name,
652 GCS_UTILITY_PKG.g_module_failure || l_api_name ||
653 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
654 END IF;
655 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure
656 -- ||l_api_name || to_char(sysdate
657 -- , ' DD-MON-YYYY HH:MI:SS'));
658 RETURN FALSE;
659 END INSR_INTERCO_LINES;
660
661 --
662 -- Function
663 -- insr_sus_lines
664
665 -- Purpose
666
667 -- This routine is responsible for inserting the suspense plug in lines
668 -- into the GCS_ENTRY_LINES table.
669
670 FUNCTION INSR_SUSPENSE_LINES (p_hierarchy_id IN NUMBER,
671 p_cal_period_id IN NUMBER,
672 p_entity_id IN NUMBER,
673 p_match_rule_code VARCHAR2,
674 p_balance_type VARCHAR2,
675 p_elim_mode IN VARCHAR2,
676 P_Currency_code IN VARCHAR2,
677 p_data_set_code IN NUMBER ,
678 p_err_code OUT NOCOPY VARCHAR2,
679 p_err_msg OUT NOCOPY VARCHAR2)
680 RETURN BOOLEAN IS
681
682 l_api_name VARCHAR2(30) := 'INSR_SUSPENSE_LINES';
683
684 -- Insert Suspense lines for unbalanced matched rows
685 -- into GCS_ENTRY-LINES.
686 BEGIN
687
688 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
689 fnd_log.STRING (fnd_log.level_procedure,
690 g_pkg_name || '.' || l_api_name,
691 gcs_utility_pkg.g_module_enter
692 || ' '
693 || l_api_name
694 || '() '
695 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
696 );
697 END IF;
698
699
700
701 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
702 fnd_log.STRING (fnd_log.level_procedure,
703 g_pkg_name || '.' || l_api_name,
704 ' Arguments passed to Insr_Suspense_Lines() '
708 ||' Match Rule Code: '||p_match_rule_code
705 ||' Hierarchy_Id: '||p_hierarchy_id
706 ||' Cal_Period_Id: '||p_cal_period_id
707 ||' Entity_Id: '||p_entity_id
709 ||' Balance_Type: '||p_balance_type
710 ||' Elim_Mode: '||p_elim_mode
711 ||' Currency_Code: '||p_currency_code
712 ||' Dataset Code:'||p_data_set_code);
713
714 END IF;
715
716 IF ((P_ELIM_MODE = 'IE') OR
717 (P_ELIM_MODE = 'IA')) THEN
718
719 IF (p_match_rule_code = 'COMPANY') THEN
720 g_no_rows := 0;
721 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
722 fnd_log.STRING (fnd_log.level_procedure,
723 g_pkg_name || '.' || l_api_name,
724 'Intercompany- Inserting necessary suspense lines'
725 || ' into GCS_ENTRY_LINES_GT'
726 || ' after matching by company'
727 );
728 END IF;
729
730 INSERT INTO GCS_ENTRY_LINES_GT
731 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
732 , FINANCIAL_ELEM_ID, LINE_ITEM_Id, INTERCOMPANY_ID
733 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
734 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
735 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
736 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
737 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
738 , YTD_DEBIT_BALANCE_E , YTD_CREDIT_BALANCE_E
739 , DESCRIPTION, YTD_BALANCE_E)
740 SELECT gihg1.entry_id
741 , MAX(Receivables_org_id)
742 , MAX(gihg1.sus_financial_elem_id), MAX(gihg1.sus_line_item_id)
743 , MAX(payables_org_id),
744
745 MAX(gihg1.SUS_PRODUCT_ID), MAX(gihg1.SUS_NATURAL_ACCOUNT_ID),
746 MAX(gihg1.SUS_CHANNEL_ID), MAX(gihg1.SUS_PROJECT_ID),
747 MAX(gihg1.SUS_CUSTOMER_ID), MAX(gihg1.SUS_TASK_ID),
748 MAX(gihg1.SUS_USER_DIM1_ID), MAX(gihg1.SUS_USER_DIM2_ID),
749 MAX(gihg1.SUS_USER_DIM3_ID), MAX(gihg1.SUS_USER_DIM4_ID),
750 MAX(gihg1.SUS_USER_DIM5_ID), MAX(gihg1.SUS_USER_DIM6_ID),
751 MAX(gihg1.SUS_USER_DIM7_ID), MAX(gihg1.SUS_USER_DIM8_ID),
752 MAX(gihg1.SUS_USER_DIM9_ID), MAX(gihg1.SUS_USER_DIM10_ID),
753
754 DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
755 SUM(NVL(ytd_debit_balance_e,0))),
756 SUM(NVL(ytd_debit_balance_e,0)), 0,
757 ABS(SUM(NVL(ytd_debit_balance_e,0))-
758 SUM(NVL(ytd_credit_balance_e,0)))),
759 DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
760 SUM(NVL(ytd_debit_balance_e,0))),
761 SUM(NVL(ytd_credit_balance_e,0)), 0,
762 ABS(SUM(NVL(ytd_debit_balance_e,0))-
763 SUM(NVL(ytd_credit_balance_e,0))))
764 , 'SUSPENSE_LINE'
765 , (DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
766 SUM(NVL(ytd_debit_balance_e,0))),
767 SUM(NVL(ytd_debit_balance_e,0)), 0,
768 ABS(SUM(NVL(ytd_debit_balance_e,0))-
769 SUM(NVL(ytd_credit_balance_e,0))))-
770 DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
771 SUM(NVL(ytd_debit_balance_e,0))),
772 SUM(NVL(ytd_credit_balance_e,0)), 0,
773 ABS(SUM(NVL(ytd_debit_balance_e,0))-
774 SUM(NVL(ytd_credit_balance_e,0)))))
775
776 FROM GCS_ENTRY_LINES_GT gel,
777 GCS_INTERCO_HDR_GT gihg1,
778 fem_cctr_orgs_attr fcoa2 ,
779 fem_cctr_orgs_attr fcoa3
780 WHERE gihg1.entry_id = gel.entry_id
781 AND gel.receivables_org_id =
782 fcoa2.company_cost_center_org_id
783 AND fcoa2.attribute_id =
784 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').attribute_id
785 AND fcoa2.version_id =
786 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').version_id
787 AND gel.payables_org_id = fcoa3.company_cost_center_org_id
788 AND fcoa3.attribute_id =
789 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').attribute_id
790 AND fcoa3.version_id =
791 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').version_id
792 GROUP BY gihg1.entry_id, fcoa2.dim_attribute_numeric_member,
793 fcoa3.dim_attribute_numeric_member;
794
795 g_no_rows := NVL(SQL%ROWCOUNT,0);
796
797 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
798 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
799 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
800 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_LINES_GT');
801
802 FND_LOG.String (fnd_log.level_procedure,
803 g_pkg_name || '.' || l_api_name,
804 'SHRD0117: '||FND_MESSAGE.get);
805 END IF;
806
810 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
807 ELSIF (p_match_rule_code = 'ORGANIZATION') THEN
808 -- In Intercompany option
809 g_no_rows := 0;
811 fnd_log.STRING (fnd_log.level_procedure,
812 g_pkg_name || '.' || l_api_name,
813 'Intercompany- Inserting necessary suspense lines'
814 || ' into GCS_ENTRY_LINES_GT'
815 || ' after matching by org '
816 );
817 END IF;
818
819 INSERT INTO GCS_ENTRY_LINES_GT
820 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
821 , FINANCIAL_ELEM_ID, LINE_ITEM_Id, INTERCOMPANY_ID
822 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
823 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
824 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
825 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
826 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
827 , YTD_DEBIT_BALANCE_E , YTD_CREDIT_BALANCE_E
828 , DESCRIPTION , YTD_BALANCE_E)
829 SELECT gihg1.entry_id, MAX(Receivables_org_id),
830 MAX(gihg1.sus_financial_elem_id),
831 MAX(gihg1.sus_line_item_id), MAX(payables_org_id),
832
833 MAX(gihg1.SUS_PRODUCT_ID), MAX(gihg1.SUS_NATURAL_ACCOUNT_ID),
834 MAX(gihg1.SUS_CHANNEL_ID), MAX(gihg1.SUS_PROJECT_ID),
835 MAX(gihg1.SUS_CUSTOMER_ID), MAX(gihg1.SUS_TASK_ID),
836 MAX(gihg1.SUS_USER_DIM1_ID), MAX(gihg1.SUS_USER_DIM2_ID),
837 MAX(gihg1.SUS_USER_DIM3_ID), MAX(gihg1.SUS_USER_DIM4_ID),
838 MAX(gihg1.SUS_USER_DIM5_ID), MAX(gihg1.SUS_USER_DIM6_ID),
839 MAX(gihg1.SUS_USER_DIM7_ID), MAX(gihg1.SUS_USER_DIM8_ID),
840 MAX(gihg1.SUS_USER_DIM9_ID), MAX(gihg1.SUS_USER_DIM10_ID),
841 DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
842 SUM(NVL(ytd_debit_balance_e,0))),
843 SUM(NVL(ytd_debit_balance_e,0)), 0,
844 ABS(SUM(NVL(ytd_debit_balance_e,0))-
845 SUM(NVL(ytd_credit_balance_e,0)))),
846 DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
847 SUM(NVL(ytd_debit_balance_e,0))),
848 SUM(NVL(ytd_credit_balance_e,0)), 0,
849 ABS(SUM(NVL(ytd_debit_balance_e,0))-
850 SUM(NVL(ytd_credit_balance_e,0))))
851 , 'SUSPENSE_LINE',
852 (DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
853 SUM(NVL(ytd_debit_balance_e,0))),
854 SUM(NVL(ytd_debit_balance_e,0)), 0,
855 ABS(SUM(NVL(ytd_debit_balance_e,0))-
856 SUM(NVL(ytd_credit_balance_e,0))))-
857 DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
858 SUM(NVL(ytd_debit_balance_e,0))),
859 SUM(NVL(ytd_credit_balance_e,0)), 0,
860 ABS(SUM(NVL(ytd_debit_balance_e,0))-
861 SUM(NVL(ytd_credit_balance_e,0)))))
862 FROM GCS_ENTRY_LINES_GT gel,
863 GCS_INTERCO_HDR_GT gihg1
864 WHERE gihg1.entry_id = gel.entry_id
865 GROUP BY gihg1.entry_id, receivables_org_id, payables_org_id;
866
867 g_no_rows := NVL(SQL%ROWCOUNT,0);
868
869 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
870 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
871 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
872 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_LINES_GT');
873
874 FND_LOG.String (fnd_log.level_procedure,
875 g_pkg_name || '.' || l_api_name,
876 'SHRD0117: '||FND_MESSAGE.get);
877
878 END IF;
879 END IF; -- Ends matching rule code in intercompany.
880 END IF; --Added to end the mode IF
881
882 g_no_rows := 0;
883 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
884 fnd_log.STRING (fnd_log.level_procedure,
885 g_pkg_name || '.' || l_api_name,
886 'Intercompany- Inserting '
887 || ' into GCS_ENTRY_LINES'
888 || ' after processing'
889 );
890 END IF;
891
892 INSERT INTO GCS_ENTRY_LINES
893 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
894 , FINANCIAL_ELEM_ID, LINE_ITEM_Id, INTERCOMPANY_ID
895 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
896 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
897 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
898 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
899 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
900 , YTD_DEBIT_BALANCE_E , YTD_CREDIT_BALANCE_E
901 , CREATION_DATE , CREATED_BY , LAST_UPDATE_DATE
902 , LAST_UPDATED_BY, LAST_UPDATE_LOGIN
903 , DESCRIPTION, YTD_BALANCE_E)
904
905 SELECT ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
906 , FINANCIAL_ELEM_ID, LINE_ITEM_Id, INTERCOMPANY_ID
907 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
908 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
909 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
910 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
914 , SYSDATE, g_fnd_user_id, g_fnd_login_id
911 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
912 , YTD_DEBIT_BALANCE_E , YTD_CREDIT_BALANCE_E
913 , SYSDATE, g_fnd_user_id
915 , DESCRIPTION, YTD_BALANCE_E
916 FROM GCS_ENTRY_LINES_GT
917 WHERE DESCRIPTION <> 'SUSPENSE_LINE'
918
919 UNION ALL
920 SELECT ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
921 , FINANCIAL_ELEM_ID, LINE_ITEM_Id, INTERCOMPANY_ID
922 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
923 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
924 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
925 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
926 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
927 , YTD_DEBIT_BALANCE_E , YTD_CREDIT_BALANCE_E
928 , SYSDATE, g_fnd_user_id
929 , SYSDATE, g_fnd_user_id, g_fnd_login_id
930 , DESCRIPTION, YTD_BALANCE_E
931 FROM GCS_ENTRY_LINES_GT
932 WHERE (DESCRIPTION = 'SUSPENSE_LINE' AND YTD_BALANCE_E <> 0);
933
934 g_no_rows := NVL(SQL%ROWCOUNT,0);
935
936 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
937 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
938 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
939 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_LINES');
940
941 FND_LOG.String (fnd_log.level_procedure,
942 g_pkg_name || '.' || l_api_name,
943 'SHRD0117: '||FND_MESSAGE.get);
944
945 END IF;
946
947
948 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
949 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
950 g_pkg_name || '.' || l_api_name,
951 GCS_UTILITY_PKG.g_module_success || l_api_name ||
952 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
953 END IF;
954 Return TRUE;
955
956
957
958 EXCEPTION
959
960 WHEN OTHERS THEN
961 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
962 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
963 g_pkg_name || '.' || l_api_name,
964 SUBSTR(SQLERRM, 1, 255));
965 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
966 g_pkg_name || '.' || l_api_name,
967 GCS_UTILITY_PKG.g_module_failure || l_api_name ||
968 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
969 END IF;
970
971 p_err_code := SQLCODE;
972 p_err_msg := SQLERRM;
973
974
975 RETURN FALSE;
976 END INSR_SUSPENSE_LINES;
977
978 -- Procedure
979 -- Insert_Interco_Trx
980 -- Purpose
981 -- Inserts eligible elimination transactions
982 -- into GCS_INTERCO_ELM_TRX after dataprep operation.
983 -- This procedure will be called from the Datapre package.
984 -- Arguments
985 -- P_entry_id Entry_id (created by dataprep) for the
986 -- monetary currency
987 -- p_stat_entry_id Entry id (created by dataprep) for the stat currency
988 -- p_Hierarchy_id Hierarchy_id for the above entries.
989 -- This hierarchy id will
990
991 -- be used to determine the matching rule like
992 -- match by organization, match by company,
993 -- or match by cost center.
994 -- x_errbuf Returns error message to concurrent manager,
995 -- if there are any errors.
996 -- x_retcode Returns error code to concurrent manager,
997 -- if there are any errors.
998
999 -- Synatx for Calling from external package.
1000
1001 -- GCS_INTERCO_DYNAMIC_PKG.INSERT_INTERCO_TRX(1112,
1002 -- 1114,
1003 -- 10041, err, err_code)
1004 --
1005
1006 --
1007
1008
1009
1010 PROCEDURE INSERT_INTERCO_TRX(p_entry_id In NUMBER,
1011 p_stat_entry_id IN NUMBER,
1012 p_hierarchy_id IN NUMBER,
1013 p_period_end_date IN DATE,
1014 x_errbuf OUT NOCOPY VARCHAR2,
1015 x_retcode OUT NOCOPY VARCHAR2) IS
1016
1017 PRAGMA AUTONOMOUS_TRANSACTION;
1018
1019 l_api_name VARCHAR2(50) := 'INSERT_INTERCO_TRX';
1020 x_match_rule_code VARCHAR2(30);
1021 l_no_rows NUMBER:= 0;
1022 x_intercompany_org_code VARCHAR2(30);
1023 x_specific_intercompany_id NUMBER;
1024 x_lob_reporting_enabled VARCHAR2(30);
1025 x_lob_hierarchy_obj_id NUMBER;
1026 x_lob_dim_column_name VARCHAR2(30);
1027 l_valid_hierarchy_id NUMBER;
1028
1029
1030 NO_MATCH_RULE_CODE Exception;
1031 Hierarchy_check_failed Exception;
1032
1033
1034
1035
1036
1037 BEGIN
1038
1039 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1040
1041 fnd_log.STRING (fnd_log.level_procedure,
1042 g_pkg_name || '.' || l_api_name,
1043 gcs_utility_pkg.g_module_enter
1044 || ' '
1045 || l_api_name
1046 || '() '
1050
1047 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1048 );
1049 END IF;
1051 -- Get the matching rule for the given hierarchy_id
1052 -- for matching intercompany eliminations such as by organization,
1053 -- by company or by cost center.
1054
1055 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1056 fnd_log.STRING (fnd_log.level_procedure,
1057 g_pkg_name || '.' || l_api_name,
1058 'Get the matching rule information'
1059 );
1060 END IF;
1061
1062 BEGIN
1063
1064 SELECT ghb.ie_by_org_code,
1065 DECODE(gcb.specific_intercompany_id, NULL,
1066 'N', 'SPECIFIC_VALUE'),
1067 gcb.specific_intercompany_id,
1068 ghb.lob_reporting_enabled_flag,
1069 ghb.lob_hierarchy_obj_id,
1070 ghb.lob_dim_column_name
1071 INTO x_match_rule_code,
1072 x_intercompany_org_code,
1073 x_specific_intercompany_id,
1074 x_lob_reporting_enabled,
1075 x_lob_hierarchy_obj_id,
1076 x_lob_dim_column_name
1077 FROM GCS_HIERARCHIES_B ghb, gcs_categories_b gcb
1078 WHERE ghb.hierarchy_id = p_hierarchy_id
1079 AND gcb.category_code = 'INTRACOMPANY'
1080 AND rownum = 1;
1081
1082
1083 EXCEPTION
1084
1085 WHEN NO_DATA_FOUND Then
1086 Raise NO_MATCH_RULE_CODE;
1087 END;
1088
1089 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1090 fnd_log.STRING (fnd_log.level_procedure,
1091 g_pkg_name || '.' || l_api_name,
1092 ' Arguments Hierarchy_id :'||p_hierarchy_id
1093 ||' Stat Entry Id: '|| p_stat_entry_id
1094 ||' Entry id: '||p_entry_id
1095 ||' Matching Rule: '||x_match_rule_code
1096 ||' Period End Date: '||p_period_end_date
1097 ||' Intercompany code: '||x_intercompany_org_code
1098 ||' Spec. interco value: '
1099 ||x_specific_intercompany_id
1100 ||' LOB Reporting Enabled: '
1101 || x_lob_reporting_enabled
1102 ||' Cost Center Hierarchy Obj Id: '
1103 ||x_lob_hierarchy_obj_id
1104 );
1105
1106 END IF;
1107
1108
1109
1110 IF (x_match_rule_code = 'ORGANIZATION') THEN
1111
1112 IF ((x_lob_reporting_enabled = 'Y')
1113 AND (x_lob_hierarchy_obj_id IS NOT NULL)) THEN
1114
1115 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1116 fnd_log.STRING (fnd_log.level_procedure,
1117 g_pkg_name || '.' || l_api_name,
1118 'Entered into LOB support block'
1119 );
1120 END IF;
1121
1122 BEGIN
1123
1124 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1125 fnd_log.STRING (fnd_log.level_procedure,
1126 g_pkg_name || '.' || l_api_name,
1127 'Checking Hierarchy date effectivity'
1128 );
1129 END IF;
1130
1131 SELECT object_definition_id INTO l_valid_hierarchy_id
1132 FROM FEM_OBJECT_DEFINITION_B fod
1133 WHERE fod.object_id = x_lob_hierarchy_obj_id
1134 AND (p_period_end_date
1135 BETWEEN NVL(fod.effective_start_date,
1136 TO_DATE('01/01/1950','MM/DD/YYYY'))
1137 AND NVL(fod.effective_end_date,
1138 TO_DATE('12/31/9999','MM/DD/YYYY')));
1139
1140
1141 EXCEPTION
1142 WHEN NO_DATA_FOUND THEN
1143 l_valid_hierarchy_id :=0;
1144
1145 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1146 fnd_log.STRING (fnd_log.level_procedure,
1147 g_pkg_name || '.' || l_api_name,
1148 'Hierarchy date effectivity failed'
1149 || ' either due to wrong hierarchy or '
1150 ||' period end date is not falling '
1151 ||' start date and end date'
1152 );
1153 END IF;
1154 null;
1155 --Raise Hierarchy_Check_Failed;
1156
1157 WHEN OTHERS THEN
1158 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1159 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1160 g_pkg_name || '.' || l_api_name
1161 ||' Hierarchy_Check ',
1162 SUBSTR(SQLERRM, 1, 255));
1163
1164 END IF;
1165
1166 END;
1167
1168
1169 IF (x_intercompany_org_code = 'SPECIFIC_VALUE') THEN
1170
1171 l_no_rows := 0;
1172
1173 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1174 fnd_log.STRING (fnd_log.level_procedure,
1175 g_pkg_name || '.' || l_api_name,
1179 END IF;
1176 'Inserting intercompany transactions for matching by'
1177 || ' organization into GCS_INTERCO_ELM_TRX'
1178 || ' - LOB REPORTING ENABLED ');
1180 Insert INTO gcs_interco_elm_trx
1181 (hierarchy_id, cal_period_id, company_cost_center_org_id,
1182 src_entity_id, src_company_id, src_cost_center_id,
1183 intercompany_id, target_company_id,
1184 target_cost_center_id, target_entity_id,
1185 currency_code, line_item_id, financial_elem_id,
1186 product_id, natural_account_id, channel_id,
1187 project_id, customer_id, task_id,
1188 user_dim1_id, user_dim2_id, user_dim3_id,
1189 user_dim4_id, user_dim5_id, user_dim6_id,
1190 user_dim7_id, user_dim8_id, user_dim9_id,
1191 user_dim10_id,creation_date,
1192 created_by, last_update_date, last_updated_by,
1193 last_update_login, elim_lob_id)
1194
1195 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
1196 gel.company_cost_center_org_id,
1197 geo1.entity_id, NULL, NULL, gel.intercompany_id,
1198 NULL,NULL, geo.entity_id, geh.currency_code,
1199 gel.line_item_id,
1200 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1201 SYSDATE, g_fnd_user_id,
1202 SYSDATE, g_fnd_user_id,
1203 g_fnd_login_id,
1204 DECODE(fcoa2.dim_attribute_numeric_member,
1205 fcoa3.dim_attribute_numeric_member,
1206 fcoa2.dim_attribute_numeric_member,
1207 fcca.dim_attribute_numeric_member)
1208 FROM GCS_ENTRY_HEADERS geh,
1209 GCS_ENTRY_LINES gel,
1210 GCS_ENTITY_CCTR_ORGS geo,
1211 GCS_ENTITY_CCTR_ORGS geo1,
1212 GCS_CONS_RELATIONSHIPS gcr,
1213 GCS_CONS_RELATIONSHIPS gcr1,
1214 fem_cctr_orgs_attr fcoa2,
1215 fem_cctr_orgs_attr fcoa3,
1216 fem_user_dim1_attr fcca
1217 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
1218 AND geh.entry_id = gel.entry_id
1219 AND gel.intercompany_id <> x_specific_intercompany_id
1220 AND gel.intercompany_id =
1221 geo.company_cost_center_org_id
1222 AND gel.company_cost_center_org_id =
1223 geo1.company_cost_center_org_id
1224 AND geh.hierarchy_id = gcr.hierarchy_id
1225 AND (p_period_end_date
1226 BETWEEN NVL(gcr.start_date, p_period_end_date)
1227 AND NVL(gcr.end_date, p_period_end_date))
1228 AND gcr.child_entity_id = geo.entity_id
1229 AND gcr.actual_ownership_flag ='Y'
1230 AND gcr.dominant_parent_flag = 'Y'
1231 AND geh.hierarchy_id = gcr1.hierarchy_id
1232 AND (p_period_end_date
1233 BETWEEN NVL(gcr1.start_date, p_period_end_date)
1234 AND NVL(gcr1.end_date, p_period_end_date))
1235 AND gcr1.child_entity_id = geo1.entity_id
1236 AND gcr1.actual_ownership_flag ='Y'
1237 AND gcr1.dominant_parent_flag = 'Y'
1238
1239 AND gel.company_cost_center_org_id =
1240 fcoa2.company_cost_center_org_id
1241 AND fcoa2.attribute_id =
1242 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').attribute_id
1243 AND fcoa2.version_id =
1244 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').version_id
1245 AND gel.intercompany_id = fcoa3.company_cost_center_org_id
1246 AND fcoa3.attribute_id =
1247 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').attribute_id
1248 AND fcoa3.version_id =
1249 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').version_id
1250 AND fcca.attribute_id = gcs_utility_pkg.g_dimension_attr_info('USER_DIM1_ID-ELIMINATION_LOB').attribute_id
1251 AND fcca.version_id = gcs_utility_pkg.g_dimension_attr_info('USER_DIM1_ID-ELIMINATION_LOB').version_id
1252
1253 AND fcca.user_dim1_id = (
1254 SELECT fcch1.parent_id
1255 FROM fem_user_dim1_hier fcch1,
1256 fem_user_dim1_hier fcch2
1257 WHERE fcch1.child_id =
1258 fcoa2.dim_attribute_numeric_member
1259 AND fcch1.hierarchy_obj_def_id =
1260 l_valid_hierarchy_id
1261 AND fcch1.parent_id <> fcch1.child_id
1262 -- *** To eliminte self rows
1263 AND fcch2.child_id =
1264 fcoa3.dim_attribute_numeric_member
1265 AND fcch2.hierarchy_obj_def_id =
1266 l_valid_hierarchy_id
1267 AND fcch2.parent_id <> fcch2.child_id
1268 -- *** To eliminte self rows
1269 AND fcch1.parent_id = fcch2.parent_id
1270 AND fcch1.parent_depth_num =
1271 (SELECT MAX(fcch3.parent_depth_num)
1275 fcoa2.dim_attribute_numeric_member
1272 FROM fem_user_dim1_hier fcch3,
1273 fem_user_dim1_hier fcch4
1274 WHERE fcch3.child_id =
1276 AND fcch3.hierarchy_obj_def_id =
1277 l_valid_hierarchy_id
1278 AND fcch3.parent_id <> fcch3.child_id
1279 -- *** To eliminte self rows
1280 AND fcch4.child_id =
1281 fcoa3.dim_attribute_numeric_member
1282 AND fcch4.hierarchy_obj_def_id =
1283 l_valid_hierarchy_id
1284 AND fcch4.parent_id <> fcch4.child_id
1285 -- *** To eliminte self rows
1286 AND fcch3.parent_id = fcch4.parent_id
1287 ))
1288
1289 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
1290 WHERE giet1.hierarchy_id = geh.hierarchy_id
1291 AND giet1.cal_period_id = geh.start_cal_period_id
1292 AND giet1.company_cost_center_org_id =
1293 gel.company_cost_center_org_id
1294 AND giet1.src_entity_id = geo1.entity_id
1295 AND giet1.target_entity_id = geo.entity_id
1296 AND giet1.intercompany_id = gel.intercompany_id
1297 AND giet1.line_item_id = gel.line_item_id) ;
1298
1299 l_no_rows := NVL(SQL%ROWCOUNT,0);
1300
1301 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1302 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1303 FND_MESSAGE.Set_Token('NUM',TO_CHAR(l_no_rows));
1304 FND_MESSAGE.Set_Token('TABLE','GCS_INTERCO_ELM_TRX');
1305
1306 FND_LOG.String (fnd_log.level_procedure,
1307 g_pkg_name || '.' || l_api_name,
1308 'SHRD0117: '||FND_MESSAGE.get);
1309 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
1310 END IF;
1311
1312 ELSE
1313
1314 l_no_rows := 0;
1315
1316 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1317 fnd_log.STRING (fnd_log.level_procedure,
1318 g_pkg_name || '.' || l_api_name,
1319 'Inserting intercompany transactions for matching by'
1320 || ' organization into GCS_INTERCO_ELM_TRX'
1321 || ' - LOB REPORTING ENABLED ');
1322 END IF;
1323 Insert INTO gcs_interco_elm_trx
1324 (hierarchy_id, cal_period_id, company_cost_center_org_id,
1325 src_entity_id, src_company_id, src_cost_center_id,
1326 intercompany_id, target_company_id,
1327 target_cost_center_id, target_entity_id,
1328 currency_code, line_item_id, financial_elem_id,
1329 product_id, natural_account_id, channel_id,
1330 project_id, customer_id, task_id,
1331 user_dim1_id, user_dim2_id, user_dim3_id,
1332 user_dim4_id, user_dim5_id, user_dim6_id,
1333 user_dim7_id, user_dim8_id, user_dim9_id,
1334 user_dim10_id,creation_date,
1335 created_by, last_update_date, last_updated_by,
1336 last_update_login, elim_lob_id)
1337
1338 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
1339 gel.company_cost_center_org_id,
1340 geo1.entity_id, NULL, NULL, gel.intercompany_id,
1341 NULL,NULL, geo.entity_id, geh.currency_code,
1342 gel.line_item_id,
1343 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1344 SYSDATE, g_fnd_user_id,
1345 SYSDATE, g_fnd_user_id,
1346 g_fnd_login_id,
1347 DECODE(fcoa2.dim_attribute_numeric_member,
1348 fcoa3.dim_attribute_numeric_member,
1349 fcoa2.dim_attribute_numeric_member,
1350 fcca.dim_attribute_numeric_member)
1351 FROM GCS_ENTRY_HEADERS geh,
1352 GCS_ENTRY_LINES gel,
1353 GCS_ENTITY_CCTR_ORGS geo,
1354 GCS_ENTITY_CCTR_ORGS geo1,
1355 GCS_CONS_RELATIONSHIPS gcr,
1356 GCS_CONS_RELATIONSHIPS gcr1,
1357 fem_cctr_orgs_attr fcoa2,
1358 fem_cctr_orgs_attr fcoa3,
1359 fem_user_dim1_attr fcca
1360 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
1361 AND geh.entry_id = gel.entry_id
1362 AND gel.intercompany_id <> gel.company_cost_center_org_id
1363 AND gel.intercompany_id =
1364 geo.company_cost_center_org_id
1368 AND (p_period_end_date
1365 AND gel.company_cost_center_org_id =
1366 geo1.company_cost_center_org_id
1367 AND geh.hierarchy_id = gcr.hierarchy_id
1369 BETWEEN NVL(gcr.start_date, p_period_end_date)
1370 AND NVL(gcr.end_date, p_period_end_date))
1371 AND gcr.child_entity_id = geo.entity_id
1372 AND gcr.actual_ownership_flag ='Y'
1373 AND gcr.dominant_parent_flag = 'Y'
1374 AND geh.hierarchy_id = gcr1.hierarchy_id
1375 AND (p_period_end_date
1376 BETWEEN NVL(gcr1.start_date, p_period_end_date)
1377 AND NVL(gcr1.end_date, p_period_end_date))
1378 AND gcr1.child_entity_id = geo1.entity_id
1379 AND gcr1.actual_ownership_flag ='Y'
1380 AND gcr1.dominant_parent_flag = 'Y'
1381
1382 AND gel.company_cost_center_org_id =
1383 fcoa2.company_cost_center_org_id
1384 AND fcoa2.attribute_id =
1385 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').attribute_id
1386 AND fcoa2.version_id =
1387 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').version_id
1388 AND gel.intercompany_id = fcoa3.company_cost_center_org_id
1389 AND fcoa3.attribute_id =
1390 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').attribute_id
1391 AND fcoa3.version_id =
1392 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').version_id
1393 AND fcca.attribute_id = gcs_utility_pkg.g_dimension_attr_info('USER_DIM1_ID-ELIMINATION_LOB').attribute_id
1394 AND fcca.version_id = gcs_utility_pkg.g_dimension_attr_info('USER_DIM1_ID-ELIMINATION_LOB').version_id
1395
1396 AND fcca.user_dim1_id = (
1397 SELECT fcch1.parent_id
1398 FROM fem_user_dim1_hier fcch1,
1399 fem_user_dim1_hier fcch2
1400 WHERE fcch1.child_id =
1401 fcoa2.dim_attribute_numeric_member
1402 AND fcch1.hierarchy_obj_def_id =
1403 l_valid_hierarchy_id
1404 AND fcch1.parent_id <> fcch1.child_id
1405 -- *** To eliminte self rows
1406 AND fcch2.child_id =
1407 fcoa3.dim_attribute_numeric_member
1408 AND fcch2.hierarchy_obj_def_id =
1409 l_valid_hierarchy_id
1410 AND fcch2.parent_id <> fcch2.child_id
1411 -- *** To eliminte self rows
1412 AND fcch1.parent_id = fcch2.parent_id
1413 AND fcch1.parent_depth_num =
1414 (SELECT MAX(fcch3.parent_depth_num)
1415 FROM fem_user_dim1_hier fcch3,
1416 fem_user_dim1_hier fcch4
1417 WHERE fcch3.child_id =
1418 fcoa2.dim_attribute_numeric_member
1419 AND fcch3.hierarchy_obj_def_id =
1420 l_valid_hierarchy_id
1421 AND fcch3.parent_id <> fcch3.child_id
1422 -- *** To eliminte self rows
1423 AND fcch4.child_id =
1424 fcoa3.dim_attribute_numeric_member
1425 AND fcch4.hierarchy_obj_def_id =
1426 l_valid_hierarchy_id
1427 AND fcch4.parent_id <> fcch4.child_id
1428 -- *** To eliminte self rows
1429 AND fcch3.parent_id = fcch4.parent_id
1430 ))
1431
1432 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
1433 WHERE giet1.hierarchy_id = geh.hierarchy_id
1434 AND giet1.cal_period_id = geh.start_cal_period_id
1435 AND giet1.company_cost_center_org_id =
1436 gel.company_cost_center_org_id
1437 AND giet1.src_entity_id = geo1.entity_id
1438 AND giet1.target_entity_id = geo.entity_id
1439 AND giet1.intercompany_id = gel.intercompany_id
1443
1440 AND giet1.line_item_id = gel.line_item_id) ;
1441
1442 l_no_rows := NVL(SQL%ROWCOUNT,0);
1444 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1445 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1446 FND_MESSAGE.Set_Token('NUM',TO_CHAR(l_no_rows));
1447 FND_MESSAGE.Set_Token('TABLE','GCS_INTERCO_ELM_TRX');
1448
1449 FND_LOG.String (fnd_log.level_procedure,
1450 g_pkg_name || '.' || l_api_name,
1451 'SHRD0117: '||FND_MESSAGE.get);
1452 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
1453 END IF;
1454 END IF; -- end of x_intercompany_org_code if
1455
1456 ELSE
1457 -- This is for LOB_REPORTING_ENABLED flag is N
1458 -- Regular matching by organization.
1459 IF (x_intercompany_org_code = 'SPECIFIC_VALUE') THEN
1460 l_no_rows := 0;
1461
1462 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1463 fnd_log.STRING (fnd_log.level_procedure,
1464 g_pkg_name || '.' || l_api_name,
1465 'Inserting intercompany transactions for matching by'
1466 || ' organization into GCS_INTERCO_ELM_TRX'
1467 || ' - LOB REPORTING Disabled');
1468 END IF;
1469
1470 Insert INTO gcs_interco_elm_trx
1471 (hierarchy_id, cal_period_id, company_cost_center_org_id,
1472 src_entity_id, src_company_id, src_cost_center_id,
1473 intercompany_id, target_company_id,
1474 target_cost_center_id, target_entity_id,
1475 currency_code, line_item_id, financial_elem_id,
1476 product_id, natural_account_id, channel_id,
1477 project_id, customer_id, task_id,
1478 user_dim1_id, user_dim2_id, user_dim3_id,
1479 user_dim4_id, user_dim5_id, user_dim6_id,
1480 user_dim7_id, user_dim8_id, user_dim9_id,
1481 user_dim10_id,creation_date,
1482 created_by, last_update_date, last_updated_by,
1483 last_update_login)
1484
1485 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
1486 gel.company_cost_center_org_id,
1487 geo1.entity_id, NULL, NULL, gel.intercompany_id,
1488 NULL,NULL, geo.entity_id, geh.currency_code,
1489 gel.line_item_id,
1490 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1491 SYSDATE, g_fnd_user_id,
1492 SYSDATE, g_fnd_user_id,
1493 g_fnd_login_id
1494 FROM GCS_ENTRY_HEADERS geh,
1495 GCS_ENTRY_LINES gel,
1496 GCS_ENTITY_CCTR_ORGS geo,
1497 GCS_ENTITY_CCTR_ORGS geo1,
1498 GCS_CONS_RELATIONSHIPS gcr,
1499 GCS_CONS_RELATIONSHIPS gcr1
1500 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
1501 AND geh.entry_id = gel.entry_id
1502 AND gel.intercompany_id <> x_specific_intercompany_id
1503 AND gel.intercompany_id =
1504 geo.company_cost_center_org_id
1505 AND gel.company_cost_center_org_id =
1506 geo1.company_cost_center_org_id
1507 AND geh.hierarchy_id = gcr.hierarchy_id
1508 AND (p_period_end_date
1509 BETWEEN NVL(gcr.start_date, p_period_end_date)
1510 AND NVL(gcr.end_date, p_period_end_date))
1511 AND gcr.child_entity_id = geo.entity_id
1512 AND gcr.actual_ownership_flag ='Y'
1513 AND gcr.dominant_parent_flag = 'Y'
1514 AND geh.hierarchy_id = gcr1.hierarchy_id
1515 AND (p_period_end_date
1516 BETWEEN NVL(gcr1.start_date, p_period_end_date)
1517 AND NVL(gcr.end_date, p_period_end_date))
1518 AND gcr1.child_entity_id = geo1.entity_id
1519 AND gcr1.actual_ownership_flag ='Y'
1520 AND gcr1.dominant_parent_flag = 'Y'
1521
1522 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
1523 WHERE giet1.hierarchy_id = geh.hierarchy_id
1524 AND giet1.cal_period_id = geh.start_cal_period_id
1525 AND giet1.company_cost_center_org_id =
1526 gel.company_cost_center_org_id
1527 AND giet1.src_entity_id = geo1.entity_id
1528 AND giet1.target_entity_id = geo.entity_id
1529 AND giet1.intercompany_id = gel.intercompany_id
1530
1531 AND giet1.line_item_id = gel.line_item_id);
1532
1533 l_no_rows := NVL(SQL%ROWCOUNT,0);
1534
1535 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1536 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1537 FND_MESSAGE.Set_Token('NUM',TO_CHAR(l_no_rows));
1538 FND_MESSAGE.Set_Token('TABLE','GCS_INTERCO_ELM_TRX');
1539
1540 FND_LOG.String (fnd_log.level_procedure,
1541 g_pkg_name || '.' || l_api_name,
1542 'SHRD0117: '||FND_MESSAGE.get);
1543 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
1544 END IF;
1545
1546 ELSE
1547 l_no_rows := 0;
1548
1549 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1550 fnd_log.STRING (fnd_log.level_procedure,
1554 || ' - LOB REPORTING Disabled');
1551 g_pkg_name || '.' || l_api_name,
1552 'Inserting intercompany transactions for matching by'
1553 || ' organization into GCS_INTERCO_ELM_TRX'
1555 END IF;
1556
1557 Insert INTO gcs_interco_elm_trx
1558 (hierarchy_id, cal_period_id, company_cost_center_org_id,
1559 src_entity_id, src_company_id, src_cost_center_id,
1560 intercompany_id, target_company_id,
1561 target_cost_center_id, target_entity_id,
1562 currency_code, line_item_id, financial_elem_id,
1563 product_id, natural_account_id, channel_id,
1564 project_id, customer_id, task_id,
1565 user_dim1_id, user_dim2_id, user_dim3_id,
1566 user_dim4_id, user_dim5_id, user_dim6_id,
1567 user_dim7_id, user_dim8_id, user_dim9_id,
1568 user_dim10_id,creation_date,
1569 created_by, last_update_date, last_updated_by,
1570 last_update_login)
1571
1572 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
1573 gel.company_cost_center_org_id,
1574 geo1.entity_id, NULL, NULL, gel.intercompany_id,
1575 NULL,NULL, geo.entity_id, geh.currency_code,
1576 gel.line_item_id,
1577 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1578 SYSDATE, g_fnd_user_id,
1579 SYSDATE, g_fnd_user_id,
1580 g_fnd_login_id
1581 FROM GCS_ENTRY_HEADERS geh,
1582 GCS_ENTRY_LINES gel,
1583 GCS_ENTITY_CCTR_ORGS geo,
1584 GCS_ENTITY_CCTR_ORGS geo1,
1585 GCS_CONS_RELATIONSHIPS gcr,
1586 GCS_CONS_RELATIONSHIPS gcr1
1587 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
1588 AND geh.entry_id = gel.entry_id
1589 AND gel.intercompany_id <> gel.company_cost_center_org_id
1590 AND gel.intercompany_id =
1591 geo.company_cost_center_org_id
1592 AND gel.company_cost_center_org_id =
1593 geo1.company_cost_center_org_id
1594 AND geh.hierarchy_id = gcr.hierarchy_id
1595 AND (p_period_end_date
1596 BETWEEN NVL(gcr.start_date, p_period_end_date)
1597 AND NVL(gcr.end_date, p_period_end_date))
1598 AND gcr.child_entity_id = geo.entity_id
1599 AND gcr.actual_ownership_flag ='Y'
1600 AND gcr.dominant_parent_flag = 'Y'
1601 AND geh.hierarchy_id = gcr1.hierarchy_id
1602 AND (p_period_end_date
1603 BETWEEN NVL(gcr1.start_date, p_period_end_date)
1604 AND NVL(gcr1.end_date, p_period_end_date))
1605 AND gcr1.child_entity_id = geo1.entity_id
1606 AND gcr1.actual_ownership_flag ='Y'
1607 AND gcr1.dominant_parent_flag = 'Y'
1608
1609 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
1610 WHERE giet1.hierarchy_id = geh.hierarchy_id
1611 AND giet1.cal_period_id = geh.start_cal_period_id
1612 AND giet1.company_cost_center_org_id =
1613 gel.company_cost_center_org_id
1614 AND giet1.src_entity_id = geo1.entity_id
1615 AND giet1.target_entity_id = geo.entity_id
1616 AND giet1.intercompany_id = gel.intercompany_id
1617
1618 AND giet1.line_item_id = gel.line_item_id);
1619
1620 l_no_rows := NVL(SQL%ROWCOUNT,0);
1621
1622 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1623 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1624 FND_MESSAGE.Set_Token('NUM',TO_CHAR(l_no_rows));
1625 FND_MESSAGE.Set_Token('TABLE','GCS_INTERCO_ELM_TRX');
1626
1627 FND_LOG.String (fnd_log.level_procedure,
1628 g_pkg_name || '.' || l_api_name,
1629 'SHRD0117: '||FND_MESSAGE.get);
1630 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
1631 END IF;
1632 END IF; -- end of x_intercompany_org_code if
1633 END IF; -- End of LOB_REPORTING_ENABLED IF clause.
1634
1635 ELSIF (x_match_rule_code = 'COMPANY') THEN
1636
1637 if (x_intercompany_org_code = 'SPECIFIC_VALUE') THEN
1638
1639 l_no_rows := 0;
1640 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1641 FND_LOG.String (fnd_log.level_procedure,
1642 g_pkg_name || '.' || l_api_name,
1643 'Specific_Value - '
1644 ||' Inserting intercompany transactions for matching by'
1645 ||' company intercompany into GCS_INTERCO_ELM_TRX');
1646 END IF;
1647
1648 Insert /* PARALLEL ( GCS_INTERCO_ELM_TRX) */
1649 INTO gcs_interco_elm_trx
1650 (hierarchy_id, cal_period_id, company_cost_center_org_id,
1651 src_entity_id, src_company_id, src_cost_center_id,
1652 intercompany_id, target_company_id,
1653 target_cost_center_id, target_entity_id,
1654 currency_code, line_item_id, financial_elem_id,
1655 product_id, natural_account_id, channel_id,
1656 project_id, customer_id, task_id,
1657 user_dim1_id, user_dim2_id, user_dim3_id,
1658 user_dim4_id, user_dim5_id, user_dim6_id,
1659 user_dim7_id, user_dim8_id, user_dim9_id,
1663
1660 user_dim10_id,creation_date,
1661 created_by, last_update_date, last_updated_by,
1662 last_update_login)
1664 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
1665 gel.company_cost_center_org_id,
1666 geo1.entity_id,fcoa2.dim_attribute_numeric_member, NULL,
1667 gel.intercompany_id,
1668 fcoa3.dim_attribute_numeric_member, NULL, geo.entity_id,
1669 geh.currency_code, gel.line_item_id,
1670
1671 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1672 SYSDATE, g_fnd_user_id,
1673 SYSDATE, g_fnd_user_id,
1674 g_fnd_login_id
1675 FROM GCS_ENTRY_HEADERS geh,
1676 GCS_ENTRY_LINES gel,
1677 GCS_ENTITY_CCTR_ORGS geo,
1678 GCS_ENTITY_CCTR_ORGS geo1,
1679 GCS_CONS_RELATIONSHIPS gcr,
1680 GCS_CONS_RELATIONSHIPS gcr1,
1681 fem_cctr_orgs_attr fcoa2,
1682 fem_cctr_orgs_attr fcoa3
1683 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
1684 AND geh.entry_id = gel.entry_id
1685 AND gel.intercompany_id <> x_specific_intercompany_id
1686 AND gel.intercompany_id =
1687 geo.company_cost_center_org_id
1688 AND gel.company_cost_center_org_id =
1689 geo1.company_cost_center_org_id
1690 AND geh.hierarchy_id = gcr.hierarchy_id
1691 AND (p_period_end_date
1692 BETWEEN NVL(gcr.start_date, p_period_end_date )
1693 AND NVL(gcr.end_date, p_period_end_date ))
1694 AND gcr.child_entity_id = geo.entity_id
1695 AND gcr.actual_ownership_flag ='Y'
1696 AND gcr.dominant_parent_flag = 'Y'
1697 AND gel.company_cost_center_org_id =
1698 fcoa2.company_cost_center_org_id
1699 AND geh.hierarchy_id = gcr1.hierarchy_id
1700 AND (p_period_end_date
1701 BETWEEN NVL(gcr1.start_date, p_period_end_date )
1702 AND NVL(gcr1.end_date, p_period_end_date ))
1703 AND gcr1.child_entity_id = geo1.entity_id
1704 AND gcr1.actual_ownership_flag ='Y'
1705 AND gcr1.dominant_parent_flag = 'Y'
1706
1707 AND fcoa2.attribute_id =
1708 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').attribute_id
1709 AND fcoa2.version_id =
1710 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').version_id
1711 AND gel.intercompany_id = fcoa3.company_cost_center_org_id
1712 AND fcoa3.attribute_id =
1713 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').attribute_id
1714 AND fcoa3.version_id =
1715 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').version_id
1716
1717
1718 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
1719 WHERE giet1.hierarchy_id = geh.hierarchy_id
1720 AND giet1.cal_period_id = geh.start_cal_period_id
1721 AND giet1.company_cost_center_org_id =
1722 gel.company_cost_center_org_id
1723 AND giet1.src_company_id =
1724 fcoa2.dim_attribute_numeric_member
1725 AND giet1.src_entity_id = geo1.entity_id
1726 AND giet1.target_entity_id = geo.entity_id
1727 AND giet1.target_company_id =
1728 fcoa3.dim_attribute_numeric_member
1729 AND giet1.intercompany_id = gel.intercompany_id
1730
1731 AND giet1.line_item_id = gel.line_item_id);
1732
1733 l_no_rows := NVL(SQL%ROWCOUNT,0);
1734
1735 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1736 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1737 FND_MESSAGE.Set_Token('NUM',TO_CHAR(l_no_rows));
1738 FND_MESSAGE.Set_Token('TABLE','GCS_INTERCO_ELM_TRX');
1739
1740 FND_LOG.String (fnd_log.level_procedure,
1741 g_pkg_name || '.' || l_api_name,
1742 'SHRD0117: '||FND_MESSAGE.get);
1743 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
1744
1745 END IF;
1746
1747
1748
1749 ELSE
1750
1751 l_no_rows := 0;
1752 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1753 FND_LOG.String (fnd_log.level_procedure,
1754 g_pkg_name || '.' || l_api_name,
1755 ' Org and Interco are different - '
1756 ||'Inserting intercompany transactions for matching by'
1757 ||' company intercompany into GCS_INTERCO_ELM_TRX');
1758 END IF;
1759
1760 Insert /* PARALLEL ( GCS_INTERCO_ELM_TRX) */
1761 INTO gcs_interco_elm_trx
1762 (hierarchy_id, cal_period_id, company_cost_center_org_id,
1763 src_entity_id, src_company_id, src_cost_center_id,
1764 intercompany_id, target_company_id,
1765 target_cost_center_id, target_entity_id,
1766 currency_code, line_item_id, financial_elem_id,
1767 product_id, natural_account_id, channel_id,
1768 project_id, customer_id, task_id,
1769 user_dim1_id, user_dim2_id, user_dim3_id,
1770 user_dim4_id, user_dim5_id, user_dim6_id,
1771 user_dim7_id, user_dim8_id, user_dim9_id,
1772 user_dim10_id,creation_date,
1773 created_by, last_update_date, last_updated_by,
1774 last_update_login)
1775
1776 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
1777 gel.company_cost_center_org_id,
1778 geo1.entity_id,fcoa2.dim_attribute_numeric_member, NULL,
1779 gel.intercompany_id,
1780 fcoa3.dim_attribute_numeric_member, NULL, geo.entity_id,
1781 geh.currency_code, gel.line_item_id,
1782
1783 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1784 SYSDATE, g_fnd_user_id,
1785 SYSDATE, g_fnd_user_id,
1786 g_fnd_login_id
1787 FROM GCS_ENTRY_HEADERS geh,
1788 GCS_ENTRY_LINES gel,
1789 GCS_ENTITY_CCTR_ORGS geo,
1790 GCS_ENTITY_CCTR_ORGS geo1,
1791 GCS_CONS_RELATIONSHIPS gcr,
1792 GCS_CONS_RELATIONSHIPS gcr1,
1793 fem_cctr_orgs_attr fcoa2,
1794 fem_cctr_orgs_attr fcoa3
1795 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
1796 AND geh.entry_id = gel.entry_id
1797 AND gel.intercompany_id =
1798 geo.company_cost_center_org_id
1799 AND gel.company_cost_center_org_id =
1800 geo1.company_cost_center_org_id
1801 AND geh.hierarchy_id = gcr.hierarchy_id
1802 AND (p_period_end_date
1803 BETWEEN NVL(gcr.start_date, p_period_end_date )
1804 AND NVL(gcr.end_date, p_period_end_date ))
1805 AND gcr.child_entity_id = geo.entity_id
1806 AND gcr.actual_ownership_flag ='Y'
1807 AND gcr.dominant_parent_flag = 'Y'
1808 AND geh.hierarchy_id = gcr1.hierarchy_id
1809 AND (p_period_end_date
1810 BETWEEN NVL(gcr1.start_date, p_period_end_date )
1811 AND NVL(gcr1.end_date, p_period_end_date ))
1812 AND gcr1.child_entity_id = geo1.entity_id
1813 AND gcr1.actual_ownership_flag ='Y'
1814 AND gcr1.dominant_parent_flag = 'Y'
1815 AND gel.company_cost_center_org_id =
1816 fcoa2.company_cost_center_org_id
1817
1818 AND fcoa2.attribute_id =
1819 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').attribute_id
1820 AND fcoa2.version_id =
1821 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').version_id
1822 AND gel.intercompany_id = fcoa3.company_cost_center_org_id
1823 AND fcoa3.attribute_id =
1824 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').attribute_id
1825 AND fcoa3.version_id =
1826 gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').version_id
1827 AND fcoa3.dim_attribute_numeric_member <>
1828 fcoa2.dim_attribute_numeric_member
1829
1830
1831 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
1832 WHERE giet1.hierarchy_id = geh.hierarchy_id
1833 AND giet1.cal_period_id = geh.start_cal_period_id
1834 AND giet1.company_cost_center_org_id =
1835 gel.company_cost_center_org_id
1836 AND giet1.src_company_id =
1837 fcoa2.dim_attribute_numeric_member
1838 AND giet1.src_entity_id = geo1.entity_id
1839 AND giet1.target_entity_id = geo.entity_id
1840 AND giet1.target_company_id =
1841 fcoa3.dim_attribute_numeric_member
1842 AND giet1.intercompany_id = gel.intercompany_id
1843
1844 AND giet1.line_item_id = gel.line_item_id);
1845
1846 l_no_rows := NVL(SQL%ROWCOUNT,0);
1847
1848 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1849 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1850 FND_MESSAGE.Set_Token('NUM',TO_CHAR(l_no_rows));
1851 FND_MESSAGE.Set_Token('TABLE','GCS_INTERCO_ELM_TRX');
1852
1853 FND_LOG.String (fnd_log.level_procedure,
1854 g_pkg_name || '.' || l_api_name,
1855 'SHRD0117: '||FND_MESSAGE.get);
1856 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
1857
1858 END IF;
1859 END IF; -- Ends Company if specific value..
1860 END IF; -- Ends If matching by
1861
1862
1863 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1864 THEN
1865
1866 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1867 g_pkg_name || '.' || l_api_name,
1868 GCS_UTILITY_PKG.g_module_success || l_api_name ||
1869 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1870 END IF;
1871
1872 COMMIT;
1873
1874 EXCEPTION
1875
1876
1877 WHEN NO_MATCH_RULE_CODE THEN
1878
1879 x_errbuf := SQLERRM;
1880
1881 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1882 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1883 g_pkg_name || '.' || l_api_name
1884 ||' NO_MATCH_RULE_CODE',
1885 SUBSTR(SQLERRM, 1, 255));
1886
1887
1888 END IF;
1889
1890 x_retcode := 2;
1891
1892
1893 WHEN Hierarchy_Check_Failed THEN
1894
1895 x_errbuf := SQLERRM;
1896
1897 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1898 THEN
1899 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1900 g_pkg_name || '.' || l_api_name
1901 || ' Hierarchy_Check_Failed',
1902 'Either hierarchy does not exist or the '
1903 ||' hierarchy date affectivity has not been '
1904 || ' passed ');
1905
1906
1907 END IF;
1908
1909 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1910 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1911 g_pkg_name || '.' || l_api_name,
1912 SUBSTR(SQLERRM, 1, 255));
1913
1914
1915 END IF;
1916
1917 x_retcode := 2;
1918
1919
1920
1921
1922 WHEN OTHERS THEN
1923
1924 x_errbuf := SQLERRM;
1925
1926 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1927 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1928 g_pkg_name || '.' || l_api_name,
1929 SUBSTR(SQLERRM, 1, 255));
1930 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1931 g_pkg_name || '.' || l_api_name,
1932 GCS_UTILITY_PKG.g_module_failure || l_api_name ||
1933 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1934 END IF;
1935
1936 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure
1937 -- ||l_api_name || to_char(sysdate
1938 -- , ' DD-MON-YYYY HH:MI:SS'));
1939 x_retcode := 2;
1940
1941 RAISE;
1942
1943
1944 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1945
1946 fnd_log.STRING (fnd_log.level_procedure,
1947 g_pkg_name || '.' || l_api_name,
1948 gcs_utility_pkg.g_module_success
1949 || ' '
1950 || l_api_name
1951 || '() '
1952 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1953 );
1954 END IF;
1955
1956 END INSERT_INTERCO_TRX;
1957
1958 END GCS_INTERCO_DYNAMIC_PKG;