Oracle APPS Useful Queries

                       ACCOUNT PAYABLES



Payable Open Interface Rejection:

 SELECT ail.*
FROM AP_INVOICES_INTERFACE ai ,
  ap_invoice_lines_interface ail ,
  ap_interface_rejections AIR
WHERE ai.GROUP_ID='EO_AUG-13_010813_443'
AND ai.status    ='REJECTED'
AND ai.invoice_id=ail.invoice_id
  --and ail.dist_code_concatenated='66810-00-999-00000000-00000000-00000-FI100'
AND air.parent_id = DECODE(air.parent_table,'AP_INVOICES_INTERFACE',AI.INVOICE_ID,'AP_INVOICE_LINES_INTERFACE',ail.invoice_line_id,NULL)
ORDER BY 2;

AP Invoices Query:

/*+INDEX (AP_INVOICES_ALL [XXDG_TEST]) */

SELECT  apia.invoice_id,
       alc.displayed_field "Invoice Type",
       apia.invoice_num "Invoice Number",
       apia.invoice_date "Invoice Date",
       apia.gl_date   "Invoice GL Date",
       nvl(apia.VOUCHER_NUM,doc_sequence_value)"Voucher Number",
       apia.description inv_Description,
       apia.invoice_currency_code,
       gcc.concatenated_segments "Liability Account",
       apia.invoice_amount,
       apia.exchange_rate,
       apia.base_amount,
       apia.amount_paid paid_Amount,
       pv.vendor_name "Supplier Name",
       pv.segment1 "Supplier Number",
       pvs.vendor_site_code "Site Code"
   
FROM ap_invoices_all apia,
     po_vendors      pv,
     po_Vendor_sites_all pvs,
     gl_code_combinations_kfv gcc,
     ap_lookup_codes alc

WHERE  1=1
AND  apia.vendor_id=pv.vendor_id
AND  apia.vendor_site_id=pvs.vendor_site_id
--AND  pv.vendor_id=pvs.vendor_id
AND  apia.accts_pay_code_combination_id=gcc.code_combination_id
AND  alc.lookup_type='INVOICE TYPE'
AND  alc.lookup_code=apia.invoice_type_lookup_code
AND  apia.org_id = 83
AND  apia.gl_date between to_date('01-04-2013','DD-MM-YYYY') and to_date('31-10-2013','DD-MM-YYYY');


Suppliers with  Attribute Value:

select pv.*
from ap_bank_account_uses_all acu
,ap_bank_accounts_all ac
,PO_VENDORS PV
where acu.vendor_id is not null
and acu.vendor_site_id is not null
AND PV.VENDOR_ID = acu.vendor_id
and acu.external_bank_account_id=ac.bank_account_id
and ac.global_attribute20 ='Domestic Transfer Long Notice'



                       ACCOUNT Receivables


Customer Profiles:

SELECT cp.cust_account_profile_id,
       cp.cust_account_id,
       cp.collector_id,
       col.NAME collector_name,
       cp.profile_class_id,
       cpc.NAME profile_class_name,
       cp.site_use_id,
      -- term.NAME standard_terms,
       cp.statement_cycle_id,
       --cyc.NAME statement_cycle_name,
       cp.autocash_hierarchy_id,
      -- hier.hierarchy_name autocash_hierarchy_name,
       cp.grouping_rule_id,
       --grp.NAME grouping_rule_name,
       cp.autocash_hierarchy_id_for_adr,
       --hier_adr.hierarchy_name autocash_hierarchy_name_adr,
       cp.*
  FROM hz_customer_profiles cp,
       ar_collectors col,
       hz_cust_profile_classes cpc
       --ar_dunning_letter_sets dun_set,
      -- ar_statement_cycles cyc,
      -- ar_autocash_hierarchies hier,
      -- ra_grouping_rules grp,
      -- ra_terms term,
      -- ar_autocash_hierarchies hier_adr
 WHERE cp.collector_id          = col.collector_id
   AND cp.profile_class_id      = cpc.profile_class_id(+)
  -- AND cp.dunning_letter_set_id = dun_set.dunning_letter_set_id(+)
  -- AND cp.statement_cycle_id    = cyc.statement_cycle_id(+)
  -- AND cp.autocash_hierarchy_id = hier.autocash_hierarchy_id(+)
  -- AND cp.grouping_rule_id      = grp.grouping_rule_id(+)
  -- AND cp.standard_terms        = term.term_id(+)
  -- AND cp.autocash_hierarchy_id_for_adr = hier_adr.autocash_hierarchy_id(+)
 --  AND cp.party_id              = vl_party_id
   AND cp.cust_account_id       = 55523
   AND cp.site_use_id           = 70114;



Customer Profile Amounts for Interest Invoice:

SELECT A.CUSTOMER_ID,
  COUNT(*)
FROM ar_customer_profile_amounts A
WHERE customer_site_use_id IN
  (SELECT SITE_USE_ID
  FROM HZ_CUST_SITE_USES_ALL
  WHERE SITE_USE_CODE    ='BILL_TO'
  AND CUST_ACCT_SITE_ID IN
    (SELECT CUST_ACCT_SITE_ID FROM HZ_CUST_ACCT_SITES_ALL WHERE ORG_ID=83
    )
  )
GROUP BY A.CUSTOMER_ID
HAVING COUNT(*) > 2
ORDER BY 1;

Query to get the Customer addresses:

SELECT /*+ INDEX(ACCT_SITE,HZ_CUST_ACCT_SITES_N2) */ site.location,
  party.party_name customer_name,
  party.party_number registry_id,
  site.site_use_id organization_id,
  site.location location_code,
  loc.address1 address_line_1,
  loc.address2 address_line_2,
  loc.address3 address_line_3,
  decode(loc.city,   NULL,   NULL,   loc.city || ', ') || decode(loc.state,   NULL,   loc.province || ', ',   loc.state || ', ') || decode(loc.postal_code,   NULL,   NULL,   loc.postal_code || ', ') || decode(loc.country,   NULL,   NULL,   loc.country) address_line_4,
  loc.address4 address_line_5,
  ar.meaning site_use_code,
  cust_acct.cust_account_id customer_id,
  cust_acct.account_number customer_number,
  cust_acct.account_name account_description
FROM hz_cust_acct_sites acct_site,
  hz_party_sites party_site,
  hz_locations loc,
  hz_cust_site_uses_all site,
  hz_parties party,
  hz_cust_accounts cust_acct,
  ar_lookups ar
WHERE site.site_use_code IN('SHIP_TO',   'SOLD_TO',   'DELIVER_TO',   'BILL_TO')
AND site.site_use_code = ar.lookup_code
AND ar.lookup_type = 'SITE_USE_CODE'
AND site.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND party_site.location_id = loc.location_id
AND acct_site.status = 'A'
AND acct_site.cust_account_id = NVL(:parameter.lov_num_param1,acct_site.cust_account_id)
AND acct_site.cust_account_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
AND cust_acct.status = 'A'
AND site.status = 'A'
ORDER BY site.location

Customer Open Balance:

SELECT rc.customer_number,
        rc.customer_id,
        SUM(apl.amount_due_remaining) Entered_Total_Balance,
        SUM(apl.amount_due_remaining*nvl(apl.exchange_rate,1)) Functional_Total_balance
  FROM ar_payment_schedules_all apl,
       ra_customers rc
  where rc.customer_id=apl.customer_id
   and  apl.org_id=223
   and rc.customer_number in (54500,54503,54744)
  GROUP BY rc.customer_id,rc.customer_number;


SELECT ar.customer_name
,      ar.customer_number
,     hsua.gl_id_rev
,     gcck.concatenated_segments "Revenue Account"
FROM  hz_cust_accounts hca
,     ar_customers    ar
,     hz_cust_acct_sites_all hcsa
,     hz_cust_site_uses_all  hsua
,     gl_code_combinations_kfv gcck

WHERE ar.customer_id=hca.cust_account_id
AND   hca.cust_account_id=hcsa.cust_account_id
AND   hsua.cust_acct_site_id=hcsa.cust_acct_site_id
AND   hsua.site_use_code='BILL_TO'
AND   gcck.code_combination_id=hsua.gl_id_rev
AND   hcsa.org_id=722
AND   hsua.org_id=722
ORDER BY 1;


Open Transactions in Oracle AR:


SELECT rcta.customer_trx_id,
  rcta.trx_number,
  rcta.trx_date,
  rcta.receipt_method_id,
  rcta.bill_to_customer_id,
  rcta.term_due_date,
  rcta.CUSTOMER_BANK_ACCOUNT_ID,
  rcta.BILL_TO_SITE_USE_ID
FROM ra_customer_trx_all rcta ,
  RA_CUST_TRX_TYPES_ALL rctt
