AR Sales Journal by GL Account Report- Obsoleted
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Sales Journal by GL Account
Application: Receivables
Source: Sales Journal by GL Account Report (XML) - Obsoleted
Short Name: RAXGLR_XML
DB package: AR_RAXGLR_XMLP_PKG
Description: Sales Journal by GL Account
Application: Receivables
Source: Sales Journal by GL Account Report (XML) - Obsoleted
Short Name: RAXGLR_XML
DB package: AR_RAXGLR_XMLP_PKG
SELECT gl_dist.code_combination_id code_combination_id, gl_dist.account_class gl_acc_type, null segment, trx.trx_number trx_number, null balancing_seg, ARPT_SQL_FUNC_UTIL.get_trx_type_details(trx.cust_trx_type_id,'NAME') trx_type, null account_seg, substrb(party.party_name,1,50) customer_name, cust_acct.account_number customer_number, gl_dist.gl_date gl_date, ARPT_SQL_FUNC_UTIL.get_trx_type_details(trx.cust_trx_type_id,'TYPE') cust_trx_type, sum(decode(sign(gl_dist.acctd_amount), -1, NULL, gl_dist.acctd_amount ) ) func_credit_amount, sum(decode(sign(gl_dist.acctd_amount), -1, gl_dist.acctd_amount * -1, NULL) ) func_debit_amount, decode(:p_currency_code, NULL, 0, (sum(decode(sign(gl_dist.amount), 1, round(gl_dist.amount, 2), 0) ) ) ) curr_credit_amount, decode(:p_currency_code, NULL, 0, (sum(decode(sign(gl_dist.amount), -1, -round(gl_dist.amount, 2), 0) ) ) ) curr_debit_amount , trx.invoice_currency_code currency_code_rev, &lp_query_show_bill cons_billing_number, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_segment', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') D_segment, AR_RAXGLR_XMLP_PKG.set_curr_code_revformula(trx.invoice_currency_code) Set_Curr_Code_Rev, AR_RAXGLR_XMLP_PKG.trx_number_consformula(trx.trx_number, trx.trx_number) trx_number_cons FROM hz_cust_accounts_all cust_acct, hz_parties party, &lp_ra_customer_trx_all trx, gl_code_combinations gl, &lp_table_show_bill &lp_ra_cust_trx_gl_dist_all gl_dist WHERE trx.complete_flag = 'Y' &P_WHERE_1 and gl_dist.account_set_flag = 'N' and &WHERE_GL_FLEX_CLAUSE &LP_START_GL_DATE &LP_END_GL_DATE and gl_dist.customer_trx_id = trx.customer_trx_id and gl_dist.set_of_books_id = :set_of_books_id and gl_dist.account_class = :gl_type and :gl_type not in ('TAX', 'REC', 'FREIGHT') and nvl( gl_dist.gl_posted_date, TO_DATE('01/01/0001','MM/DD/YYYY')) = decode(:p_posting_status, 'POSTED',gl_dist.gl_posted_date, 'UNPOSTED',TO_DATE('01/01/0001','MM/DD/YYYY'), nvl(gl_dist.gl_posted_date,TO_DATE('01/01/0001','MM/DD/YYYY'))) and cust_acct.cust_account_id = trx.bill_to_customer_id and cust_acct.party_id = party.party_id &lp_where_show_bill and gl.code_combination_id = gl_dist.code_combination_id &LP_TRX_DATE_LOW &LP_TRX_DATE_HIGH &LP_TRX_TYPE_LOW &LP_TRX_TYPE_HIGH &LP_TRX_NUMBER_LOW &LP_TRX_NUMBER_HIGH &LP_CUSTOMER_LOW &LP_CUSTOMER_HIGH &LP_CUSTOMER_NUMBER_LOW &LP_CUSTOMER_NUMBER_HIGH &LP_ZERO_ROUND &P_ORG_WHERE_CUST_ACCT &P_ORG_WHERE_TRX &P_ORG_WHERE_PS &P_ORG_WHERE_CI &P_ORG_WHERE_GL_DIST group by gl_dist.account_class, gl_dist.code_combination_id, &ACCT_FLEX_ALL_SEG, trx.trx_number, &ACCT_FLEX_BAL_SEG, ARPT_SQL_FUNC_UTIL.get_trx_type_details(trx.cust_trx_type_id,'NAME'), &ACCT_FLEX_ACCT_SEG, party.party_name, cust_acct.account_number, gl_dist.gl_date, ARPT_SQL_FUNC_UTIL.get_trx_type_details(trx.cust_trx_type_id,'TYPE'), trx.invoice_currency_code, &lp_query_show_bill,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_segment', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') order by 3 ASC ,&ACCT_FLEX_BAL_SEG, &ACCT_FLEX_ACCT_SEG, &ACCT_FLEX_ALL_SEG, decode(upper(:p_sort_by), 'CUSTOMER',party.party_name, 'INVOICE NUMBER', trx.trx_number, null ), gl_dist.gl_date |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Reporting Level | LOV Oracle | ||
Reporting Context | LOV Oracle | ||
Order By | LOV Oracle | ||
GL Account Low | Char | ||
GL Account High | Char | ||
GL Date From | Date | ||
GL Date To | Date | ||
Entered Currency | LOV Oracle | ||
GL Account Type | LOV Oracle | ||
Posting Status | LOV Oracle | ||
Customer Name Low | LOV Oracle | ||
Customer Name High | LOV Oracle | ||
Customer Number Low | LOV Oracle | ||
Customer Number High | LOV Oracle | ||
Transaction Date From | Date | ||
Transaction Date To | Date | ||
Transaction Type Low | LOV Oracle | ||
Transaction Type High | LOV Oracle | ||
Transaction Number Low | LOV Oracle | ||
Transaction Number High | LOV Oracle | ||
Exclude Rounding equals zero | LOV Oracle |