Monday 15 January 2018

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
 
 
 
 
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

3 comments:

Anonymous said...

Nice blog, I really like what you write in this blog, I also have some relevant Information about Best HR Training In Hyderabad | Hr training institute in Hyderabad! if you want more information.
Oracle Fusion HCM Online Training

Rainbow Training Institute said...

Nice post……. your article is really informative and helpful for me and other bloggers too

Spark and Scala Online Training
Spark Scala Training
Hyderabad

Mohammad Borghol said...

hi all,

anyone have a query to get Amount changed for PO "Oracle fusion",
any information please

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *