IGI Standing Charges: Transaction History - draft

Description
Categories: BI Publisher, Financials
Application: Public Sector Financials International
Source: Standing Charges: Transaction History Report (XML) - Not Supported: Reserved For Future Use
Short Name: IGIRRTRH_XML
DB package: IGI_IGIRRTRH_XMLP_PKG
select
       fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')                               Company,
        decode(gl_dist.gl_date,
               NULL, :rp_no, :rp_yes)                    Postable,
        trx.invoice_currency_code                        Currency_A,
                sc.charge_reference  charge_ref,
        DECODE
                  (
                      type.type,
                      'INV', :lp_meaning_inv,
                      'DEP', :lp_meaning_dep,
                      'GUAR', :lp_meaning_guar,
                      'CM', :lp_meaning_cm,
                      'DM', :lp_meaning_dm,
                      'CB', :lp_meaning_cb,
                       '')                        Class,
        trx.trx_number                                   Invoice_Number,
        rtrim( rpad(type.name,8)  )                           Type,
        rtrim( rpad(party.party_name, 30)  )               Customer_Name,
        cust.account_number                             Customer_Number,
        trx.trx_date                                     Invoice_Date,
        gl_dist.gl_date         Gl_Date,
        gl_dist.amount                          Invoice_Currency,
        gl_dist.acctd_amount            Functional_Currency_A,
&lp_query_show_bill cons_billing_number, 
	&Company D_Company, 
	IGI_IGIRRTRH_XMLP_PKG.c_company_summary_labelformula(cc.segment1) C_COMPANY_SUMMARY_LABEL, 
	IGI_IGIRRTRH_XMLP_PKG.c_postable_summary_labelformul(decode ( gl_dist.gl_date , NULL , :rp_no , :rp_yes )) C_POSTABLE_SUMMARY_LABEL, 
	IGI_IGIRRTRH_XMLP_PKG.c_currency_summary_labelformul(trx.invoice_currency_code) C_CURRENCY_SUMMARY_LABEL, 
	IGI_IGIRRTRH_XMLP_PKG.c_data_not_foundformula(trx.invoice_currency_code) C_DATA_NOT_FOUND, 
	IGI_IGIRRTRH_XMLP_PKG.c_class_summary_labelformula(DECODE ( type.type , 'INV' , :lp_meaning_inv , 'DEP' , :lp_meaning_dep , 'GUAR' , :lp_meaning_guar , 'CM' , :lp_meaning_cm , 'DM' , :lp_meaning_dm , 'CB' , :lp_meaning_cb , '' )) C_CLASS_SUMMARY_LABEL, 
	IGI_IGIRRTRH_XMLP_PKG.invoice_consformula(trx.trx_number, &lp_query_show_bill) invoice_cons
from    ra_cust_trx_types               type,
        hz_parties                      party,
        hz_cust_accounts                cust,
        gl_code_combinations    cc,
        ra_cust_trx_line_gl_dist        gl_dist,
                  &lp_table_show_bill
        ra_customer_trx         trx,
        igi_rpi_standing_charges        sc
where   trx.complete_flag = 'Y'
and     trx.set_of_books_id = :sob_id
and     exists
           ( select 'x'
              from   igi_ar_system_options
              where rpi_header_context_code = trx.interface_header_context  )
and     ( trx.invoice_currency_code between :p_start_currency_code
        and :p_end_currency_code)
&lp_start_trx_date
&lp_end_trx_date
and      type.cust_trx_type_id = trx.cust_trx_type_id
and      type.type in ('INV', 'DEP', 'GUAR', 'CM', 'DM', 'CB')
&lp_invoice_type_low
&lp_invoice_type_high
&lp_trx_type_type_low
&lp_trx_type_type_high
and      trx.bill_to_customer_id = cust.cust_account_id
and      party.party_id = cust.party_id
and      cc.code_combination_id = gl_dist.code_combination_id
and &lp_start_company_segment
and &lp_end_company_segment
and     gl_dist.customer_trx_id = trx.customer_trx_id
and     gl_dist.account_class = 'REC'
&lp_start_gl_date
&lp_end_gl_date
&lp_where_show_bill
and     gl_dist.latest_rec_flag = 'Y'
AND (:P_FROM_SC_REF IS NULL
OR UPPER(sc.charge_reference) between 
       UPPER( nvl(:P_FROM_SC_REF,sc.charge_reference) )  and  
       UPPER( nvl(:P_TO_SC_REF,    sc.charge_reference)  )    )
AND     trx.interface_header_attribute1 =
                                       ltrim(to_char(sc.standing_charge_id(+)))
order by fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
         type.post_to_gl desc,
         trx.invoice_currency_code,
                   type.type,
         rtrim( rpad(type.name,8) ),
         decode(upper(:p_order_by),
                'CUSTOMER', party.party_name ,
                'PHONETIC', party.organization_name_phonetic,
                'INVOICE NUMBER', trx.trx_number),
         decode (upper(:p_order_by),'CUSTOMER',trx.trx_number,NULL),
         gl_dist.gl_date
Parameter Name SQL text Validation
Chart of Accounts ID
 
Standing Charge Reference High
 
LOV Oracle
Standing Charge Reference Low
 
LOV Oracle
Balancing Segment High
 
Balancing Segment Low
 
Currency Code High
 
LOV Oracle
Currency Code Low
 
LOV Oracle
Invoice Type High
 
LOV Oracle
Invoice Type Low
 
LOV Oracle
Transaction Type
 
LOV Oracle
Transaction Date High
 
Date
Transaction Date Low
 
Date
GL Date High
 
Date
GL Date Low
 
Date
Order By
 
LOV Oracle
Ask a question