GCS Unmapped Data Submission Trial Balance Data Source

Description
Categories: BI Publisher
Application: Financial Consolidation Hub (Obsolete)
Source:
Short Name: GCS_DS_TB_UNMAPPED_SOURCE
DB package:
	SELECT fcov.company_cost_center_org_name ORGANIZATION,
        			   fliv.line_item_name LINEITEM,
					   /*startSelectList*/		ffeb.financial_elem_name , 		fnab.natural_account_name , 		fpb.product_name , 		fpjb.project_name , 		ftb.task_name , 		fud1.user_dim1_name ,   /*selectLiterals*//*endSelectList*/ 
					   fciv.company_cost_center_org_name INTERCOMPANY,
					   fcov.company_cost_center_org_id,
        			   fliv.line_item_id,
					   /*startSelectIdList*/		gel.financial_elem_id,    		gel.natural_account_id,    		gel.product_id,    		gel.project_id,    		gel.task_id,    		gel.user_dim1_id,      /*groupLiterals*//*endSelectIdList*/ 
					   fciv.company_cost_center_org_id INTERCOMPANY_ID,
                       decode(:source_system_code, 10, NULL, to_char(sum(gel.ytd_debit_balance_e),nvl(:format_mask,fnd_currency.safe_get_format_mask(gel.currency_code,50)))) ytd_debit_balance_e,
					   decode(:source_system_code, 10, NULL, to_char(sum(gel.ytd_credit_balance_e),nvl(:format_mask,fnd_currency.safe_get_format_mask(gel.currency_code,50)))) ytd_credit_balance_e,
                       decode(:source_system_code, 10, to_char(sum(gel.ytd_balance_e),nvl(:format_mask,fnd_currency.safe_get_format_mask(gel.currency_code,50))), NULL) ytd_balance_e,
					   decode(:source_system_code, 10, to_char(sum(gel.ytd_balance_f),nvl(:format_mask,fnd_currency.safe_get_format_mask(gel.currency_code,50))), NULL) ytd_balance_f,
					   nvl(:CURRENCY_CODE, gel.currency_code) currency_code1,
					   nvl(:CURRENCY_NAME, fct.name) currency_name1,
					   decode(:source_system_code, 10, min(creation_row_sequence), NULL) creation_row_sequence
				  FROM fem_balances gel,
                       fnd_currencies_tl	fct,		
    				   fem_cctr_orgs_tl fcov,
        			   fem_ln_items_tl fliv,
					   /*startFromList*/                  fem_fin_elems_tl ffeb,			                  fem_nat_accts_tl fnab,			                  fem_products_tl fpb,			                  fem_projects_tl fpjb,			                  fem_tasks_tl ftb,			                  FEM_USER_DIM1_TL fud1,			  /*fromLiterals*//*endFromList*/
					   fem_cctr_orgs_tl fciv
				 WHERE gel.source_system_code = :source_system_code
				   AND gel.dataset_code = :dataset_code
				   AND gel.ledger_id = :ledger_id
				   AND gel.CAL_PERIOD_ID = :cal_period_id
                   AND ( gel.FINANCIAL_ELEM_ID <> DECODE(:balance_type_code,
				                                         'ACTUAL',140, 
														 NULL
														 )
						 OR gel.FINANCIAL_ELEM_ID = DECODE(:balance_type_code,
						                                   'ACTUAL', NULL, 
														   140
														  )
					    )
                   AND DECODE(:SOURCE_SYSTEM_CODE,
				              10, '1',  
				              DECODE(:CURRENCY_TYPE_CODE,'BASE_CURRENCY',gel.currency_code,'1')
							  ) = 
				   DECODE(:SOURCE_SYSTEM_CODE,
				          10, '1',
				          DECODE(:CURRENCY_TYPE_CODE,'BASE_CURRENCY',:currency_code,'1')
						  )
                   AND DECODE(:SOURCE_SYSTEM_CODE,
				               10, gel.currency_type_code,
							   '1'
							  ) = 
					DECODE(:SOURCE_SYSTEM_CODE,
					       10, DECODE(:CURRENCY_CODE,
						              :ENTITY_CURRENCY_CODE,'ENTERED',
									  'TRANSLATED'
									  ), 
						   '1')
				   AND gel.currency_code = fct.currency_code(+)
				   AND fct.language(+) = :pLanguageCode
				   AND fcov.company_cost_center_org_id = gel.company_cost_center_org_id		
				   AND fcov.language = :pLanguageCode
				   AND fliv.line_item_id = gel.line_item_id
				   AND fliv.language = :pLanguageCode
				   /*startWhereClause*/	AND	ffeb.financial_elem_id = gel.financial_elem_id			AND	ffeb.language  = :pLanguageCode        	AND	fnab.natural_account_id = gel.natural_account_id			AND	fnab.language  = :pLanguageCode        	AND	fpb.product_id = gel.product_id			AND	fpb.language  = :pLanguageCode        	AND	fpjb.project_id = gel.project_id			AND	fpjb.language  = :pLanguageCode        	AND	ftb.task_id = gel.task_id			AND	ftb.language  = :pLanguageCode        	AND	fud1.user_dim1_id = gel.user_dim1_id			AND	fud1.language  = :pLanguageCode          /*whereLiterals*//*endWhereClause*/ 
				   AND fciv.company_cost_center_org_id = gel.intercompany_id
				   AND fciv.language = :pLanguageCode
				   AND   EXISTS  (SELECT  1  
	               FROM    fem_cctr_orgs_hier fcoh,  
	                       fem_global_vs_combo_defs fgvscd_master,  
	                       gcs_system_options gso,  
	                       fem_global_vs_combo_defs fgvscd_child,  
	                       fem_xdim_dimensions fxd,  
	                       fem_object_definition_b fodb,  
	                       gcs_entity_cctr_orgs feco  
	               WHERE   fcoh.child_id = gel.company_cost_center_org_id  
	               AND     fxd.dimension_id = 8  
	               AND     fodb.object_id = fxd.default_mvs_hierarchy_obj_id  
	               AND     fcoh.hierarchy_obj_def_id = fodb.object_definition_id  
	               AND     fgvscd_master.global_vs_combo_id = gso.fch_global_vs_combo_id  
	               AND     fgvscd_master.dimension_id = 8  
                       AND     fgvscd_child.global_vs_combo_id = :sub_global_vs_combo_id		  
	               AND     fgvscd_child.dimension_id = 8  
	               AND     fcoh.parent_value_set_id = fgvscd_master.value_set_id  
	               AND     fcoh.child_value_set_id = fgvscd_child.value_set_id  
	               AND     feco.entity_id = :entity_id  
                   AND     fcoh.parent_id = feco.company_cost_center_org_id) 
                 GROUP BY fcov.company_cost_center_org_name,
        			   fliv.line_item_name,
					   /*startDSTBGroupbyName*/		ffeb.financial_elem_name , 		fnab.natural_account_name , 		fpb.product_name , 		fpjb.project_name , 		ftb.task_name , 		fud1.user_dim1_name ,   /*selectLiterals*//*endDSTBGroupbyName*/ 
					   fciv.company_cost_center_org_name,
					   fcov.company_cost_center_org_id,
        			   fliv.line_item_id,
					   /*startDSTBGroupbyId*/		gel.financial_elem_id,    		gel.natural_account_id,    		gel.product_id,    		gel.project_id,    		gel.task_id,    		gel.user_dim1_id,      /*groupLiterals*//*endDSTBGroupbyId*/ 
					   fciv.company_cost_center_org_id,
					   fct.name,
					   gel.currency_code
				 ORDER BY fcov.company_cost_center_org_name,
        			   fliv.line_item_name,
					   /*startOrderClause*/		ffeb.financial_elem_name , 		fnab.natural_account_name , 		fpb.product_name , 		fpjb.project_name , 		ftb.task_name , 		fud1.user_dim1_name ,   /*orderLiterals*//*endOrderClause*/ 
        			   fciv.company_cost_center_org_name,
					   fct.name