WHERE 1                   =1--rcta.bill_to_customer_id=_CUSTOMER_ID        --55041
AND rcta.CUST_TRX_TYPE_ID = rctt.CUST_TRX_TYPE_ID
AND rcta.org_id           = rctt.org_id
AND rctt.TYPE            IN ('INV','CM','DM')
  --and rcta.CUST_TRX_TYPE_ID = NVL(P_TRAN_TYPE_ID,rcta.CUST_TRX_TYPE_ID)
AND NVL(rcta.RECEIPT_METHOD_ID,0) IN (2403,2223) --2131
AND rcta.org_id                    =403
  --AND trunc(rcta.trx_date) >= NVL(:P_TRANS_DATE_FROM,trunc(rcta.trx_date))
  -- AND trunc(rcta.trx_date) <= NVL(:P_TRANS_DATE_TO, trunc(rcta.trx_date))
  --AND rcta.trx_number between  NVL(:P_TRANS_NUM_FROM, rcta.trx_number) and NVL(:P_TRANS_NUM_TO, rcta.trx_number)
AND rcta.COMPLETE_FLAG ='Y'
AND EXISTS
  (SELECT customer_trx_id
  FROM ar_payment_schedules_all
  WHERE customer_trx_id = rcta.customer_trx_id
    -- having sum(amount_due_original) = sum(amount_due_remaining)
  HAVING SUM(amount_due_remaining) <> 0
  GROUP BY customer_trx_id
  )
ORDER BY 1;

AR Invoice Interface:

SELECT   rila.batch_source_name,
         rila.interface_line_attribute2                  "Invoice Number",
         rila.line_type,
         rila.amount                                     "Line Amount",
         rila.interface_line_id                          "Invoice Id",
         rida.interface_distribution_id                  "Distribution Id",
         rida.amount                                     "Distribution Amount",
         rila.trx_date                                   "Invoice Date",
         rila.gl_date                                    "GL Date",
         riea.message_text                               "Error Message",
         riea.invalid_value                              "Incorrect Value"
FROM     ra_interface_errors_all          riea,
         ra_interface_lines_all           rila,
         ra_interface_distributions_all   rida
WHERE    riea.interface_line_id           =  rila.interface_line_id
AND      riea.interface_line_id           =  rida.interface_line_id
AND      rila.org_id                      =  722

SELECT   rila.batch_source_name,
rila.interface_line_attribute2                  "Invoice Number",
rila.line_type,
rila.amount                                     "Line Amount",
rila.interface_line_id                          "Invoice Id",
rida.interface_distribution_id                  "Distribution Id",
rida.amount                                     "Distribution Amount",
rila.trx_date                                   "Invoice Date",
rila.gl_date                                    "GL Date",
riea.message_text                               "Error Message",
riea.invalid_value                              "Incorrect Value"
,rila.org_id         "org_id"
FROM     ra_interface_errors_all          riea,
ra_interface_lines_all           rila,
ra_interface_distributions_all   rida
WHERE    riea.interface_line_id           =  rila.interface_line_id
AND      riea.interface_line_id           =  rida.interface_line_id
AND      rila.org_id            in (383,
462)
ORDER BY 2 DESC



Customer's Receipt Method:


SELECT rc.customer_id
      ,rc.customer_name
      ,rc.customer_number
      ,arm.name     "Receipt Method Name"
FROM ra_customers rc
    ,hz_cust_acct_sites_all hcsl
    ,hz_cust_site_uses_all  hcs
    ,ra_cust_receipt_methods rcrm
    ,ar_receipt_methods     arm
WHERE hcsl.cust_account_id=rc.customer_id
AND   hcs.cust_acct_site_id= hcsl.cust_acct_site_id
AND   hcs.site_use_code='BILL_TO'
AND   hcs.site_use_id=rcrm.site_use_id
AND   arm.receipt_method_id= rcrm.receipt_method_id
--AND   rc.customer_id=1042
AND   hcsl.org_id=83
AND   hcsl.status='A'
AND   rc.status='A'
and   arm.name='XXXX Direct Debit';
--AND   rcrm.primary_flag='Y'
--AND   trunc(sysdate) between trunc(nvl(rcrm.start_date,sysdate)) and trunc(nvl(rcrm.end_date,sysdate))
--and   arm.name='XXDE - Direct Debit 45';
--1155 remitance Batch Name



 Customers with None or Zero CreditLimits :

