1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

11i supplier extract queries

Discussion in 'Oracle Apps Technical' started by niranjan, Jul 10, 2017.

  1. niranjan

    niranjan Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    50
    Location:
    Ellicott City
    Code (SQL):
    ------------------------------------------------------------
    ------------------------------------------------------------
    /* 1. 11i Vendors Extract */
    /* Save as: 11i_suppliers.csv */
    ------------------------------------------------------------
    ------------------------------------------------------------
    SELECT pv.vendor_id, pv.vendor_name, pv.vendor_name_alt, pv.segment1,
           pv.employee_id, NULL employee_number, pv.vendor_type_lookup_code,
           pv.customer_num, pv.one_time_flag, pv.terms_id, t.NAME terms_name,
           pv.set_of_books_id, pv.pay_date_basis_lookup_code,
           pv.pay_group_lookup_code, pv.payment_priority,
           pv.invoice_currency_code, pv.payment_currency_code,
           pv.hold_all_payments_flag, pv.hold_reason, pv.num_1099, pv.type_1099,
           pv.withholding_status_lookup_code, pv.withholding_start_date,
           pv.organization_type_lookup_code, pv.start_date_active,
           pv.end_date_active, pv.payment_method_lookup_code, pv.terms_date_basis,
           pv.receipt_required_flag, pv.qty_rcv_tolerance,
           pv.qty_rcv_exception_code, pv.enforce_ship_to_location_code,
           pv.days_early_receipt_allowed, pv.days_late_receipt_allowed,
           pv.receipt_days_exception_code, pv.exclusive_payment_flag,
           pv.auto_tax_calc_flag, pv.auto_tax_calc_override,
           pv.amount_includes_tax_flag, pv.tax_verification_date,
           pv.state_reportable_flag, pv.federal_reportable_flag,
           pv.attribute_category, pv.attribute1, pv.attribute2, pv.attribute3,
           pv.attribute4, pv.attribute5, pv.attribute6, pv.attribute7,
           pv.attribute8, pv.attribute9, pv.attribute10, pv.attribute11,
           pv.attribute12, pv.attribute13, pv.attribute14, pv.attribute15,
           pv.vat_registration_num, pv.exclude_freight_from_discount,
           pv.tax_reporting_name, pv.allow_awt_flag, pv.awt_group_id,
           awg.NAME awg_group_name, pv.match_option, pv.create_debit_memo_flag,
           pv.match_status_flag, pv.individual_1099, NULL business_group_id,
           NULL business_group_name
      FROM po_vendors pv, ap_terms_tl t, ap_awt_groups awg
    WHERE pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NULL
       AND t.term_id(+) = pv.terms_id
       AND NVL(pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND pv.awt_group_id = awg.GROUP_ID(+)
       AND EXISTS (
              SELECT 1
                FROM ap_invoices_all i
               WHERE i.vendor_id = pv.vendor_id
                 AND i.invoice_date >= ADD_MONTHS (TRUNC (SYSDATE), -24))
    UNION
    -- Solomon vendors
    SELECT pv.vendor_id, pv.vendor_name, pv.vendor_name_alt, pv.segment1,
           pv.employee_id, NULL employee_number, pv.vendor_type_lookup_code,
           pv.customer_num, pv.one_time_flag, pv.terms_id, t.NAME terms_name,
           pv.set_of_books_id, pv.pay_date_basis_lookup_code,
           pv.pay_group_lookup_code, pv.payment_priority,
           pv.invoice_currency_code, pv.payment_currency_code,
           pv.hold_all_payments_flag, pv.hold_reason, pv.num_1099, pv.type_1099,
           pv.withholding_status_lookup_code, pv.withholding_start_date,
           pv.organization_type_lookup_code, pv.start_date_active,
           pv.end_date_active, pv.payment_method_lookup_code, pv.terms_date_basis,
           pv.receipt_required_flag, pv.qty_rcv_tolerance,
           pv.qty_rcv_exception_code, pv.enforce_ship_to_location_code,
           pv.days_early_receipt_allowed, pv.days_late_receipt_allowed,
           pv.receipt_days_exception_code, pv.exclusive_payment_flag,
           pv.auto_tax_calc_flag, pv.auto_tax_calc_override,
           pv.amount_includes_tax_flag, pv.tax_verification_date,
           pv.state_reportable_flag, pv.federal_reportable_flag,
           pv.attribute_category, pv.attribute1, pv.attribute2, pv.attribute3,
           pv.attribute4, pv.attribute5, pv.attribute6, pv.attribute7,
           pv.attribute8, pv.attribute9, pv.attribute10, pv.attribute11,
           pv.attribute12, pv.attribute13, pv.attribute14, pv.attribute15,
           pv.vat_registration_num, pv.exclude_freight_from_discount,
           pv.tax_reporting_name, pv.allow_awt_flag, pv.awt_group_id, awg.NAME awg_group_name,
           pv.match_option, pv.create_debit_memo_flag, pv.match_status_flag,
           pv.individual_1099, NULL business_group_id, NULL business_group_name
      FROM po_vendors pv, ap_terms_tl t, ap_awt_groups awg
    WHERE pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NOT NULL
       AND t.term_id(+) = pv.terms_id
       AND NVL(pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND pv.awt_group_id = awg.GROUP_ID(+)
    UNION
    -- Employees
    SELECT pv.vendor_id, pv.vendor_name, pv.vendor_name_alt, pv.segment1,
           pv.employee_id, ppf.employee_number, pv.vendor_type_lookup_code,
           pv.customer_num, pv.one_time_flag, pv.terms_id, t.NAME terms_name,
           pv.set_of_books_id, pv.pay_date_basis_lookup_code,
           pv.pay_group_lookup_code, pv.payment_priority,
           pv.invoice_currency_code, pv.payment_currency_code,
           pv.hold_all_payments_flag, pv.hold_reason, pv.num_1099, pv.type_1099,
           pv.withholding_status_lookup_code, pv.withholding_start_date,
           pv.organization_type_lookup_code, pv.start_date_active,
           pv.end_date_active, pv.payment_method_lookup_code, pv.terms_date_basis,
           pv.receipt_required_flag, pv.qty_rcv_tolerance,
           pv.qty_rcv_exception_code, pv.enforce_ship_to_location_code,
           pv.days_early_receipt_allowed, pv.days_late_receipt_allowed,
           pv.receipt_days_exception_code, pv.exclusive_payment_flag,
           pv.auto_tax_calc_flag, pv.auto_tax_calc_override,
           pv.amount_includes_tax_flag, pv.tax_verification_date,
           pv.state_reportable_flag, pv.federal_reportable_flag,
           pv.attribute_category, pv.attribute1, pv.attribute2, pv.attribute3,
           pv.attribute4, pv.attribute5, pv.attribute6, pv.attribute7,
           pv.attribute8, pv.attribute9, pv.attribute10, pv.attribute11,
           pv.attribute12, pv.attribute13, pv.attribute14, pv.attribute15,
           pv.vat_registration_num, pv.exclude_freight_from_discount,
           pv.tax_reporting_name, pv.allow_awt_flag, pv.awt_group_id, awg.NAME awg_group_name,
           pv.match_option, pv.create_debit_memo_flag, pv.match_status_flag,
           pv.individual_1099, NULL business_group_id, bg.NAME business_group_name
      FROM po_vendors pv, ap_terms_tl t, apps.per_people_x ppf, per_person_types ppt, apps.per_assignments_x ppa,
           per_business_groups bg, ap_awt_groups awg
    WHERE pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND t.term_id(+) = pv.terms_id
       AND NVL(pv.vendor_type_lookup_code,-1) = 'EMPLOYEE'
       AND pv.employee_id = ppf.person_id
       AND ppf.person_type_id = ppt.person_type_id
       AND ppt.SYSTEM_PERSON_TYPE IN ('EMP')
       AND ppa.PERSON_ID = ppf.person_id
       AND ppa.ASSIGNMENT_TYPE = 'E'
       AND ppf.BUSINESS_GROUP_ID = bg.BUSINESS_GROUP_ID
       AND pv.awt_group_id = awg.GROUP_ID(+);

    ------------------------------------------------------------
    ------------------------------------------------------------
    /* 2. 11i Vendor Sites Extract */
    /* Save as: 11i_supplier_sites.csv */
    ------------------------------------------------------------
    ------------------------------------------------------------
    -- Oracle Vendors
    SELECT pvs.vendor_site_id, pvs.vendor_id, pvs.vendor_site_code,
           pvs.vendor_site_code_alt, pvs.purchasing_site_flag,
           pvs.rfq_only_site_flag, pvs.pay_site_flag, pvs.address_line1,
           REPLACE (pvs.address_lines_alt, CHR (10), ' ') address_lines_alt,
           pvs.address_line2, pvs.address_line3, pvs.city, pvs.state, pvs.zip,
           pvs.province, pvs.country, pvs.area_code, pvs.phone, pvs.inactive_date,
           pvs.fax, pvs.fax_area_code, pvs.telex, pvs.payment_method_lookup_code,
           pvs.terms_date_basis, pvs.pay_group_lookup_code, pvs.payment_priority,
           pvs.terms_id, t.NAME terms_name, pvs.pay_date_basis_lookup_code,
           pvs.invoice_currency_code, pvs.payment_currency_code,
           pvs.hold_all_payments_flag, pvs.hold_reason,
           pvs.hold_unmatched_invoices_flag, pvs.auto_tax_calc_flag,
           pvs.auto_tax_calc_override, pvs.amount_includes_tax_flag,
           pvs.exclusive_payment_flag, pvs.tax_reporting_site_flag,
           pvs.attribute_category, pvs.attribute1, pvs.attribute2, pvs.attribute3,
           pvs.attribute4, pvs.attribute5, pvs.attribute6, pvs.attribute7,
           pvs.attribute8, pvs.attribute9, pvs.attribute10, pvs.attribute11,
           pvs.attribute12, pvs.attribute13, pvs.attribute14, pvs.attribute15,
           pvs.exclude_freight_from_discount, pvs.vat_registration_num,
           pvs.offset_vat_code, pvs.org_id,
           (SELECT hou.NAME
              FROM hr_operating_units hou
             WHERE hou.organization_id = pvs.org_id) operating_unit,
           pvs.address_line4, pvs.county, pvs.address_style, pvs.LANGUAGE,
           pvs.allow_awt_flag, pvs.awt_group_id, pvs.match_option,
           pvs.country_of_origin_code, pvs.create_debit_memo_flag,
           pvs.supplier_notif_method, pvs.email_address, pvs.remittance_email,
           pvs.primary_pay_site_flag, pvs.tolerance_id, pvs.services_tolerance_id
      FROM po_vendors pv, po_vendor_sites_all pvs, ap_terms_tl t
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NULL
       AND NVL (pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND t.term_id(+) = pv.terms_id
       AND EXISTS (
              SELECT 1
                FROM ap_invoices_all i
               WHERE i.vendor_id = pv.vendor_id
                 AND i.invoice_date >= ADD_MONTHS (TRUNC (SYSDATE), -24))
    UNION
    -- Solomon Vendors
    SELECT pvs.vendor_site_id, pvs.vendor_id, pvs.vendor_site_code,
           pvs.vendor_site_code_alt, pvs.purchasing_site_flag,
           pvs.rfq_only_site_flag, pvs.pay_site_flag, pvs.address_line1,
           REPLACE (pvs.address_lines_alt, CHR (10), ' ') address_lines_alt,
           pvs.address_line2, pvs.address_line3, pvs.city, pvs.state, pvs.zip,
           pvs.province, pvs.country, pvs.area_code, pvs.phone, pvs.inactive_date,
           pvs.fax, pvs.fax_area_code, pvs.telex, pvs.payment_method_lookup_code,
           pvs.terms_date_basis, pvs.pay_group_lookup_code, pvs.payment_priority,
           pvs.terms_id, t.NAME terms_name, pvs.pay_date_basis_lookup_code,
           pvs.invoice_currency_code, pvs.payment_currency_code,
           pvs.hold_all_payments_flag, pvs.hold_reason,
           pvs.hold_unmatched_invoices_flag, pvs.auto_tax_calc_flag,
           pvs.auto_tax_calc_override, pvs.amount_includes_tax_flag,
           pvs.exclusive_payment_flag, pvs.tax_reporting_site_flag,
           pvs.attribute_category, pvs.attribute1, pvs.attribute2, pvs.attribute3,
           pvs.attribute4, pvs.attribute5, pvs.attribute6, pvs.attribute7,
           pvs.attribute8, pvs.attribute9, pvs.attribute10, pvs.attribute11,
           pvs.attribute12, pvs.attribute13, pvs.attribute14, pvs.attribute15,
           pvs.exclude_freight_from_discount, pvs.vat_registration_num,
           pvs.offset_vat_code, pvs.org_id,
           (SELECT hou.NAME
              FROM hr_operating_units hou
             WHERE hou.organization_id = pvs.org_id) operating_unit,
           pvs.address_line4, pvs.county, pvs.address_style, pvs.LANGUAGE,
           pvs.allow_awt_flag, pvs.awt_group_id, pvs.match_option,
           pvs.country_of_origin_code, pvs.create_debit_memo_flag,
           pvs.supplier_notif_method, pvs.email_address, pvs.remittance_email,
           pvs.primary_pay_site_flag, pvs.tolerance_id, pvs.services_tolerance_id
      FROM po_vendors pv, po_vendor_sites_all pvs, ap_terms_tl t
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NOT NULL
       AND NVL (pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND t.term_id(+) = pv.terms_id
    UNION
    -- Employees
    SELECT pvs.vendor_site_id, pvs.vendor_id, pvs.vendor_site_code,
           pvs.vendor_site_code_alt, pvs.purchasing_site_flag,
           pvs.rfq_only_site_flag, pvs.pay_site_flag, pvs.address_line1,
           REPLACE (pvs.address_lines_alt, CHR (10), ' ') address_lines_alt,
           pvs.address_line2, pvs.address_line3, pvs.city, pvs.state, pvs.zip,
           pvs.province, pvs.country, pvs.area_code, pvs.phone, pvs.inactive_date,
           pvs.fax, pvs.fax_area_code, pvs.telex, pvs.payment_method_lookup_code,
           pvs.terms_date_basis, pvs.pay_group_lookup_code, pvs.payment_priority,
           pvs.terms_id, t.NAME terms_name, pvs.pay_date_basis_lookup_code,
           pvs.invoice_currency_code, pvs.payment_currency_code,
           pvs.hold_all_payments_flag, pvs.hold_reason,
           pvs.hold_unmatched_invoices_flag, pvs.auto_tax_calc_flag,
           pvs.auto_tax_calc_override, pvs.amount_includes_tax_flag,
           pvs.exclusive_payment_flag, pvs.tax_reporting_site_flag,
           pvs.attribute_category, pvs.attribute1, pvs.attribute2, pvs.attribute3,
           pvs.attribute4, pvs.attribute5, pvs.attribute6, pvs.attribute7,
           pvs.attribute8, pvs.attribute9, pvs.attribute10, pvs.attribute11,
           pvs.attribute12, pvs.attribute13, pvs.attribute14, pvs.attribute15,
           pvs.exclude_freight_from_discount, pvs.vat_registration_num,
           pvs.offset_vat_code, pvs.org_id,
           (SELECT hou.NAME
              FROM hr_operating_units hou
             WHERE hou.organization_id = pvs.org_id) operating_unit,
           pvs.address_line4, pvs.county, pvs.address_style, pvs.LANGUAGE,
           pvs.allow_awt_flag, pvs.awt_group_id, pvs.match_option,
           pvs.country_of_origin_code, pvs.create_debit_memo_flag,
           pvs.supplier_notif_method, pvs.email_address, pvs.remittance_email,
           pvs.primary_pay_site_flag, pvs.tolerance_id, pvs.services_tolerance_id
      FROM po_vendors pv,
           po_vendor_sites_all pvs,
           ap_terms_tl t,
           apps.per_people_x ppf,
           per_person_types ppt,
           apps.per_assignments_x ppa
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND t.term_id(+) = pv.terms_id
       AND NVL (pv.vendor_type_lookup_code, -1) = 'EMPLOYEE'
       AND pv.employee_id = ppf.person_id
       AND ppf.person_type_id = ppt.person_type_id
       AND ppt.system_person_type IN ('EMP')
       AND ppa.person_id = ppf.person_id
       AND ppa.assignment_type = 'E';
     

    ------------------------------------------------------------
    ------------------------------------------------------------
    /* 3. 11i Vendor Site Contacts Extract */
    /* Save as: 11i_supplier_site_contacts.csv */
    ------------------------------------------------------------
    ------------------------------------------------------------
    -- Oracle Vendors
    SELECT pvc.vendor_contact_id, pvc.vendor_site_id, pvc.inactive_date,
           pvc.first_name, pvc.middle_name, pvc.last_name, pvc.prefix, pvc.title,
           pvc.area_code, pvc.phone, pvc.contact_name_alt, pvc.first_name_alt,
           pvc.last_name_alt, pvc.department, pvc.email_address, pvc.url,
           pvc.alt_area_code, pvc.alt_phone, pvc.fax_area_code, pvc.fax
      FROM po_vendors pv, po_vendor_sites_all pvs, po_vendor_contacts pvc
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NULL
       AND NVL (pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND pvs.vendor_site_id = pvc.vendor_site_id
       AND NVL (pvc.inactive_date, SYSDATE) >= SYSDATE
       AND EXISTS (
              SELECT 1
                FROM ap_invoices_all i
               WHERE i.vendor_id = pv.vendor_id
                 AND i.invoice_date >= ADD_MONTHS (TRUNC (SYSDATE), -24))
    UNION
    -- Solomon Vendors
    SELECT pvc.vendor_contact_id, pvc.vendor_site_id, pvc.inactive_date,
           pvc.first_name, pvc.middle_name, pvc.last_name, pvc.prefix, pvc.title,
           pvc.area_code, pvc.phone, pvc.contact_name_alt, pvc.first_name_alt,
           pvc.last_name_alt, pvc.department, pvc.email_address, pvc.url,
           pvc.alt_area_code, pvc.alt_phone, pvc.fax_area_code, pvc.fax
      FROM po_vendors pv, po_vendor_sites_all pvs, po_vendor_contacts pvc
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NOT NULL
       AND NVL (pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND pvs.vendor_site_id = pvc.vendor_site_id
       AND NVL (pvc.inactive_date, SYSDATE) >= SYSDATE
    UNION
    -- Employees
    SELECT pvc.vendor_contact_id, pvc.vendor_site_id, pvc.inactive_date,
           pvc.first_name, pvc.middle_name, pvc.last_name, pvc.prefix, pvc.title,
           pvc.area_code, pvc.phone, pvc.contact_name_alt, pvc.first_name_alt,
           pvc.last_name_alt, pvc.department, pvc.email_address, pvc.url,
           pvc.alt_area_code, pvc.alt_phone, pvc.fax_area_code, pvc.fax
      FROM po_vendors pv,
           po_vendor_sites_all pvs,
           apps.per_people_x ppf,
           per_person_types ppt,
           apps.per_assignments_x ppa,
           po_vendor_contacts pvc
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND NVL (pv.vendor_type_lookup_code, -1) = 'EMPLOYEE'
       AND pv.employee_id = ppf.person_id
       AND ppf.person_type_id = ppt.person_type_id
       AND ppt.system_person_type IN ('EMP')
       AND ppa.person_id = ppf.person_id
       AND ppa.assignment_type = 'E'
       AND pvs.vendor_site_id = pvc.vendor_site_id
       AND NVL (pvc.inactive_date, SYSDATE) >= SYSDATE;

    ------------------------------------------------------------
    ------------------------------------------------------------
    /* 4. 11i Bank Branches Extract - Run from DEV */
    /* Save as: 11i_bank_branches.csv */
    ------------------------------------------------------------
    ------------------------------------------------------------
    -- Bank branches without Dups            
    SELECT * FROM
    (
    SELECT abb.bank_branch_id, mdrx_get_bank_branch_id (abb.bank_branch_id) bank_branch_id_new,
           abb.bank_name, abb.bank_branch_name,
           REPLACE (abb.description, CHR (10), ' ') description,
           abb.address_line1, abb.address_line2, abb.address_line3, abb.city,
           abb.state, abb.zip, abb.province, abb.country, abb.area_code,
           abb.phone, abb.contact_first_name, abb.contact_middle_name,
           abb.contact_last_name, abb.contact_prefix, abb.contact_title,
           abb.bank_num, abb.institution_type, abb.address_style, abb.bank_number,
           abb.address_line4, abb.county, abb.eft_user_number, abb.eft_swift_code,
           abb.end_date, abb.edi_id_number, abb.bank_branch_type,
           abb.bank_name_alt, abb.bank_branch_name_alt, abb.address_lines_alt,
           abb.active_date, abb.tp_header_id, abb.ece_tp_location_code,
           abb.rfc_identifier, abb.bank_admin_email
      FROM ap_bank_branches abb                                             --1723
    WHERE NVL (abb.end_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
       AND EXISTS (
              SELECT *
                FROM ap_bank_accounts_all aba
               WHERE NVL (aba.inactive_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
                 AND aba.bank_branch_id = abb.bank_branch_id)
    ) a
    WHERE a.bank_branch_id = a.bank_branch_id_new;            

    ------------------------------------------------------------
    ------------------------------------------------------------
    /* 5. 11i Bank Account Extract - Run from DEV */
    /* Save as: 11i_bank_accounts.csv */
    ------------------------------------------------------------
    ------------------------------------------------------------
    SELECT aba.bank_account_id, aba.bank_account_name, aba.bank_account_num,
           --aba.bank_branch_id,
           bb.bank_num,
           mdrx_get_bank_branch_id (aba.bank_branch_id) bank_branch_id,
           (SELECT hou.NAME
              FROM hr_operating_units hou
             WHERE hou.organization_id = aba.org_id) operating_unit_name,
           aba.set_of_books_id, aba.currency_code, aba.description,
           aba.contact_first_name, aba.contact_middle_name, aba.contact_last_name,
           aba.contact_prefix, aba.contact_title, aba.contact_area_code,
           aba.contact_phone, aba.bank_account_type, aba.attribute_category,
           aba.attribute1, aba.check_digits, aba.org_id,
           aba.bank_account_name_alt, aba.account_holder_name,
           aba.account_holder_name_alt, aba.allow_multi_assignments_flag,
           aba.iban_number, abau.vendor_id, abau.vendor_site_id,
           abau.primary_flag, abau.start_date, abau.end_date, pv.segment1,
           pvs.vendor_site_code
      FROM ap_bank_accounts_all aba,
           ap_bank_account_uses_all abau,
           po_vendors pv,
           po_vendor_sites_all pvs,
           ap_bank_branches bb
    WHERE NVL (aba.inactive_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
       AND aba.bank_account_id = abau.external_bank_account_id
       AND TRUNC (SYSDATE) BETWEEN NVL (abau.start_date, TRUNC (SYSDATE))
                               AND NVL (abau.end_date, TRUNC (SYSDATE))
       AND abau.vendor_id = pv.vendor_id
       AND abau.vendor_site_id = pvs.vendor_site_id(+)
       AND aba.bank_branch_id = bb.bank_branch_id
       --AND bb.bank_num = '053000219'
       ORDER BY bank_branch_id;
    Code (SQL):
    ------------------------------------------------------------
    ------------------------------------------------------------
    /* 1. 11i Vendors Extract */
    /* Save as: 11i_suppliers.csv */
    ------------------------------------------------------------
    ------------------------------------------------------------
    SELECT pv.vendor_id, pv.vendor_name, pv.vendor_name_alt, pv.segment1,
           pv.employee_id, NULL employee_number, pv.vendor_type_lookup_code,
           pv.customer_num, pv.one_time_flag, pv.terms_id, t.NAME terms_name,
           pv.set_of_books_id, pv.pay_date_basis_lookup_code,
           pv.pay_group_lookup_code, pv.payment_priority,
           pv.invoice_currency_code, pv.payment_currency_code,
           pv.hold_all_payments_flag, pv.hold_reason, pv.num_1099, pv.type_1099,
           pv.withholding_status_lookup_code, pv.withholding_start_date,
           pv.organization_type_lookup_code, pv.start_date_active,
           pv.end_date_active, pv.payment_method_lookup_code, pv.terms_date_basis,
           pv.receipt_required_flag, pv.qty_rcv_tolerance,
           pv.qty_rcv_exception_code, pv.enforce_ship_to_location_code,
           pv.days_early_receipt_allowed, pv.days_late_receipt_allowed,
           pv.receipt_days_exception_code, pv.exclusive_payment_flag,
           pv.auto_tax_calc_flag, pv.auto_tax_calc_override,
           pv.amount_includes_tax_flag, pv.tax_verification_date,
           pv.state_reportable_flag, pv.federal_reportable_flag,
           pv.attribute_category, pv.attribute1, pv.attribute2, pv.attribute3,
           pv.attribute4, pv.attribute5, pv.attribute6, pv.attribute7,
           pv.attribute8, pv.attribute9, pv.attribute10, pv.attribute11,
           pv.attribute12, pv.attribute13, pv.attribute14, pv.attribute15,
           pv.vat_registration_num, pv.exclude_freight_from_discount,
           pv.tax_reporting_name, pv.allow_awt_flag, pv.awt_group_id,
           awg.NAME awg_group_name, pv.match_option, pv.create_debit_memo_flag,
           pv.match_status_flag, pv.individual_1099, NULL business_group_id,
           NULL business_group_name
      FROM po_vendors pv, ap_terms_tl t, ap_awt_groups awg
    WHERE pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NULL
       AND t.term_id(+) = pv.terms_id
       AND NVL(pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND pv.awt_group_id = awg.GROUP_ID(+)
       AND EXISTS (
              SELECT 1
                FROM ap_invoices_all i
               WHERE i.vendor_id = pv.vendor_id
                 AND i.invoice_date >= ADD_MONTHS (TRUNC (SYSDATE), -24))
    UNION
    -- Solomon vendors
    SELECT pv.vendor_id, pv.vendor_name, pv.vendor_name_alt, pv.segment1,
           pv.employee_id, NULL employee_number, pv.vendor_type_lookup_code,
           pv.customer_num, pv.one_time_flag, pv.terms_id, t.NAME terms_name,
           pv.set_of_books_id, pv.pay_date_basis_lookup_code,
           pv.pay_group_lookup_code, pv.payment_priority,
           pv.invoice_currency_code, pv.payment_currency_code,
           pv.hold_all_payments_flag, pv.hold_reason, pv.num_1099, pv.type_1099,
           pv.withholding_status_lookup_code, pv.withholding_start_date,
           pv.organization_type_lookup_code, pv.start_date_active,
           pv.end_date_active, pv.payment_method_lookup_code, pv.terms_date_basis,
           pv.receipt_required_flag, pv.qty_rcv_tolerance,
           pv.qty_rcv_exception_code, pv.enforce_ship_to_location_code,
           pv.days_early_receipt_allowed, pv.days_late_receipt_allowed,
           pv.receipt_days_exception_code, pv.exclusive_payment_flag,
           pv.auto_tax_calc_flag, pv.auto_tax_calc_override,
           pv.amount_includes_tax_flag, pv.tax_verification_date,
           pv.state_reportable_flag, pv.federal_reportable_flag,
           pv.attribute_category, pv.attribute1, pv.attribute2, pv.attribute3,
           pv.attribute4, pv.attribute5, pv.attribute6, pv.attribute7,
           pv.attribute8, pv.attribute9, pv.attribute10, pv.attribute11,
           pv.attribute12, pv.attribute13, pv.attribute14, pv.attribute15,
           pv.vat_registration_num, pv.exclude_freight_from_discount,
           pv.tax_reporting_name, pv.allow_awt_flag, pv.awt_group_id, awg.NAME awg_group_name,
           pv.match_option, pv.create_debit_memo_flag, pv.match_status_flag,
           pv.individual_1099, NULL business_group_id, NULL business_group_name
      FROM po_vendors pv, ap_terms_tl t, ap_awt_groups awg
    WHERE pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NOT NULL
       AND t.term_id(+) = pv.terms_id
       AND NVL(pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND pv.awt_group_id = awg.GROUP_ID(+)
    UNION
    -- Employees
    SELECT pv.vendor_id, pv.vendor_name, pv.vendor_name_alt, pv.segment1,
           pv.employee_id, ppf.employee_number, pv.vendor_type_lookup_code,
           pv.customer_num, pv.one_time_flag, pv.terms_id, t.NAME terms_name,
           pv.set_of_books_id, pv.pay_date_basis_lookup_code,
           pv.pay_group_lookup_code, pv.payment_priority,
           pv.invoice_currency_code, pv.payment_currency_code,
           pv.hold_all_payments_flag, pv.hold_reason, pv.num_1099, pv.type_1099,
           pv.withholding_status_lookup_code, pv.withholding_start_date,
           pv.organization_type_lookup_code, pv.start_date_active,
           pv.end_date_active, pv.payment_method_lookup_code, pv.terms_date_basis,
           pv.receipt_required_flag, pv.qty_rcv_tolerance,
           pv.qty_rcv_exception_code, pv.enforce_ship_to_location_code,
           pv.days_early_receipt_allowed, pv.days_late_receipt_allowed,
           pv.receipt_days_exception_code, pv.exclusive_payment_flag,
           pv.auto_tax_calc_flag, pv.auto_tax_calc_override,
           pv.amount_includes_tax_flag, pv.tax_verification_date,
           pv.state_reportable_flag, pv.federal_reportable_flag,
           pv.attribute_category, pv.attribute1, pv.attribute2, pv.attribute3,
           pv.attribute4, pv.attribute5, pv.attribute6, pv.attribute7,
           pv.attribute8, pv.attribute9, pv.attribute10, pv.attribute11,
           pv.attribute12, pv.attribute13, pv.attribute14, pv.attribute15,
           pv.vat_registration_num, pv.exclude_freight_from_discount,
           pv.tax_reporting_name, pv.allow_awt_flag, pv.awt_group_id, awg.NAME awg_group_name,
           pv.match_option, pv.create_debit_memo_flag, pv.match_status_flag,
           pv.individual_1099, NULL business_group_id, bg.NAME business_group_name
      FROM po_vendors pv, ap_terms_tl t, apps.per_people_x ppf, per_person_types ppt, apps.per_assignments_x ppa,
           per_business_groups bg, ap_awt_groups awg
    WHERE pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND t.term_id(+) = pv.terms_id
       AND NVL(pv.vendor_type_lookup_code,-1) = 'EMPLOYEE'
       AND pv.employee_id = ppf.person_id
       AND ppf.person_type_id = ppt.person_type_id
       AND ppt.SYSTEM_PERSON_TYPE IN ('EMP')
       AND ppa.PERSON_ID = ppf.person_id
       AND ppa.ASSIGNMENT_TYPE = 'E'
       AND ppf.BUSINESS_GROUP_ID = bg.BUSINESS_GROUP_ID
       AND pv.awt_group_id = awg.GROUP_ID(+);

    ------------------------------------------------------------
    ------------------------------------------------------------
    /* 2. 11i Vendor Sites Extract */
    /* Save as: 11i_supplier_sites.csv */
    ------------------------------------------------------------
    ------------------------------------------------------------
    -- Oracle Vendors
    SELECT pvs.vendor_site_id, pvs.vendor_id, pvs.vendor_site_code,
           pvs.vendor_site_code_alt, pvs.purchasing_site_flag,
           pvs.rfq_only_site_flag, pvs.pay_site_flag, pvs.address_line1,
           REPLACE (pvs.address_lines_alt, CHR (10), ' ') address_lines_alt,
           pvs.address_line2, pvs.address_line3, pvs.city, pvs.state, pvs.zip,
           pvs.province, pvs.country, pvs.area_code, pvs.phone, pvs.inactive_date,
           pvs.fax, pvs.fax_area_code, pvs.telex, pvs.payment_method_lookup_code,
           pvs.terms_date_basis, pvs.pay_group_lookup_code, pvs.payment_priority,
           pvs.terms_id, t.NAME terms_name, pvs.pay_date_basis_lookup_code,
           pvs.invoice_currency_code, pvs.payment_currency_code,
           pvs.hold_all_payments_flag, pvs.hold_reason,
           pvs.hold_unmatched_invoices_flag, pvs.auto_tax_calc_flag,
           pvs.auto_tax_calc_override, pvs.amount_includes_tax_flag,
           pvs.exclusive_payment_flag, pvs.tax_reporting_site_flag,
           pvs.attribute_category, pvs.attribute1, pvs.attribute2, pvs.attribute3,
           pvs.attribute4, pvs.attribute5, pvs.attribute6, pvs.attribute7,
           pvs.attribute8, pvs.attribute9, pvs.attribute10, pvs.attribute11,
           pvs.attribute12, pvs.attribute13, pvs.attribute14, pvs.attribute15,
           pvs.exclude_freight_from_discount, pvs.vat_registration_num,
           pvs.offset_vat_code, pvs.org_id,
           (SELECT hou.NAME
              FROM hr_operating_units hou
             WHERE hou.organization_id = pvs.org_id) operating_unit,
           pvs.address_line4, pvs.county, pvs.address_style, pvs.LANGUAGE,
           pvs.allow_awt_flag, pvs.awt_group_id, pvs.match_option,
           pvs.country_of_origin_code, pvs.create_debit_memo_flag,
           pvs.supplier_notif_method, pvs.email_address, pvs.remittance_email,
           pvs.primary_pay_site_flag, pvs.tolerance_id, pvs.services_tolerance_id
      FROM po_vendors pv, po_vendor_sites_all pvs, ap_terms_tl t
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NULL
       AND NVL (pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND t.term_id(+) = pv.terms_id
       AND EXISTS (
              SELECT 1
                FROM ap_invoices_all i
               WHERE i.vendor_id = pv.vendor_id
                 AND i.invoice_date >= ADD_MONTHS (TRUNC (SYSDATE), -24))
    UNION
    -- Solomon Vendors
    SELECT pvs.vendor_site_id, pvs.vendor_id, pvs.vendor_site_code,
           pvs.vendor_site_code_alt, pvs.purchasing_site_flag,
           pvs.rfq_only_site_flag, pvs.pay_site_flag, pvs.address_line1,
           REPLACE (pvs.address_lines_alt, CHR (10), ' ') address_lines_alt,
           pvs.address_line2, pvs.address_line3, pvs.city, pvs.state, pvs.zip,
           pvs.province, pvs.country, pvs.area_code, pvs.phone, pvs.inactive_date,
           pvs.fax, pvs.fax_area_code, pvs.telex, pvs.payment_method_lookup_code,
           pvs.terms_date_basis, pvs.pay_group_lookup_code, pvs.payment_priority,
           pvs.terms_id, t.NAME terms_name, pvs.pay_date_basis_lookup_code,
           pvs.invoice_currency_code, pvs.payment_currency_code,
           pvs.hold_all_payments_flag, pvs.hold_reason,
           pvs.hold_unmatched_invoices_flag, pvs.auto_tax_calc_flag,
           pvs.auto_tax_calc_override, pvs.amount_includes_tax_flag,
           pvs.exclusive_payment_flag, pvs.tax_reporting_site_flag,
           pvs.attribute_category, pvs.attribute1, pvs.attribute2, pvs.attribute3,
           pvs.attribute4, pvs.attribute5, pvs.attribute6, pvs.attribute7,
           pvs.attribute8, pvs.attribute9, pvs.attribute10, pvs.attribute11,
           pvs.attribute12, pvs.attribute13, pvs.attribute14, pvs.attribute15,
           pvs.exclude_freight_from_discount, pvs.vat_registration_num,
           pvs.offset_vat_code, pvs.org_id,
           (SELECT hou.NAME
              FROM hr_operating_units hou
             WHERE hou.organization_id = pvs.org_id) operating_unit,
           pvs.address_line4, pvs.county, pvs.address_style, pvs.LANGUAGE,
           pvs.allow_awt_flag, pvs.awt_group_id, pvs.match_option,
           pvs.country_of_origin_code, pvs.create_debit_memo_flag,
           pvs.supplier_notif_method, pvs.email_address, pvs.remittance_email,
           pvs.primary_pay_site_flag, pvs.tolerance_id, pvs.services_tolerance_id
      FROM po_vendors pv, po_vendor_sites_all pvs, ap_terms_tl t
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NOT NULL
       AND NVL (pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND t.term_id(+) = pv.terms_id
    UNION
    -- Employees
    SELECT pvs.vendor_site_id, pvs.vendor_id, pvs.vendor_site_code,
           pvs.vendor_site_code_alt, pvs.purchasing_site_flag,
           pvs.rfq_only_site_flag, pvs.pay_site_flag, pvs.address_line1,
           REPLACE (pvs.address_lines_alt, CHR (10), ' ') address_lines_alt,
           pvs.address_line2, pvs.address_line3, pvs.city, pvs.state, pvs.zip,
           pvs.province, pvs.country, pvs.area_code, pvs.phone, pvs.inactive_date,
           pvs.fax, pvs.fax_area_code, pvs.telex, pvs.payment_method_lookup_code,
           pvs.terms_date_basis, pvs.pay_group_lookup_code, pvs.payment_priority,
           pvs.terms_id, t.NAME terms_name, pvs.pay_date_basis_lookup_code,
           pvs.invoice_currency_code, pvs.payment_currency_code,
           pvs.hold_all_payments_flag, pvs.hold_reason,
           pvs.hold_unmatched_invoices_flag, pvs.auto_tax_calc_flag,
           pvs.auto_tax_calc_override, pvs.amount_includes_tax_flag,
           pvs.exclusive_payment_flag, pvs.tax_reporting_site_flag,
           pvs.attribute_category, pvs.attribute1, pvs.attribute2, pvs.attribute3,
           pvs.attribute4, pvs.attribute5, pvs.attribute6, pvs.attribute7,
           pvs.attribute8, pvs.attribute9, pvs.attribute10, pvs.attribute11,
           pvs.attribute12, pvs.attribute13, pvs.attribute14, pvs.attribute15,
           pvs.exclude_freight_from_discount, pvs.vat_registration_num,
           pvs.offset_vat_code, pvs.org_id,
           (SELECT hou.NAME
              FROM hr_operating_units hou
             WHERE hou.organization_id = pvs.org_id) operating_unit,
           pvs.address_line4, pvs.county, pvs.address_style, pvs.LANGUAGE,
           pvs.allow_awt_flag, pvs.awt_group_id, pvs.match_option,
           pvs.country_of_origin_code, pvs.create_debit_memo_flag,
           pvs.supplier_notif_method, pvs.email_address, pvs.remittance_email,
           pvs.primary_pay_site_flag, pvs.tolerance_id, pvs.services_tolerance_id
      FROM po_vendors pv,
           po_vendor_sites_all pvs,
           ap_terms_tl t,
           apps.per_people_x ppf,
           per_person_types ppt,
           apps.per_assignments_x ppa
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND t.term_id(+) = pv.terms_id
       AND NVL (pv.vendor_type_lookup_code, -1) = 'EMPLOYEE'
       AND pv.employee_id = ppf.person_id
       AND ppf.person_type_id = ppt.person_type_id
       AND ppt.system_person_type IN ('EMP')
       AND ppa.person_id = ppf.person_id
       AND ppa.assignment_type = 'E';
     

    ------------------------------------------------------------
    ------------------------------------------------------------
    /* 3. 11i Vendor Site Contacts Extract */
    /* Save as: 11i_supplier_site_contacts.csv */
    ------------------------------------------------------------
    ------------------------------------------------------------
    -- Oracle Vendors
    SELECT pvc.vendor_contact_id, pvc.vendor_site_id, pvc.inactive_date,
           pvc.first_name, pvc.middle_name, pvc.last_name, pvc.prefix, pvc.title,
           pvc.area_code, pvc.phone, pvc.contact_name_alt, pvc.first_name_alt,
           pvc.last_name_alt, pvc.department, pvc.email_address, pvc.url,
           pvc.alt_area_code, pvc.alt_phone, pvc.fax_area_code, pvc.fax
      FROM po_vendors pv, po_vendor_sites_all pvs, po_vendor_contacts pvc
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NULL
       AND NVL (pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND pvs.vendor_site_id = pvc.vendor_site_id
       AND NVL (pvc.inactive_date, SYSDATE) >= SYSDATE
       AND EXISTS (
              SELECT 1
                FROM ap_invoices_all i
               WHERE i.vendor_id = pv.vendor_id
                 AND i.invoice_date >= ADD_MONTHS (TRUNC (SYSDATE), -24))
    UNION
    -- Solomon Vendors
    SELECT pvc.vendor_contact_id, pvc.vendor_site_id, pvc.inactive_date,
           pvc.first_name, pvc.middle_name, pvc.last_name, pvc.prefix, pvc.title,
           pvc.area_code, pvc.phone, pvc.contact_name_alt, pvc.first_name_alt,
           pvc.last_name_alt, pvc.department, pvc.email_address, pvc.url,
           pvc.alt_area_code, pvc.alt_phone, pvc.fax_area_code, pvc.fax
      FROM po_vendors pv, po_vendor_sites_all pvs, po_vendor_contacts pvc
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NOT NULL
       AND NVL (pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND pvs.vendor_site_id = pvc.vendor_site_id
       AND NVL (pvc.inactive_date, SYSDATE) >= SYSDATE
    UNION
    -- Employees
    SELECT pvc.vendor_contact_id, pvc.vendor_site_id, pvc.inactive_date,
           pvc.first_name, pvc.middle_name, pvc.last_name, pvc.prefix, pvc.title,
           pvc.area_code, pvc.phone, pvc.contact_name_alt, pvc.first_name_alt,
           pvc.last_name_alt, pvc.department, pvc.email_address, pvc.url,
           pvc.alt_area_code, pvc.alt_phone, pvc.fax_area_code, pvc.fax
      FROM po_vendors pv,
           po_vendor_sites_all pvs,
           apps.per_people_x ppf,
           per_person_types ppt,
           apps.per_assignments_x ppa,
           po_vendor_contacts pvc
    WHERE NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE)
                       AND NVL (pv.end_date_active, SYSDATE)
       AND NVL (pv.vendor_type_lookup_code, -1) = 'EMPLOYEE'
       AND pv.employee_id = ppf.person_id
       AND ppf.person_type_id = ppt.person_type_id
       AND ppt.system_person_type IN ('EMP')
       AND ppa.person_id = ppf.person_id
       AND ppa.assignment_type = 'E'
       AND pvs.vendor_site_id = pvc.vendor_site_id
       AND NVL (pvc.inactive_date, SYSDATE) >= SYSDATE;

    ------------------------------------------------------------
    ------------------------------------------------------------
    /* 4. 11i Bank Branches Extract - Run from DEV */
    /* Save as: 11i_bank_branches.csv */
    ------------------------------------------------------------
    ------------------------------------------------------------
    -- Bank branches without Dups            
    SELECT * FROM
    (
    SELECT abb.bank_branch_id, mdrx_get_bank_branch_id (abb.bank_branch_id) bank_branch_id_new,
           abb.bank_name, abb.bank_branch_name,
           REPLACE (abb.description, CHR (10), ' ') description,
           abb.address_line1, abb.address_line2, abb.address_line3, abb.city,
           abb.state, abb.zip, abb.province, abb.country, abb.area_code,
           abb.phone, abb.contact_first_name, abb.contact_middle_name,
           abb.contact_last_name, abb.contact_prefix, abb.contact_title,
           abb.bank_num, abb.institution_type, abb.address_style, abb.bank_number,
           abb.address_line4, abb.county, abb.eft_user_number, abb.eft_swift_code,
           abb.end_date, abb.edi_id_number, abb.bank_branch_type,
           abb.bank_name_alt, abb.bank_branch_name_alt, abb.address_lines_alt,
           abb.active_date, abb.tp_header_id, abb.ece_tp_location_code,
           abb.rfc_identifier, abb.bank_admin_email
      FROM ap_bank_branches abb                                             --1723
    WHERE NVL (abb.end_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
       AND EXISTS (
              SELECT *
                FROM ap_bank_accounts_all aba
               WHERE NVL (aba.inactive_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
                 AND aba.bank_branch_id = abb.bank_branch_id)
    ) a
    WHERE a.bank_branch_id = a.bank_branch_id_new;            

    ------------------------------------------------------------
    ------------------------------------------------------------
    /* 5. 11i Bank Account Extract - Run from DEV */
    /* Save as: 11i_bank_accounts.csv */
    ------------------------------------------------------------
    ------------------------------------------------------------
    SELECT aba.bank_account_id, aba.bank_account_name, aba.bank_account_num,
           --aba.bank_branch_id,
           bb.bank_num,
           mdrx_get_bank_branch_id (aba.bank_branch_id) bank_branch_id,
           (SELECT hou.NAME
              FROM hr_operating_units hou
             WHERE hou.organization_id = aba.org_id) operating_unit_name,
           aba.set_of_books_id, aba.currency_code, aba.description,
           aba.contact_first_name, aba.contact_middle_name, aba.contact_last_name,
           aba.contact_prefix, aba.contact_title, aba.contact_area_code,
           aba.contact_phone, aba.bank_account_type, aba.attribute_category,
           aba.attribute1, aba.check_digits, aba.org_id,
           aba.bank_account_name_alt, aba.account_holder_name,
           aba.account_holder_name_alt, aba.allow_multi_assignments_flag,
           aba.iban_number, abau.vendor_id, abau.vendor_site_id,
           abau.primary_flag, abau.start_date, abau.end_date, pv.segment1,
           pvs.vendor_site_code
      FROM ap_bank_accounts_all aba,
           ap_bank_account_uses_all abau,
           po_vendors pv,
           po_vendor_sites_all pvs,
           ap_bank_branches bb
    WHERE NVL (aba.inactive_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
       AND aba.bank_account_id = abau.external_bank_account_id
       AND TRUNC (SYSDATE) BETWEEN NVL (abau.start_date, TRUNC (SYSDATE))
                               AND NVL (abau.end_date, TRUNC (SYSDATE))
       AND abau.vendor_id = pv.vendor_id
       AND abau.vendor_site_id = pvs.vendor_site_id(+)
       AND aba.bank_branch_id = bb.bank_branch_id
       --AND bb.bank_num = '053000219'
       ORDER BY bank_branch_id;
     
  2. pavank

    pavank Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    delhi
    hiii i have a query of p2p is give a data only that have an item if not item then data is not found i want to create query that give data have item and also data of purchase order if it have no item po_number and organization_id is parameter bnd variable


    SELECT distinct invoice_num,api.invoice_id, invoice_date, invoice_type_lookup_code, vendor_name,
    vendor_site_code, api.invoice_currency_code, poh.segment1 po_number,
    poh.creation_date, poh.created_by, currency_code, msi.segment1 item,
    quantity, line_num, quantity,poh.org_id,
    pol.quantity * pol.unit_price total_amount
    FROM ap_invoices_all api,
    ap_invoice_distributions_all aida,
    po_headers_all poh,
    po_distributions_all pda,
    po_vendors pv,
    po_vendor_sites_all pvs,
    po_lines_all pol,
    mtl_system_items_b msi,
    org_organization_definitions ood
    WHERE poh.segment1 = po_number
    AND api.invoice_id = aida.invoice_id
    AND poh.po_header_id = pda.po_header_id
    AND aida.po_distribution_id = pda.po_distribution_id
    AND api.vendor_id = pv.vendor_id
    AND api.vendor_site_id = pvs.vendor_site_id
    AND poh.po_header_id = pol.po_header_id
    AND ood.operating_unit = pol.org_id
    AND ood.organization_id = organization_id
    AND msi.organization_id = ood.organization_id
    AND msi.inventory_item_id=pol.item_id