ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN F26C78167A214EFBE0530100007F336B GL Balancing Y select ffvv.flex_value value, ffvv.description||case when ffvv.enabled_flag='Y' and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) then null else ' (inactive)' end description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where xxen_util.flex_value_accessible(fnd_global.resp_appl_id,fnd_global.resp_id,ffvv.flex_value_set_id,ffvv.flex_value)='Y' and fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and (fifsgv.application_id, fifsgv.id_flex_code, fifsgv.id_flex_num, fifsgv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y') and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and (:$flex$.chart_of_accounts_id is not null or :$flex$.chart_of_accounts is not null or :$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null or :$flex$.book is not null) and fifsgv.id_flex_num in ( select /*+ no_unnest*/ fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( fifsv.id_flex_num=:$flex$.chart_of_accounts_id or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or (:$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null) and fifsgv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) ) ) or fifsgv.id_flex_num in (select fbc.accounting_flex_structure from fa_book_controls fbc where xxen_util.contains(:$flex$.book,fbc.book_type_code)='Y') ) ) order by ffvv.flex_value US 382972533FD4AC4EE0633D74000A9B93Y select rx.organization_name ledger, haouv.name operating_unit, rx.rec_postable_flag postable, rx.rec_balance "&bal_segment_p", rx.rec_balance_desc "&bal_segment_d", rx.trx_currency currency, arpt_sql_func_util.get_lookup_meaning('INV/CM',trx_types.type) class, rx.trx_number invoice_number, rx.doc_sequence_value document_number, trx_types.name "Type", substrb(bill_to_party.party_name,1,50) customer_name, bill_to.account_number customer_number, bill_to_site.location customer_site, rx.trx_date invoice_date, rx.receivables_gl_date gl_date, rx.trx_amount entered_amount, rx.trx_acctd_amount functional_amount, rx.trx_amount-arpt_sql_func_util.get_sum_of_trx_lines(rx.customer_trx_id,'TAX') total_line_amount, rx.trx_amount-arpt_sql_func_util.get_sum_of_trx_lines(rx.customer_trx_id,'FREIGHT') total_freight_amount, arpt_sql_func_util.get_sum_of_trx_lines(rx.customer_trx_id,'TAX') total_tax_amount, (case when trx_types.type='CM' then -1*( select sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr) from ra_customer_trx_lines_all rctl, jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where rx.customer_trx_id=rctl.customer_trx_id and rctl.line_type='LINE' and nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and jrav.entity_id=jtlv.tax_type_id and jrav.reporting_code='IGST' and jtlv.tax_type_name like 'IGST_%' and jtlv.tax_type_name not like 'IGST_EXP_R%' and jtlv.tax_type_name<>'IGST_EOU_RFD' and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and jtlv.application_id=222 and jrav.entity_code='TAX_TYPE' and jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') else (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where jtlv.trx_id=rx.customer_trx_id and jrav.entity_id=jtlv.tax_type_id and jrav.reporting_code='IGST' and jtlv.tax_type_name like 'IGST_%' and jtlv.tax_type_name not like 'IGST_EXP_R%' and jtlv.tax_type_name<>'IGST_EOU_RFD' and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code='INVOICE' and jtlv.application_id=222 and jrav.entity_code='TAX_TYPE' and jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') end) "IGST", (case when trx_types.type='CM' then -1*( select sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr) from ra_customer_trx_lines_all rctl, jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where rx.customer_trx_id=rctl.customer_trx_id and rctl.line_type='LINE' and nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and jrav.entity_id=jtlv.tax_type_id and jrav.reporting_code='CGST' and jtlv.tax_type_name like 'CGST_%' and jtlv.tax_type_name not like 'CGST_EOU_R%' and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and jtlv.application_id=222 and jrav.entity_code='TAX_TYPE' and jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') else (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where jtlv.trx_id=rx.customer_trx_id and jrav.entity_id=jtlv.tax_type_id and jrav.reporting_code='CGST' and jtlv.tax_type_name like 'CGST_%' and jtlv.tax_type_name not like 'CGST_EOU_R%' and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code='INVOICE' and jtlv.application_id=222 and jrav.entity_code='TAX_TYPE' and jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') end) "CGST", (case when trx_types.type='CM' then -1*( select sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr) from ra_customer_trx_lines_all rctl, jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where rx.customer_trx_id=rctl.customer_trx_id and rctl.line_type='LINE' and nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and jrav.entity_id=jtlv.tax_type_id and jrav.reporting_code='SGST' and jtlv.tax_type_name like 'SGST_%' and jtlv.tax_type_name not like 'SGST_EOU_R%' and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and jtlv.application_id=222 and jrav.entity_code='TAX_TYPE' and jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') else (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where jtlv.trx_id=rx.customer_trx_id and jrav.entity_id=jtlv.tax_type_id and jrav.reporting_code='SGST' and jtlv.tax_type_name like 'SGST_%' and jtlv.tax_type_name not like 'SGST_EOU_R%' and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code='INVOICE' and jtlv.application_id=222 and jrav.entity_code='TAX_TYPE' and jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') end ) "SGST", (case when trx_types.type='CM' then -1*( select sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr) from ra_customer_trx_lines_all rctl, jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where rx.customer_trx_id=rctl.customer_trx_id and rctl.line_type='LINE' and nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and jrav.entity_id=jtlv.tax_type_id and jrav.reporting_code='CESS' and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and jtlv.application_id=222 and jrav.entity_code='TAX_TYPE' and jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') else (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where jtlv.trx_id=rx.customer_trx_id and jrav.entity_id=jtlv.tax_type_id and jrav.reporting_code='CESS' and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code='INVOICE' and jtlv.application_id=222 and jrav.entity_code='TAX_TYPE' and jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') end) "Compensation CESS", (case when trx_types.type='CM' then -1*( select sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr) from ra_customer_trx_lines_all rctl, jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where rx.customer_trx_id=rctl.customer_trx_id and rctl.line_type='LINE' and nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and jrav.entity_id=jtlv.tax_type_id and jrav.reporting_code='CUSTOMS' and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and jtlv.application_id=222 and jrav.entity_code='TAX_TYPE' and jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') else (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where jtlv.trx_id=rx.customer_trx_id and jrav.entity_id=jtlv.tax_type_id and jrav.reporting_code='CUSTOMS' and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code='INVOICE' and jtlv.application_id=222 and jrav.entity_code='TAX_TYPE' and jrav.reporting_type_code='TAX_TYPES_CLASSIFICATION') end) custom, (case when trx_types.type='CM' then -1*( select sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr) from ra_customer_trx_lines_all rctl, jai_tax_lines_v jtlv where rx.customer_trx_id=rctl.customer_trx_id and rctl.line_type='LINE' and nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and jtlv.application_id=222 and jtlv.tax_type_code like 'SGST_EOU_R%') else ( select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv where rx.customer_trx_id=jtlv.trx_id and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code='INVOICE' and jtlv.application_id=222 and jtlv.tax_type_code like 'SGST_EOU_R%') end) "SGST Refund on Deemed Exports", (case when trx_types.type='CM' then -1*( select sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr) from ra_customer_trx_lines_all rctl, jai_tax_lines_v jtlv where rx.customer_trx_id=rctl.customer_trx_id and rctl.line_type='LINE' and nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and jtlv.application_id=222 and jtlv.tax_type_code like 'CGST_EOU_R%') else ( select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv where rx.customer_trx_id=jtlv.trx_id and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code='INVOICE' and jtlv.application_id=222 and jtlv.tax_type_code like 'CGST_EOU_R%') end) "CGST Refund on Deemed Exports", (case when trx_types.type='CM' then -1*( select sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr) from ra_customer_trx_lines_all rctl, jai_tax_lines_v jtlv where rx.customer_trx_id=rctl.customer_trx_id and rctl.line_type='LINE' and nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and jtlv.application_id=222 and jtlv.tax_type_code like 'TCS%') else ( select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv where rx.customer_trx_id=jtlv.trx_id and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code='INVOICE' and jtlv.application_id=222 and jtlv.tax_type_code like 'TCS%') end) "Tax Collected Source (TCS)", (case when trx_types.type='CM' then -1*( select sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr) from ra_customer_trx_lines_all rctl, jai_tax_lines_v jtlv where rx.customer_trx_id=rctl.customer_trx_id and rctl.line_type='LINE' and nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and jtlv.application_id=222 and jtlv.tax_type_code='SAL_OTHER_CHARGES_BCD') else ( select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv where rx.customer_trx_id=jtlv.trx_id and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code='INVOICE' and jtlv.application_id=222 and jtlv.tax_type_code='SAL_OTHER_CHARGES_BCD') end) "BCD", (case when trx_types.type='CM' then -1*( select sum((case when jtlv.event_class_code='CREDIT_MEMO' then -1 else 1 end)*jtlv.rounded_tax_amt_fun_curr) from ra_customer_trx_lines_all rctl, jai_tax_lines_v jtlv where rx.customer_trx_id=rctl.customer_trx_id and rctl.line_type='LINE' and nvl(rctl.previous_customer_trx_id,rctl.customer_trx_id)=jtlv.trx_id and nvl(rctl.previous_customer_trx_line_id,rctl.customer_trx_line_id)=jtlv.trx_line_id and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code in ('INVOICE','CREDIT_MEMO') and jtlv.application_id=222 and (jtlv.tax_type_name like 'IGST_EXP_R%' or jtlv.tax_type_name='IGST_EOU_RFD')) else ( select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv where rx.customer_trx_id=jtlv.trx_id and jtlv.entity_code='TRANSACTIONS' and jtlv.event_class_code='INVOICE' and jtlv.application_id=222 and (jtlv.tax_type_name like 'IGST_EXP_R%' or jtlv.tax_type_name='IGST_EOU_RFD')) end) "IGST Export Refund", rx.rec_natacct receivables_account, rx.rec_natacct_desc receivables_account_desc, rx.rec_account receivables_account_full, rx.rec_account_desc receivables_account_full_desc, rx.functional_currency_code functional_currency, rx.exchange_type exchange_rate_type, rx.exchange_date exchange_rate_date, rx.exchange_rate exchange_rate, gdct.user_conversion_type exchange_rate_type, terms.name payment_terms, rx.trx_due_date invoice_due_date, methods.name payment_method, arpt_sql_func_util.get_lookup_meaning('YES/NO', nvl(bill_to_site.tax_header_level_flag, nvl(bill_to.tax_header_level_flag, rx.tax_header_level_flag))) tax_calculation_level, bas.name batch_source, ba.name batch_name, rx.cons_bill_number consolidated_bill_number, doc_seq.name document_sequence_name, substrb(ship_to_party.party_name,1,50) ship_to_customer_name, ship_to.account_number ship_to_customer_number, ship_to_site.location ship_to_customer_site, (select jprl_.registration_number from ra_customer_trx_lines_all rctla, jai_party_regs jpr_, jai_party_reg_lines jprl_ where rctla.customer_trx_Id=rx.customer_trx_id and rctla.line_type='LINE' and jpr_.party_id=rctla.warehouse_id and jpr_.party_reg_id=jprl_.party_reg_id and jpr_.party_type_code='IO' and jprl_.registration_type_code='GST' and jprl_.effective_to is null and rownum=1 ) warehouse_registration_number, (select jprl_.registration_number from jai_party_regs jpr_, jai_party_reg_lines jprl_, hz_cust_accounts hca, ra_customer_trx_all rcta where jpr_.party_reg_id=jprl_.party_reg_id and jpr_.party_type_code='THIRD_PARTY_SITE' and jprl_.registration_type_code='GST' and jprl_.effective_to is null and rcta.customer_trx_id=rx.customer_trx_id and rcta.sold_to_customer_id=hca.cust_account_id and to_char(jpr_.party_id)=hca.orig_system_reference and jpr_.customer_flag='Y' and jpr_.party_site_id in (select cust_acct_site_id from hz_cust_site_uses_all hcsu where hcsu.site_use_id=rcta.bill_to_site_use_id and hcsu.status='A') and rownum=1 ) customer_registration_number from ar_transactions_rep_itf rx, ra_terms terms, gl_daily_conversion_types gdct, fnd_document_sequences doc_seq, hz_cust_accounts ship_to, hz_parties ship_to_party, hz_cust_accounts bill_to, hz_parties bill_to_party, hz_cust_site_uses_all ship_to_site, hz_cust_site_uses_all bill_to_site, ra_cust_trx_types_all trx_types, ar_receipt_methods methods, ra_batches_all ba, ra_batch_sources_all bas, hr_all_organization_units_vl haouv where 1=1 and rx.ship_to_customer_id=ship_to.cust_account_id(+) and rx.exchange_type=gdct.conversion_type(+) and ship_to.party_id=ship_to_party.party_id(+) and rx.ship_to_site_use_id=ship_to_site.site_use_id(+) and rx.bill_to_customer_id=bill_to.cust_account_id and bill_to.party_id=bill_to_party.party_id and rx.bill_to_site_use_id=bill_to_site.site_use_id and rx.cust_trx_type_id=trx_types.cust_trx_type_id and rx.term_id=terms.term_id(+) and rx.doc_sequence_id=doc_seq.doc_sequence_id(+) and rx.receipt_method_id=methods.receipt_method_id(+) and nvl(rx.org_id, -99)=nvl(trx_types.org_id, -99) and rx.batch_id=ba.batch_id(+) and rx.batch_source_id=bas.batch_source_id(+) and nvl(rx.org_id, -99)=nvl(bas.org_id, -99) and rx.request_id=fnd_global.conc_request_id and rx.org_id=haouv.organization_id (+) order by rx.organization_name, rx.rec_balance, rx.rec_postable_flag, rx.trx_currency, arpt_sql_func_util.get_lookup_meaning('INV/CM',trx_types.type), rx.trx_number, rx.doc_sequence_value #,##0.000;[Red]-#,##0.000 B054531DF5620463E0530100007F09D5 XXEN_AR US APL: AR Transaction Register Application: Receivables Source: Transaction Register Short Name: ARRXINVR The report has now been enhanced to allow multiple accessible Ledgers or Operating Units to be selected in the Reporting Context parameter. Additionally, the Reporting Context parameter has been made optional. Leaving it null will allow the report to be run across all accessible Ledgers or Operating Units. &bal_segment_d &bal_segment_p 1=1 :p_adjustment_type_high :p_adjustment_type_low :p_application_name :p_attribute_set :p_bal_seg_high :p_bal_seg_low :p_batch_source_name :p_coa_id :p_concurrent_program_name :p_document_sequence_name :p_document_sequence_num_high :p_document_sequence_num_low :p_entered_currency_high :p_entered_currency_low :p_gl_account_flex_high :p_gl_account_flex_low :p_gl_date_from :p_gl_date_to :p_ledger_id :p_out_of_balance_only :p_output_format :p_period_name :p_report_code :p_report_id :p_reporting_entity_id :p_reporting_entity_name :p_reporting_level :p_request_type :p_summary_account :p_transaction_class :p_transaction_date_high :p_transaction_date_low :p_transaction_due_date_high :p_transaction_due_date_low :p_transaction_type_high :p_transaction_type_low 1 -80 :p_request_type Char SUBMIT US Request Type 2 -70 :p_application_name Char AR US FARX_APP_NAME 3 -60 :p_concurrent_program_name Char RXARINVR US FARX_CONC_NAME 4 -50 :p_report_id Number select distinct report_id from fa_rx_reports r, fnd_concurrent_programs c, fnd_application a where r.application_id = a.application_id and r.application_id = c.application_id and r.concurrent_program_id = c.concurrent_program_id and a.application_short_name = :$FLEX$.FARX_APP_NAME and c.concurrent_program_name = :$FLEX$.FARX_CONC_NAME US FARX_REPORT_ID 5 -40 :p_output_format Char TEXT Y US Output Format 6 -30 :p_coa_id Number select gsob.chart_of_accounts_id from gl_sets_of_books gsob, ar_system_parameters asp, hr_all_organization_units haou where gsob.set_of_books_id=asp.set_of_books_id and asp.org_id=haou.organization_id and ( :$flex$.reporting_context is null or :$flex$.reporting_level='1000' and xxen_util.contains(:$flex$.reporting_context,gsob.name)='Y' or :$flex$.reporting_level='3000' and xxen_util.contains(:$flex$.reporting_context,haou.name)='Y' ) and rownum=1 US Chart of Accounts Id 7 &bal_segment_d select fifsv.form_left_prompt || ' Desc' from fnd_segment_attribute_values fsav , fnd_id_flex_segments_vl fifsv where fsav.application_id = 101 and fsav.id_flex_code = 'GL#' and fsav.segment_attribute_type = 'GL_BALANCING' and fsav.id_flex_num = :p_coa_id and fsav.attribute_value = 'Y' and fifsv.application_id = fsav.application_id and fifsv.id_flex_code = fsav.id_flex_code and fifsv.id_flex_num = fsav.id_flex_num and fifsv.application_column_name = fsav.application_column_name US Chart of Accounts Id 8 &bal_segment_p select fifsv.form_left_prompt from fnd_segment_attribute_values fsav , fnd_id_flex_segments_vl fifsv where fsav.application_id = 101 and fsav.id_flex_code = 'GL#' and fsav.segment_attribute_type = 'GL_BALANCING' and fsav.id_flex_num = :p_coa_id and fsav.attribute_value = 'Y' and fifsv.application_id = fsav.application_id and fifsv.id_flex_code = fsav.id_flex_code and fifsv.id_flex_num = fsav.id_flex_num and fifsv.application_column_name = fsav.application_column_name US Chart of Accounts Id 9 -20 :p_attribute_set LOV Oracle FARX_ATTRIBUTE_SET Y select attr.attribute_set id, attr.attribute_set value, attr.user_attribute_set description from fa_rx_attrsets_vl attr where attr.report_id=:$flex$.farx_report_id and (attr.public_flag='Y' or fnd_global.resp_id in (select sec.responsibility_id from fa_rx_security sec where sec.report_id=attr.report_id and sec.attribute_set=attr.attribute_set)) order by decode(attr.attribute_set,'DEFAULT',0,1), attr.user_attribute_set, attr.attribute_set DEFAULT Y US FARX_ATTRIBUTE_SET 10 -10 :p_report_code Char ARRXINVR Y US Report Code 11 10 :p_reporting_level LOV Oracle FND_MO_REPORTING_LEVEL Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='FND_MO_REPORTING_LEVEL' and lookup_code in (1000,3000) order by value,description 1000 Y US Reporting Level 12 20 1=1 decode(:p_reporting_level,'1000',rx.organization_name,'3000',haouv.name,null) = :p_reporting_entity_name LOV custom Y select entity_name value, null description from fnd_mo_reporting_entities_v where reporting_level=:$flex$.fnd_mo_reporting_level and ( :$flex$.fnd_mo_reporting_level='1000' and ledger_category_code='PRIMARY' or :$flex$.fnd_mo_reporting_level='3000' ) order by value,description SELECT entity_name FROM FND_MO_REPORTING_ENTITIES_V WHERE reporting_level = :$flex$.fnd_mo_reporting_level and rownum=1 US Reporting Context 13 30 :p_bal_seg_low LOV GL Balancing F26C78167A214EFBE0530100007F336B Y select ffvv.flex_value value, ffvv.description||case when ffvv.enabled_flag='Y' and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) then null else ' (inactive)' end description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where xxen_util.flex_value_accessible(fnd_global.resp_appl_id,fnd_global.resp_id,ffvv.flex_value_set_id,ffvv.flex_value)='Y' and fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and (fifsgv.application_id, fifsgv.id_flex_code, fifsgv.id_flex_num, fifsgv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y') and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and (:$flex$.chart_of_accounts_id is not null or :$flex$.chart_of_accounts is not null or :$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null or :$flex$.book is not null) and fifsgv.id_flex_num in ( select /*+ no_unnest*/ fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( fifsv.id_flex_num=:$flex$.chart_of_accounts_id or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or (:$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null) and fifsgv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) ) ) or fifsgv.id_flex_num in (select fbc.accounting_flex_structure from fa_book_controls fbc where xxen_util.contains(:$flex$.book,fbc.book_type_code)='Y') ) ) order by ffvv.flex_value US Company Segment Low 14 40 :p_bal_seg_high LOV GL Balancing F26C78167A214EFBE0530100007F336B Y select ffvv.flex_value value, ffvv.description||case when ffvv.enabled_flag='Y' and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) then null else ' (inactive)' end description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where xxen_util.flex_value_accessible(fnd_global.resp_appl_id,fnd_global.resp_id,ffvv.flex_value_set_id,ffvv.flex_value)='Y' and fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and (fifsgv.application_id, fifsgv.id_flex_code, fifsgv.id_flex_num, fifsgv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y') and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and (:$flex$.chart_of_accounts_id is not null or :$flex$.chart_of_accounts is not null or :$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null or :$flex$.book is not null) and fifsgv.id_flex_num in ( select /*+ no_unnest*/ fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( fifsv.id_flex_num=:$flex$.chart_of_accounts_id or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or (:$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null) and fifsgv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) ) ) or fifsgv.id_flex_num in (select fbc.accounting_flex_structure from fa_book_controls fbc where xxen_util.contains(:$flex$.book,fbc.book_type_code)='Y') ) ) order by ffvv.flex_value :$flex$.company_segment_low US Company Segment High 15 50 :p_gl_date_from Date trunc(sysdate,'MON') Y US GL Date From 16 60 :p_gl_date_to Date last_day(:$flex$.gl_date_From) Y US GL Date To 17 70 :p_gl_account_flex_low LOV custom Y select gcck.concatenated_segments value ,null description from gl_code_combinations_kfv gcck where gcck.chart_of_accounts_id = :$flex$.chart_of_accounts_id and gcck.enabled_flag = 'Y' and gcck.detail_posting_allowed = 'Y' and gcck.summary_flag = 'N' order by gcck.concatenated_segments US Receivables Account Low 18 80 :p_gl_account_flex_high LOV custom Y select gcck.concatenated_segments value ,null description from gl_code_combinations_kfv gcck where gcck.chart_of_accounts_id = :$flex$.chart_of_accounts_id and gcck.enabled_flag = 'Y' and gcck.detail_posting_allowed = 'Y' and gcck.summary_flag = 'N' order by gcck.concatenated_segments :$flex$.Receivables_Account_low US Receivables Account High 19 90 :p_entered_currency_low LOV Oracle AR_SRS_CURRENCY Y select currency_code id, currency_code value, name description from fnd_currencies_vl where currency_flag='Y' and enabled_flag in ('Y','N') order by currency_code US Entered Currency Low 20 100 :p_entered_currency_high LOV Oracle AR_SRS_CURRENCY Y select currency_code id, currency_code value, name description from fnd_currencies_vl where currency_flag='Y' and enabled_flag in ('Y','N') order by currency_code :$flex$.entered_currency_low US Entered Currency High 21 110 :p_transaction_date_low Date US Transaction Date Low 22 120 :p_transaction_date_high Date :$flex$.transaction_date_low US Transaction Date High 23 130 :p_batch_source_name LOV custom Y select name id, name value, null description from ra_batch_sources_all where nvl(org_id,-99) in (select -99 from dual union select hou.organization_id from hr_operating_units hou, gl_sets_of_books gsob where :$flex$.reporting_level = '1000' and to_number(hou.set_of_books_id) = gsob.set_of_books_id and xxen_util.contains(:$flex$.reporting_context,gsob.name) = 'Y' union select haouv.organization_id from hr_all_organization_units_vl haouv where :$flex$.reporting_level = '3000' and xxen_util.contains(:$flex$.reporting_context,haouv.name) = 'Y' ) order by value,description US Batch Source Name 24 140 :p_transaction_type_low LOV custom Y select name id, name value, null description from ra_cust_trx_types_all where nvl(org_id,-99) in (select -99 from dual union select hou.organization_id from hr_operating_units hou, gl_sets_of_books gsob where :$flex$.reporting_level = '1000' and to_number(hou.set_of_books_id) = gsob.set_of_books_id and xxen_util.contains(:$flex$.reporting_context,gsob.name) = 'Y' union select haouv.organization_id from hr_all_organization_units_vl haouv where :$flex$.reporting_level = '3000' and xxen_util.contains(:$flex$.reporting_context,haouv.name) = 'Y' ) order by value, description US Transaction Type Low 25 150 :p_transaction_type_high LOV custom Y select name id, name value, null description from ra_cust_trx_types_all where nvl(org_id,-99) in (select -99 from dual union select hou.organization_id from hr_operating_units hou, gl_sets_of_books gsob where :$flex$.reporting_level = '1000' and to_number(hou.set_of_books_id) = gsob.set_of_books_id and xxen_util.contains(:$flex$.reporting_context,gsob.name) = 'Y' union select haouv.organization_id from hr_all_organization_units_vl haouv where :$flex$.reporting_level = '3000' and xxen_util.contains(:$flex$.reporting_context,haouv.name) = 'Y' ) order by value, description :$flex$.transaction_type_low US Transaction Type High 26 160 :p_transaction_class LOV Oracle AR_INVOICE_CLASS Y select lookup_code id, meaning value, null description from ar_lookups where lookup_code in ('INV','DEP','CM','GUAR','DM','CB') and lookup_type='INV/CM' order by meaning,lookup_code US Transaction Class 27 170 :p_document_sequence_name LOV custom Y select doc_sequence_id id, name value, null description from fnd_document_sequences where doc_sequence_id in (select distinct doc_sequence_id from fnd_doc_sequence_assignments where application_id=222 and set_of_books_id in (select gsob.set_of_books_id from gl_sets_of_books gsob where :$flex$.reporting_level = '1000' and xxen_util.contains(:$flex$.reporting_context,gsob.name) = 'Y' union select aspa.set_of_books_id from hr_all_organization_units_vl haouv, ar_system_parameters_all aspa where :$flex$.reporting_level = '3000' and aspa.org_id = haouv.organization_id and xxen_util.contains(:$flex$.reporting_context,haouv.name) = 'Y' ) ) order by value US Document Sequence Name 28 180 :p_document_sequence_num_low LOV custom Y select doc_sequence_value id, doc_sequence_value value, null description from ar_doc_sequence_audit where doc_sequence_id=:$flex$.Document_Sequence_Name and :$flex$.Document_Sequence_Name is not null order by value,description US Document Sequence Number From 29 190 :p_document_sequence_num_high LOV custom Y select doc_sequence_value id, doc_sequence_value value, null description from ar_doc_sequence_audit where doc_sequence_id=:$flex$.Document_Sequence_Name and :$flex$.Document_Sequence_Name is not null order by value,description :$flex$.document_sequence_number_from US Document Sequence Number To