XLA Trading Partner Summary

SELECT
    gcc.code_combination_id    code_combination_id          
    ,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')         accounting_flexfield
    ,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION')           accounting_flexfield_desc
     &p_party_col
    ,HPS.party_site_name      party_site_name
    ,MAX(DECODE(GPS.effective_period_num, XLA_TP_SUMMARY_RPT_PKG.min_period(GPS.effective_period_num), NVL(XCB.beginning_balance_dr,0), 0))begin_balance_dr
    ,MAX(DECODE(GPS.effective_period_num, XLA_TP_SUMMARY_RPT_PKG.min_period(GPS.effective_period_num), NVL(XCB.beginning_balance_cr,0), 0))begin_balance_cr
    ,MAX(DECODE(GPS.effective_period_num, XLA_TP_SUMMARY_RPT_PKG.max_period(GPS.effective_period_num), (NVL(XCB.beginning_balance_dr,0)+ NVL(XCB.period_balance_dr,0)), 0)) -
     MAX(DECODE(GPS.effective_period_num, XLA_TP_SUMMARY_RPT_PKG.min_period(GPS.effective_period_num), NVL(XCB.beginning_balance_dr,0), 0)) period_net_dr
    ,MAX(DECODE(GPS.effective_period_num, XLA_TP_SUMMARY_RPT_PKG.max_period(GPS.effective_period_num), (NVL(XCB.beginning_balance_cr,0)+ NVL(XCB.period_balance_cr,0)), 0)) -
     MAX(DECODE(GPS.effective_period_num, XLA_TP_SUMMARY_RPT_PKG.min_period(GPS.effective_period_num), NVL(XCB.beginning_balance_cr,0), 0)) period_net_cr	
FROM
    xla_control_balances           XCB
    ,gl_code_combinations          GCC
    ,gl_period_statuses            GPS
    ,hz_parties                    HZP
    ,hz_party_sites                HPS
     &p_party_tab	
WHERE
    XCB.ledger_id                  = :P_LEDGER_ID
AND XCB.application_id             = :P_RESP_APPLICATION_ID
AND XCB.period_name                = GPS.period_name
AND XCB.application_id             = GPS.application_id
AND XCB.ledger_id                  = GPS.ledger_id
AND GPS.effective_period_num BETWEEN :P_PERIOD_NUM_FROM AND :P_PERIOD_NUM_TO
AND GCC.code_combination_id        = XCB.code_combination_id
    &p_party_join
	&p_party_name_join
AND &ACCT_FLEX_WHERE_CLAUSE
GROUP BY
    gcc.code_combination_id
    ,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')         
    ,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION')          
    &p_party_group
    ,HPS.party_site_name