ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B0133B97EC1B0451E0530100007F85D5Y select x.ledger ,x.operating_unit ,x.c_hold_type hold_type ,x.c_nls_hold_code hold_name ,x.c_hold_code hold_code ,x.c_nls_hold_desc "Hold Description" ,x.c_hold_reason hold_reason ,x.c_hold_date hold_date ,decode(x.c_held_by,5,(select alc5.displayed_field from ap_lookup_codes alc5 where alc5.lookup_type(+)='NLS TRANSLATION' and alc5.lookup_code(+)='SYSTEM'),xxen_util.user_name(x.c_held_by)) held_by ,x.c_postable "Accounting Allowed" ,x.c_batch_name batch_name ,x.c_vendor_name trading_partner ,x.c_vendor_site site ,x.c_invoice_num invoice_number ,(select poh.segment1 from po_line_locations_all plla, po_headers_all poh where plla.line_location_id=x.c_hold_line_location_id and plla.po_header_id=poh.po_header_id ) po_number ,(select pol.line_num from po_line_locations_all plla, po_lines_all pol where plla.line_location_id=x.c_hold_line_location_id and plla.po_line_id=pol.po_line_id ) po_line_number ,(select listagg(ail.line_number,', ') within group (order by ail.line_number) from ap_invoice_lines ail where ail.invoice_id=x.c_invoice_id and ail.po_line_location_id=x.c_hold_line_location_id ) invoice_line_number ,to_char(x.c_invoice_date,'fmMonth YYYY') invoice_month ,x.c_invoice_date invoice_date ,x.c_original_amount "Original Amount (Func Curr)" ,x.c_amount_remaining "Amount Remaining (Func Curr)" ,x.c_description description ,trunc(x.c_inv_creation_date) invoice_creation_date ,trunc(x.payment_date) payment_date ,x.invoice_pay_group ,x.dispute_reason ,(select distinct listagg(description,', ') within group (order by description) description from ( select distinct aid.invoice_id , ah.hold_lookup_code , ap_utilities_pkg.get_charge_account(aid.dist_code_combination_id,gls.chart_of_accounts_id,'APXINROH') description from ap_invoice_distributions aid , gl_sets_of_books gls , ap_holds ah where ah.hold_lookup_code = 'DIST ACCT INVALID' and ah.release_lookup_code is null and aid.invoice_id = ah.invoice_id and gls.set_of_books_id = aid.set_of_books_id and ( exists (select 'x' from gl_code_combinations c where aid.dist_code_combination_id = c.code_combination_id (+) and ( c.code_combination_id is null or c.detail_posting_allowed_flag = 'N' or trunc(c.start_date_active) > aid.accounting_date or trunc(c.end_date_active) < aid.accounting_date or c.template_id is not null or c.enabled_flag <> 'Y' or c.summary_flag <>'N' )) or (aid.dist_code_combination_id = -1) ) ) d where d.invoice_id = x.c_invoice_id and d.hold_lookup_code = x.c_hold_code ) invalid_account, to_char(x.c_invoice_date,'YYYY/MM') || ' - ' || to_char(x.c_invoice_date,'fmMonth YYYY') invoice_month_label &il_columns from ( select -- invoices on hold gsob.name ledger, haou.name operating_unit, hp.party_name c_vendor_name, inv1.party_id c_party_id, inv1.vendor_id c_vendor_id, vs.vendor_site_id c_vendor_site_id, vs.vendor_site_code c_vendor_site, decode(h.hold_lookup_code, null,:c_nls_na, h.hold_lookup_code) c_hold_code, decode(h.hold_lookup_code, null,:c_nls_na, alc.displayed_field) c_nls_hold_code, decode(h.hold_lookup_code, null,:c_nls_na, alc.description) c_nls_hold_desc, decode(ahc.postable_flag,'Y',:c_nls_yes,:c_nls_no) c_postable, inv1.invoice_date c_invoice_date, b.batch_name c_batch_name, inv1.invoice_id c_invoice_id, inv1.invoice_num c_invoice_num, h.line_location_id c_hold_line_location_id, h.hold_reason c_hold_reason, h.hold_date c_hold_date, h.held_by c_held_by, decode(inv1.invoice_currency_code, gsob.currency_code, inv1.invoice_amount, inv1.base_amount) c_original_amount, decode(inv1.invoice_currency_code, gsob.currency_code, inv1.invoice_amount, inv1.base_amount) - decode(inv1.payment_currency_code, gsob.currency_code, nvl(inv1.amount_paid,0) + nvl(discount_amount_taken,0), decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit) + decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit)) c_amount_remaining, inv1.description c_description, 'Invoice Hold' c_hold_type, inv1.creation_date c_inv_creation_date, inv1.pay_group_lookup_code invoice_pay_group, inv1.dispute_reason, (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 = inv1.invoice_id ) payment_date from gl_sets_of_books gsob, hr_all_organization_units haou, hz_parties hp, ap_supplier_sites vs, ap_invoices inv1, ap_batches_all b, ap_payment_schedules s, ap_holds h, ap_hold_codes ahc, ap_lookup_codes alc, fnd_currencies_vl f where 2=2 and hp.party_id = inv1.party_id and gsob.set_of_books_id = inv1.set_of_books_id and haou.organization_id = inv1.org_id and vs.vendor_id (+) = inv1.vendor_id and vs.vendor_site_id (+) = inv1.vendor_site_id and h.invoice_id = inv1.invoice_id and b.batch_id(+) = inv1.batch_id and s.invoice_id(+) = inv1.invoice_id and h.release_lookup_code is null and f.currency_code = gsob.currency_code and alc.lookup_type = 'HOLD CODE' and alc.lookup_code = h.hold_lookup_code and ahc.hold_lookup_code = h.hold_lookup_code group by gsob.name, haou.name, h.hold_lookup_code, alc.displayed_field, alc.description, ahc.postable_flag, hp.party_name, inv1.party_id, inv1.vendor_id, vs.vendor_site_id, vs.vendor_site_code, inv1.invoice_date, inv1.invoice_id, b.batch_name, inv1.invoice_num, h.line_location_id, h.hold_reason, h.hold_date, h.held_by, decode(inv1.invoice_currency_code, gsob.currency_code, inv1.invoice_amount, inv1.base_amount), decode(inv1.invoice_currency_code, gsob.currency_code, inv1.invoice_amount, inv1.base_amount) - decode(inv1.payment_currency_code, gsob.currency_code, nvl(inv1.amount_paid,0) + nvl(discount_amount_taken,0), decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit) + decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit)), inv1.description, inv1.creation_date, inv1.pay_group_lookup_code, inv1.dispute_reason union select -- payments on hold gsob.name ledger, haou.name operating_unit, hp.party_name c_vendor_name, inv1.party_id c_party_id, inv1.vendor_id c_vendor_id, vs.vendor_site_id c_vendor_site_id, vs.vendor_site_code c_vendor_site, :c_nls_na c_hold_code, 'Scheduled Payment Hold' c_nls_hold_code, :c_nls_na c_nls_hold_desc, :c_nls_na c_postable, inv1.invoice_date c_invoice_date, b.batch_name c_batch_name, inv1.invoice_id c_nr_invoice_id, inv1.invoice_num c_invoice_num, to_number(null) c_hold_line_location_id, to_char(null) c_hold_reason, cast(null as date) c_hold_date, to_number(null) c_held_by, decode(inv1.invoice_currency_code, gsob.currency_code, inv1.invoice_amount, inv1.base_amount) c_original_amount, decode(inv1.invoice_currency_code, gsob.currency_code, inv1.invoice_amount,inv1.base_amount) - decode(inv1.payment_currency_code, gsob.currency_code, nvl(inv1.amount_paid,0) + nvl(discount_amount_taken,0), decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit) + decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit)) c_amount_remaining, max(s.iby_hold_reason) c_description, 'Scheduled Payment Hold' c_hold_type, inv1.creation_date c_inv_creation_date, inv1.pay_group_lookup_code invoice_pay_group, inv1.dispute_reason, (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 = inv1.invoice_id ) payment_date from gl_sets_of_books gsob, hr_all_organization_units haou, hz_parties hp, ap_supplier_sites vs, ap_invoices inv1, ap_batches_all b, ap_payment_schedules s, fnd_currencies_vl f where 2=2 and hp.party_id = inv1.party_id and gsob.set_of_books_id = inv1.set_of_books_id and haou.organization_id = inv1.org_id and vs.vendor_id (+) = inv1.vendor_id and vs.vendor_site_id (+) = inv1.vendor_site_id and b.batch_id(+) = inv1.batch_id and s.invoice_id = inv1.invoice_id and s.hold_flag = 'Y' and f.currency_code = gsob.currency_code group by gsob.name, haou.name, hp.party_name, inv1.party_id, inv1.vendor_id, vs.vendor_site_code, vs.vendor_site_id, inv1.invoice_date, inv1.invoice_id, b.batch_name, inv1.invoice_num, decode(inv1.invoice_currency_code, gsob.currency_code, inv1.invoice_amount, inv1.base_amount), decode(inv1.invoice_currency_code, gsob.currency_code, inv1.invoice_amount,inv1.base_amount) - decode(inv1.payment_currency_code, gsob.currency_code, nvl(inv1.amount_paid,0) + nvl(discount_amount_taken,0), decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit) + decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit)), inv1.creation_date, inv1.pay_group_lookup_code, inv1.dispute_reason union select -- vendor sites on hold gsob.name ledger, haou.name operating_unit, hp.party_name c_vendor_name, inv1.party_id c_party_id, v.vendor_id c_vendor_id, vs.vendor_site_id c_vendor_site_id, vs.vendor_site_code c_vendor_site, :c_nls_na c_hold_code, 'Supplier Site Hold' c_nls_hold_code, :c_nls_na c_nls_hold_desc, :c_nls_na c_postable, inv1.invoice_date c_invoice_date, b.batch_name c_batch_name, inv1.invoice_id c_invoice_id, inv1.invoice_num c_invoice_num, to_number(null) c_hold_line_location_id, to_char(null) c_hold_reason, cast(null as date) c_hold_date, to_number(null) c_held_by, decode(inv1.invoice_currency_code, gsob.currency_code, inv1.invoice_amount, inv1.base_amount) c_original_amount, decode(inv1.invoice_currency_code,gsob.currency_code, inv1.invoice_amount,inv1.base_amount) - decode(inv1.payment_currency_code, gsob.currency_code, nvl(inv1.amount_paid,0) + nvl(discount_amount_taken,0), decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit) + decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit)) c_amount_remaining, inv1.description c_description, 'Supplier Site Hold' c_hold_type, inv1.creation_date c_inv_creation_date, inv1.pay_group_lookup_code invoice_pay_group, inv1.dispute_reason, (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 = inv1.invoice_id ) payment_date from gl_sets_of_books gsob, hr_all_organization_units haou, hz_parties hp, ap_suppliers v, ap_supplier_sites vs, ap_invoices inv1, ap_batches_all b, ap_payment_schedules s, fnd_currencies_vl f where 2=2 and hp.party_id = inv1.party_id and gsob.set_of_books_id = inv1.set_of_books_id and haou.organization_id = inv1.org_id and v.vendor_id (+) = inv1.vendor_id and vs.vendor_id (+) = inv1.vendor_id and vs.vendor_site_id (+) = inv1.vendor_site_id and b.batch_id(+) = inv1.batch_id and s.invoice_id(+) = inv1.invoice_id and vs.hold_all_payments_flag = 'Y' and f.currency_code = gsob.currency_code and inv1.cancelled_date is null and inv1.payment_status_flag != 'Y' group by gsob.name, haou.name, hp.party_name, inv1.party_id, v.vendor_id, vs.vendor_site_id, vs.vendor_site_code, b.batch_name, inv1.invoice_date, inv1.invoice_id, inv1.invoice_num, decode(inv1.invoice_currency_code, gsob.currency_code, inv1.invoice_amount, inv1.base_amount), decode(inv1.invoice_currency_code,gsob.currency_code, inv1.invoice_amount,inv1.base_amount) - decode(inv1.payment_currency_code, gsob.currency_code, nvl(inv1.amount_paid,0) + nvl(discount_amount_taken,0), decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.amount_paid,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit) + decode(f.minimum_accountable_unit, null, round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)), f.precision), round(((decode(inv1.payment_cross_rate_type, 'EMU FIXED', 1/inv1.payment_cross_rate, inv1.exchange_rate)) * nvl(inv1.discount_amount_taken,0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit)), inv1.description, inv1.creation_date, inv1.pay_group_lookup_code, inv1.dispute_reason ) x &il_from where 1=1 &il_where order by decode(:p_order_by, 'Hold Name',c_nls_hold_code,x.c_vendor_name) ,x.c_vendor_name ,x.c_vendor_site ,x.c_invoice_date ,x.c_invoice_num ,x.c_nls_hold_code Added India GST columns and the ITC Reversal Days parameter, displayed only when India Localization is installed. SQLAP #,##0.00;[Red](#,##0.00) AR AP Invoice on Hold Application: المدفوعات Source: تقرير إيقاف الفاتورة (بتنسيق XML) - غير مدعم: محجوز للاستخدام الآجل Short Name: APXINROH_XML DB package: AP_APXINROH_XMLP_PKG D AP Invoice on Hold Application: Kreditoren Source: Rechnungen - gesperrt (XML) - Nicht unterstützt: Reserviert für zukünftige Verwendung Short Name: APXINROH_XML DB package: AP_APXINROH_XMLP_PKG E AP Invoice on Hold Informe importado de facturas estándar de Oracle en espera Fuente: Informe de facturas en espera (XML) Nombre corto: APXINROH_XML Paquete DB: AP_APXINROH_XMLP_PKG F AP Invoice on Hold Application: Payables Source: Etat des factures bloquées (XML) - Non pris en charge : réservé pour une utilisation future Short Name: APXINROH_XML DB package: AP_APXINROH_XMLP_PKG I AP Invoice on Hold Application: Payables Source: Rapporto Fatture bloccate (XML) - non supportato: riservato per usi futuri Short Name: APXINROH_XML DB package: AP_APXINROH_XMLP_PKG JA AP Invoice on Hold Oracle標準請求書の保留レポートをインポート 出典はこちらです。請求書保留報告書(XML) 短縮名。APXINROH_XML DBパッケージです。AP_APXINROH_XMLP_PKG KO AP Invoice on Hold 임포트 된 Oracle 표준 송장 보류 보고서 출처 : 송장 보류 보고서 (XML) 짧은 이름 : APXINROH_XML DB 패키지 : AP_APXINROH_XMLP_PKG PTB AP Invoice on Hold Relatório de fatura padrão importada da Oracle em espera Fonte: Relatório de Fatura em Espera (XML) Nome curto: APXINROH_XML Pacote DB: AP_APXINROH_XMLP_PKG RU AP Invoice on Hold Импортированный стандартный счет-фактура Oracle в отчете о состоянии ожидания Источник: Счет-фактура на удержание отчета (XML) Короткое имя: APXINROH_XML БД-пакет: AP_APXINROH_XMLP_PKG S AP Invoice on Hold Importerad Oracle-standardfaktura i väntan Källa: Invoice on Hold Report (XML) Kort namn: APXINROH_XML DB-paket: AP_APXINROH_XMLP_PKG TR AP Invoice on Hold Beklemedeki Oracle standart faturası içe aktarıldı Kaynak: Bekletilen Fatura Raporu (XML) Kısa isim: APXINROH_XML DB paketi: AP_APXINROH_XMLP_PKG US AP Invoice on Hold Based on Oracle standard Invoice on Hold report Source: Invoice on Hold Report (XML) Short Name: APXINROH_XML When India Localization is installed, the following GST columns are also included: GST Registration No, Supplier GSTIN (Invoice Date), GST PAN No, GST TAN No, CGST Amount, SGST Amount, IGST Amount, CESS Amount, CGST Rate, SGST Rate, IGST Rate, CESS Rate, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code, Days Since Invoice, Amount Settled, Amount Unpaid, ITC Reversal Due, ITC Reversal Exposure. ZHS AP Invoice on Hold Application: 应付帐款 Source: 暂挂发票报表 (XML) - 不支持：已保留供将来使用 Short Name: APXINROH_XML DB package: AP_APXINROH_XMLP_PKG Enginatics &il_columns &il_from &il_where 1=1 2=2 :c_nls_na :c_nls_no :c_nls_yes :p_order_by 1 -40 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &il_columns ,nvl(il_reg_site.gstin,il_reg_hdr.gstin_hdr) "GST Registration 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=x.c_vendor_id and jpr.party_site_id=x.c_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<=x.c_invoice_date and (jprl.effective_to is null or jprl.effective_to>=x.c_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=x.c_vendor_id and jpr.party_site_id=x.c_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<=x.c_invoice_date and (jprl.effective_to is null or jprl.effective_to>=x.c_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=x.c_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<=x.c_invoice_date and (jprl.effective_to is null or jprl.effective_to>=x.c_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=x.c_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<=x.c_invoice_date and (jprl.effective_to is null or jprl.effective_to>=x.c_invoice_date) and rownum=1 ))) "Supplier GSTIN (Invoice Date)", 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", il_tax.cgst_amount "CGST Amount", il_tax.sgst_amount "SGST Amount", il_tax.igst_amount "IGST Amount", il_tax.cess_amount "CESS Amount", il_tax.cgst_tax_rate "CGST Rate", il_tax.sgst_tax_rate "SGST Rate", il_tax.igst_tax_rate "IGST Rate", il_tax.cess_tax_rate "CESS Rate", il_tax.custom_amount, il_tax.unclassified_amount "Unclassified Tax Amount", il_tax.taxable_value, il_tax.hsn_sac_code "HSN SAC Code", trunc(sysdate)-trunc(x.c_invoice_date) "Days Since Invoice", x.c_original_amount-x.c_amount_remaining "Amount Settled", x.c_amount_remaining "Amount Unpaid", xxen_util.yes(case when trunc(sysdate)-trunc(x.c_invoice_date)>nvl(:itc_reversal_days,180) and x.c_amount_remaining<>0 then 'Y' end) "ITC Reversal Due", case when trunc(sysdate)-trunc(x.c_invoice_date)>nvl(:itc_reversal_days,180) and x.c_amount_remaining<>0 and x.c_original_amount<>0 then nullif(round((nvl(il_tax.cgst_amount,0)+nvl(il_tax.sgst_amount,0)+nvl(il_tax.igst_amount,0)+nvl(il_tax.cess_amount,0))*x.c_amount_remaining/x.c_original_amount,2),0) end "ITC Reversal Exposure" Y US India Localization 3 &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, 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 ) il_tax Y US India Localization 4 &il_where and x.c_vendor_site_id=il_reg_site.party_site_id(+) and x.c_vendor_id=il_reg_hdr.party_id(+) and x.c_invoice_id=il_tax.trx_id(+) Y US India Localization 5 -30 :c_nls_no Char xxen_util.meaning('N','YES_NO',0) US c_nls_no 6 -20 :c_nls_yes Char Y US c_nls_yes 7 -10 :c_nls_na Char select alc.displayed_field from ap_lookup_codes alc where alc.lookup_type = 'NLS REPORT PARAMETER' and alc.lookup_code = 'NA' US c_nls_na 8 10 1=1 x.operating_unit=:p_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 coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 9 20 1=1 x.c_hold_code=xxen_util.lookup_code(:p_hold_code,'HOLD CODE',200) LOV custom Y select alc.displayed_field value, null description from ap_lookup_codes alc where alc.lookup_type='HOLD CODE' and alc.lookup_code in (select ahc.hold_lookup_code from ap_hold_codes ahc where (ahc.hold_type like '%REASON' or ahc.hold_type in ('INSUFFICIENT INFORMATION','PERIOD HOLD TYPE','PERIOD RELEASE TYPE')) and nvl(alc.inactive_date,sysdate+1)>sysdate) and alc.enabled_flag='Y' order by alc.displayed_field US Hold Name 10 30 1=1 x.c_vendor_name=:p_party_name LOV custom Y select hp.party_name value, null description from hz_parties hp where exists (select 'Payee Party' from iby_external_payees_v ibp where ibp.payee_party_id=hp.party_id and ibp.payment_function in ('PAYABLES_DISB','EMPLOYEE_EXP','LOANS_PAYMENTS','AR_CUSTOMER_REFUNDS') ) order by hp.party_name US Trading Partner 11 50 :p_order_by LOV Oracle AP_SRS_ORDER_HOLD_VENDOR Y select lookup_code id, displayed_field value, null description from ap_lookup_codes where nvl(inactive_date,sysdate+1)>sysdate and lookup_type='RPT ORDER BY' order by value,description Hold Name Y US Order By 12 60 1=1 x.c_inv_creation_date>=:p_start_creation_date Date US Entered Date From 13 70 1=1 x.c_inv_creation_date<:p_end_creation_date+1 Date last_day(:$flex$.Entered_Date_From) US Entered Date To 14 90 2=2 nvl(s.due_date, sysdate)>=:p_start_due_date Date US Due Date From 15 100 2=2 nvl(s.due_date, sysdate)<:p_end_due_date+1 Date last_day(:$flex$.Due_Date_From) US Due Date To 16 110 2=2 ( nvl(s.discount_date, sysdate)>=:p_start_discount_date or nvl(s.second_discount_date, sysdate)>=:p_start_discount_date or nvl(s.third_discount_date, sysdate)>=:p_start_discount_date ) Date US Discount Date From 17 120 2=2 ( nvl(s.discount_date, sysdate)<:p_end_discount_date+1 or nvl(s.second_discount_date, sysdate)<:p_end_discount_date+1 or nvl(s.third_discount_date, sysdate)<:p_end_discount_date+1 ) Date last_day(:$flex$.Discount_Date_From) US Discount Date To 18 130 :itc_reversal_days Number 180 AR أيام عكس ITC D ITC-Stornotage F Jours d'annulation ITC US ITC Reversal Days ZHS ITC 冲销天数 1DC36F09516FFB750A3BACA5BC611FBF Pivot: By Hold, Invoice Month SYSADMIN 9 Accounting Allowed 20 Amount Remaining (Func Curr) 42 Amount Settled 43 Amount Unpaid 10 BATCH_NAME 34 CESS Amount 38 CESS Rate 31 CGST Amount 35 CGST Rate 21 DESCRIPTION 25 DISPUTE_REASON 41 Days Since Invoice 29 GST PAN No 28 GST Registration No 30 GST TAN No 8 HELD_BY 5 HOLD_CODE 7 HOLD_DATE 4 HOLD_NAME 1 6 HOLD_REASON 3 HOLD_TYPE 40 HSN SAC Code 33 IGST Amount 37 IGST Rate 26 INVALID_ACCOUNT 22 INVOICE_CREATION_DATE 18 INVOICE_DATE 3 16 INVOICE_LINE_NUMBER 17 INVOICE_MONTH 27 INVOICE_MONTH_LABEL 13 INVOICE_NUMBER 4 24 INVOICE_PAY_GROUP 44 ITC Reversal Due 45 ITC Reversal Exposure 1 LEDGER 2 OPERATING_UNIT 19 Original Amount (Func Curr) 23 PAYMENT_DATE 15 PO_LINE_NUMBER 14 PO_NUMBER 32 SGST Amount 36 SGST Rate 12 SITE 46 Supplier GSTIN (Invoice Date) 11 TRADING_PARTNER 2 39 Unclassified Tax Amount Amount Remaining (Func Curr) VALUE 6 SUM HOLD_NAME ROW 2 INVOICE_MONTH_LABEL ROW 7 INVOICE_NUMBER VALUE 10 COUNT LEDGER FILTER 3 OPERATING_UNIT FILTER 8 Original Amount (Func Curr) VALUE 4 SUM S Site BB6465DD9DCE38EF0465D29D59C485A4 Pivot: By Trading Party, Invoice Month SYSADMIN 9 Accounting Allowed 20 Amount Remaining (Func Curr) 42 Amount Settled 43 Amount Unpaid 10 BATCH_NAME 34 CESS Amount 38 CESS Rate 31 CGST Amount 35 CGST Rate 21 DESCRIPTION 25 DISPUTE_REASON 41 Days Since Invoice 29 GST PAN No 28 GST Registration No 30 GST TAN No 8 HELD_BY 5 HOLD_CODE 7 HOLD_DATE 4 HOLD_NAME 3 6 HOLD_REASON 3 HOLD_TYPE 40 HSN SAC Code 33 IGST Amount 37 IGST Rate 26 INVALID_ACCOUNT 22 INVOICE_CREATION_DATE 18 INVOICE_DATE 2 16 INVOICE_LINE_NUMBER 17 INVOICE_MONTH 27 INVOICE_MONTH_LABEL 13 INVOICE_NUMBER 4 24 INVOICE_PAY_GROUP 44 ITC Reversal Due 45 ITC Reversal Exposure 1 LEDGER 2 OPERATING_UNIT 19 Original Amount (Func Curr) 23 PAYMENT_DATE 15 PO_LINE_NUMBER 14 PO_NUMBER 32 SGST Amount 36 SGST Rate 12 SITE 46 Supplier GSTIN (Invoice Date) 11 TRADING_PARTNER 1 39 Unclassified Tax Amount Amount Remaining (Func Curr) VALUE 6 SUM INVOICE_MONTH_LABEL ROW 7 INVOICE_NUMBER VALUE 10 COUNT LEDGER FILTER 2 OPERATING_UNIT FILTER 8 Original Amount (Func Curr) VALUE 3 SUM TRADING_PARTNER ROW 4 S Site SYSADMIN BB6465DD9DCE38EF0465D29D59C485A4 VYBHAV.REDDY BB6465DD9DCE38EF0465D29D59C485A4 1DC36F09516FFB750A3BACA5BC611FBF