Saturday, July 15, 2017

Oracle Supplier Extract Queries

------------------------------------------------------------
          /* Vendors Extract Query */
------------------------------------------------------------
--> Oracle 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 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
--> Oracle 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(+);

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect