find out Supplier,Sites,Contacts Information

--find supplier info :

SELECT pov.vendor_id, pov.vendor_name supplier, pov.vendor_type_lookup_code,

sl.location_code shipto_location, bl.location_code billto_location,

pov.customer_num, pov.ship_via_lookup_code, pov.fob_lookup_code,

rt.NAME terms, pov.set_of_books_id, pov.credit_status_lookup_code,

pov.credit_limit

FROM ra_terms rt, hr_locations bl, hr_locations sl, po_vendors pov

WHERE pov.vendor_name LIKE 'xxx%'

AND pov.ship_to_location_id = sl.location_id(+)

AND pov.bill_to_location_id = bl.location_id(+)

AND pov.terms_id = rt.term_id(+)

ORDER BY 1

/

-- find Supplier sites :

SELECT pov.vendor_name supplier, povs.vendor_site_id,

povs.vendor_site_code site, povs.address_line1 a1ddress,

povs.address_line2 a2ddress, povs.address_line3 a3ddress,

povs.city || ', ' || povs.state || ' ' || povs.zip a4ddress,

povs.ship_to_location_id, povs.bill_to_location_id,

povs.ship_via_lookup_code, povs.freight_terms_lookup_code,

povs.fob_lookup_code

FROM po_vendors pov, po_vendor_sites povs

WHERE pov.vendor_id = '' AND pov.vendor_id = povs.vendor_id

ORDER BY 1

/

-- TO find Supplier contacts :

SELECT vc.vendor_contact_id, vc.vendor_site_id, vc.first_name,

vc.middle_name, vc.last_name, vc.prefix, vc.title, vc.mail_stop,

vc.area_code, vc.phone, vc.department, vc.email_address, vc.url,

vc.alt_area_code, vc.alt_phone, vc.fax_area_code, vc.inactive_date,

vc.fax

FROM po_vendor_contacts vc

WHERE vc.vendor_site_id = ''

ORDER BY 1

/

SELECT ood.organization_code org_code

, a.master_organization_id master_org_id

, o1.organization_code master_org_cd

, o1.organization_name master_org_nm

, a.cost_organization_id cost_org_id

, o2.organization_code cost_org_cd

, o2.organization_name cost_org_nm

, a.source_organization_id source_org_id

, o3.organization_code source_org_cd

, o3.organization_name source_org_nm

, mfg1.meaning primary_cost_method

, mfg2.meaning negative_balance

, mfg11.meaning gl_update_code

, a.calendar_code calendar_code

, a.default_demand_class default_demand_class

, mfg12.meaning encumbrance_reversal_flag

, mfg3.meaning locator_control

, mfg4.meaning interorg_transfer_code

, DECODE (a.maintain_fifo_qty_stack_type,

NULL, '',

mfg6.meaning

) maintain_fifo_cost

, mfg7.meaning serial_number_type

, mfg8.meaning lot_number_uniqueness

, mfg9.meaning lot_number_generation

, DECODE (a.lot_number_zero_padding,

NULL, '',

mfg10.meaning

) lot_number_zero_padding

, b.rule_name atp_rule_name

, c.picking_rule_name picking_rule_name

, a.default_locator_order_value

, a.default_subinv_order_value

, a.interorg_trnsfr_charge_percent intorg_charge_percent

, a.auto_serial_alpha_prefix

, a.start_auto_serial_number

, a.auto_lot_alpha_prefix

, a.lot_number_length

, mfg13.meaning serial_generation

, mfg14.meaning source_type

, a.source_subinventory source_subinv

FROM mtl_parameters a,

mtl_atp_rules b,

mtl_picking_rules c,

org_organization_definitions ood,

org_organization_definitions o1,

org_organization_definitions o2,

org_organization_definitions o3,

mfg_lookups mfg1,

mfg_lookups mfg2,

mfg_lookups mfg3,

mfg_lookups mfg4,

mfg_lookups mfg6,

mfg_lookups mfg7,

mfg_lookups mfg8,

mfg_lookups mfg9,

mfg_lookups mfg10,

mfg_lookups mfg11,

mfg_lookups mfg12,

mfg_lookups mfg13,

mfg_lookups mfg14

WHERE a.organization_id = 207

AND a.master_organization_id = o1.organization_id(+)

AND a.cost_organization_id = o2.organization_id(+)

AND a.source_organization_id = o3.organization_id(+)

AND a.organization_id = ood.organization_id(+)

AND a.default_atp_rule_id = b.rule_id(+)

AND a.default_picking_rule_id = c.picking_rule_id(+)

AND mfg1.lookup_type(+) = 'MTL_PRIMARY_COST'

AND a.primary_cost_method = mfg1.lookup_code(+)

AND mfg2.lookup_type(+) = 'SYS_YES_NO'

AND a.negative_inv_receipt_code = mfg2.lookup_code(+)

AND mfg3.lookup_type(+) = 'MTL_LOCATION_CONTROL'

AND a.stock_locator_control_code = mfg3.lookup_code(+)

AND mfg4.lookup_type(+) = 'MTL_INTER_INV_TRANSFER'

AND a.matl_interorg_transfer_code = mfg4.lookup_code(+)

AND mfg6.lookup_type(+) = 'SYS_YES_NO'

AND a.maintain_fifo_qty_stack_type = mfg6.lookup_code(+)

AND mfg7.lookup_type(+) = 'MTL_SERIAL_NUMBER_TYPE'

AND a.serial_number_type = mfg7.lookup_code(+)

AND mfg8.lookup_type(+) = 'MTL_LOT_UNIQUENESS'

AND a.lot_number_uniqueness = mfg8.lookup_code(+)

AND mfg9.lookup_type(+) = 'MTL_LOT_GENERATION'

AND a.lot_number_generation = mfg9.lookup_code(+)

AND mfg10.lookup_type(+) = 'SYS_YES_NO'

AND a.lot_number_zero_padding = mfg10.lookup_code(+)

AND mfg11.lookup_type(+) = 'SYS_YES_NO'

AND a.general_ledger_update_code = mfg11.lookup_code(+)

AND mfg12.lookup_type(+) = 'SYS_YES_NO'

AND a.encumbrance_reversal_flag = mfg12.lookup_code(+)

AND mfg13.lookup_type(+) = 'MTL_SERIAL_GENERATION'

AND a.serial_number_generation = mfg13.lookup_code(+)

AND mfg14.lookup_type(+) = 'MTL_SOURCE_TYPES'

AND a.source_type = mfg14.lookup_code(+)

/