Query to list all form personalization

This query is used to list all form personalization by passing form  function name:

SELECT
         FUNCTION_NAME,
         FORM_NAME,
         ID,
         CREATED_BY,
         CREATION_DATE,
         LAST_UPDATED_BY,
         LAST_UPDATE_DATE,
         LAST_UPDATE_LOGIN,
         SEQUENCE,
         RULE_KEY,
         DESCRIPTION,
         RULE_TYPE,
         ENABLED,
         TRIGGER_EVENT,
         TRIGGER_OBJECT,
         CONDITION,
         FIRE_IN_ENTER_QUERY
    FROM FND_FORM_CUSTOM_RULES
   WHERE     (   (function_name = 'PO_POXPOEPO' AND rule_type = 'A')
              OR (form_name = 'POXPOEPO' AND rule_type = 'F'))
         AND rule_key IS NULL
ORDER BY sequence

How to purge e-mail notifications from the Workflow queue

Some times Due to large number of e-mail notifications to accumulated in the queue Workflow mailer will not start,To fix this issue we need purge the notifications from the Workflow queue.

The below outlines the steps, Please take proper backup before performing the below.

1) You need to update the notifications you do not want sent, in the WF_NOTIFICATIONS table.

2) Check the WF_NOTIFICATIONS table as below. Records where status = 'OPEN' and mail_status = 'MAIL' are notifications that will have an e-mail notification sent.

SQL> select notification_id,status,mail_status,begin_date from WF_NOTIFICATIONS where status = 'OPEN' and mail_status = 'MAIL';

3) This should show you which notifications are waiting to be e-mailed.

4) To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. The mailer will think the e-mail has already been sent and it will not send it again.

SQL> update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

-> This will update all notifications waiting to be sent by the mailer.

5) Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.

SQL>sqlplus apps/apps_pwd @$FND_TOP/patch/115/sql/wfntfqup APPS APPS_PWD APPLSYS

6) Now you can start your WF Containers and then Mailer

How to Enable Personalization link for OAF Pages

How to Enable Personalization link for OAF Pages

Set the values of following profiles to enable Personalization Page link in OAF Pages:
1. FND: Personalization Region Link Enabled = Yes
2. Personalize Self-Service Defn = Yes
3. Disable Self-Service Personal - No

After Setting the Values of above profiles:

Query to get GL drill down to AP

