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 |
