AR Sales Journal by GL Account Report- Obsoleted

Description
Categories: BI Publisher, Financials
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
Chart Of Accounts Id
 
Exclude Rounding equals zero
 
LOV Oracle
Transaction Number High
 
LOV Oracle
Transaction Number Low
 
LOV Oracle
Transaction Type High
 
LOV Oracle
Transaction Type Low
 
LOV Oracle
Transaction Date To
 
Date
Transaction Date From
 
Date
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Posting Status
 
LOV Oracle
GL Account Type
 
LOV Oracle
Entered Currency
 
LOV Oracle
GL Date To
 
Date
GL Date From
 
Date
GL Account High
 
GL Account Low
 
Order By
 
LOV Oracle
Reporting Context
 
LOV Oracle
Reporting Level
 
LOV Oracle