AR Receipt Journal

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Receipt Journal Report (XML)
Short Name: ARXRJR_XML
DB package: AR_ARXRJR_XMLP_PKG
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
Ask a question
Parameter Name SQL text Validation
Chart of accounts id
 
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Payment Method
 
LOV Oracle
Receipt Class
 
LOV Oracle
Account Type
 
LOV Oracle
Report Mode
 
LOV Oracle
Order By
 
LOV Oracle
Entered Currency
 
LOV Oracle
GL Account High
 
GL Account Low
 
Posting Status
 
LOV Oracle
GL Date To
 
Date
GL Date From
 
Date
Company Segment High
 
Company Segment Low
 
Reporting Context
 
LOV Oracle
Reporting Level
 
LOV Oracle