AR Sales Journal by GL Account Report- Obsoleted

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

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