SELECT rc.customer_name "Customer Name",
       rc.customer_number "Customer Number",
       hcsu.site_use_code  "Site Use Code",
       hcpa.currency_code  "Currency Code",    
       hcpa.overall_credit_limit "Credit Limit",
       hcpa.trx_credit_limit "Order Credit Limit"
FROM ra_customers rc
    ,hz_cust_acct_sites_all hcas
    ,hz_cust_site_uses_all hcsu
    ,hz_cust_profile_amts hcpa
    ,hz_customer_profiles hcp
 
WHERE hcas.status='A'
AND   hcsu.status='A'
AND   hcas.org_id=83
AND   hcsu.org_id=83
AND   rc.customer_id=hcas.cust_account_id
AND   hcas.cust_acct_site_id=hcsu.cust_acct_site_id
AND   hcpa.site_use_id=hcsu.site_use_id(+)
AND   hcp.cust_account_id=rc.customer_id
AND   hcp.status='A'
AND    rc.status='A'
AND   hcpa.cust_account_profile_id =hcp.cust_account_profile_id(+)
--AND   rc.customer_id =127006;
AND   hcsu.site_use_code='BILL_TO'
AND   (hcpa.overall_credit_limit <= 0 or hcpa.overall_credit_limit is null)

order by 2

AR Transaction Information:

select a.Customer_name,
     a.customer_number,
     b.trx_number,
     c.line_number,
     b.trx_date,
     c.REVENUE_AMOUNT "Freight Cost",
     b.attribute4 AMOUNT, e.Segment1 Account
from ar_customers a,
    RA_CUSTOMER_TRX_all b,
    RA_CUSTOMER_TRX_LINES_all c,
    RA_CUST_TRX_LINE_GL_DIST_all d,
    GL_CODE_COMBINATIONS e
where b.trx_date between to_date('01-FEB-2013','DD-MON-YYYY') and TO_DATE('01-FEB-2013','DD-MON-YYYY')
and b.org_id = '183'
and b.bill_to_customer_id = a.customer_id
and b.customer_trx_id = c.customer_trx_id
and b.customer_trx_id = d.customer_trx_id
and c.customer_trx_id = d.customer_trx_id
AND C.customer_trx_line_id = d.customer_trx_line_id
and d.Code_Combination_id = e.code_combination_id
order by 3,4 asc;


Customer Collector Name Update:

exec dbms_application_info.set_client_info(403);

update hz_customer_profiles
set collector_id = 2100
where collector_id = 2640 and site_use_id is not null
and site_use_id in (select site_use_id from hz_cust_site_uses where org_id = 403);

227 rows updated;

commit;


select distinct customer_number from (select rc.party_id,hps.party_site_id,rc.customer_name,rc.customer_number ,HPS.STATUS
from ra_customers rc
,    hz_party_sites hps
WHERE rc.party_id=hps.party_id
AND   hps.status='A'
AND  exists( select 'x' from hz_cust_acct_sites_all a where a.cust_account_id=rc.customer_id and org_id =247 and status='A')
AND  rc.customer_number in ('22611','53371'))a;


To Inactivate Customers:

UPDATE hz_parties
   SET status = 'A' ,
       last_update_date = sysdate
 WHERE Party_id = 113649;

UPDATE hz_cust_accounts
   SET status = 'A' ,
       last_update_date = sysdate
 WHERE party_id = 113649;

UPDATE hz_cust_acct_sites_all
   SET status = 'A' ,
       last_update_date = sysdate
 WHERE party_site_id = 106463;

 UPDATE hz_cust_site_uses_all
 SET STATUS='A',
     PRIMARY_FLAG='Y',
     last_update_date = sysdate
 WHERE SITE_USE_ID=180282;

  UPDATE hz_cust_site_uses_all
 SET STATUS='A',
     PRIMARY_FLAG='Y',
     last_update_date = sysdate
 WHERE SITE_USE_ID=180283;

 UPDATE HZ_PARTY_SITES
SET IDENTIFYING_ADDRESS_FLAG='Y',
    last_update_date = sysdate
WHERE PARTY_SITE_ID=119592;



                       AOL


Query to find Parameters and Value Sets associated with a Concurrent Program


