Oracle eBusiness R12 Supplier Query

SELECT aps.vendor_name                  SUPPLIER_NAME
,      aps.segment1                     SUPPLIER_NUM
,      aps.national_insurance_number    NI_NUMBER
,      aps.company_registration_number  COMPANY_REG_NUM
,      aps.allow_awt_flag               SUPP_ALLOW_WHT
,      aps.unique_tax_reference_num     SUB_CONT_CERT_NUM
,      aps.vendor_type_lookup_code      TYPE
,      haou.name                        ORG_NAME
,      apss.vendor_site_code            SITE_CODE
,      apss.allow_awt_flag              SUBCONTRACTOR
,      apss.email_address               EMAIL
,      iep.remit_advice_email           REMIT_EMAIL
,      apss.attribute5                  PAYEE_NAME
,      apss.pay_site_flag               PAY_SITE
,      ieppm.payment_method_code        PAY_METHOD
,      apss.hold_all_payments_flag      HOLD_PAYMT
,      apss.attribute6                  INVESTIGATE_FLAG
,      hp.party_name                    BANK_NAME
,      hop.organization_name            BANK_BRANCH_NAME
,      hop.bank_or_branch_number        BRANCH_SORT_CODE
,      ieba.masked_bank_account_num     BANK_ACCT
,      apss.payment_currency_code       PAYMENT_CURR
,      apss.purchasing_site_flag        PURCH_SITE
,      apss.invoice_currency_code       INVOICE_CURR
,      apss.allow_awt_flag              SITE_ALLOW_WHT
,      apss.pay_group_lookup_code       PAY_GROUP
,      apt.name                         PAYMENT_TERM
,      apss.pay_date_basis_lookup_code  PAY_DATE_BASIS
,      hl_bill.location_code            BILL_TO_ADDRESS
,      hl_ship.location_code            SHIP_TO_ADDRESS
,      apss.supplier_notif_method       NOTIF_METHOD
,      zr.registration_status_code      TAX_REG_STATUS
,      zr.tax                           TAX
,      zr.tax_regime_code               TAX_REGIME_CODE
,      zr.registration_number           TAX_REG_NUMBER
,      gcc.concatenated_segments        PAY_ACCT
,      gccp.concatenated_segments       PREPAY_ACCT
FROM   ap_suppliers                 aps
,      ap_supplier_sites_all        apss
,      hr_all_organization_units    haou
,      ap_terms                     apt
,      hr_locations                 hl_bill
,      hr_locations                 hl_ship
,      gl_code_combinations_kfv     gcc
,      gl_code_combinations_kfv     gccp
,      zx_party_tax_profile         zptp
,      zx_registrations             zr
,      iby_ext_bank_accounts        ieba
,      iby_external_payees_all      iep
,      iby_pmt_instr_uses_all       ipiu
,      iby_ext_party_pmt_mthds      ieppm
,      hz_parties                   hp
,      hz_organization_profiles     hop
WHERE  1 = 1
AND    aps.enabled_flag = 'Y'
AND    aps.vendor_type_lookup_code != 'EMPLOYEE'
AND    aps.vendor_id = apss.vendor_id
AND    apss.org_id = haou.organization_id
AND    haou.date_from < SYSDATE
AND    NVL(apss.inactive_date,SYSDATE + 1) > SYSDATE
AND    apt.term_id(+) = apss.terms_id
AND    apt.start_date_active(+) < SYSDATE
AND    apss.bill_to_location_id = hl_bill.location_id(+)
AND    apss.ship_to_location_id = hl_ship.location_id(+)
AND    NVL(hl_bill.inactive_date(+),SYSDATE + 1) > SYSDATE
AND    NVL(hl_ship.inactive_date(+),SYSDATE + 1) > SYSDATE
AND    gcc.code_combination_id = apss.accts_pay_code_combination_id
AND    gccp.code_combination_id = apss.prepay_code_combination_id
AND    NVL(gcc.start_date_active,SYSDATE - 1) < SYSDATE
AND    NVL(gccp.start_date_active,SYSDATE - 1) < SYSDATE
AND    zptp.party_id(+) = apss.party_site_id
AND    zptp.party_tax_profile_id = zr.party_tax_profile_id(+)
AND    NVL(zr.effective_from(+),SYSDATE - 1) < SYSDATE
AND    ipiu.instrument_id=ieba.ext_bank_account_id(+)
AND    NVL(ieba.start_date(+),SYSDATE - 1) < SYSDATE
AND    NVL(iep.inactive_date(+),SYSDATE + 1) > SYSDATE
AND    ipiu.ext_pmt_party_id(+)=iep.ext_payee_id
AND    ipiu.instrument_type(+)='BANKACCOUNT'
AND    ipiu.payment_flow(+)='DISBURSEMENTS'
AND    NVL(ipiu.start_date(+),SYSDATE - 1) < SYSDATE
AND    ieppm.ext_pmt_party_id(+) = ipiu.ext_pmt_party_id
AND    NVL(ieppm.inactive_date(+),SYSDATE + 1) > SYSDATE
AND    apss.vendor_site_id = iep.supplier_site_id(+)
AND    hp.party_type(+) = 'ORGANIZATION'
AND    hop.party_id(+) = ieba.branch_id
AND    hp.party_id(+) = ieba.bank_id
AND    NVL(hop.effective_start_date(+),SYSDATE - 1) < SYSDATE
AND    zptp.party_type_code(+) = 'THIRD_PARTY_SITE'
ORDER BY 1,8,9
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s