SELECT 'AP' source,
               asup.segment1 vendor_number,
               asup.vendor_name,
               h.je_category,
               h.je_source,
               h.period_name je_period_name,
               h.name journal_name,
               h.status journal_status,
               h.created_by je_created_by,
               h.creation_date je_created_date,
               glcc.concatenated_segments account,
               l.description,
               xlal.accounting_class_code,
               xlal.description xlal_description,
               xlal.accounting_date accounting_date,
               xlate.transaction_number xlate_transaction_number,
               gir.subledger_doc_sequence_value voucher_number,
               h.currency_code entered_currencey,
               (SELECT SUM (NVL (begin_balance_dr, 0))
                       - SUM (NVL (begin_balance_cr, 0))
                  FROM gl_balances gb
                 WHERE     gb.code_combination_id = glcc.code_combination_id
                       AND gb.ledger_id = 2091
                       AND gb.actual_flag = 'A'
                       AND gb.period_name = NVL (:period, h.period_name)
                       AND gb.currency_code = h.currency_code)
                  begining_balance,
               (SELECT SUM (NVL (period_net_dr, 0))
                       - SUM (NVL (period_net_cr, 0))
                       + (SUM (NVL (begin_balance_dr, 0))
                          - SUM (NVL (begin_balance_cr, 0)))
                  FROM gl_balances gb
                 WHERE     gb.code_combination_id = glcc.code_combination_id
                       AND gb.ledger_id = 2091
                       AND gb.actual_flag = 'A'
                       AND gb.period_name = NVL (:period, h.period_name)
                       AND gb.currency_code = h.currency_code)
                  ending_balance,
               l.accounted_dr,
               l.accounted_cr,
               xlal.accounted_dr trx_accounted_dr_jod,
               xlal.accounted_cr trx_accounted_cr_jod,
               NVL (l.accounted_dr, 0) - NVL (l.accounted_cr, 0) balance,
               NULL po_number,
               NULL receipt_num,
               (SELECT bank_name || ' - ' || bank_account_num_electronic
                  FROM ar_cash_receipts_v v, iby_ext_bank_accounts_v vv
                 WHERE vv.ext_bank_account_id = v.customer_bank_account_id
                       AND gir.subledger_doc_sequence_value =
                              v.document_number)
                  customer_bank,
               (SELECT BANK_ACCOUNT_NAME || ' - ' || BANK_ACCOUNT_NUM
                  FROM ce_bank_acct_uses ba,
                       ce_bank_accounts cba,
                       ce_bank_branches_v bb,
                       ar_receipt_method_accounts v
                 WHERE     bb.bank_name LIKE '%'
                       AND bb.bank_branch_name LIKE '%'
                       AND bb.bank_institution_type = 'BANK'
                       AND bb.branch_party_id = cba.bank_branch_id
                       AND cba.bank_account_id = ba.bank_account_id
                       AND cba.account_classification = 'INTERNAL'
                       AND ba.bank_acct_use_id = v.remit_bank_acct_use_id
                       AND CASH_CCID = glcc.code_combination_id
                       AND RECEIPT_METHOD_ID = 10000)
                  bank_account_name
          FROM gl_je_batches b,
               gl_je_headers h,
               gl_je_lines l,
               gl_code_combinations_kfv glcc,
               gl_import_references gir,
               xla_ae_lines xlal,
               xla_ae_headers xlah,
               xla_events xlae,
               xla.xla_transaction_entities xlate,
               ap_suppliers asup
         WHERE     1 = 1
               AND glcc.chart_of_accounts_id = 50408
               AND h.ledger_id = 2091
               AND b.je_batch_id = h.je_batch_id
               AND h.je_header_id = l.je_header_id
               AND l.je_header_id = gir.je_header_id
               AND l.je_line_num = gir.je_line_num
               AND xlal.code_combination_id = glcc.code_combination_id
               AND gir.gl_sl_link_table = xlal.gl_sl_link_table
               AND gir.gl_sl_link_id = xlal.gl_sl_link_id
               AND xlal.ae_header_id = xlah.ae_header_id
               AND xlah.event_id = xlae.event_id
               AND xlae.entity_id = xlate.entity_id
               AND xlae.application_id = xlate.application_id
               AND h.je_source = 'Payables'
               AND asup.vendor_id(+) = xlal.party_id
               AND h.period_name = NVL (:period, h.period_name)
               AND glcc.concatenated_segments BETWEEN NVL (
                                                         :p_min_flex,
                                                         glcc.concatenated_segments)
                                                  AND NVL (
                                                         :p_max_flex,
                                                         glcc.concatenated_segments)

To Remove Responsibility from user

Declare

CURSOR cur_resp
   IS
      SELECT application_short_name, responsibility_key, security_group_key
        FROM fnd_responsibility fr,
             fnd_application fa,
             fnd_security_groups frg,
             fnd_responsibility_tl frt
       WHERE     fr.application_id = fa.application_id
             AND fr.data_group_id = frg.security_group_id
             AND fr.responsibility_id = frt.responsibility_id
             AND frt.language = 'US'
             AND fr.responsibility_id = 12345;
BEGIN
   FOR i IN cur_resp
   LOOP
      BEGIN
         fnd_user_pkg.delresp (username         => 'ALI',
                               resp_app         => i.application_short_name,
                               resp_key         => i.responsibility_key,
                               security_group   => i.security_group_key);
     
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (SQLERRM);
      END;

      DBMS_OUTPUT.put_line (
         
         || i.application_short_name
         || ' '
         || i.responsibility_key
         || ' '
         || i.security_group_key);

   END LOOP;

   COMMIT;
END;

To Add user Responsibility like another user

DECLARE
   lc_user_name              VARCHAR2 (100) := 'ALI';
   lc_resp_appl_short_name   VARCHAR2 (100) := 'SYSADMIN';
   lc_responsibility_key     VARCHAR2 (100) := 'SYSTEM_ADMINISTRATOR';
   lc_security_group_key     VARCHAR2 (100) := 'STANDARD';
   ld_resp_start_date        DATE := TO_DATE ('25-MAR-2015');
   ld_resp_end_date          DATE := NULL;
BEGIN
   fnd_user_pkg.addresp (username         => lc_user_name,
                         resp_app         => lc_resp_appl_short_name,
                         resp_key         => lc_responsibility_key,
                         security_group   => lc_security_group_key,
                         description      => NULL,
                         start_date       => ld_resp_start_date,
                         end_date         => ld_resp_end_date);

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;

Query to get Concurrent Programs

 SELECT f.user_concurrent_program_name,
         f.concurrent_program_name,
         f.output_print_style,
         f.output_file_type,
         column_seq_num no,
         end_user_column_name parameter,
         a.form_left_prompt prompt,
         b.flex_value_set_name value_set,
         DECODE (a.default_type,
                 'C', 'Constant',
                 'P', 'Profile',
                 'S', 'SQL Statement')
            default_type,
         a.DEFAULT_VALUE,
         required_flag required,
         srw_param token,
         a.enabled_flag display,
         g.application_table_name,
         g.additional_where_clause,
         srw_param
    FROM fnd_descr_flex_col_usage_vl a,
         fnd_flex_value_sets b,
         fnd_flex_validation_tables g,
         fnd_concurrent_programs_vl f
   WHERE     1 = 1
         AND SUBSTR (descriptive_flexfield_name, 7) LIKE 'XX%'
         AND a.flex_value_set_id = b.flex_value_set_id
         AND SUBSTR (descriptive_flexfield_name, 7) = f.concurrent_program_name
         AND b.flex_value_set_id = g.flex_value_set_id(+)
ORDER BY descriptive_flexfield_name, column_seq_num

Query to get Customer Contact

SELECT hcasa.org_id,
       role_acct.account_number,
       hcasa.orig_system_reference,
       rel.subject_id,
       rel.object_id,
       party.party_id party_id,
       rel_party.party_id rel_party_id,
       acct_role.cust_account_id,
       acct_role.cust_acct_site_id,
       party.person_pre_name_adjunct contact_prefix,
       SUBSTR (party.person_first_name, 1, 40) contact_first_name,
       SUBSTR (party.person_middle_name, 1, 40) contact_middle_name,
       SUBSTR (party.person_last_name, 1, 50) contact_last_name,
       party.person_name_suffix contact_suffix,
       acct_role.status,
       org_cont.job_title contact_job_title,
       org_cont.job_title_code contact_job_title_code,
       rel_party.address1 contact_address1,
       rel_party.address2 contact_address2,
       rel_party.address3 contact_address3,
       rel_party.address4 contact_address4,
       rel_party.country contact_country,
       rel_party.state contact_state,
       rel_party.city contact_city,
       rel_party.county contact_county,
       rel_party.postal_code contact_postal_code
  FROM hz_contact_points cont_point,
       hz_cust_account_roles acct_role,
       hz_parties party,
       hz_parties rel_party,
       hz_relationships rel,
       hz_org_contacts org_cont,
       hz_cust_accounts role_acct,
       hz_contact_restrictions cont_res,
       hz_person_language per_lang,
       hz_cust_acct_sites_all hcasa
 WHERE     acct_role.party_id = rel.party_id
       AND acct_role.role_type = 'CONTACT'
       AND org_cont.party_relationship_id = rel.relationship_id
       AND rel.subject_id = party.party_id
       AND rel_party.party_id = rel.party_id
       AND cont_point.owner_table_id(+) = rel_party.party_id
       AND cont_point.contact_point_type(+) = 'EMAIL'
       AND cont_point.primary_flag(+) = 'Y'
       AND acct_role.cust_account_id = role_acct.cust_account_id
       AND role_acct.party_id = rel.object_id
       AND party.party_id = per_lang.party_id(+)
       AND per_lang.native_language(+) = 'Y'
       AND party.party_id = cont_res.subject_id(+)
       AND cont_res.subject_table(+) = 'HZ_PARTIES'
       AND role_acct.cust_account_id = hcasa.cust_account_id
       AND hcasa.cust_acct_site_id = acct_role.cust_acct_site_id

Query to get Customer Information