SELECT
        fcpl.user_concurrent_program_name "Concurrent Program Name",
        fcp.concurrent_program_name "Short Name",
        fdfcuv.column_seq_num "Column Seq Number",
        fdfcuv.end_user_column_name "Parameter Name",
        fdfcuv.form_left_prompt "Prompt",
        fdfcuv.enabled_flag " Enabled Flag",
        fdfcuv.required_flag "Required Flag",
        fdfcuv.display_flag "Display Flag",
        fdfcuv.flex_value_set_id "Value Set Id",
        ffvs.flex_value_set_name "Value Set Name",
        flv.meaning "Default Type",
        fdfcuv.DEFAULT_VALUE "Default Value"

FROM
        fnd_concurrent_programs fcp,
        fnd_concurrent_programs_tl fcpl,
        fnd_descr_flex_col_usage_vl fdfcuv,
        fnd_flex_value_sets ffvs,
        fnd_lookup_values flv

WHERE
        fcp.concurrent_program_id = fcpl.concurrent_program_id
        AND    fcpl.user_concurrent_program_name = :conc_prg_name
        AND    fdfcuv.descriptive_flexfield_name = '$SRS$.'
                 || fcp.concurrent_program_name
        AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
        AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
        AND    flv.lookup_code(+) = fdfcuv.default_type
        AND    fcpl.LANGUAGE = USERENV ('LANG')
        AND    flv.LANGUAGE(+) = USERENV ('LANG')

ORDER BY fdfcuv.column_seq_num;

Users & Their Responsibilities Query:

SELECT
  a.USER_Name,
  ppf.full_name,
  a.EMPLOYEE_ID,
  b.RESPONSIBILITY_ID,
  c.RESPONSIBILITY_NAME,
  b.START_DATE  Resp_Start_date,
  b.END_DATE    Resp_END_DATE
FROM apps.FND_USER a,
  apps.FND_USER_RESP_GROUPS b,
  apps.FND_RESPONSIBILITY_VL c,
  apps.per_all_people_f  ppf
WHERE a.USER_ID        =b.USER_ID
AND b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
AND PPF.PERSON_ID(+) = a.Employee_id
AND c.responsibility_name like 'XXES%'
and a.end_date is null
order by 1;


List of concurrent programs Failed from given date:


