JG General Ledger Journal and Balance - draft

Description
Categories: BI Publisher
Application: Regional Localizations
Source: General Ledger Journal and Balance Report (XML) - Not Supported: Reserved For Future Use
Short Name: JGZZGLJRB_XML
DB package: JG_JGZZGLJRB_XMLP_PKG
SELECT a.*,
	JG_JGZZGLJRB_XMLP_PKG.legal_entity_nameformula(a.LEGAL_ENTITY_ID, :LEGAL_ENTITY_NAME) LEGAL_ENTITY_NAME, 
	JG_JGZZGLJRB_XMLP_PKG.le_address_line_1formula(a.LEGAL_ENTITY_ID, :LE_ADDRESS_LINE_1) LE_ADDRESS_LINE_1, 
	JG_JGZZGLJRB_XMLP_PKG.le_address_line_2formula(a.LEGAL_ENTITY_ID, :LE_ADDRESS_LINE_2) LE_ADDRESS_LINE_2, 
	JG_JGZZGLJRB_XMLP_PKG.le_address_line_3formula(a.LEGAL_ENTITY_ID, :LE_ADDRESS_LINE_3) LE_ADDRESS_LINE_3, 
	JG_JGZZGLJRB_XMLP_PKG.le_cityformula(a.LEGAL_ENTITY_ID, :LE_CITY) LE_CITY, 
	JG_JGZZGLJRB_XMLP_PKG.le_postal_codeformula(a.LEGAL_ENTITY_ID, :LE_POSTAL_CODE) LE_POSTAL_CODE, 
	JG_JGZZGLJRB_XMLP_PKG.le_registration_numberformula(a.LEGAL_ENTITY_ID, :LE_REGISTRATION_NUMBER) LE_REGISTRATION_NUMBER, 
	JG_JGZZGLJRB_XMLP_PKG.le_activity_codeformula(a.LEGAL_ENTITY_ID, :LE_ACTIVITY_CODE) LE_ACTIVITY_CODE,
	JG_JGZZGLJRB_XMLP_PKG.acct_level10_valueformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL10_VALUE, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level10_value_descformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL10_VALUE_DESC, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level9_valueformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL9_VALUE, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level9_value_descformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL9_VALUE_DESC, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level8_valueformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL8_VALUE, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level8_value_descformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL8_VALUE_DESC, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level7_valueformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL7_VALUE, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level7_value_descformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL7_VALUE_DESC, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level6_valueformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL6_VALUE, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level6_value_descformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL6_VALUE_DESC, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level5_valueformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL5_VALUE, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level5_value_descformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL5_VALUE_DESC, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level4_valueformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL4_VALUE, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level4_value_descformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL4_VALUE_DESC, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level3_valueformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL3_VALUE, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level3_value_descformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL3_VALUE_DESC, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level2_valueformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL2_VALUE, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level2_value_descformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL2_VALUE_DESC, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level1_valueformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL1_VALUE, 
	JG_JGZZGLJRB_XMLP_PKG.acct_level1_value_descformula(a.segment1, a.ACCT_VALUE_DESC) ACCT_LEVEL1_VALUE_DESC
from
		 (SELECT 
    &SELECT_LE LEGAL_ENTITY_ID,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('acct', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'N', 'VALUE') ACCT,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_value', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE') ACCT_VALUE,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('balancing_segment_value', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE') BALANCING_SEGMENT_VALUE,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_value', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'VALUE') COST_CENTER_VALUE,
    &INTERCOMPANY_SEGMENT_VALUE INTERCOMPANY_SEGMENT_VALUE,
    &MANAGEMENT_SEGMENT_VALUE MANAGEMENT_SEGMENT_VALUE,
    &SECONDARY_TRACK_SEGMENT_VALUE SECONDARY_TRACK_SEGMENT_VALUE,
    &SELECT_BEGIN_BAL BEGIN_BALANCE,
    GLBAL.PERIOD_NAME PERIOD_NAME,
    GLBAL.CODE_COMBINATION_ID CCID, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'N', 'FULL_DESCRIPTION') ACCT_DESC, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_value_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'FULL_DESCRIPTION') ACCT_VALUE_DESC, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('balancing_segment_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'FULL_DESCRIPTION') BALANCING_SEGMENT_DESC, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'FULL_DESCRIPTION') COST_CENTER_DESC, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('intercompany_segment_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_INTERCOMPANY', 'N', 'DESCRIPTION') INTERCOMPANY_SEGMENT_DESC, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('management_segment_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_MANAGEMENT', 'N', 'DESCRIPTION') MANAGEMENT_SEGMENT_DESC, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('sec_track_segment_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_SECONDARY_TRACKING', 'N', 'DESCRIPTION') SEC_TRACK_SEGMENT_DESC ,
	cc.segment1 segment1
FROM
    GL_PERIOD_STATUSES GLP,
    GL_CODE_COMBINATIONS CC,
    &FROM_LNSV
    GL_BALANCES GLBAL
WHERE GLBAL.LEDGER_ID = :P_LEDGER_ID
&BUDENC_WHERE_BAL
AND (GLBAL.PERIOD_NET_DR != 0  OR GLBAL.PERIOD_NET_CR != 0)
&CURR_WHERE_BAL
AND GLBAL.CURRENCY_CODE = :BAL_CURRENCY
AND GLBAL.ACTUAL_FLAG = :P_BALANCE_TYPE
AND GLBAL.TEMPLATE_ID IS NULL
AND GLBAL.PERIOD_NAME = GLP.PERIOD_NAME
AND GLBAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
&LE_WHERE
&PERIOD_WHERE
AND GLP.APPLICATION_ID = 101
AND GLP.LEDGER_ID = :P_LEDGER_ID
&DAS_WHERE
&SEG_SECURITY_WHERE
AND &TEMP_ACCT_WHERE
AND CC.CHART_OF_ACCOUNTS_ID = :P_COA_ID) a
Parameter Name SQL text Validation
Chart Of Accounts Id
 
LOV Oracle
Access Set ID
 
Number
User Parameter 5
 
User Parameter 4
 
User Parameter 3
 
User Parameter 2
 
User Parameter 1
 
First Page Number
 
Number
Page Number Format
 
LOV Oracle
Top Level Parent Account
 
Account Level
 
LOV Oracle
Period To
 
LOV Oracle
Period From
 
LOV Oracle
Account To
 
Account From
 
End Date
 
Date
Start Date
 
Date
Budget or Encumbrance Name
 
LOV Oracle
Balance Type
 
LOV Oracle
Entered Currency
 
LOV Oracle
Currency Type
 
LOV Oracle
Ledger Currency
 
LOV Oracle
Ledger Id
 
LOV Oracle