ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN D5B83C6E7A1F4E92B0C7A45D19E6F238Y select y.package_name, y.procedure_name, y.overload, y.data_level, y.position, y.argument_name, y.data_type, y.type_name, y.in_out, y.defaulted, y.referenced_by from ( select ua.package_name, ua.object_name procedure_name, ua.overload, ua.data_level, ua.position, ua.argument_name, ua.data_type, ua.type_owner||decode(ua.type_name,null,null,'.'||ua.type_name)||decode(ua.type_subname,null,null,'.'||ua.type_subname) type_name, ua.in_out, ua.defaulted, x.referenced_by, ua.sequence from ( select /*+ no_merge */ s.package_name, s.object_name, listagg(s.referencing_package,', ') within group (order by s.referencing_package) referenced_by from ( select distinct upper(regexp_substr(us.text,'([a-z0-9_]+)\.([a-z0-9_]+)[[:space:]]*(\(|$)',1,rowgen.column_value,'i',1)) package_name, upper(regexp_substr(us.text,'([a-z0-9_]+)\.([a-z0-9_]+)[[:space:]]*(\(|$)',1,rowgen.column_value,'i',2)) object_name, us.name referencing_package from user_source us, table(xxen_util.rowgen(regexp_count(us.text,'[a-z0-9_]+\.[a-z0-9_]+[[:space:]]*(\(|$)',1,'i'))) rowgen where :api_package_name is null and us.type='PACKAGE BODY' and us.name like 'XXEN%' and us.name in (select us2.name from user_source us2 where us2.type='PACKAGE BODY' and us2.line=1 and us2.name like 'XXEN%' and lower(us2.text) not like '%wrapped%') and regexp_like(us.text,'[a-z0-9_]+\.[a-z0-9_]+[[:space:]]*(\(|$)','i') ) s where s.package_name not like 'XXEN%' group by s.package_name, s.object_name ) x, user_arguments ua where x.package_name=ua.package_name and x.object_name=ua.object_name union all select ua.package_name, ua.object_name, ua.overload, ua.data_level, ua.position, ua.argument_name, ua.data_type, ua.type_owner||decode(ua.type_name,null,null,'.'||ua.type_name)||decode(ua.type_subname,null,null,'.'||ua.type_subname), ua.in_out, ua.defaulted, to_char(null), ua.sequence from user_arguments ua where :api_package_name is not null and 3=3 ) y where 1=1 order by y.package_name, y.procedure_name, nvl(y.overload,'1'), y.sequence New report. Shows an instance's own signatures of the Oracle EBS APIs called by the Blitz Report extension packages, so a customer's signatures can be compared against a working instance when an extension package fails to compile. Extension packages call EBS public APIs with long named-parameter lists. Where a customer's patch level lacks a parameter or a record field, the package fails to compile with PLS-00306 or PLS-00302 and is dropped by the installer, and every report and upload behind it then fails with PLS-00201. Identifying the offending parameter previously needed a hand-written query per API. Two modes. With API Package Name empty the scope is derived from the source of the installed extension packages (user_arguments joined to package.procedure references extracted from user_source), which also populates Referenced By. With API Package Name supplied the source scan is skipped by a bind filter and the named package's full signature is reported, which is the mode to use when the package that calls it has been dropped and its source is therefore gone. The API Procedure Name list reads user_procedures rather than user_arguments and matches its parent with a plain equality rather than xxen_util.contains, API Package Name being single valued: 0.11 seconds instead of 29.6. The dictionary views are the user_ ones throughout, the code being APPS owned. That is a correctness fix as well as the narrower choice: OWF_MGR carries its own copies of FND_FILE, FND_FUNCTION, FND_MESSAGE, FND_PREFERENCE and FND_PROFILE, so joining all_arguments on package name alone reported those arguments twice, under an owner that is not the package the code calls. Measured on r12, the derived scope is 32,935 arguments across 307 APPS packages plus 87 rows that are not APPS owned: 44 under OWF_MGR and 43 SYS and XDB rows that are database built-ins rather than EBS APIs. The no_merge on the source derived set is measured, not precautionary: the shipped statement returns its 32,935 rows in 23.9 seconds with it and had not finished after 250 seconds without it. This is not only the narrower choice: OWF_MGR carries its own copies of FND_FILE, FND_FUNCTION, FND_MESSAGE, FND_PREFERENCE and FND_PROFILE, so joining all_arguments on package name alone reported every argument of those five twice, under an owner that is not the package the code actually calls. Record arguments are expanded, so record fields such as pn_lease_pvt.lease_terms_rec.rou_asset_flag are reported and PLS-00302 component errors are diffable too. Verified on r12: default mode 626 APIs and 33,022 argument rows in 24 seconds; API Package Name=PA_PROJECT_PUB 3,409 rows in 2.5 seconds with the source scan skipped; the APIs behind six of the eight extension packages dropped at a customer are reported and attributed to the correct calling package. US Blitz Report Extension Package API Signatures Shows this instance's signatures of the Oracle EBS APIs that Blitz Report's extension packages call: every argument of every procedure, with its position, data type, in/out mode, whether it is defaulted, and the fields of any record argument. Its purpose is comparison. An extension package that calls an EBS API by named parameters fails to compile where a patch level does not have that parameter (PLS-00306) or that record field (PLS-00302), the installer then drops the package, and its reports and uploads fail with PLS-00201 at runtime. Running this report here and on a working instance and comparing the two shows exactly which argument or field is absent. Left empty, it reports every EBS API called by the extension packages installed here, and Referenced By names the calling package. That list is derived from the installed package source, so an extension package that failed to compile is not in it - name its API in the API Package Name parameter instead, which reports that package's full signature whether or not anything here calls it. Types and constants declared in a package specification are not arguments and so do not appear here. Use the DBA Text Search report on the specification for those. Enginatics 1=1 3=3 :api_package_name 1 10 3=3 ua.package_name=:api_package_name LOV custom Y Y select distinct uo.object_name value, null description from user_objects uo where uo.object_type='PACKAGE' order by value US API Package Name 2 :api_package_name US API Package Name 3 20 1=1 y.procedure_name=:api_procedure_name LOV custom Y select distinct up.procedure_name value, null description from user_procedures up where up.object_name=:$flex$.API_Package_Name and up.procedure_name is not null order by value US API Procedure Name 4 30 1=1 y.argument_name=:argument_name Char US Argument Name