SELECT fcr.request_id,
         fu.user_name,
         fcp.user_concurrent_program_name,
         TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') actual_start_date,
         TO_CHAR (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') actual_completion_date,
         fcr.logfile_name
    FROM apps.fnd_concurrent_requests fcr,
         apps.fnd_concurrent_programs_tl fcp,
         apps.fnd_user fu
   WHERE     fcr.program_application_id = fcp.application_id
         AND fcr.concurrent_program_id = fcp.concurrent_program_id
         AND fcr.requested_by = fu.user_id
         AND fcr.phase_code = 'C'
         AND fcr.status_code = 'E'
         AND fcr.actual_completion_date > to_date('&Enter_Date','DD-MON-YYYY')
         AND fcp.user_concurrent_program_name NOT IN
                ('Request Set Stage', 'Report Set')
         AND fcp.user_concurrent_program_name IN ('Interest Invoice Creation Process') --added
ORDER BY 1


Query to find Concurrent program ran by users with arguments:

SELECT DISTINCT USER_NAME
FROM
  (SELECT A.REQUEST_ID,
    TO_CHAR(A.REQUESTED_START_DATE,'DD-MON-YY HH24:MI:SS'),
    FU.USER_NAME,
    A.ARGUMENT_TEXT ALL_ARGUMNETS,
    A.ARGUMENT11,
    DECODE(A.ARGUMENT11,'D','In Detail','P','Summarize by Accounting Period','A','Summarize by Accounting Date',NULL) Transefer_To_Gl_Interface,
    A.REQUEST_DATE,
    A.REQUEST_ID,
    FRT.RESPONSIBILITY_NAME
  FROM --FND_CONC_REQ_HISTORY  A,
    FND_CONCURRENT_REQUESTS A,
    FND_USER FU,
    FND_RESPONSIBILITY_VL FRT,
    FND_CONCURRENT_PROGRAMS_VL fcpv
  WHERE A.CONCURRENT_PROGRAM_ID         =fcpv.concurrent_program_id
  AND FU.USER_ID                        =A.REQUESTED_BY
  AND FU.USER_NAME                     <>'FINANSJOBS'
  AND A.ARGUMENT11                     <> 'D'
  AND FRT.RESPONSIBILITY_ID             = A.RESPONSIBILITY_ID
  AND fcpv.user_concurrent_program_name ='Interest Invoice Creation Process'
  ORDER BY RESPONSIBILITY_NAME
  );


KFF Value set :

SELECT FSEG.id_flex_num,
  ffv.flex_value,
  ffv.enabled_flag,
  ffv.summary_flag,
  ffv.start_date_active,
  ffvt.*
FROM APPLSYS.fnd_id_flex_structures FFS ,
  APPS.fnd_id_flex_segments FSEG ,
  fnd_flex_values ffv ,
  fnd_flex_values_tl ffvt
WHERE FFS.application_id      =101
AND FFS.id_flex_code          = 'GL#'
AND FFS.id_flex_num           =FSEG.id_flex_num
AND ffv.flex_value_set_id     =fseg.flex_value_set_id
AND ffv.flex_value_id         =ffvt.flex_value_id
AND FFS.id_flex_structure_code='XXFR_ACCOUNTING_FLEX'
AND ffvt.created_by           =11979;

CCID Cross Validation Rules:

select fst.id_flex_structure_name"CoA"
, r.flex_validation_rule_name"Rule Name"
, r.enabled_flag"Enb?"
, r.ERROR_SEGMENT_COLUMN_NAME"Error Seg"
, length(tl.error_message_text)"Error Length"
, tl.ERROR_MESSAGE_TEXT"Message"
, tl.CREATION_DATE
FROM fnd_flex_validation_rules r,
fnd_flex_vdation_rules_tl tl,
fnd_id_flex_structures_vl fst
WHERE r.application_id = tl.application_id
AND fst.ID_FLEX_num = r.id_flex_num
AND r.id_flex_code = tl.id_flex_code
AND r.id_flex_num = tl.id_flex_num
AND r.flex_validation_rule_name = tl.flex_validation_rule_name
AND r.application_id = 101
--AND substr(fst.id_flex_structure_name,1,2) in ('BE','LU','ES') -- LIMITS RESULTS TO SPECIFIC CHARTS OF ACCOUNTS
--AND length(tl.error_message_text) > 150 --- THIS IS USED FOR CHECK FOR MESSAGES OVER 150 CHARACTERS THAT CAN CAUSE SQL ERRORS IN I-Expenses
AND fst.id_flex_structure_name='XXFI Accounting Flexfield'
ORDER BY 1,2
SELECT * FROM FND_FLEX_VALIDATION_RULE_LINES WHERE ID_FLEX_NUM=50592;



Query to Know the API's in a Module :

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AR_%API%'
order by
a.owner, a.name;

FOR Finding API's in ORACLE APPS R12:

select distinct '@proto3 ' ||
package_name || ' ' ||
object_name || ' ' ||
nvl(overload,0)
from all_arguments aa
where 1=1
and object_id in
(select uo.object_id
from
user_objects uo,
FND_IREP_CLASSES fic
where 1=1
and uo.object_type = 'PACKAGE'
and uo.object_name = fic.irep_name
and fic.CLASS_TYPE = 'PLSQL');

UTL_MAIL Verification:

BEGIN
  UTL_MAIL.send(sender     => 'AdministrationDenmarkDebitor@bestseller.com',
                recipients => 'dasthagiri.t@xxxx.com',
                cc         => 'suresh@xxxs.com',
                bcc        => 'mahesh@xxxx.com',
                subject    => 'UTL_MAIL Test',
                message    => 'If you get this message it worked!');
END;


UTL_FILE Example:

DECLARE
V_file  utl_file.file_type;
begin
V_file := UTL_FILE.FOPEN('D:\edi\xxfa\AP\Payments','text_file.txt','W',32767);
utl_file.put_line(v_file,RPad('TEST',2000,' TEST'));
utl_file.fclose(v_file)
EXCEPTION
       WHEN UTL_FILE.INVALID_PATH THEN
           DBMS_OUTPUT.PUT_LINE ('invalid_path');
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.INVALID_MODE THEN
           DBMS_OUTPUT.PUT_LINE ('invalid_mode');
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.INVALID_FILEHANDLE THEN
           DBMS_OUTPUT.PUT_LINE ('invalid_filehandle');
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.INVALID_OPERATION THEN
           DBMS_OUTPUT.PUT_LINE ('invalid_operation');
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.READ_ERROR THEN
           DBMS_OUTPUT.PUT_LINE ('read_error');
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.WRITE_ERROR THEN
          DBMS_OUTPUT.PUT_LINE ('write_error');
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.INTERNAL_ERROR THEN
          DBMS_OUTPUT.PUT_LINE ('internal_error');
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
          RAISE;          
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ('other write error');
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
          RAISE;
    END;