Supplier Master SQL Query in Oracle Fusion and Purchase Order Details SQL in Oracle Fusion

Supplier Master SQL Query in Oracle Fusion

SELECT PS.VENDOR_NAME,

PS.SEGMENT1 SUPPLIER_NUMBER,

NVL2(PS.TAX_REPORTING_NAME, 'Secure', NULL) TAX_REPORTING_NAME,

PS.EMPLOYEE_ID,

pvs.vendor_site_spk_id, pvs.vendor_site_id, pvs.effective_end_date,

       pvs.effective_start_date, pvs.effective_sequence,

       pvs.object_version_number, pvs.inactive_date, pvs.vendor_id,

       pvs.prc_bu_id, pvs.location_id, pvs.party_site_id,

       pvs.vendor_site_code, pvs.purchasing_site_flag, pvs.rfq_only_site_flag,

       pvs.pay_site_flag, pvs.tp_header_id, pvs.services_tolerance_id,

       pvs.tolerance_id, pvs.terms_id, pvs.exclude_freight_from_discount,

       pvs.bank_charge_bearer, pvs.pay_on_code, pvs.default_pay_site_id,

       pvs.pay_on_receipt_summary_code, pvs.ece_tp_location_code,

       pvs.pcard_site_flag, pvs.match_option, pvs.country_of_origin_code,

       pvs.create_debit_memo_flag, pvs.supplier_notif_method,

       pvs.email_address, pvs.primary_pay_site_flag, pvs.shipping_control,

       pvs.selling_company_identifier, pvs.gapless_inv_num_flag,

       pvs.retainage_rate, pvs.auto_calculate_interest_flag, pvs.hold_by,

       pvs.hold_date, pvs.hold_flag, pvs.purchasing_hold_reason,

       pvs.vendor_site_code_alt, pvs.attention_ar_flag, pvs.area_code,

       pvs.phone, pvs.customer_num, pvs.ship_via_lookup_code,

       pvs.freight_terms_lookup_code, pvs.fob_lookup_code, pvs.fax,

       pvs.fax_area_code, pvs.telex, pvs.terms_date_basis,

       pvs.pay_group_lookup_code, pvs.payment_priority,

       pvs.invoice_amount_limit, pvs.pay_date_basis_lookup_code,

       pvs.always_take_disc_flag, pvs.invoice_currency_code,

       pvs.payment_currency_code, pvs.hold_all_payments_flag,

       pvs.hold_future_payments_flag, pvs.hold_reason,

       pvs.hold_unmatched_invoices_flag, pvs.payment_hold_date,

       pvs.tax_reporting_site_flag, pvs.last_update_date, pvs.last_updated_by,

       pvs.last_update_login, pvs.creation_date, pvs.created_by,

       pvs.request_id, pvs.program_application_id, pvs.program_id,

       pvs.program_update_date, 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.global_attribute1,

       pvs.global_attribute2, pvs.global_attribute3, pvs.global_attribute4,

       pvs.global_attribute5, pvs.global_attribute6, pvs.global_attribute7,

       pvs.global_attribute8, pvs.global_attribute9, pvs.global_attribute10,

       pvs.global_attribute11, pvs.global_attribute12, pvs.global_attribute13,

       pvs.global_attribute14, pvs.global_attribute15, pvs.global_attribute16,

       pvs.global_attribute17, pvs.global_attribute18, pvs.global_attribute19,

       pvs.global_attribute20, pvs.global_attribute_category, pvs.carrier_id,

       pvs.allow_substitute_receipts_flag, pvs.allow_unordered_receipts_flag,

       pvs.enforce_ship_to_location_code, pvs.qty_rcv_exception_code,

       pvs.receipt_days_exception_code, pvs.inspection_required_flag,

       pvs.receipt_required_flag, pvs.qty_rcv_tolerance,

       pvs.days_early_receipt_allowed, pvs.days_late_receipt_allowed,

       pvs.receiving_routing_id, pvs.shipping_network_location,

       pvs.phone_country_code, pvs.phone_extension, pvs.fax_country_code,

       pvs.tax_country_code, pvs.attribute16, pvs.attribute17,

       pvs.attribute18, pvs.attribute19, pvs.attribute20, pvs.attribute_date1,

       pvs.attribute_date10, pvs.attribute_date2, pvs.attribute_date3,

       pvs.attribute_date4, pvs.attribute_date5, pvs.attribute_date6,

       pvs.attribute_date7, pvs.attribute_date8, pvs.attribute_date9,

       pvs.attribute_number1, pvs.attribute_number10, pvs.attribute_number2,

       pvs.attribute_number3, pvs.attribute_number4, pvs.attribute_number5,

       pvs.attribute_number6, pvs.attribute_number7, pvs.attribute_number8,

       pvs.attribute_number9, pvs.attribute_timestamp1,

       pvs.attribute_timestamp10, pvs.attribute_timestamp2,

       pvs.attribute_timestamp3, pvs.attribute_timestamp4,

       pvs.attribute_timestamp5, pvs.attribute_timestamp6,

       pvs.attribute_timestamp7, pvs.attribute_timestamp8,

       pvs.attribute_timestamp9, pvs.global_attribute_date1,

       pvs.global_attribute_date10, pvs.global_attribute_date2,

       pvs.global_attribute_date3, pvs.global_attribute_date4,

       pvs.global_attribute_date5, pvs.global_attribute_date6,

       pvs.global_attribute_date7, pvs.global_attribute_date8,

       pvs.global_attribute_date9, pvs.global_attribute_number1,

       pvs.global_attribute_number10, pvs.global_attribute_number2,

       pvs.global_attribute_number3, pvs.global_attribute_number4,

       pvs.global_attribute_number5, pvs.global_attribute_number6,

       pvs.global_attribute_number7, pvs.global_attribute_number8,

       pvs.global_attribute_number9, pvs.global_attribute_timestamp1,

       pvs.global_attribute_timestamp10, pvs.global_attribute_timestamp2,

       pvs.global_attribute_timestamp3, pvs.global_attribute_timestamp4,

       pvs.global_attribute_timestamp5, pvs.global_attribute_timestamp6,

       pvs.global_attribute_timestamp7, pvs.global_attribute_timestamp8,

       pvs.global_attribute_timestamp9, pvs.aging_period_days,

       pvs.aging_onset_point, pvs.consumption_advice_frequency,

       pvs.consumption_advice_summary, pvs.pay_on_use_flag,

       pvs.mode_of_transport, pvs.service_level, hzl.address1 address_line1,

       hzl.address2 address_line2, hzl.address3 address_line3,

 hzl.address4 address_line4, hzl.address_style address_style,

