ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4721551A9E05362FB09050512 SELECT rownum unq_ident /*Bug 16613775*/ ,ai.invoice_id prepay_id ,decode ( :format ,'EXCEL' ,'="' || TO_CHAR (ai.invoice_num) || '"' ,ai.invoice_num ) invoice_number ,TO_CHAR(ai.gl_date, FND_PROFILE.value ('ICX_DATE_FORMAT_MASK')) gl_date ,ai.invoice_date invoice_date ,ai.doc_sequence_value doc_sequence_value ,ai.invoice_type_lookup_code invoice_type ,pov.vendor_name supplier_name ,pov.segment1 supplier_code ,ai.payment_status_flag paid_unpaid ,TO_CHAR(ai.earliest_settlement_date, FND_PROFILE.value ('ICX_DATE_FORMAT_MASK')) date_settled ,ai.invoice_currency_code curr_code ,aid.accounting_date accounting_date ,hou.organization_id operating_unit_id ,hou.name organization_name ,NVL(xdl.unrounded_entered_dr,0) - NVL(xdl.unrounded_entered_cr,0) adv_amt_fr_curr ,NVL(xdl.unrounded_accounted_dr,0) - NVL(xdl.unrounded_accounted_cr,0) adv_amt_fn_curr ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_from_date) pre_amt_from_fr ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_from_date) * NVL(ai.exchange_rate,1) pre_amt_from_fn ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_to_date) pre_amt_to_fr ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_to_date) * NVL(ai.exchange_rate,1) pre_amt_to_fn /*Bug11655402 : Added decode below */ ,DECODE(ai.payment_status_flag, 'Y', NVL(aid.prepay_amount_remaining,aid.amount), NVL(aid.prepay_amount_remaining,0)) bal_amt_fr_curr ,DECODE(ai.payment_status_flag, 'Y', NVL(aid.prepay_amount_remaining,aid.amount), NVL(aid.prepay_amount_remaining,0)) * NVL(ai.exchange_rate,1) bal_amt_fn_curr /*,TO_CHAR(ac.check_date, FND_PROFILE.value ('ICX_DATE_FORMAT_MASK')) payment_date*/ /*Bug11791895*/ ,AP_BAL_PKG.get_no_of_holds(ai.invoice_id) holdbacks ,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_ff_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') acct_ff ,fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE') balancing_segment ,fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'VALUE') cost_center_segment ,fnd_flex_xml_publisher_apis.process_kff_combination_1('natural_acct_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE') natural_acct_segment ,fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_select_desc', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'FULL_DESCRIPTION') balancing_desc ,fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_select_desc', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'FULL_DESCRIPTION') cost_center_desc ,fnd_flex_xml_publisher_apis.process_kff_combination_1('natural_acct_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE') natural_acct_desc ,ai.attribute3 issuing_dept_code /*,NVL(avprpv.prepay_amount_applied,0) pre_amt_appl_fr_curr ,NVL(avprpv.prepay_amount_applied,0) * NVL(ai.exchange_rate,1) pre_amt_appl_fn_curr ,decode ( :format ,'EXCEL' ,'="' || TO_CHAR (decode(aip.invoice_payment_type, 'PREPAY', ai.invoice_num, ac.check_number)) || '"' ,decode(aip.invoice_payment_type, 'PREPAY', ai.invoice_num, ac.check_number) ) int_num ,ac.check_id check_id*/ /*Bug11791895*/ ,AP_BAL_PKG.get_settlement_date(AI.invoice_id,AIL.line_number) settlement_date ,AP_BAL_PKG.description(gcc.segment4,'SEGMENT4') desc_segment4 ,AP_BAL_PKG.description(ai.attribute3,'ATTRIBUTE3') desc_attribute3 ,gcc.segment1 segment1 ,gcc.segment2 segment2 ,gcc.segment3 segment3 ,gcc.segment4 segment4 ,gcc.segment5 segment5 ,gcc.segment6 segment6 ,gcc.segment7 segment7 ,gcc.segment8 segment8 ,gcc.segment9 segment9 ,gcc.segment10 segment10 ,gcc.segment11 segment11 ,gcc.segment12 segment12 ,gcc.segment13 segment13 ,gcc.segment14 segment14 ,gcc.segment15 segment15 ,gcc.segment16 segment16 ,gcc.segment17 segment17 ,gcc.segment18 segment18 ,gcc.segment19 segment19 ,gcc.segment20 segment20 ,gcc.segment21 segment21 ,gcc.segment22 segment22 ,gcc.segment23 segment23 ,gcc.segment24 segment24 ,gcc.segment25 segment25 ,gcc.segment26 segment26 ,gcc.segment27 segment27 ,gcc.segment28 segment28 ,gcc.segment29 segment29 ,gcc.segment30 segment30 ,xah.gl_transfer_status_code gl_transfer_status_code ,gjh.status posting_status FROM ap_invoices ai ,ap_invoice_lines_all ail ,ap_invoice_distributions_all aid ,xla_ae_headers xah ,xla_ae_lines xal ,gl_code_combinations gcc ,po_vendors pov ,xla_distribution_links xdl ,hr_operating_units hou /*,ap_invoice_payments_all aip*/ /*Commenetd as code cleanup for Bug 16613775*/ /*,ap_checks_all ac */ /*Commenetd as code cleanup for Bug 16613775*/ ,gl_import_references gir ,gl_je_headers gjh /*Bug13389653 - Removed table avprpv */ /*,(SELECT avprpv.org_id org_id ,avprpv.prepay_id prepay_id ,avprpv.prepay_line_number prepay_line_number ,avprpv.prepay_amount_applied prepay_amount_applied FROM ap_view_prepays_fr_prepay_v avprpv WHERE NVL(avprpv.accounting_date, :gd_from_date) <= :gd_to_date) avprpv */ WHERE ai.invoice_id = ail.invoice_id AND ail.invoice_id = aid.invoice_id AND ail.line_number = aid.invoice_line_number AND xal.code_combination_id = gcc.code_combination_id AND ai.vendor_id = pov.vendor_id AND ai.invoice_type_lookup_code = 'PREPAYMENT' AND ai.set_of_books_id = xah.ledger_id --Bug8260360 AND aid.accounting_event_id = xah.event_id AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id AND xal.ae_header_id = xah.ae_header_id AND xdl.ae_header_id = xal.ae_header_id AND xdl.ae_line_num = xal.ae_line_num AND hou.organization_id = ai.org_id AND xah.application_id = 200 AND xal.application_id = 200 AND xdl.application_id = 200 /* AND xdl.rounding_class_code <> 'LIABILITY'*/ /*Commented for bug 16733413*/ AND xal.accounting_class_code <> 'LIABILITY' /*Added for bug 16733413*/ AND xdl.source_distribution_type <> 'AP_PMT_DIST' --Used to confirm the Invoice is Accounted (Final) AND xah.accounting_entry_status_code <> 'D' --Used to confirm the Invoice is Accounted AND aid.accrual_posted_flag = 'Y' --Used to Obtain the records with the Balance Type 'Actual' AND xah.balance_type_code = 'A' AND NVL (aid.accounting_date, ai.gl_date) <= :gd_to_date AND ai.cancelled_date IS NULL /*AND ai.invoice_id = aip.invoice_id(+) */ /*Commented as code cleanup for Bug 16613775 Start*/ /*AND aip.check_id = ac.check_id(+) AND ac.void_date IS NULL Bug 16613775 End */ /* Bug13389653 */ /* AND ail.invoice_id = avprpv.prepay_id(+) AND ail.line_number = avprpv.prepay_line_number(+) AND ail.org_id = avprpv.org_id(+) */ /* End of Bug13389653 */ AND xal.gl_sl_link_id = gir.gl_sl_link_id(+) AND xal.gl_sl_link_table = gir.gl_sl_link_table(+) AND gir.je_header_id = gjh.je_header_id(+) AND xal.ledger_id = NVL(gjh.ledger_id,xal.ledger_id) AND &flex_where_gl_account &gc_currency &gc_status &gc_org_where &gc_supplier &gc_where_clause &gc_additional_where ORDER BY gl_date Y SQLAP APPREBFC AR AP تقرير رصيد الدفعة المقدمة - العملة الوظيفية (تشيكي) Imported from BI Publisher Application: المدفوعات Source: تقرير رصيد الدفعة المقدمة - العملة الوظيفية (تشيكي) Short Name: APPREBFC DB package: AP_BAL_PKG D AP Vorauszahlungssalden - Bilanzwährung (Tschechisch) Imported from BI Publisher Description: Auswertung "Vorauszahlungssalden" - Bilanzwährung (Tschechisch) Application: Kreditoren Source: Vorauszahlungssalden - Bilanzwährung (Tschechisch) Short Name: APPREBFC DB package: AP_BAL_PKG E AP República Checa - Saldo de Anticipo: Divisa Funcional Imported from BI Publisher Application: Payables Source: República Checa - Saldo de Anticipo: Divisa Funcional Short Name: APPREBFC DB package: AP_BAL_PKG F AP Etat des soldes d'acompte - Devise comptable (République tchèque) Imported from BI Publisher Application: Payables Source: Etat des soldes d'acompte - Devise comptable (République tchèque) Short Name: APPREBFC DB package: AP_BAL_PKG I AP Rapporto Saldo anticipo - moneta di conto (Repubblica Ceca) Imported from BI Publisher Application: Payables Source: Rapporto Saldo anticipo - moneta di conto (Repubblica Ceca) Short Name: APPREBFC DB package: AP_BAL_PKG JA AP 前払金残高レポート - 機能通貨(チェコ) Imported from BI Publisher Application: Payables Source: 前払金残高レポート - 機能通貨(チェコ) Short Name: APPREBFC DB package: AP_BAL_PKG KO AP 선급금 잔액 보고서 - 기준 통화(체코) Imported from BI Publisher Application: Payables Source: 선급금 잔액 보고서 - 기준 통화(체코) Short Name: APPREBFC DB package: AP_BAL_PKG PTB AP Relatório de Saldo do Pagamento Antecipado - Moeda Funcional (Tcheca) Imported from BI Publisher Application: Payables Source: Relatório de Saldo do Pagamento Antecipado - Moeda Funcional (Tcheca) Short Name: APPREBFC DB package: AP_BAL_PKG RU AP Отчет об остатке предоплаты - функциональная валюта (Чехия) Imported from BI Publisher Application: Кредиторы Source: Отчет об остатке предоплаты - функциональная валюта (Чехия) Short Name: APPREBFC DB package: AP_BAL_PKG S AP Rapport om saldo för förskottsbelopp - funktionell valuta (Tjeckien) Imported from BI Publisher Application: Payables Source: Rapport om saldo för förskottsbelopp - funktionell valuta (Tjeckien) Short Name: APPREBFC DB package: AP_BAL_PKG TR AP Ön Ödemeli Bakiye Raporu - Fonksiyonel Para Birimi (Çek Cumh.) Imported from BI Publisher Application: Borçlar Muhasebesi Source: Ön Ödemeli Bakiye Raporu - Fonksiyonel Para Birimi (Çek Cumh.) Short Name: APPREBFC DB package: AP_BAL_PKG US AP Prepayment Balance Report - Functional Currency (CZech) Imported from BI Publisher Application: Payables Source: Prepayment Balance Report - Functional Currency (CZech) Short Name: APPREBFC DB package: AP_BAL_PKG ZHS AP 预付款余额报表 - 本位币（捷克） Imported from BI Publisher Application: 应付帐款 Source: 预付款余额报表 - 本位币（捷克） Short Name: APPREBFC DB package: AP_BAL_PKG BI Publisher &flex_where_gl_account &gc_additional_where &gc_currency &gc_org_where &gc_status &gc_supplier &gc_where_clause :account_from_param :account_to_param :coa_id_param :curr_code_param :dummy_param :format :from_date_param :gc_additional_where :gc_currency :gc_from_clause :gc_ledger_name :gc_org_where :gc_pre_amt_appl :gc_status :gc_supplier :gc_where_clause :gd_from_date :gd_to_date :org_id_param :paid_only_param :period_from_param :period_to_param :posted_only_param :supplier_from_param :supplier_to_param :to_date_param 1 -10 :coa_id_param LOV Oracle AX_SRS_COA Y select chart_of_accounts_id id, chart_of_accounts_id value, name description from gl_sets_of_books where set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID') order by value,description SELECT chart_of_accounts_id FROM gl_access_sets WHERE access_set_id = fnd_profile.value('GL_ACCESS_SET_ID') Y AR معرف جدول تصنيف الحسابات معرف جدول تصنيف الحسابات D Kennung Kontenplan Kennung Kontenplan E Id Plan de Cuentas Id Plan de Cuentas F ID plan de comptes ID plan de comptes I Id piano dei conti Id piano dei conti JA 勘定体系ID 勘定体系ID KO 계정 코드집 ID 계정 코드집 ID PTB ID do Plano de Contas ID do Plano de Contas RU Идент. плана счетов Идент. плана счетов S Kontoplans-id Kontoplans-id TR Hesaplar Grafiği No Hesaplar Grafiği No US Chart Of Accounts ID Chart Of Accounts ID ZHS 科目表标识 科目表标识 2 10 :org_id_param LOV Oracle FND_MO_OU Y select organization_id id, organization_name value, null description from mo_glob_org_access_tmp order by value,description AR وحدة التشغيل وحدة التشغيل D Betriebseinheit Betriebseinheit E Unidad Operativa Unidad Operativa F Unité opérationnelle Unité opérationnelle I Unità operativa Unità operativa JA 営業単位 営業単位 KO 운영 단위 운영 단위 PTB Unidade Operacional Unidade Operacional RU Операционная единица Операционная единица S Operativ enhet Operativ enhet TR Faaliyet Birimi Faaliyet Birimi US Operating Unit Operating Unit ZHS 业务实体 业务实体 3 20 :from_date_param Date SELECT sysdate FROM DUAL Y AR اعتبارًا من تاريخ من تاريخ D Vom Von Datum E A Fecha De Fecha F En date du Date de début I In data Data - Da JA 現在 日付:自 KO 기준 일자 일자: 부터 PTB Data de Referência Data - De RU На дату Начальная дата S Per datum Från datum TR Başlama Tarihi Başlangıç Tarihi US As Of Date From Date ZHS 截止日期 自日期 4 30 :paid_only_param LOV Oracle AP_SRS_YES_NO_OPT Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='YES_NO' order by value,description AR المدفوع فقط المدفوع فقط (نعم/لا) D Nur bezahlte Nur bezahlte (J/N) E Sólo Pagado Sólo Pagado (S/N) F Réglé seulement Réglé seulement (O/N) I Solo pagato Solo pagato (S/N) JA 支払済のみ 支払済のみ(Y/N) KO 지급완료만 지급완료만(Y/N) PTB Somente Pago Somente Pago (S/N) RU Только оплаченные Только оплаченные (да/нет) S Endast betald Endast betald (J/N) TR Sadece Ödenen Sadece Ödenen (E/H) US Paid Only Paid Only (Y/N) ZHS 仅限于已付 仅限于已付 (Y/N) 5 40 :posted_only_param LOV Oracle AP_SRS_YES_NO_OPT Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='YES_NO' order by value,description AR المرحل فقط المرحل فقط (نعم/لا) D Nur verbuchte Nur verbuchte (J/N) E Sólo Contabilizado Sólo Contabilizado (S/N) F Imputé seulement Imputé seulement (O/N) I Solo contabilizzato Solo contabilizzato (S/N) JA 転記済のみ 転記済のみ(Y/N) KO 전기됨만 전기됨만(Y/N) PTB Somente Contabilizado Somente Contabilizado (S/N) RU Только проведенные Только проведенные (да/нет) S Endast postad Endast postad (J/N) TR Sadece Muhasebeleşen Sadece Muhasebeleşen (E/H) US Posted Only Posted Only (Y/N) ZHS 仅限于已过帐 仅限于已过帐 (Y/N)