AR Receipt Journal

SELECT
       &lp_company_seg                                       C_COMPANY_SEG,
       DECODE(:p_order_by,
             'Type', st.meaning, &lp_accounting_flex)     C_FLEX_OR_STATUS_1,
        DECODE(:p_order_by,
               'Type', &lp_accounting_flex, st.meaning)     C_FLEX_OR_STATUS_2,
        cr.cash_receipt_id                                    C_CASH_RECEIPT_ID,
        rc.name                                               C_RECEIPT_CLASS,
        rm.name                                               C_PAYMENT_METHOD,
        cr.receipt_number                                     C_RECEIPT_NUM,
        substrb(party.party_name,1,50)                                       C_CUSTOMER_NAME,
        cust_acct.account_number                                     C_CUSTOMER_NUM,
        site_uses.location                                           C_LOCATION,
        cr.doc_sequence_value                       C_DOCUMENT_NUM,
         &LP_NAME 		C_BATCH,
        &LP_TRXDATE                      C_RECEIPT_DATE,
        &LP_GLDATE		C_GL_DATE,
        &C_BAL_OR_TRANS_AMOUNT              C_AMOUNT,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_company_seg_name', 'SQLGL', 'GL#', gc.CHART_OF_ACCOUNTS_ID, NULL, gc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'DESCRIPTION') C_COMPANY_SEG_NAME,
	--&C_COMPANY_LPROMPT C_COMPANY_LPROMPT,
	DECODE(:p_order_by,
             'Type', st.meaning, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_fos1_disp', 'SQLGL', 'GL#', gc.CHART_OF_ACCOUNTS_ID, NULL, gc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'))     c_fos1_disp,
        DECODE(:p_order_by,
               'Type', fnd_flex_xml_publisher_apis.process_kff_combination_1('c_fos2_disp', 'SQLGL', 'GL#', gc.CHART_OF_ACCOUNTS_ID, NULL, gc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'), st.meaning)     c_fos2_disp
	--fnd_flex_xml_publisher_apis.process_kff_combination_1('c_fos1_disp', 'SQLGL', 'GL#', gc.CHART_OF_ACCOUNTS_ID, NULL, gc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FOS1_DISP,
	--fnd_flex_xml_publisher_apis.process_kff_combination_1('c_fos2_disp', 'SQLGL', 'GL#', gc.CHART_OF_ACCOUNTS_ID, NULL, gc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FOS2_DISP,
--ADDED AS FIX
	,AR_ARXRJR_XMLP_PKG.F_ACC_MESSAGEFormatTrigger()
FROM    &p_hist crh,
        &p_dist  d,
        &p_gl gc,
        &p_batch b,
        &p_cash cr,
        &p_cust cust_acct,
        &p_party party,
        &p_site site_uses,
        &p_rm rm,
        &p_rc rc,
        &p_look st
 WHERE   crh.batch_id = b.batch_id(+)
 AND    crh.cash_receipt_id = cr.cash_receipt_id
 AND    crh.cash_receipt_history_id = d.source_id
 AND    d.source_table = 'CRH'
 AND    cr.pay_from_customer = cust_acct.cust_account_id(+)
 AND    cust_acct.party_id = party.party_id(+)
 AND    cr.customer_site_use_id = site_uses.site_use_id(+)
 AND    cr.receipt_method_id = rm.receipt_method_id
 AND    rm.receipt_class_id = rc.receipt_class_id
 AND    st.lookup_type = 'DISTRIBUTION_SOURCE_TYPE'
 AND    st.lookup_code = d.source_type
 AND    gc.code_combination_id = d.code_combination_id
 AND    NVL(cr.org_id,-99) = NVL(crh.org_id,-99) and NVL(cr.org_id,-99)=NVL(d.org_id,-99) and
      nvl(cr.confirmed_flag,'Y') = 'Y'
 AND NVL(crh.org_id,-99)=NVL(b.org_id(+),-99)
&p_cr_where
&p_cust_where
&p_site_where
&LP_GL_DATE_LOW
&LP_GL_DATE_HIGH
&LP_SOURCE_TYPE
&LP_RECEIPT_CLASS
&LP_PAYMENT_METHOD
&lp_customer_name_low
&lp_customer_name_high
&lp_customer_num_low
&lp_customer_num_high
&lp_company_low
&lp_company_high
&lp_account_low
&lp_account_high
&LP_CURRENCY
&LP_GROUP_BY
&C_HAVING
ORDER BY 1 ASC,2 ASC,3 ASC,5 ASC,6 ASC,8 ASC,9 ASC,10 ASC ,cr.receipt_number
&lp_order_by
--ADDED AS FIX