hzl.address_lines_phonetic address_lines_alt, hzl.city city,
hzl.state state, hzl.county county, hzl.country country,
 hzl.postal_code zip, hzl.province province,
hzl.location_language language_code, hps.duns_number_c duns_number
FROM poz_supplier_sites_all_m pvs, hz_locations hzl, hz_party_sites hps ,POZ_SUPPLIERS_V PS
WHERE hzl.location_id = pvs.location_id
AND hps.party_site_id = pvs.party_site_id
AND pvs.vendor_id=PS.VENDOR_ID

Complete Tax Setups in Oracle Fusion

 

To See Supplier contacts You can use this below table

 

POZ_ALL_CONTACTS_V

 

 

 

Purchase Order SQL query in Oracle Fusion

 

SELECT PS.VENDOR_NAME,
PS.SEGMENT1 SUPPLIER_NUMBER,
NVL2(PS.TAX_REPORTING_NAME, 'Secure', NULL) TAX_REPORTING_NAME,
pvs.vendor_site_code,
pha.PO_HEADER_ID,
pha.MODE_OF_TRANSPORT,
pha.SERVICE_LEVEL,
pha.DOCUMENT_STATUS,
pha.PRC_BU_ID,
pha.REQ_BU_ID,
pha.AGENT_ID,
pha.TYPE_LOOKUP_CODE,
pha.REVISION_NUM,
pha.REVISED_DATE,
pha.APPROVED_FLAG,
pha.APPROVED_DATE,
pha.AMOUNT_LIMIT,
pha.MIN_RELEASE_AMOUNT,
NVL2(pha.NOTE_TO_AUTHORIZER,'Secure',NULL)NOTE_TO_AUTHORIZER,
NVL2(pha.NOTE_TO_VENDOR,'Secure',NULL)NOTE_TO_VENDOR,
NVL2(pha.NOTE_TO_RECEIVER,'Secure',NULL)NOTE_TO_RECEIVER,
pla.PARENT_ITEM_ID,
pla.SUPPLIER_PARENT_ITEM,
pla.BASE_MODEL_PRICE,
pla.OPTIONS_PRICE,
pla.ITEM_ID,
pla.ITEM_REVISION,
pla.CATEGORY_ID,
pla.ITEM_DESCRIPTION,
pla.UOM_CODE,
pla.QUANTITY_COMMITTED,
pla.COMMITTED_AMOUNT,
pla.LIST_PRICE_PER_UNIT,
pla.UNIT_PRICE,
pla.QUANTITY
FROM PO_HEADERS_ALL pha ,POZ_SUPPLIERS_V PS , poz_supplier_sites_all_m pvs ,PO_LINES_ALL pla
where pha.vendor_id=ps.vendor_id
AND pvs.vendor_id=PS.VENDOR_ID
and pha.po_header_id=pla.po_header_id


To Learn OAF Online from Beginning , Please go to the OAF Tutorial