SELECT party.party_type customer_type,
         party.party_name organization_name,
         acct.account_number,
         acct.account_name account_description,
         acct.customer_class_code classification,
         acct.sales_channel_code sales_channel,
         (SELECT meaning
            FROM fnd_lookup_values                                --ar_lookups
           WHERE     lookup_type = 'CUSTOMER_TYPE'
                 AND language = USERENV ('LANG')
                 AND lookup_code = acct.customer_type)
            account_type,
         (SELECT meaning
            FROM fnd_lookups
           WHERE lookup_type = 'OKL_CUSTOMER_STATUS'
                 AND lookup_code = acct.status)
            customer_status,
         (SELECT territory_short_name
            FROM fnd_territories_vl
           WHERE territory_code = hl.country)
            country,
         hl.address1 address_line_1,
         hl.city,
         hl.postal_code,
         (SELECT meaning
            FROM fnd_lookups
           WHERE lookup_type = 'OKL_CUSTOMER_STATUS'
                 AND lookup_code = hps.status)
            address_status,
         (SELECT hr.name
            FROM hr_all_organization_units hr
           WHERE hr.organization_id = hcsua.org_id)
            operating_unit,
         hcsa.customer_category_code category,
         (SELECT meaning
            FROM fnd_lookups
           WHERE lookup_type = 'ACCOUNT_STATUS' AND lookup_code = hcsa.status)
            acc_site_status,
         hcsua.location,
         hcsua.primary_flag primary,
         (SELECT concatenated_segments
            FROM gl_code_combinations_kfv glcc
           WHERE hcsua.gl_id_rec = glcc.code_combination_id)
            receivable_account,
         (SELECT concatenated_segments
            FROM gl_code_combinations_kfv glcc
           WHERE hcsua.gl_id_rev = glcc.code_combination_id)
            revenue_account,
         (SELECT t.name
            FROM ra_terms_vl t
           WHERE t.term_id = hcsua.payment_term_id)
            payment_terms,
         (SELECT resource_name
            FROM jtf_rs_salesreps jrs,
                 jtf_rs_resource_extns b,
                 jtf_rs_resource_extns_tl t
           WHERE     jrs.resource_id = b.resource_id
                 AND t.language = USERENV ('LANG')
                 AND b.resource_id = t.resource_id
                 AND b.category = t.category
                 AND b.category IN
                        ('EMPLOYEE',
                         'OTHER',
                         'PARTY',
                         'PARTNER',
                         'SUPPLIER_CONTACT')
                 AND jrs.salesrep_id = hcsua.primary_salesrep_id
                 AND jrs.org_id = hcsua.org_id)
            salesperson,
         (SELECT t.name
            FROM oe_transaction_types_tl t, oe_transaction_types_syn b
           WHERE     b.transaction_type_id(+) = t.transaction_type_id
                 AND t.language = USERENV ('LANG')
                 AND t.transaction_type_id = hcsua.order_type_id)
            order_type,
         (SELECT name
            FROM qp_list_headers_tl t, qp_list_headers_b b
           WHERE     b.list_header_id(+) = t.list_header_id
                 AND t.language = USERENV ('LANG')
                 AND t.list_header_id = hcsua.price_list_id)
            price_list,
         (SELECT t.name
            FROM ra_terms_vl t, hz_cust_site_uses_all hcsua1
           WHERE     t.term_id = hcsua1.payment_term_id
                 AND hcsua1.cust_acct_site_id = hcsa.cust_acct_site_id
                 AND hcsua1.site_use_id = hcsua.site_use_id
                 AND hcsua1.site_use_code = 'SHIP_TO')
            ship_payment_terms,
         (SELECT resource_name
            FROM jtf_rs_salesreps jrs,
                 jtf_rs_resource_extns b,
                 jtf_rs_resource_extns_tl t,
                 hz_cust_site_uses_all hcsua1
           WHERE     jrs.resource_id = b.resource_id
                 AND t.language = USERENV ('LANG')
                 AND b.resource_id = t.resource_id
                 AND b.category = t.category
                 AND b.category IN
                        ('EMPLOYEE',
                         'OTHER',
                         'PARTY',
                         'PARTNER',
                         'SUPPLIER_CONTACT')
                 AND jrs.salesrep_id = hcsua1.primary_salesrep_id
                 AND hcsua1.cust_acct_site_id = hcsa.cust_acct_site_id
                 AND hcsua1.site_use_id = hcsua.site_use_id
                 AND hcsua1.site_use_code = 'SHIP_TO')
            ship_salesperson,
         (SELECT t.name
            FROM oe_transaction_types_tl t,
                 oe_transaction_types_syn b,
                 hz_cust_site_uses_all hcsua1
           WHERE     b.transaction_type_id(+) = t.transaction_type_id
                 AND t.language = USERENV ('LANG')
                 AND hcsua1.site_use_code = 'SHIP_TO'
                 AND t.transaction_type_id = hcsua1.order_type_id
                 AND hcsua1.site_use_id = hcsua.site_use_id
                 AND hcsua1.cust_acct_site_id = hcsa.cust_acct_site_id)
            ship_order_type,
         (SELECT name
            FROM qp_list_headers_tl t,
                 qp_list_headers_b b,
                 hz_cust_site_uses_all hcsua1
           WHERE     b.list_header_id(+) = t.list_header_id
                 AND t.language = USERENV ('LANG')
                 AND t.list_header_id = hcsua1.price_list_id
                 AND hcsua1.site_use_id = hcsua.site_use_id
                 AND hcsua1.site_use_code = 'SHIP_TO'
                 AND hcsua1.cust_acct_site_id = hcsa.cust_acct_site_id)
            ship_price_list,
         (SELECT hcpc.name
            FROM hz_cust_profile_classes hcpc
           WHERE hcpc.profile_class_id = profile.profile_class_id)
            profile_class,
         (SELECT ac.name
            FROM ar_collectors ac
           WHERE ac.collector_id = profile.collector_id)
            collector,
         profile.credit_classification,
         profile.credit_checking,
         profile.credit_hold,
         profile.percent_collectable,
         profile.standard_terms,
         profile.override_terms,
         profile.discount_terms allow_discount,
         profile.discount_grace_days,
         profile.auto_rec_incl_disputed_flag,
         profile.send_statements,
         profile.credit_balance_statements send_credit_balance,
         (SELECT ast.name
            FROM ar_statement_cycles ast
           WHERE ast.statement_cycle_id = profile.statement_cycle_id)
            statement_cycle,
         profile.dunning_letters,
         profile.tax_printing_option,
         (SELECT rgr.name
            FROM ra_grouping_rules rgr
           WHERE rgr.grouping_rule_id = profile.grouping_rule_id)
            grouping_rule,
         hcpa.currency_code profile_currency,
         hcpa.overall_credit_limit credit_limit,
         hcpa.trx_credit_limit order_credit_limit,
         DECODE (profile.late_charge_calculation_trx,
                 'LATE', profile.late_charge_calculation_trx)
            late_payments_only,
         DECODE (profile.late_charge_calculation_trx,
                 'OVERDUE', profile.late_charge_calculation_trx)
            overdue_invoices_only,
         DECODE (profile.late_charge_calculation_trx,
                 'OVERDUE_LATE', profile.late_charge_calculation_trx)
            overdue_invoices_late_payments,
         (SELECT t.name
            FROM fnd_currencies_tl t, fnd_currencies b
           WHERE     b.currency_code = t.currency_code
                 AND t.language = USERENV ('LANG')
                 AND t.currency_code = hcpa.currency_code)
            late_chrg_currency
    FROM hz_cust_accounts acct,
         hz_parties party,
         hz_customer_profiles profile,
         hz_party_sites hps,
         hz_cust_site_uses_all hcsua,
         hz_cust_acct_sites_all hcsa,
         hz_locations hl,
         hz_cust_profile_amts hcpa
   WHERE     1 = 1
         AND hcsua.site_use_code = 'BILL_TO'
         AND acct.party_id = party.party_id
         AND profile.party_id = party.party_id
         AND acct.cust_account_id = profile.cust_account_id
         AND profile.site_use_id = hcsua.site_use_id
         AND hps.party_id = party.party_id
         AND hcsa.cust_acct_site_id = hcsua.cust_acct_site_id
         AND hcsa.cust_account_id = acct.cust_account_id
         AND hcsa.party_site_id = hps.party_site_id
         AND hps.location_id = hl.location_id
         AND hcpa.cust_account_id = profile.cust_account_id
         AND hcpa.cust_account_profile_id = profile.cust_account_profile_id
         AND hcpa.site_use_id IS NOT NULL
         AND hcpa.site_use_id = profile.site_use_id
