ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 55DEB4D48B1D727FE0530100007F0ADEY select ap_inv.ledger, ap_inv.operating_unit, ap_inv.supplier, ap_inv.supplier_number, ap_inv.supplier_type, ap_inv.employee_name, ap_inv.supplier_site, ap_inv.invoice_num, ap_inv.doc_sequence_value document_number, ap_inv.document_category, ap_inv.exchange_rate, ap_inv.exchange_date, ap_inv.exchange_rate_type, ap_inv.invoice_status, ap_inv.accounting_status, ap_inv.batch_name, ap_inv.invoice_creation_date, ap_inv.invoice_received_date, ap_inv.invoice_date, ap_inv.validation_date, ap_inv.validated_by, ap_inv.invoice_gl_date, ap_inv.due_date, ap_inv.days_due, ap_inv.invoice_type, ap_inv.invoice_source, ap_inv.invoice_description, ap_inv.has_attachment, ap_inv.invoice_po_matched, ap_inv.invoice_currency_code, ap_inv.payment_currency_code, &attachment_amount_columns ap_inv.auto_tax_calculation_method, ap_inv.invoice_pay_group, ap_inv.invoice_exclusive_payment_flag, ap_inv.dispute_reason, ap_inv.supplier_site_payment_hold, ap_inv.scheduled_payment_hold, ap_inv.scheduled_payment_hold_reason, ap_holds.holds invoice_holds, ap_holds.hold_dates invoice_hold_dates, ap_holds.holds_held_by invoice_holds_held_by, ap_holds.hold_po_references invoice_hold_purchase_orders, ap_holds.hold_receipt_references invoice_hold_receipts, ap_inv.asset_book_type, ap_inv.asset_category, &invoice_detail_columns &dff_columns2 ap_inv.accts_pay_account, ap_inv.accts_pay_account_descripton, ap_inv.recurring_pmt_number, ap_inv.recurring_pmt_period_type, ap_inv.recurring_number_of_periods, ap_inv.recurring_pmt_description, ap_inv.supplier_taxpayer_id, ap_inv.supplier_tax_registration, ap_inv.supplier_inactive_on, ap_inv.customer_num, ap_inv.one_time_supplier, ap_inv.supplier_credit_status, ap_inv.credit_limit, ap_inv.supplier_withholding_status, ap_inv.withholding_start_date, ap_inv.supplier_vat_code, ap_inv.supplier_site_alt, ap_inv.purchasing_site, ap_inv.rfq_site, ap_inv.pay_site, ap_inv.tax_reporting_site, ap_inv.p_card_site, ap_inv.attention_ar, ap_inv.address_line1, ap_inv.address_line2, ap_inv.address_line3, ap_inv.address_line4, ap_inv.city, ap_inv.state, ap_inv.zip, ap_inv.county, ap_inv.province, ap_inv.country, ap_inv.area_code, ap_inv.phone, ap_inv.fax_area_code, ap_inv.fax, ap_inv.supplier_notif_method, ap_inv.email_address, ap_inv.remittance_email, &gcc_dist_segment_columns &l_ap_sla_sel2 &attachment_columns ap_inv.created_by, ap_inv.creation_date, ap_inv.last_updated_by, ap_inv.last_update_date, ap_inv.line_created_by, ap_inv.line_creation_date, ap_inv.line_last_updated_by, ap_inv.line_last_update_date, ap_inv.dist_created_by, ap_inv.dist_creation_date, ap_inv.dist_last_updated_by, ap_inv.dist_last_update_date, ap_inv.invoice_id from ( select ap_inv.ledger, ap_inv.operating_unit, ap_inv.supplier, ap_inv.supplier_number, ap_inv.supplier_type, ap_inv.employee_name, ap_inv.supplier_site, ap_inv.invoice_num, ap_inv.doc_sequence_value document_number, ap_inv.document_category, ap_inv.exchange_rate, ap_inv.exchange_date, ap_inv.exchange_rate_type, ap_inv.invoice_status, ap_inv.accounting_status, ap_inv.batch_name, ap_inv.invoice_creation_date, ap_inv.invoice_received_date, ap_inv.invoice_date, ap_inv.validation_date, ap_inv.validated_by, ap_inv.invoice_gl_date, ap_inv.due_date, ap_inv.days_due, ap_inv.invoice_type, ap_inv.invoice_source, ap_inv.invoice_description, ap_inv.has_attachment, ap_inv.invoice_po_matched, ap_inv.invoice_currency_code, ap_inv.payment_currency_code, decode(ap_inv.first_invoice,'Y',ap_inv.invoice_amount) invoice_amount, ap_inv.items_total_ items_total, ap_inv.retainage_total_ retainage_total, ap_inv.prepayments_applied_total_ prepayments_applied_total, ap_inv.witholding_total_ witholding_total, decode(ap_inv.first_invoice,'Y', nvl(ap_inv.items_total_,0) + nvl(ap_inv.retainage_total_,0) - nvl(abs(ap_inv.prepayments_applied_total_),0) - nvl(ap_inv.witholding_total_,0) ) invoice_subtotal, decode(ap_inv.first_invoice,'Y',ap_inv.total_tax_amount) tax_total, decode(ap_inv.first_invoice,'Y',ap_inv.self_assessed_tax_amount) self_assessed_tax_amount, ap_inv.freight_total_ freight_total, ap_inv.miscellaneous_total_ miscellaneous_total, decode(ap_inv.first_invoice,'Y', nvl(ap_inv.items_total_,0) + nvl(ap_inv.retainage_total_,0) - nvl(abs(ap_inv.prepayments_applied_total_),0) - nvl(ap_inv.witholding_total_,0) + nvl(ap_inv.total_tax_amount,0) + nvl(ap_inv.freight_total_,0) + nvl(ap_inv.miscellaneous_total_,0) ) invoice_total, decode(ap_inv.first_invoice,'Y',ap_inv.amount_applicable_to_discount) amount_applicable_to_discount, decode(ap_inv.first_invoice,'Y',ap_inv.discount_amount_taken) discount_amount_taken, decode(ap_inv.first_invoice,'Y',ap_inv.approved_amount) approved_amount, decode(ap_inv.first_invoice,'Y',ap_inv.pay_curr_invoice_amount) payment_curr_invoice_amount, decode(ap_inv.first_invoice,'Y',ap_inv.amount_paid) amount_paid, ap_inv.functional_currency, decode(ap_inv.first_invoice,'Y',ap_inv.invoice_amount_functional) invoice_amount_functional, decode(ap_inv.first_invoice,'Y',ap_inv.tax_amount_functional) tax_amount_functional, decode(ap_inv.first_invoice,'Y',ap_inv.amt_applicable_to_disc_funct) amt_applicable_to_disc_funct, decode(ap_inv.first_invoice,'Y',ap_inv.discount_amount_taken_funct) discount_amount_taken_funct, decode(ap_inv.first_invoice,'Y',ap_inv.approved_amount_functional) approved_amount_functional, decode(ap_inv.first_invoice,'Y',ap_inv.amount_paid_functional) amount_paid_functional, ap_inv.payment_num, ap_inv.payment_date, decode(ap_inv.first_psched,'Y',ap_inv.gross_amount) payment_num_gross_amount, decode(ap_inv.first_psched,'Y',ap_inv.amount_remaining) payment_num_amount_remaining, &il_pass_through &aging_bucket_cols2 &aging_bucket_cols3 ap_inv.payment_cross_rate, ap_inv.payment_cross_rate_date, ap_inv.discount_date, ap_inv.future_pay_due_date, ap_inv.payment_method, ap_inv.payment_priority, ap_inv.invoice_payment_status, ap_inv.schedule_payment_status, ap_inv.second_discount_date, ap_inv.third_discount_date, decode(ap_inv.first_psched,'Y',ap_inv.discount_amount_available) discount_amount_available, decode(ap_inv.first_psched,'Y',ap_inv.second_disc_amt_available) second_disc_amt_available, decode(ap_inv.first_psched,'Y',ap_inv.third_disc_amt_available) third_disc_amt_available, decode(ap_inv.first_psched,'Y',ap_inv.discount_amount_remaining) discount_amount_remaining, decode(ap_inv.first_psched,'Y',ap_inv.inv_curr_gross_amount) inv_curr_gross_amount, ap_inv.bank_name, ap_inv.iban, ap_inv.bank_account_num, ap_inv.bank_branch_name, ap_inv.invoice_terms, ap_inv.terms_date, ap_inv.invoice_cancelled_date, ap_inv.invoice_cancelled_by, decode(ap_inv.first_invoice,'Y',ap_inv.invoice_cancelled_amount) invoice_cancelled_amount, decode(ap_inv.first_invoice,'Y',ap_inv.invoice_temp_cancelled_amount) invoice_temp_cancelled_amount, ap_inv.auto_tax_calculation_method, ap_inv.invoice_pay_group, ap_inv.invoice_exclusive_payment_flag, ap_inv.dispute_reason, ap_inv.supplier_site_payment_hold, ap_inv.scheduled_payment_hold, ap_inv.scheduled_payment_hold_reason, ap_inv.asset_book_type, ap_inv.asset_category, &invoice_detail_columns &dff_columns2 ap_inv.accts_pay_account, ap_inv.accts_pay_account_descripton, ap_inv.recurring_pmt_number, ap_inv.recurring_pmt_period_type, ap_inv.recurring_number_of_periods, ap_inv.recurring_pmt_description, ap_inv.supplier_taxpayer_id, ap_inv.supplier_tax_registration, ap_inv.supplier_inactive_on, ap_inv.customer_num, ap_inv.one_time_supplier, ap_inv.supplier_credit_status, ap_inv.credit_limit, ap_inv.supplier_withholding_status, ap_inv.withholding_start_date, ap_inv.supplier_vat_code, ap_inv.supplier_site_alt, ap_inv.purchasing_site, ap_inv.rfq_site, ap_inv.pay_site, ap_inv.tax_reporting_site, ap_inv.p_card_site, ap_inv.attention_ar, ap_inv.address_line1, ap_inv.address_line2, ap_inv.address_line3, ap_inv.address_line4, ap_inv.city, ap_inv.state, ap_inv.zip, ap_inv.county, ap_inv.province, ap_inv.country, ap_inv.area_code, ap_inv.phone, ap_inv.fax_area_code, ap_inv.fax, ap_inv.supplier_notif_method, ap_inv.email_address, ap_inv.remittance_email, &gcc_dist_segment_columns &l_ap_sla_sel ap_inv.created_by, ap_inv.creation_date, ap_inv.last_updated_by, ap_inv.last_update_date, ap_inv.line_created_by, ap_inv.line_creation_date, ap_inv.line_last_updated_by, ap_inv.line_last_update_date, ap_inv.dist_created_by, ap_inv.dist_creation_date, ap_inv.dist_last_updated_by, ap_inv.dist_last_update_date, ap_inv.line_number line_number_key, ap_inv.dist_accounting_date dist_accounting_date_key, ap_inv.dist_line_number dist_line_number_key, ap_inv.dist_code_combination_id, ap_inv.doc_sequence_value, ap_inv.total_tax_amount, ap_inv.pay_curr_invoice_amount, ap_inv.gross_amount, ap_inv.amount_remaining, ap_inv.org_id, ap_inv.first_invoice, ap_inv.first_psched, ap_inv.first_line, ap_inv.functional_currency base_currency, ap_inv.invoice_amount_functional invoice_amount_base, ap_inv.tax_amount_functional tax_amount_base, ap_inv.amt_applicable_to_disc_funct amt_applicable_to_disc_base, ap_inv.discount_amount_taken_funct discount_amount_taken_base, ap_inv.approved_amount_functional approved_amount_base, ap_inv.amount_paid_functional amount_paid_base, ap_inv.invoice_id from ( select ap_inv.*, decode(ap_inv.first_invoice,'Y',(select ap_invoices_utility_pkg.get_item_total(ap_inv.invoice_id, ap_inv.org_id) from dual)) items_total_, decode(ap_inv.first_invoice,'Y',(select ap_invoices_utility_pkg.get_retained_total(ap_inv.invoice_id, ap_inv.org_id) from dual)) retainage_total_, decode(ap_inv.first_invoice,'Y',(select ap_invoices_utility_pkg.get_prepaid_amount(ap_inv.invoice_id) from dual)) prepayments_applied_total_, decode(ap_inv.first_invoice,'Y',(select ap_invoices_utility_pkg.get_amount_withheld(ap_inv.invoice_id) from dual)) witholding_total_, decode(ap_inv.first_invoice,'Y',(select ap_invoices_utility_pkg.get_freight_total(ap_inv.invoice_id, ap_inv.org_id) from dual)) freight_total_, decode(ap_inv.first_invoice,'Y',(select ap_invoices_utility_pkg.get_misc_total(ap_inv.invoice_id, ap_inv.org_id) from dual)) miscellaneous_total_ from ( select gl.name ledger, haouv.name operating_unit, nvl(aps.vendor_name,hp.party_name) supplier, nvl(aps.segment1,hp.party_number) supplier_number, xxen_util.meaning(aps.vendor_type_lookup_code,'VENDOR TYPE',201) supplier_type, (select papf.full_name from per_all_people_f papf where papf.person_id=aps.employee_id and rownum<=1) employee_name, nvl(assa.vendor_site_code,hps.party_site_number) supplier_site, aia.invoice_num, (select xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) from dual) invoice_status, (select decode(ap_invoices_pkg.get_posting_status(aia.invoice_id),'N',xxen_util.meaning('N','YES_NO',0),'D',xxen_util.meaning('N','YES_NO',0),'Y',xxen_util.yes('Y'),'P',xxen_util.meaning('P','POSTING STATUS',200),'S',xxen_util.meaning('S','POSTING STATUS',200)) from dual) accounting_status, aba.batch_name, xxen_util.client_time(aia.creation_date) invoice_creation_date, xxen_util.client_time(aia.invoice_received_date) invoice_received_date, aia.invoice_date, aia.gl_date invoice_gl_date, ( select min(xe.creation_date) from xla.xla_transaction_entities xte, xla_events xe where aia.invoice_id=nvl(xte.source_id_int_1,-99) and aia.set_of_books_id=xte.ledger_id and xte.application_id=200 and xte.entity_code='AP_INVOICES' and xte.application_id=xe.application_id and xte.entity_id=xe.entity_id and xe.event_type_code like '%VALIDATED%' ) validation_date, ( select xxen_util.user_name(max(xe.created_by) keep (dense_rank first order by xe.creation_date)) from xla.xla_transaction_entities xte, xla_events xe where aia.invoice_id=nvl(xte.source_id_int_1,-99) and aia.set_of_books_id=xte.ledger_id and xte.application_id=200 and xte.entity_code='AP_INVOICES' and xte.application_id=xe.application_id and xte.entity_id=xe.entity_id and xe.event_type_code like '%VALIDATED%' ) validated_by, apsa.due_date, ceil(sysdate-apsa.due_date) days_due, xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type, aia.source invoice_source, aia.description invoice_description, (select xxen_util.meaning('Y','YES_NO',0) from fnd_attached_documents fad where to_char(aia.invoice_id)=fad.pk1_value and fad.entity_name='AP_INVOICES' and rownum=1) has_attachment, (select xxen_util.meaning('Y','YES_NO',0) from ap_invoice_lines_all aila2 where aia.invoice_id=aila2.invoice_id and aila2.po_header_id is not null and rownum=1) invoice_po_matched, aia.invoice_currency_code, aia.payment_currency_code, aia.invoice_amount, aia.total_tax_amount, aia.self_assessed_tax_amount, aia.amount_applicable_to_discount, aia.discount_amount_taken, aia.approved_amount, aia.pay_curr_invoice_amount, aia.amount_paid, gl.currency_code functional_currency, decode(aia.invoice_currency_code,gl.currency_code,aia.invoice_amount,aia.base_amount) invoice_amount_functional, aia.total_tax_amount*nvl(aia.exchange_rate,1) tax_amount_functional, aia.amount_applicable_to_discount*nvl(aia.exchange_rate,1) amt_applicable_to_disc_funct, aia.discount_amount_taken/aia.payment_cross_rate*nvl(aia.exchange_rate,1) discount_amount_taken_funct, aia.approved_amount*nvl(aia.exchange_rate,1) approved_amount_functional, aia.amount_paid/aia.payment_cross_rate*nvl(aia.exchange_rate,1) amount_paid_functional, aia.dispute_reason, aia.doc_sequence_value, (select fdsc.name from fnd_doc_sequence_categories fdsc where fdsc.code=aia.doc_category_code and rownum<=1) document_category, aia.exchange_rate, aia.exchange_date, (select gdct.user_conversion_type from gl_daily_conversion_types gdct where gdct.conversion_type=aia.exchange_rate_type) exchange_rate_type, apsa.payment_priority, apsa.payment_num, ( select max(aca.check_date) from ap_invoice_payments_all aipa, ap_checks_all aca where apsa.invoice_id=aipa.invoice_id and apsa.payment_num=aipa.payment_num and aipa.check_id=aca.check_id and aca.void_date is null and aca.stopped_date is null ) payment_date, apsa.gross_amount, apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) amount_remaining, &aging_bucket_cols1 &il_select aia.payment_cross_rate, aia.payment_cross_rate_date, apsa.discount_date, apsa.future_pay_due_date, case when assa.hold_all_payments_flag='Y' and aia.cancelled_date is null and aia.payment_status_flag<>'Y' then xxen_util.meaning('Y','YES_NO',0) end supplier_site_payment_hold, xxen_util.yes(apsa.hold_flag) scheduled_payment_hold, decode(apsa.hold_flag,'Y',apsa.iby_hold_reason) scheduled_payment_hold_reason, nvl(xxen_util.meaning(apsa.payment_method_code,'PAYMENT METHOD',200),apsa.payment_method_code) payment_method, xxen_util.meaning(aia.payment_status_flag,'INVOICE PAYMENT STATUS',200) invoice_payment_status, xxen_util.meaning(apsa.payment_status_flag,'INVOICE PAYMENT STATUS',200) schedule_payment_status, apsa.second_discount_date, apsa.third_discount_date, apsa.discount_amount_available, apsa.second_disc_amt_available, apsa.third_disc_amt_available, apsa.discount_amount_remaining, apsa.inv_curr_gross_amount, (select cbv.bank_name from ce_banks_v cbv where ieba.bank_id=cbv.bank_party_id) bank_name, ieba.masked_iban iban, ieba.masked_bank_account_num bank_account_num, (select cbbv.bank_branch_name from ce_bank_branches_v cbbv where cbbv.branch_party_id=ieba.branch_id and rownum<=1) bank_branch_name, at.name invoice_terms, aia.terms_date, aia.cancelled_date invoice_cancelled_date, aia.cancelled_amount invoice_cancelled_amount, xxen_util.user_name(aia.cancelled_by) invoice_cancelled_by, aia.temp_cancelled_amount invoice_temp_cancelled_amount, xxen_util.meaning(aia.auto_tax_calc_flag,'AP_TAX_CALCULATION_METHOD',200) auto_tax_calculation_method, aia.pay_group_lookup_code invoice_pay_group, xxen_util.yes(aia.exclusive_payment_flag) invoice_exclusive_payment_flag, xxen_util.concatenated_segments(aia.accts_pay_code_combination_id) accts_pay_account, (select xxen_util.segments_description(aia.accts_pay_code_combination_id) from dual) accts_pay_account_descripton, aila.line_number, xxen_util.meaning(aila.line_type_lookup_code,'INVOICE LINE TYPE',200) line_type, xxen_util.meaning(aila.line_source,'LINE SOURCE',200) line_source, decode(aila.discarded_flag,'Y','Y') line_discarded, replace(aila.description,'~','-') line_description, (select msiv.concatenated_segments from mtl_system_items_vl msiv, po_line_locations_all plla where aila.po_line_location_id=plla.line_location_id and msiv.inventory_item_id=aila.inventory_item_id and msiv.organization_id=plla.ship_to_organization_id and rownum<=1) item_code, (select msiv.description from mtl_system_items_vl msiv, po_line_locations_all plla where aila.po_line_location_id=plla.line_location_id and msiv.inventory_item_id=aila.inventory_item_id and msiv.organization_id=plla.ship_to_organization_id and rownum<=1) item_description, aila.unit_meas_lookup_code line_uom, (select mp.organization_code from po_line_locations_all plla, mtl_parameters mp where aila.po_line_location_id=plla.line_location_id and plla.ship_to_organization_id=mp.organization_id and rownum<=1) inventory_organization, (select sum(rt.quantity) from rcv_transactions rt where rt.transaction_id=nvl(aida.rcv_transaction_id,aila.rcv_transaction_id) and rt.transaction_type='RECEIVE') received_quantity, aila.amount line_amount, nvl(aila.base_amount,aila.amount) line_amount_functional, aila.accounting_date line_accounting_date, decode(aila.deferred_acctg_flag,'Y',xxen_util.meaning(aila.deferred_acctg_flag,'YES_NO',0)) deferred_option, aila.def_acctg_start_date deferred_start_date, aila.def_acctg_end_date deferred_end_date, aila.tax_regime_code, aila.tax, aila.tax_jurisdiction_code, aila.tax_rate_code, aila.tax_rate, (select fbc.book_type_name from fa_book_controls fbc where fbc.book_type_code=aila.asset_book_type_code) asset_book_type, (select fcbk.concatenated_segments from fa_categories_b_kfv fcbk where fcbk.category_id=aila.asset_category_id) asset_category, hl_ship_to.location_code ship_to_location_code, aida.distribution_line_number dist_line_number, xxen_util.meaning(aida.line_type_lookup_code,'INVOICE DISTRIBUTION TYPE',200) distribution_type, aida.period_name, aida.accounting_date dist_accounting_date, case when '&show_aida'='Y' then aida.quantity_invoiced else aila.quantity_invoiced end quantity_invoiced, case when '&show_aida'='Y' then aida.unit_price else aila.unit_price end unit_price, aida.amount dist_amount, nvl(aida.base_amount,aida.amount) dist_amount_functional, aida.invoice_price_variance dist_invoice_price_variance, aida.base_invoice_price_variance dist_inv_price_variance_funct, aida.dist_code_combination_id, aida.invoice_distribution_id, aida.set_of_books_id dist_ledger_id, aida.accounting_event_id dist_accounting_event_id, xxen_util.concatenated_segments(aida.dist_code_combination_id) expense_account, xxen_util.concatenated_segments(aida.price_var_code_combination_id) price_variance_account, (select xxen_util.segments_description(aida.dist_code_combination_id) from dual) expense_account_descripton, (select xxen_util.segments_description(aida.price_var_code_combination_id) from dual) price_variance_account_desc, xxen_util.meaning(aida.dist_match_type,'MATCH_STATUS',200) dist_match_type, decode(aida.match_status_flag,'A','Validated','T','Tested','S','Stopped','Never Validated') dist_match_status, (select pha.segment1 from po_headers_all pha where nvl((select pda.po_header_id from po_distributions_all pda where nvl(aida.po_distribution_id,aila.po_distribution_id)=pda.po_distribution_id),aia.quick_po_header_id)=pha.po_header_id) po_number, nvl((select max(rt.transaction_date) from rcv_transactions rt where rt.transaction_id = nvl(aida.rcv_transaction_id,aila.rcv_transaction_id) and rt.transaction_type in ('RECEIVE','DELIVER')), (select max(rt.transaction_date) from rcv_transactions rt where rt.po_line_id = aila.po_line_id and rt.po_line_location_id = aila.po_line_location_id and rt.transaction_type in ('RECEIVE','DELIVER')) ) receipt_date, (select listagg(x.receipt_num,',') within group (order by x.receipt_num) receipt_num from (select distinct rsh.receipt_num from rcv_transactions rt, rcv_shipment_lines rsl, rcv_shipment_headers rsh where rt.transaction_id=nvl(aida.rcv_transaction_id,aila.rcv_transaction_id) and rsl.shipment_line_id=rt.shipment_line_id and rt.transaction_type in ('RECEIVE','DELIVER') and rsh.shipment_header_id=rt.shipment_header_id ) x ) receipt_number, xxen_util.meaning(aida.assets_tracking_flag,'YES_NO',0) dist_asset_tracking_flag, aida.assets_addition_flag dist_assets_addition_flag, replace(aida.description,'~','-') dist_description, aida.expenditure_item_date pa_expenditure_item_date, aida.expenditure_type pa_expenditure_type, coalesce(haouv1.name,haouv3.name,haouv2.name) expenditure_organization, nvl(xxen_util.meaning(aida.pa_addition_flag,'PA_ADDITION_FLAG',275),aida.pa_addition_flag) pa_addition_flag, aida.project_accounting_context, ppa.segment1 project_number, ppa.name project_name, ppa.description project_description, ppa.project_type, ppa.project_status_code, ppa.start_date project_start_date, ppa.completion_date project_completion_date, pt.task_number, pt.task_name, pt.description task_description, pt.service_type_code, pt.start_date task_start_date, pt.completion_date task_completion_date, arpa.recurring_pay_num recurring_pmt_number, arpa.rec_pay_period_type recurring_pmt_period_type, arpa.num_of_periods recurring_number_of_periods, arpa.description recurring_pmt_description, aps.num_1099 supplier_taxpayer_id, aps.vat_registration_num supplier_tax_registration, aps.end_date_active supplier_inactive_on, aps.customer_num, xxen_util.yes(aps.one_time_flag) one_time_supplier, aps.credit_status_lookup_code supplier_credit_status, aps.credit_limit, aps.withholding_status_lookup_code supplier_withholding_status, aps.withholding_start_date, aps.vat_code supplier_vat_code, assa.vendor_site_code_alt supplier_site_alt, xxen_util.yes(assa.purchasing_site_flag) purchasing_site, xxen_util.yes(assa.rfq_only_site_flag) rfq_site, xxen_util.yes(assa.pay_site_flag) pay_site, xxen_util.yes(assa.tax_reporting_site_flag) tax_reporting_site, xxen_util.yes(assa.pcard_site_flag) p_card_site, xxen_util.yes(assa.attention_ar_flag) attention_ar, nvl(assa.address_line1,hl.address1) address_line1, nvl(assa.address_line2,hl.address2) address_line2, nvl(assa.address_line3,hl.address3) address_line3, nvl(assa.address_line4,hl.address4) address_line4, nvl(assa.city,hl.city) city, nvl(assa.state,hl.state) state, nvl(assa.zip,hl.postal_code) zip, nvl(assa.county,hl.county) county, nvl(assa.province,hl.province) province, nvl(assa.country,hl.country) country, assa.area_code, assa.phone, assa.fax_area_code, assa.fax, assa.supplier_notif_method, assa.email_address, assa.remittance_email, &dff_columns xxen_util.user_name(aia.created_by) created_by, xxen_util.client_time(aia.creation_date) creation_date, xxen_util.user_name(aia.last_updated_by) last_updated_by, xxen_util.client_time(aia.last_update_date) last_update_date, xxen_util.user_name(aila.created_by) line_created_by, xxen_util.client_time(aila.creation_date) line_creation_date, xxen_util.user_name(aila.last_updated_by) line_last_updated_by, xxen_util.client_time(aila.last_update_date) line_last_update_date, xxen_util.user_name(aida.created_by) dist_created_by, xxen_util.client_time(aida.creation_date) dist_creation_date, xxen_util.user_name(aida.last_updated_by) dist_last_updated_by, xxen_util.client_time(aida.last_update_date) dist_last_update_date, aia.invoice_id, aia.org_id, decode(row_number() over (partition by apsa.invoice_id order by nvl(apsa.payment_num,1),aila.line_number,aida.accounting_date,aida.distribution_line_number),1,'Y') first_invoice, decode(row_number() over (partition by apsa.invoice_id,apsa.payment_num order by aila.line_number,aida.accounting_date,aida.distribution_line_number),1,'Y') first_psched, decode(row_number() over (partition by apsa.invoice_id,aila.line_number order by aida.accounting_date,aida.distribution_line_number),1,'Y') first_line from gl_ledgers gl, hr_all_organization_units_vl haouv, hr_all_organization_units_vl haouv1, hr_all_organization_units_vl haouv2, hr_all_organization_units_vl haouv3, ap_invoices_all aia, ap_batches_all aba, ap_payment_schedules_all apsa, iby_ext_bank_accounts ieba, ap_suppliers aps, ap_supplier_sites_all assa, hz_parties hp, hz_party_sites hps, hz_locations hl, hr_locations hl_ship_to, (select aila.* from ap_invoice_lines_all aila where '&show_aila'='Y') aila, (select aida2.* from ap_invoice_distributions_all aida2, gl_code_combinations gcc where '&show_aida'='Y' and 2=2 and aida2.dist_code_combination_id=gcc.code_combination_id) aida, ap_recurring_payments_all arpa, ap_terms at, pa_projects_all ppa, pa_tasks pt &il_from where 1=1 and aia.set_of_books_id=gl.ledger_id and aia.org_id=haouv.organization_id(+) and aia.expenditure_organization_id=haouv1.organization_id(+) and aila.expenditure_organization_id=haouv2.organization_id(+) and aida.expenditure_organization_id=haouv3.organization_id(+) and aia.batch_id=aba.batch_id(+) and aia.invoice_id=apsa.invoice_id and apsa.external_bank_account_id=ieba.ext_bank_account_id(+) and aia.vendor_id=aps.vendor_id(+) and aia.vendor_site_id=assa.vendor_site_id(+) and aia.party_id=hp.party_id(+) and aia.party_site_id=hps.party_site_id(+) and hps.location_id=hl.location_id(+) and aila.ship_to_location_id=hl_ship_to.location_id(+) and decode(apsa.payment_num,1,apsa.invoice_id)=aila.invoice_id(+) and aila.invoice_id=aida.invoice_id(+)and aila.line_number=aida.invoice_line_number(+) and aida.project_id=ppa.project_id(+)and aida.task_id=pt.task_id(+)and aia.recurring_payment_id=arpa.recurring_payment_id(+) and aia.terms_id=at.term_id(+) and ( :p_has_dist_criteria is null or aia.invoice_id in --need this to apply dist level restrictions in case report is run at header or line level ( select aida2.invoice_id from ap_invoice_distributions_all aida2, gl_code_combinations gcc where 2=2 and nvl(aila.line_number,aida2.invoice_line_number)=aida2.invoice_line_number and nvl(aida.distribution_line_number,aida2.distribution_line_number)=aida2.distribution_line_number and aida2.dist_code_combination_id=gcc.code_combination_id ) ) &il_where ) ap_inv ) ap_inv, &l_ap_sla_table gl_code_combinations_kfv gcck where &l_ap_sla_join ap_inv.dist_code_combination_id=gcck.code_combination_id(+) ) ap_inv, ( select distinct x.invoice_id, listagg(x.hold_name,', ') within group (order by x.hold_name) over (partition by x.invoice_id) holds, case count(distinct fnd_date.date_to_displaydate(x.hold_date)) over (partition by x.invoice_id) when 1 then to_char(fnd_date.date_to_displaydate(x.hold_date)) else listagg(to_char(fnd_date.date_to_displaydate(x.hold_date)),', ') within group (order by x.hold_name) over (partition by x.invoice_id) end hold_dates, case count(distinct x.held_by_user_name) over (partition by x.invoice_id) when 1 then x.held_by_user_name else listagg(x.held_by_user_name,', ') within group (order by x.hold_name) over (partition by x.invoice_id) end holds_held_by, x.po_ref hold_po_references, x.receipt_ref hold_receipt_references from ( select distinct aha.invoice_id, alc.displayed_field hold_name, first_value(aha.hold_date) over (partition by aha.invoice_id,aha.hold_lookup_code order by aha.hold_date) hold_date, first_value(decode(aha.held_by,5,xxen_util.meaning('SYSTEM','NLS TRANSLATION',200),xxen_util.user_name(aha.held_by))) over (partition by aha.invoice_id,aha.hold_lookup_code order by aha.hold_date) held_by_user_name, po.po_ref, rcv.receipt_ref from ap_holds_all aha, ap_lookup_codes alc, (select distinct x.invoice_id, listagg(x.po_ref,',') within group (order by x.po_ref) over (partition by x.invoice_id) po_ref from (select distinct aha.invoice_id, nvl(pha.clm_document_number,pha.segment1)||'('||nvl(to_char(pra.release_num),' ')||')('||nvl(pla.line_num_display, to_char(pla.line_num))||')('||plla.shipment_num||')' po_ref from ap_holds_all aha, po_line_locations_all plla, po_lines_all pla, po_headers_all pha, po_releases_all pra where aha.release_lookup_code is null and plla.line_location_id=aha.line_location_id and pla.po_line_id=plla.po_line_id and pha.po_header_id=plla.po_header_id and pra.po_release_id (+)=plla.po_release_id ) x ) po, (select distinct x.invoice_id, listagg(x.receipt_ref,',') within group (order by x.receipt_ref) over (partition by x.invoice_id) receipt_ref from (select distinct aha.invoice_id, rsh.receipt_num||'('||rsl.line_num||')' receipt_ref from ap_holds_all aha, rcv_transactions rt, rcv_shipment_lines rsl, rcv_shipment_headers rsh where aha.release_lookup_code is null and rt.transaction_id=aha.rcv_transaction_id and rsl.shipment_line_id=rt.shipment_line_id and rsh.shipment_header_id=rt.shipment_header_id ) x ) rcv where aha.release_lookup_code is null and aha.hold_lookup_code=alc.lookup_code and alc.lookup_type='HOLD CODE' and aha.invoice_id=po.invoice_id(+) and aha.invoice_id=rcv.invoice_id(+) ) x ) ap_holds, (select fad.attached_document_id attachment_id, fad.entity_name, fad.pk1_value entity_id, fad.seq_num, fdd.user_name type, fdct.user_name category, fdt.title, fdt.description, case fd.datatype_id when 1 then (select fdst.short_text from fnd_documents_short_text fdst where fdst.media_id = fd.media_id) when 5 then fd.url when 6 then (select fl.file_name from fnd_lobs fl where fl.file_id = fd.media_id) else null end content, case when fd.datatype_id = 6 then fd.media_id else null end file_id from fnd_attached_documents fad, fnd_documents fd, fnd_documents_tl fdt, fnd_document_datatypes fdd, fnd_document_categories_tl fdct where fad.document_id = fd.document_id and fd.document_id = fdt.document_id and fdt.language = userenv('lang') and fd.datatype_id = fdd.datatype_id and fdd.language = userenv('lang') and fd.category_id = fdct.category_id and fdct.language = userenv('lang') and fd.datatype_id in (1,5,6) ) attchmt, gl_code_combinations_kfv gcck where 3=3 and ap_inv.invoice_id=ap_holds.invoice_id(+) and decode(:p_show_attachments,'Y','AP_INVOICES',null)=attchmt.entity_name(+) and decode(:p_show_attachments,'Y',to_char(ap_inv.invoice_id),null)=attchmt.entity_id(+) and ap_inv.dist_code_combination_id=gcck.code_combination_id(+) order by ap_inv.operating_unit, ap_inv.supplier, ap_inv.supplier_number, ap_inv.invoice_date, ap_inv.invoice_gl_date, ap_inv.invoice_num, ap_inv.payment_num, ap_inv.line_number_key, ap_inv.dist_accounting_date_key, ap_inv.dist_line_number_key, attchmt.seq_num Added India GST columns joined through the invoice line, displayed only when India Localization is installed. SQLAP #,##0.00;[Red]-#,##0.00 (:Ledger is not null or :Operating_Unit is not null) AR AP Invoices and Lines تقرير تقادم الفاتورة التفصيلي مع تفاصيل البند والمبالغ D AP Invoices and Lines Detaillierter Bericht über die Fälligkeit von Rechnungen mit Positionsdetails und Beträgen E AP Invoices and Lines Informe detallado de la antigüedad de las facturas con detalles de las partidas e importes F AP Invoices and Lines Facture détaillée Rapport sur le vieillissement avec les détails et les montants des postes I AP Invoices and Lines Rapporto dettagliato sull'invecchiamento delle fatture con dettagli e importi delle voci JA AP Invoices and Lines ライン項目の詳細と金額が記載された請求書の詳細なエージングレポート KO AP Invoices and Lines 개별 항목 세부 정보 및 금액이 포함 된 세부 송장 월령 보고서 PTB AP Invoices and Lines Relatório detalhado do envelhecimento da fatura com detalhes e valores dos itens de linha RU AP Invoices and Lines Подробный отчет о старении счета-фактуры с деталями и суммами по отдельным позициям S AP Invoices and Lines Detaljfakturaåldringsrapport med radinformation och belopp TR AP Invoices and Lines Satır öğesi ayrıntıları ve tutarlarını içeren Ayrıntılı Fatura Yaşlandırma raporu US AP Invoices and Lines Detail Invoice Aging report with line item details and amounts Parameter explanation: - Invoice on Hold (LOV: Yes/No) Default Value = Null (Hold Status not considered as per current behaviour) Yes - Only Invoices that currently have an unreleased hold will be retrieved No - Only Invoices that do not currently have an unreleased hold will be retrieved - Hold Name (LOV: AP Hold Names) Default Value Null If specified - only Invoices that currently have an unreleased hold of the specified name will be retrieved Hold column explanation: - Holds - a list of the current unreleased holds against the invoice - Hold Dates - the Hold Date - Holds Held By - the name of user who applied the holds - Hold PO References - Identifies the matching POs for PO Matching Holds in the format: PO Number(Release number)(Line Number)(Shipment Number) - Hold Receipt References - Identifies the matching Receipts for Receipt Matching Holds in the format: Receipt Number(Line Number) When India Localization is installed, the following GST columns are also included: GST Supplier Registration No, GST PAN No, GST TAN No, Supplier GSTIN (Invoice Date), CGST Amount, SGST Amount, IGST Amount, CESS Amount, CGST Rate, SGST Rate, IGST Rate, CESS Rate, CGST Recoverable, CGST Non-Recoverable, SGST Recoverable, SGST Non-Recoverable, IGST Recoverable, IGST Non-Recoverable, Reverse Charge Flag, Place of Supply, Ship From State, Custom Amount, Unclassified Tax Amount, Unclassified Tax Rate Codes, Receipt Tax Not On Invoice, Taxable Value, HSN SAC Code. Please enter a Ledger or Operating Unit ZHS AP Invoices and Lines 详细的发票账龄报告，包括项目细节和金额 Enginatics &aging_bucket_cols1 &aging_bucket_cols2 &aging_bucket_cols3 &attachment_amount_columns &attachment_columns &dff_columns &dff_columns2 &gcc_dist_segment_columns &il_columns &il_from &il_pass_through &il_reg_columns &il_select &il_where &invoice_detail_columns &l_ap_sla_join &l_ap_sla_sel &l_ap_sla_sel2 &l_ap_sla_table &show_aida &show_aila 1=1 2=2 3=3 :p_has_dist_criteria :p_show_attachments 1 -100 &attachment_amount_columns decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.invoice_amount) invoice_amount, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.items_total) items_total, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.retainage_total) retainage_total, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.prepayments_applied_total) prepayments_applied_total, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.witholding_total) witholding_total, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.invoice_subtotal) invoice_subtotal, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.total_tax_amount) tax_total, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.self_assessed_tax_amount) self_assessed_tax_amount, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.freight_total) freight_total, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.miscellaneous_total) miscellaneous_total, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.invoice_total) invoice_total, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.amount_applicable_to_discount) amount_applicable_to_discount, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.discount_amount_taken) discount_amount_taken, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.approved_amount) approved_amount, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.pay_curr_invoice_amount) payment_curr_invoice_amount, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.amount_paid) amount_paid, ap_inv.base_currency functional_currency, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.invoice_amount_base) invoice_amount_functional, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.tax_amount_base) tax_amount_functional, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.amt_applicable_to_disc_base) amt_applicable_to_disc_funct, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.discount_amount_taken_base) discount_amount_taken_funct, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.approved_amount_base) approved_amount_functional, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.amount_paid_base) amount_paid_functional, ap_inv.payment_num, ap_inv.payment_date, decode(ap_inv.first_psched||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.gross_amount) payment_num_gross_amount, decode(ap_inv.first_psched||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.amount_remaining) payment_num_amount_remaining, &il_reg_columns &il_columns &aging_bucket_cols2 ap_inv.payment_cross_rate, ap_inv.payment_cross_rate_date, ap_inv.discount_date, ap_inv.future_pay_due_date, ap_inv.payment_method, ap_inv.payment_priority, ap_inv.invoice_payment_status, ap_inv.schedule_payment_status, ap_inv.second_discount_date, ap_inv.third_discount_date, decode(ap_inv.first_psched||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.discount_amount_available) discount_amount_available, decode(ap_inv.first_psched||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.second_disc_amt_available) second_disc_amt_available, decode(ap_inv.first_psched||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.third_disc_amt_available) third_disc_amt_available, decode(ap_inv.first_psched||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.discount_amount_remaining) discount_amount_remaining, decode(ap_inv.first_psched||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.inv_curr_gross_amount) inv_curr_gross_amount, ap_inv.bank_name, ap_inv.iban, ap_inv.bank_account_num, ap_inv.bank_branch_name, ap_inv.invoice_terms, ap_inv.terms_date, ap_inv.invoice_cancelled_date, ap_inv.invoice_cancelled_by, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.invoice_cancelled_amount) invoice_cancelled_amount, decode(ap_inv.first_invoice||case when nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then 'Y' end,'YY',ap_inv.invoice_temp_cancelled_amount) invoice_temp_cancelled_amount, Char Y US Attachment Amount Columns 2 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 3 &il_columns case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .cgst_amount end "CGST Amount", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .sgst_amount end "SGST Amount", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .igst_amount end "IGST Amount", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .cess_amount end "CESS Amount", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .cgst_tax_rate end "CGST Rate", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .sgst_tax_rate end "SGST Rate", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .igst_tax_rate end "IGST Rate", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .cess_tax_rate end "CESS Rate", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .cgst_rec_amount end "CGST Recoverable", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .cgst_nrec_amount end "CGST Non-Recoverable", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .sgst_rec_amount end "SGST Recoverable", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .sgst_nrec_amount end "SGST Non-Recoverable", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .igst_rec_amount end "IGST Recoverable", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .igst_nrec_amount end "IGST Non-Recoverable", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .reverse_charge_flag end reverse_charge_flag, case when ap_inv.first_invoice='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .place_of_supply end "Place of Supply", case when ap_inv.first_invoice='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .ship_from_state end ship_from_state, case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .custom_amount end "Custom Amount", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .unclassified_amount end "Unclassified Tax Amount", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ( select listagg(ut.tax_rate_code,', ') within group (order by ut.tax_rate_code) from ( select distinct jtlv.trx_id, jtlv.trx_line_id, jtlv.tax_rate_code from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where jtlv.tax_type_id=jrav.entity_id(+) and jrav.entity_code(+)='TAX_TYPE' and jrav.reporting_type_code(+)='TAX_TYPES_CLASSIFICATION' and jrav.reporting_code is null and jtlv.application_id=200 ) ut where ut.trx_id=ap_inv.invoice_id and nvl(ut.trx_line_id,0)=nvl(ap_inv.line_number_key,0) ) end "Unclassified Tax Rate Codes", case when ap_inv.first_invoice='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then (select sum(jtl.rounded_tax_amt_fun_curr) from jai_tax_det_factors jtdf, jai_tax_lines jtl where jtdf.trx_id=ap_inv.invoice_id and jtdf.entity_code='AP_INVOICES' and jtdf.application_id=200 and jtdf.party_type='Supplier' and jtdf.applied_to_trx_id is not null and jtdf.applied_to_trx_type='RECEIVE' and jtdf.det_factor_id=jtl.det_factor_id and nvl(jtl.self_assessed_flag,'N')<>'Y' and nvl(jai_boe_tax_det_pkg.is_boe_tax(jtl.tax_type_id),'N')<>'Y' and not exists (select null from ap_invoice_lines_all aila2 where aila2.invoice_id=jtl.trx_id and aila2.description=jtl.tax_rate_code and aila2.reference_2=to_char(jtl.tax_line_id)) ) end "Receipt Tax Not On Invoice", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .taxable_value end "Taxable Value", case when ap_inv.first_line='Y' and nvl(attchmt.seq_num,-1)=nvl(min(attchmt.seq_num) over (partition by ap_inv.invoice_id),-1) then ap_inv .hsn_sac_code end "HSN SAC Code", Y US India Localization 4 &il_from ,(select jpr.party_id, jpr.party_site_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_site, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_site, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_site, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_site from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.supplier_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id, jpr.party_site_id) il_reg_site ,(select jpr.party_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin_hdr, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_hdr, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_hdr, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_hdr, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_hdr from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.supplier_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id) il_reg_hdr, (select jtlv.trx_id, jtlv.trx_line_id, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_tax_amt_trx_curr)) cgst_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rounded_tax_amt_trx_curr)) sgst_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rounded_tax_amt_trx_curr)) igst_amount, sum(decode(jrav.reporting_code,'CESS',jtlv.rounded_tax_amt_trx_curr)) cess_amount, max(decode(jrav.reporting_code,'CGST',jtlv.tax_rate_code)) cgst_tax_rate, max(decode(jrav.reporting_code,'SGST',jtlv.tax_rate_code)) sgst_tax_rate, max(decode(jrav.reporting_code,'IGST',jtlv.tax_rate_code)) igst_tax_rate, max(decode(jrav.reporting_code,'CESS',jtlv.tax_rate_code)) cess_tax_rate, sum(decode(jrav.reporting_code,'CGST',jtlv.rec_tax_amt_trx_curr)) cgst_rec_amount, sum(decode(jrav.reporting_code,'CGST',jtlv.nrec_tax_amt_trx_curr)) cgst_nrec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rec_tax_amt_trx_curr)) sgst_rec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.nrec_tax_amt_trx_curr)) sgst_nrec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rec_tax_amt_trx_curr)) igst_rec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.nrec_tax_amt_trx_curr)) igst_nrec_amount, max(jtlv.self_assessed_flag) reverse_charge_flag, sum(decode(jrav.reporting_code,'CUSTOM',jtlv.rounded_tax_amt_trx_curr)) custom_amount, sum(decode(jrav.reporting_code,null,jtlv.rounded_tax_amt_trx_curr)) unclassified_amount, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_taxable_amt_trx_curr,'IGST',jtlv.rounded_taxable_amt_trx_curr)) taxable_value, max(jrc.reporting_code) hsn_sac_code from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav, jai_tax_det_factors jtdf, jai_reporting_codes jrc where jrav.entity_id(+)=jtlv.tax_type_id and jrav.entity_code(+)='TAX_TYPE' and jrav.reporting_type_code(+)='TAX_TYPES_CLASSIFICATION' and jtlv.det_factor_id=jtdf.det_factor_id(+) and jtdf.application_id(+)=200 and nvl(jtdf.hsn_code_id,jtdf.sac_code_id)=jrc.reporting_code_id(+) and jtlv.application_id=200 group by jtlv.trx_id, jtlv.trx_line_id ) il_tax Y US India Localization 5 &il_pass_through ap_inv .cgst_amount, ap_inv .sgst_amount, ap_inv .igst_amount, ap_inv .cess_amount, ap_inv .cgst_tax_rate, ap_inv .sgst_tax_rate, ap_inv .igst_tax_rate, ap_inv .cess_tax_rate, ap_inv .cgst_rec_amount, ap_inv .cgst_nrec_amount, ap_inv .sgst_rec_amount, ap_inv .sgst_nrec_amount, ap_inv .igst_rec_amount, ap_inv .igst_nrec_amount, ap_inv .reverse_charge_flag, ap_inv .place_of_supply, ap_inv .ship_from_state, ap_inv .gst_supplier_registration_no, ap_inv .gst_pan_no, ap_inv .gst_tan_no, ap_inv .gstin_invoice_date, ap_inv .custom_amount, ap_inv .unclassified_amount, ap_inv .taxable_value, ap_inv .hsn_sac_code, Y US India Localization 6 &il_reg_columns ap_inv .gst_supplier_registration_no "GST Supplier Registration No", ap_inv .gst_pan_no "GST PAN No", ap_inv .gst_tan_no "GST TAN No", ap_inv .gstin_invoice_date "Supplier GSTIN (Invoice Date)", Y US India Localization 7 &il_select il_tax.cgst_amount, il_tax.sgst_amount, il_tax.igst_amount, il_tax.cess_amount, il_tax.cgst_tax_rate, il_tax.sgst_tax_rate, il_tax.igst_tax_rate, il_tax.cess_tax_rate, il_tax.cgst_rec_amount, il_tax.cgst_nrec_amount, il_tax.sgst_rec_amount, il_tax.sgst_nrec_amount, il_tax.igst_rec_amount, il_tax.igst_nrec_amount, il_tax.reverse_charge_flag, (select max(jtdf.ship_to_state) from jai_tax_det_factors jtdf where jtdf.trx_id=aia.invoice_id and jtdf.application_id=200) place_of_supply, (select max(jtdf.ship_from_state) from jai_tax_det_factors jtdf where jtdf.trx_id=aia.invoice_id and jtdf.application_id=200) ship_from_state, nvl(il_reg_site.gstin,il_reg_hdr.gstin_hdr) gst_supplier_registration_no, nvl(il_reg_site.pan_site,nvl(il_reg_site.sec_pan_site,nvl(il_reg_hdr.pan_hdr,il_reg_hdr.sec_pan_hdr))) gst_pan_no, nvl(il_reg_site.tan_site,nvl(il_reg_site.sec_tan_site,nvl(il_reg_hdr.tan_hdr,il_reg_hdr.sec_tan_hdr))) gst_tan_no, nvl(coalesce( ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.supplier_flag='Y' and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.party_id=aia.vendor_id and jpr.party_site_id=aia.vendor_site_id and jprl.registration_type_code in ('GST','GSTIN') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.effective_from<=aia.invoice_date and (jprl.effective_to is null or jprl.effective_to>=aia.invoice_date) and rownum=1 ), ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.supplier_flag='Y' and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.party_id=aia.vendor_id and jpr.party_site_id=aia.vendor_site_id and regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.effective_from<=aia.invoice_date and (jprl.effective_to is null or jprl.effective_to>=aia.invoice_date) and rownum=1 )),coalesce( ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.supplier_flag='Y' and jpr.party_type_code='THIRD_PARTY' and jpr.party_id=aia.vendor_id and jprl.registration_type_code in ('GST','GSTIN') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.effective_from<=aia.invoice_date and (jprl.effective_to is null or jprl.effective_to>=aia.invoice_date) and rownum=1 ), ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.supplier_flag='Y' and jpr.party_type_code='THIRD_PARTY' and jpr.party_id=aia.vendor_id and regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.effective_from<=aia.invoice_date and (jprl.effective_to is null or jprl.effective_to>=aia.invoice_date) and rownum=1 ))) gstin_invoice_date, il_tax.custom_amount, il_tax.unclassified_amount, il_tax.taxable_value, il_tax.hsn_sac_code, Y US India Localization 8 &il_where and aia.vendor_site_id=il_reg_site.party_site_id(+) and aia.vendor_id=il_reg_hdr.party_id(+) and aila.invoice_id=il_tax.trx_id(+) and nvl(aila.line_number,0)=nvl(il_tax.trx_line_id(+),0) Y US India Localization 9 -10 :p_has_dist_criteria Char select 'Y' from dual where :$flex$.accounting_period is not null or :$flex$.accounting_date_from is not null or :$flex$.accounting_date_to is not null or :$flex$.expense_account_from is not null or :$flex$.expense_account_to is not null US Distribution Selection Criteria 10 10 1=1 gl.name=:ledger and haouv.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='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(+)) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name coalesce(xxen_util.previous_parameter_value(:parameter_id),xxen_util.default_ledger) F Grand livre US Ledger 11 &gcc_dist_segment_columns select distinct 'gcck.'||lower(fifsv.application_column_name)||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",'||chr(10)|| 'xxen_util.segment_description(gcck.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcck.chart_of_accounts_id'|| ( select ', gcck.'||lower(fifs.application_column_name) from fnd_id_flex_segments fifs where fifsv.application_id=fifs.application_id and fifsv.id_flex_code=fifs.id_flex_code and fifsv.id_flex_num=fifs.id_flex_num and fifsv.enabled_flag=fifs.enabled_flag and fifs.flex_value_set_id=(select ffvs.parent_flex_value_set_id from fnd_flex_value_sets ffvs where fifsv.flex_value_set_id=ffvs.flex_value_set_id) )||') "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length-5)||' desc",' text, min(fifsv.id_flex_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_id_flex_num, min(fifsv.segment_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_segment_num from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where gl.ledger_id in(select asp.set_of_books_id from ap_system_parameters asp) and (:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and rownum=1 ) and fifsv.enabled_flag='Y' order by min_id_flex_num, min_segment_num F Grand livre US Ledger 12 20 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name F Unité d'exploitation US Operating Unit 13 30 &aging_bucket_cols1 select 'case when ceil(sysdate-apsa.due_date) between ' || to_char(aap.days_start) || ' and ' || to_char(aap.days_to) || ' and apsa.amount_remaining != 0 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end bucket' || to_char(aap.period_sequence_num) || ',' from (select aapl.period_sequence_num, aapl.days_start, aapl.days_to, substr(aapl.report_heading1 || nvl2(aapl.report_heading1,nvl2(aapl.report_heading2,' ',null),null) || aapl.report_heading2,1,xxen_report.max_column_length) title from ap_aging_periods aap, ap_aging_period_lines aapl where aap.aging_period_id = aapl.aging_period_id and aap.period_name = :p_aging_bucket_name order by aapl.period_sequence_num ) aap union select ' case when ceil(sysdate-apsa.due_date)<=0 and apsa.amount_remaining != 0 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end current_bucket, case when ceil(sysdate-apsa.due_date) between 1 and 30 and apsa.amount_remaining != 0 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_1_30, case when ceil(sysdate-apsa.due_date) between 31 and 60 and apsa.amount_remaining != 0 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_31_60, case when ceil(sysdate-apsa.due_date) between 61 and 90 and apsa.amount_remaining != 0 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_61_90, case when ceil(sysdate-apsa.due_date) between 91 and 120 and apsa.amount_remaining != 0 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_91_120, case when ceil(sysdate-apsa.due_date) between 121 and 150 and apsa.amount_remaining != 0 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_121_150, case when ceil(sysdate-apsa.due_date) between 151 and 180 and apsa.amount_remaining != 0 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_151_180, case when ceil(sysdate-apsa.due_date) >=181 and apsa.amount_remaining != 0 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end greater_than_180,' from dual where not exists (select null from ap_aging_periods aap where aap.period_name = :p_aging_bucket_name and nvl(aap.status,'A') = 'A') LOV custom Y select x.value, x.description from ( select aap.period_name value, aap.description description from ap_aging_periods aap where nvl(aap.status,'A') = 'A' union select '*Default*' value, null description from dual ) x order by x.value *Default* Y F Nom du seau de vieillissement US Aging Bucket Name 14 &aging_bucket_cols2 select 'case ap_inv.first_psched when ''Y'' then ap_inv.bucket' || to_char(aap.period_sequence_num) || ' end "' || aap.title || '",' from (select aapl.period_sequence_num, aapl.days_start, aapl.days_to, substr(aapl.report_heading1 || nvl2(aapl.report_heading1,nvl2(aapl.report_heading2,' ',null),null) || aapl.report_heading2,1,xxen_report.max_column_length) title from ap_aging_periods aap, ap_aging_period_lines aapl where aap.aging_period_id = aapl.aging_period_id and aap.period_name = :p_aging_bucket_name order by aapl.period_sequence_num ) aap union select 'case ap_inv.first_psched when ''Y'' then ap_inv.current_bucket end "Current", case ap_inv.first_psched when ''Y'' then ap_inv.between_1_30 end between_1_30, case ap_inv.first_psched when ''Y'' then ap_inv.between_31_60 end between_31_60, case ap_inv.first_psched when ''Y'' then ap_inv.between_61_90 end between_61_90, case ap_inv.first_psched when ''Y'' then ap_inv.between_91_120 end between_91_120, case ap_inv.first_psched when ''Y'' then ap_inv.between_121_150 end between_121_150, case ap_inv.first_psched when ''Y'' then ap_inv.between_151_180 end between_151_180, case ap_inv.first_psched when ''Y'' then ap_inv.greater_than_180 end greater_than_180,' from dual where not exists (select null from ap_aging_periods aap where aap.period_name = :p_aging_bucket_name and nvl(aap.status,'A') = 'A') F Nom du seau de vieillissement US Aging Bucket Name 15 &aging_bucket_cols3 select 'ap_inv.bucket'||to_char(aap.period_sequence_num)||' bucket'||to_char(aap.period_sequence_num)||',' from (select aapl.period_sequence_num from ap_aging_periods aap, ap_aging_period_lines aapl where aap.aging_period_id=aapl.aging_period_id and aap.period_name=:p_aging_bucket_name order by aapl.period_sequence_num) aap union select 'ap_inv.current_bucket,' from dual where not exists (select null from ap_aging_periods aap where aap.period_name=:p_aging_bucket_name and nvl(aap.status,'A')='A') US Aging Bucket Name 16 40 1=1 :display_level=:display_level LOV custom Y select 'Headers' value, 'Show Invoice Headers and Payment Schedules only' description from dual union all select 'Lines' value, 'Show Invoice Headers and Lines only' description from dual union all select 'Lines and Distributions' value, 'Show Invoice Headers, Lines and Distributions' description from dual Lines and Distributions Headers Y US Display Level 17 &invoice_detail_columns ap_inv.line_number, ap_inv.line_type, ap_inv.line_source, ap_inv.line_discarded, ap_inv.line_description, ap_inv.item_code, ap_inv.item_description, ap_inv.line_uom, ap_inv.inventory_organization, ap_inv.received_quantity, case ap_inv.first_line when 'Y' then ap_inv.line_amount end line_amount, case ap_inv.first_line when 'Y' then ap_inv.line_amount_functional end line_amount_functional, ap_inv.line_accounting_date, ap_inv.deferred_option, ap_inv.deferred_start_date, ap_inv.deferred_end_date, ap_inv.tax_regime_code, ap_inv.tax, ap_inv.tax_jurisdiction_code, ap_inv.tax_rate_code, ap_inv.tax_rate, ap_inv.po_number, ap_inv.ship_to_location_code, ap_inv.receipt_date, ap_inv.receipt_number, case ap_inv.first_line when 'Y' then ap_inv.quantity_invoiced end quantity_invoiced, ap_inv.unit_price, Lines F Niveau d'affichage US Display Level 18 &invoice_detail_columns ap_inv.line_number, ap_inv.line_type, ap_inv.line_source, ap_inv.line_discarded, ap_inv.line_description, ap_inv.item_code, ap_inv.item_description, ap_inv.line_uom, ap_inv.inventory_organization, ap_inv.received_quantity, case ap_inv.first_line when 'Y' then ap_inv.line_amount end line_amount, case ap_inv.first_line when 'Y' then ap_inv.line_amount_functional end line_amount_functional, ap_inv.line_accounting_date, ap_inv.deferred_option, ap_inv.deferred_start_date, ap_inv.deferred_end_date, ap_inv.tax_regime_code, ap_inv.tax, ap_inv.tax_jurisdiction_code, ap_inv.tax_rate_code, ap_inv.tax_rate, ap_inv.po_number, ap_inv.ship_to_location_code, ap_inv.receipt_date, ap_inv.receipt_number, ap_inv.dist_line_number, ap_inv.distribution_type, ap_inv.period_name, ap_inv.dist_accounting_date, ap_inv.quantity_invoiced, ap_inv.unit_price, ap_inv.dist_amount, ap_inv.dist_amount_functional, ap_inv.dist_invoice_price_variance, ap_inv.dist_inv_price_variance_funct, ap_inv.expense_account, ap_inv.price_variance_account, ap_inv.expense_account_descripton, ap_inv.price_variance_account_desc, ap_inv.dist_match_type, ap_inv.dist_match_status, ap_inv.dist_asset_tracking_flag, ap_inv.dist_assets_addition_flag, ap_inv.dist_description, ap_inv.pa_expenditure_item_date, ap_inv.pa_expenditure_type, ap_inv.expenditure_organization, ap_inv.pa_addition_flag, ap_inv.project_accounting_context, ap_inv.project_number, ap_inv.project_name, ap_inv.project_description, ap_inv.project_type, ap_inv.project_status_code, ap_inv.project_start_date, ap_inv.project_completion_date, ap_inv.task_number, ap_inv.task_name, ap_inv.task_description, ap_inv.service_type_code, ap_inv.task_start_date, ap_inv.task_completion_date, Lines and Distributions F Niveau d'affichage US Display Level 19 &show_aida Y Lines and Distributions US Display Level 20 &show_aila Y Lines% F Niveau d'affichage US Display Level 21 50 1=1 aps.vendor_name=:supplier_name LOV AP Supplier B9847D20A0E4742FE0538931640A6379 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id F Fournisseur US Supplier 22 60 1=1 assa.vendor_site_code=:vendor_site_code LOV AP Supplier Site 8E2FF36EDF0279D2E0530100007F1FF2 Y select distinct assa.vendor_site_code value, assa.address_line1||', '||assa.city||', '||nvl(ftv.territory_short_name,assa.country) description from hr_all_organization_units_vl haouv, ap_suppliers aps, ap_supplier_sites_all assa, fnd_territories_vl ftv where (:$flex$.ap_supplier is null or xxen_util.contains(:$flex$.ap_supplier,aps.vendor_name)='Y') and (:$flex$.ap_supplier_number is null or xxen_util.contains(:$flex$.ap_supplier_number,aps.segment1)='Y') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and haouv.organization_id=assa.org_id and aps.vendor_id=assa.vendor_id and assa.country=ftv.territory_code(+) order by assa.vendor_site_code F Site du fournisseur US Supplier Site 23 70 1=1 aia.invoice_num=:invoice_number LOV AP Invoice Number 8E2FF36EDF3079D2E0530100007F1FF2 Y Y select aia.invoice_num value, aps.vendor_name||' ('||aia.invoice_date||')' description from ap_invoices_all aia, ap_suppliers aps where (:$flex$.operating_unit is null or aia.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or aia.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and aia.vendor_id=aps.vendor_id order by aia.invoice_date desc F Numéro de facture US Invoice Number 24 80 1=1 aia.invoice_date>=(select gps.start_date from gl_period_statuses gps where gps.period_name=:invoice_period_name and gps.set_of_books_id=gl.ledger_id and gps.application_id=200) and aia.invoice_date<(select gps.end_date+1 from gl_period_statuses gps where gps.period_name=:invoice_period_name and gps.set_of_books_id=gl.ledger_id and gps.application_id=200) LOV AP Period C23FDB3DBE0DC71FE0530100007FACC7 Y select gps.period_name value, xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',222)||': '||gps.start_date||' - '||gps.end_date description from gl_period_statuses gps where gps.ledger_id= ( select asp.set_of_books_id from ap_system_parameters asp, gl_ledgers gl, hr_operating_units hou where asp.set_of_books_id=gl.ledger_id and asp.set_of_books_id=hou.set_of_books_id and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,hou.name)='Y') and rownum=1 ) and gps.start_date<=sysdate+400 and gps.application_id=200 order by gps.start_date desc F Date de l'invitation Période US Invoice Date Period 25 90 1=1 aia.invoice_date>=:invoice_date_from Date select trunc(sysdate,'month') from dual where :$flex$.invoice_number is null and :$flex$.invoice_date_period is null and :$flex$.accounting_period is null and :$flex$.payment_period is null F Date de l'Inv. US Invoice Date From 26 100 1=1 aia.invoice_date<:invoice_date_to+1 Date last_day(:$flex$.invoice_date_from) F Date de l'inv. US Invoice Date To 27 101 3=3 ap_inv.validation_date>=:validation_date_from Date US Validation Date From 28 1=1 exists ( select null from xla.xla_transaction_entities xte, xla_events xe where aia.invoice_id=nvl(xte.source_id_int_1,-99) and aia.set_of_books_id=xte.ledger_id and xte.application_id=200 and xte.entity_code='AP_INVOICES' and xte.application_id=xe.application_id and xte.entity_id=xe.entity_id and xe.event_type_code like '%VALIDATED%' and xe.creation_date>=:validation_date_from ) US Validation Date From 29 102 3=3 ap_inv.validation_date<:validation_date_to+1 Date last_day(:$flex$.validation_date_from) US Validation Date To 30 1=1 exists ( select null from xla.xla_transaction_entities xte, xla_events xe where aia.invoice_id=nvl(xte.source_id_int_1,-99) and aia.set_of_books_id=xte.ledger_id and xte.application_id=200 and xte.entity_code='AP_INVOICES' and xte.application_id=xe.application_id and xte.entity_id=xe.entity_id and xe.event_type_code like '%VALIDATED%' and xe.creation_date<:validation_date_to+1 ) US Validation Date To 31 110 1=1 aia.gl_date>=:invoice_gl_date_from Date US Invoice GL Date From 32 120 1=1 aia.gl_date<:invoice_gl_date_to+1 Date last_day(:$flex$.Invoice_GL_Date_From) US Invoice GL Date To 33 130 2=2 select 'aida2.accounting_date(+)>=to_date('''||to_char(gps.start_date,'YYYY/MM/DD')||''',''YYYY/MM/DD'') and aida2.accounting_date (+) < to_date('''||to_char(gps.end_date+1,'YYYY/MM/DD')||''',''YYYY/MM/DD'')' from gl_period_statuses gps, gl_ledgers gl where gps.period_name=:accounting_period_name and gps.set_of_books_id=gl.ledger_id and gps.application_id=200 and (:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and rownum=1 LOV AP Period C23FDB3DBE0DC71FE0530100007FACC7 Y select gps.period_name value, xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',222)||': '||gps.start_date||' - '||gps.end_date description from gl_period_statuses gps where gps.ledger_id= ( select asp.set_of_books_id from ap_system_parameters asp, gl_ledgers gl, hr_operating_units hou where asp.set_of_books_id=gl.ledger_id and asp.set_of_books_id=hou.set_of_books_id and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,hou.name)='Y') and rownum=1 ) and gps.start_date<=sysdate+400 and gps.application_id=200 order by gps.start_date desc F Période comptable US Accounting Period 34 140 2=2 aida2.accounting_date(+)>=:accounting_date_from Date F Date comptable du US Accounting Date From 35 150 2=2 aida2.accounting_date(+)<:accounting_date_to+1 Date last_day(:$flex$.Accounting_Date_From) F Date de comptabilisation au US Accounting Date To 36 160 1=1 aia.invoice_id in ( select aipa.invoice_id from gl_period_statuses gps, ap_checks_all aca, ap_invoice_payments_all aipa where gps.period_name=:payment_period_name and gl.ledger_id=gps.set_of_books_id and gps.application_id=200 and aca.check_date>=gps.start_date and aca.check_date<gps.end_date+1 and aca.void_date is null and aca.stopped_date is null and aca.check_id=aipa.check_id ) Char US Payment Period 37 170 1=1 (select max(aca.check_date) from ap_invoice_payments_all aipa, ap_checks_all aca where aipa.check_id = aca.check_id and aca.void_date is null and aca.stopped_date is null and aipa.invoice_id = apsa.invoice_id and aipa.payment_num = apsa.payment_num )>=:payment_date_from Date US Payment Date From 38 180 1=1 (select max(aca.check_date) from ap_invoice_payments_all aipa, ap_checks_all aca where aipa.check_id = aca.check_id and aca.void_date is null and aca.stopped_date is null and aipa.invoice_id = apsa.invoice_id and aipa.payment_num = apsa.payment_num )<:payment_date_to+1 Date last_day(:$flex$.payment_date_from) US Payment Date To 39 190 1=1 aia.payment_status_flag in ('N','P') and apsa.amount_remaining<>0 LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y select 'Y' from dual where :$flex$.invoice_number is null and :$flex$.invoice_date_period is null and :$flex$.invoice_date_from is null and :$flex$.invoice_date_to is null and :$flex$.accounting_period is null and :$flex$.accounting_date_from is null and :$flex$.accounting_date_to is null and :$flex$.payment_period is null and :$flex$.payment_date_from is null and :$flex$.payment_date_to is null and :$flex$.invoice_status is null and :$flex$.payment_status is null and :$flex$.creation_date_from is null and :$flex$.creation_date_to is null and :$flex$.validation_date_from is null and :$flex$.validation_date_to is null F Ouvert uniquement US Open only 40 200 1=1 ceil(sysdate-apsa.due_date)>:days_due Number F Jours de retard Jours d'échéance (supérieurs à) US Days Overdue Days Due (Greater than) 41 210 1=1 aia.invoice_type_lookup_code=xxen_util.lookup_code(:invoice_type,'INVOICE TYPE',200) LOV AP Invoice Type B20C1D394FC36EDDE0530100007F5621 Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type(+)='INVOICE TYPE' and flvv.view_application_id(+)=200 and flvv.security_group_id(+)=0 order by flvv.meaning F Type de facture US Invoice Type 42 220 1=1 xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) = :p_invoice_status LOV custom Y select flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type in ('NLS TRANSLATION','PREPAY STATUS') and flvv.view_application_id = 200 and flvv.security_group_id = fnd_global.security_group_id and flvv.lookup_code in ('APPROVED', 'NEEDS REAPPROVAL', 'NEVER APPROVED', 'UNAPPROVED', 'UNPAID', 'FULL', 'PERMANENT', 'AVAILABLE', 'SELECTED FOR PAYMENT', 'SELECTED FOR VALIDATION', 'SELECTED FOR APPROVAL' ) order by flvv.meaning US Invoice Status 43 230 1=1 aia.payment_status_flag=xxen_util.lookup_code(:payment_status,'INVOICE PAYMENT STATUS',200) LOV custom Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type(+)='INVOICE PAYMENT STATUS' and flvv.view_application_id(+)=200 and flvv.security_group_id(+)=0 order by flvv.lookup_code F Statut du paiement US Payment Status 44 240 2=2 select x.segment_name||'>='''||x.seg_val||''''||decode(rownum,count,null,' and') from ( select count(*) over () count, 'gcc.'||fifs.application_column_name segment_name, substr(:p_expense_account_from,sum(fifs.display_size+1) over (order by fifs.segment_num rows between unbounded preceding and current row)-fifs.display_size,fifs.display_size) seg_val from fnd_id_flex_segments fifs where fifs.enabled_flag='Y' and fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ap_system_parameters asp) and (:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and rownum=1) order by fifs.segment_num ) x LOV custom Y Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.chart_of_accounts_id= ( select gl.chart_of_accounts_id from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ap_system_parameters asp) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and rownum=1 ) and gcck.detail_posting_allowed='Y' and gcck.enabled_flag='Y' and gcck.summary_flag='N' order by gcck.concatenated_segments F Compte de dépenses De US Expense Account From 45 250 2=2 select x.segment_name||'<='''||x.seg_val||''''||decode(rownum,count,null,' and') from ( select count(*) over () count, 'gcc.'||fifs.application_column_name segment_name, substr(:p_expense_account_to,sum(fifs.display_size+1) over (order by fifs.segment_num rows between unbounded preceding and current row)-fifs.display_size,fifs.display_size) seg_val from fnd_id_flex_segments fifs where fifs.enabled_flag='Y' and fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ap_system_parameters asp) and (:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and rownum=1) order by fifs.segment_num ) x LOV custom Y Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.chart_of_accounts_id= ( select gl.chart_of_accounts_id from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ap_system_parameters asp) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and rownum=1 ) and gcck.detail_posting_allowed='Y' and gcck.enabled_flag='Y' and gcck.summary_flag='N' order by gcck.concatenated_segments :$flex$.Expense_Account_From F Compte de dépenses à US Expense Account To 46 260 1=1 aia.cancelled_date is null LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y select 'Y' from dual where :$flex$.invoice_number is null F Exclure Annulé US Exclude Cancelled 47 265 1=1 exists (select null from ap_invoice_lines_all aila2 where aia.invoice_id=aila2.invoice_id and aila2.po_header_id is not null) LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Invoice PO Matched 48 1=1 not exists (select null from ap_invoice_lines_all aila2 where aia.invoice_id=aila2.invoice_id and aila2.po_header_id is not null) N US Invoice PO Matched 49 270 1=1 ( apsa.hold_flag = 'Y' or (assa.hold_all_payments_flag = 'Y' and aia.payment_status_flag != 'Y' and aia.cancelled_date is null ) or exists (select null from ap_holds_all aha where aha.invoice_id = aia.invoice_id and aha.release_lookup_code is null ) ) LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y F Facture en attente US Invoice on Hold 50 1=1 not ( apsa.hold_flag = 'Y' or (assa.hold_all_payments_flag = 'Y' and aia.payment_status_flag != 'Y' and aia.cancelled_date is null ) or exists (select null from ap_holds_all aha where aha.invoice_id = aia.invoice_id and aha.release_lookup_code is null ) ) N F Facture en attente US Invoice on Hold 51 280 1=1 ( (:p_hold_code = 'Scheduled Payment Hold' and apsa.hold_flag = 'Y' ) or (:p_hold_code = 'Supplier Site Hold' and assa.hold_all_payments_flag = 'Y' and aia.payment_status_flag != 'Y' and aia.cancelled_date is null ) or exists (select null from ap_holds_all aha where aha.invoice_id = aia.invoice_id and aha.release_lookup_code is null and aha.hold_lookup_code = xxen_util.lookup_code(:p_hold_code,'HOLD CODE',200) ) ) LOV custom Y select x.value, x.description from ( select displayed_field value, null description from ap_lookup_codes where lookup_type='HOLD CODE' and lookup_code in(select hold_lookup_code from ap_hold_codes where (hold_type like '%REASON' or hold_type in ('INSUFFICIENT INFORMATION','PERIOD HOLD TYPE','PERIOD RELEASE TYPE')) and nvl(inactive_date,sysdate+1)>sysdate) and enabled_flag='Y' union select 'Scheduled Payment Hold' value, null description from dual union select 'Supplier Site Hold' value, null description from dual ) x order by x.value, x.description F Nom du détenteur US Hold Name 52 290 1=1 exists (select null from fnd_attached_documents fad where to_char(aia.invoice_id)=fad.pk1_value and aia.invoice_id=fad.pk1_value and fad.entity_name='AP_INVOICES') LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y F A l'attachement US Has Attachment 53 1=1 not exists (select null from fnd_attached_documents fad where to_char(aia.invoice_id)=fad.pk1_value and aia.invoice_id=fad.pk1_value and fad.entity_name='AP_INVOICES') N F A l'attachement US Has Attachment 54 300 1=1 aia.creation_date>=:creation_date_from Date US Creation Date From 55 310 1=1 aia.creation_date<:creation_date_to+1 Date last_day(:$flex$.creation_date_from) US Creation Date To 56 320 &dff_columns select xxen_util.dff_columns(p_table_name=>'po_vendors',p_table_alias=>'aps',p_column_name_prefix=>'Supplier: ',p_display_mode=>:dff_display)|| xxen_util.dff_columns(p_table_name=>'po_vendor_sites_all',p_table_alias=>'assa',p_column_name_prefix=>'Site: ',p_display_mode=>:dff_display)|| xxen_util.dff_columns(p_table_name=>'ap_invoices_all',p_column_name_prefix=>'Invoice: ',p_display_mode=>:dff_display)|| xxen_util.dff_columns(p_table_name=>'ap_invoices_all',p_column_name_prefix=>'Invoice Localization: ',p_regional_localization=>'Y',p_display_mode=>:dff_display)|| case when :display_level like 'Lines%' then xxen_util.dff_columns(p_table_name=>'ap_invoice_lines_all',p_column_name_prefix=>'Invoice Line: ',p_display_mode=>:dff_display) end|| case when :display_level='Lines and Distributions' then xxen_util.dff_columns(p_table_name=>'ap_invoice_distributions_all',p_column_name_prefix=>'Dist Line: ',p_display_mode=>:dff_display) end from dual LOV Blitz Report DFF Display 53B31EFFC6B85470E0630100007F501D Y select 'V' id, 'Value' value, 'Value only' description from dual union all select 'C' id, 'Value: Description' value, 'Concatenated value and description' description from dual union all select 'S' id, 'Value, Description' value, 'Value and description in separate columns' description from dual fnd_profile.value('XXEN_REPORT_DFF_DISPLAY') US DFF Display 57 &dff_columns2 select xxen_util.dff_columns(p_table_name=>'po_vendors',p_column_name_prefix=>'Supplier: ',p_prefix=>'ap_inv.',p_display_mode=>:dff_display)|| xxen_util.dff_columns(p_table_name=>'po_vendor_sites_all',p_table_alias=>'assa',p_column_name_prefix=>'Site: ',p_prefix=>'ap_inv.',p_display_mode=>:dff_display)|| xxen_util.dff_columns(p_table_name=>'ap_invoices_all',p_column_name_prefix=>'Invoice: ',p_prefix=>'ap_inv.',p_display_mode=>:dff_display)|| xxen_util.dff_columns(p_table_name=>'ap_invoices_all',p_column_name_prefix=>'Invoice Localization: ',p_regional_localization=>'Y',p_prefix=>'ap_inv.',p_display_mode=>:dff_display)|| case when :display_level like 'Lines%' then xxen_util.dff_columns(p_table_name=>'ap_invoice_lines_all',p_column_name_prefix=>'Invoice Line: ',p_prefix=>'ap_inv.',p_display_mode=>:dff_display) end|| case when :display_level='Lines and Distributions' then xxen_util.dff_columns(p_table_name=>'ap_invoice_distributions_all',p_column_name_prefix=>'Dist Line: ',p_prefix=>'ap_inv.',p_display_mode=>:dff_display) end from dual US DFF Display 58 330 :p_show_attachments LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Attachments 59 &attachment_columns attchmt.seq_num attachment_seq, attchmt.type attachment_type, attchmt.category attachment_category, attchmt.title attachment_title, attchmt.description attachment_description, attchmt.content attachment_content, US Show Attachments 60 340 &l_ap_sla_table ( select xdl.source_distribution_id_num_1 sla_ap_inv_dist_id, xal.ledger_id sla_ledger_id, xdl.event_id sla_event_id, xxen_util.meaning(xal.accounting_class_code,'XLA_ACCOUNTING_CLASS',602,'Y') sla_accounting_class, xal.accounted_dr sla_accounted_dr, xal.accounted_cr sla_accounted_cr, gcck.concatenated_segments sla_account from xla_distribution_links xdl, xla_ae_lines xal, gl_code_combinations_kfv gcck where xdl.source_distribution_type='AP_INV_DIST' and xal.ae_header_id=xdl.ae_header_id and xal.ae_line_num=xdl.ae_line_num and xal.accounting_class_code<>'LIABILITY' and gcck.code_combination_id=xal.code_combination_id ) ap_sla, LOV custom Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$flex$.display_level<>'Headers' US Show SLA Accounting 61 &l_ap_sla_join ap_sla.sla_ap_inv_dist_id(+)=ap_inv.invoice_distribution_id and ap_sla.sla_ledger_id(+)=ap_inv.dist_ledger_id and ap_sla.sla_event_id(+)=ap_inv.dist_accounting_event_id and US Show SLA Accounting 62 &l_ap_sla_sel ap_sla.sla_accounted_dr ap_sla_accounted_dr, ap_sla.sla_accounted_cr ap_sla_accounted_cr, ap_sla.sla_account ap_sla_account, ap_sla.sla_accounting_class ap_sla_accounting_class, US Show SLA Accounting 63 &l_ap_sla_sel2 ap_inv.ap_sla_accounted_dr ap_sla_accounted_dr, ap_inv.ap_sla_accounted_cr ap_sla_accounted_cr, ap_inv.ap_sla_account ap_sla_account, ap_inv.ap_sla_accounting_class ap_sla_accounting_class, US Show SLA Accounting