ORDER BY acct.account_number

Query to get Supplier Bank

SELECT DISTINCT ieb.bank_name
               FROM ap.ap_supplier_sites_all apss,
                    apps.iby_ext_bank_accounts ieba,
                    apps.iby_account_owners iao,
                    apps.iby_ext_banks_v ieb
              WHERE     apss.vendor_id = 123
                    AND iao.account_owner_party_id = ac.party_id
                    AND ieba.ext_bank_account_id = iao.ext_bank_account_id
                    AND ieba.bank_id = ieb.bank_party_id

Query to get major Supplier Data

SELECT  (SELECT NAME

                   FROM hr_all_organization_units

                  WHERE organization_id = sups.org_id) operating_unit,

                sup.vendor_name, vendor_name_alt alternativ_name,

                sup.segment1 vendor_number, vendor_site_code,

                vendor_type_lookup_code, sup.end_date_active, address_line1,

                sup.creation_date,

                (SELECT territory_short_name

                   FROM fnd_territories_vl

                  WHERE territory_code = sups.country) country,

                q.party_name contact_name, q.phone_number, q.email_address,

                q.fax_number, (SELECT NAME

                                 FROM ap_terms_vl

                                WHERE term_id = sups.terms_id) terms,

                sup.attribute1 CATEGORY, sup.attribute2 sub_category,

                purchasing_site_flag, pay_site_flag, rfq_only_site_flag,

                (SELECT concatenated_segments

                   FROM gl_code_combinations_kfv

                  WHERE code_combination_id =

                               sups.prepay_code_combination_id)

                                                               prepay_account,

                (SELECT concatenated_segments

                   FROM gl_code_combinations_kfv

                  WHERE code_combination_id =

                           sups.accts_pay_code_combination_id)

                                                            liability_account,

                (SELECT employee_number

                   FROM per_all_people_f

                  WHERE person_id = sup.employee_id

                    AND SYSDATE BETWEEN effective_start_date

                                    AND effective_end_date) employee_number

           FROM ap_suppliers sup,

                ap_supplier_sites_all sups,

                (SELECT DISTINCT hzr.object_id, hp.party_name,

                                 hcpp.raw_phone_number phone_number,

                                 hcpe.email_address,

                                 hcpf.raw_phone_number fax_number

                            FROM hz_parties hp,

                                 fnd_user fu,

                                 hz_relationships hzr,

                                 hz_parties hzr_hp,

                                 hz_party_usg_assignments hpua,

                                 hz_contact_points hcpp,

                                 hz_contact_points hcpf,

                                 hz_contact_points hcpe

                           WHERE hp.party_id = hzr.subject_id

                             -- AND hzr.object_id = sup.party_id -- party of supplier

                             AND hzr.relationship_type = 'CONTACT'

                             AND hzr.relationship_code = 'CONTACT_OF'

                             AND hzr.subject_type = 'PERSON'

                             AND hzr.object_type = 'ORGANIZATION'

                             AND (   hzr.end_date IS NULL

                                  OR hzr.end_date > SYSDATE

                                 )

                             AND hzr.status = 'A'

                             AND hzr_hp.party_id = hzr.party_id

                             AND fu.person_party_id(+) = hp.party_id

                             AND hpua.party_id = hp.party_id

                             AND hpua.status_flag = 'A'

                             AND hpua.party_usage_code = 'SUPPLIER_CONTACT'

                             AND hcpp.owner_table_name(+) = 'HZ_PARTIES'

                             AND hcpp.owner_table_id(+) = hzr.party_id

                             AND hcpp.phone_line_type(+) = 'GEN'

                             AND hcpp.contact_point_type(+) = 'PHONE'

                             AND hcpp.primary_flag(+) = 'Y'

                             AND hcpe.owner_table_name(+) = 'HZ_PARTIES'

                             AND hcpe.owner_table_id(+) = hzr.party_id

                             AND hcpe.contact_point_type(+) = 'EMAIL'

                             AND hcpe.primary_flag(+) = 'Y'

                             AND hcpe.status(+) = 'A'

                             AND hcpp.status(+) = 'A'

                             AND hcpf.owner_table_name(+) = 'HZ_PARTIES'

                             AND hcpf.owner_table_id(+) = hzr.party_id

                             AND hcpf.phone_line_type(+) = 'FAX'

                             AND hcpf.contact_point_type(+) = 'PHONE'

                             AND hcpf.status(+) = 'A'

                             AND hcpe.primary_flag(+) = 'N'

                             AND (   hpua.effective_end_date IS NULL

                                  OR hpua.effective_end_date > SYSDATE

                                 )) q

          WHERE sups.vendor_id = sup.vendor_id AND q.object_id(+) =

                                                                  sup.party_id

ORDER BY        sup.vendor_id, 4, 5