Skip to main content

Query to find the Supplier and Supplier Site Information

SELECT *
FROM   (SELECT PoVendors.vendor_id
               AS
                      SUPPLIER_ID,
               PoVendorSitesAll.vendor_site_id
               AS
                      SUPPLIER_SITE_ID,
               PoVendorContacts.vendor_contact_id
               AS
                      SUPPLIER_CONTACT_ID,
               PoVendors.vendor_name
               AS
                      SUPPLIER_NAME,
               Decode(PoVendorContacts.first_name, NULL,
               PoVendorContacts.last_name,
PoVendorContacts.last_name
||', '
||PoVendorContacts.first_name) AS
SUPPLIER_CONTACT_NAME,
PoVendorSitesAll.address_line1
|| ' '
|| PoVendorSitesAll.address_line2
|| ' '
||PoVendorSitesAll.address_line3
|| ' '
|| PoVendorSitesAll.city
|| ' '
|| PoVendorSitesAll.state
|| ' '
||PoVendorSitesAll.zip
|| ' '
|| PoVendorSitesAll.country                                        AS
ADDRESS,
Decode(Trunc(Length(PoVendorContacts.area_code
||' '
||PoVendorContacts.phone) / 26), 0,
PoVendorContacts.area_code
|| Decode(PoVendorContacts.area_code, NULL, NULL,
Decode(PoVendorContacts.phone, NULL, NULL,
' '))
||PoVendorContacts.phone,
PoVendorContacts.area_code
||PoVendorContacts.phone)
AS PHONE,
PoVendorContacts.email_address                                     AS
EMAIL_ADDRESS,
Decode(Trunc(Length(PoVendorContacts.fax_area_code
||' '
||PoVendorContacts.fax) / 26), 0,
PoVendorContacts.fax_area_code
||
Decode(
PoVendorContacts.fax_area_code, NULL, NULL,
Decode(
PoVendorContacts.fax, NULL, NULL,
' '))
||PoVendorContacts.fax,
PoVendorContacts.fax_area_code
||PoVendorContacts.fax)                                            AS FAX,
PoVendorSitesAll.vendor_site_code                                  AS
SUPPLIER_SITE,
ou.name                                                            AS
organization_name
FROM   po_vendors PoVendors,
po_vendor_contacts PoVendorContacts,
po_vendor_sites_all PoVendorSitesAll,
hr_all_organization_units ou
WHERE  Nvl(PoVendors.enabled_flag, 'Y') = 'Y'
AND PoVendors.vendor_id = PoVendorSitesAll.vendor_id
AND ( PoVendors.vendor_type_lookup_code IS NULL
OR PoVendors.vendor_type_lookup_code <> 'EMPLOYEE' )
AND SYSDATE BETWEEN Nvl(PoVendors.start_date_active, SYSDATE) AND
Nvl(PoVendors.end_date_active, SYSDATE)
AND PoVendorSitesAll.purchasing_site_flag = 'Y'
AND Nvl(PoVendorSitesAll.rfq_only_site_flag, 'N') = 'N'
AND PoVendorSitesAll.org_id = :1
AND SYSDATE <= Nvl(PoVendorSitesAll.inactive_date, SYSDATE)
AND PoVendorSitesAll.vendor_site_id = PoVendorContacts.vendor_site_id(+)
AND SYSDATE <= Nvl(PoVendorContacts.inactive_date(+), SYSDATE)
AND ou.organization_id = PoVendorSitesAll.org_id) QRSLT
WHERE  (( Upper(supplier_name) LIKE Upper(:2)
          AND ( supplier_name LIKE :3
                 OR supplier_name LIKE :4
                 OR supplier_name LIKE :5
                 OR supplier_name LIKE :6 ) ))
ORDER  BY supplier_name ASC,
          supplier_site ASC 

Comments

Post a Comment

Popular posts from this blog

SQL Query to extract Oracle Purchase Order Information

SELECT   poh.po_header_id,    poh.type_lookup_code PO_TYPE,   poh.authorization_status PO_STATUS,   poh.segment1 PO_NUMBER,   pov.vendor_name SUPPLIER_NAME,   povs.vendor_site_code Location,   hrls.location_code Ship_To,   hrlb.location_code Bill_to,   pol.line_num ,   msib.segment1 Item,   pol.unit_price,   pol.quantity,   pod.amount_billed Amount,   pod.destination_subinventory,   ppf.full_name Buyer_Name,   poh.closed_Code  FROM   PO_HEADERS_ALL poh,   PO_LINES_ALL pol,   mtl_system_items_b msib,   PO_LINE_LOCATIONS_ALL poll,   PO_DISTRIBUTIONS_ALL pod,   po_vendors pov,   po_vendor_sites_All povs,   hr_locations_all hrls,   hr_locations_all hrlb,   per_all_people_f ppf,   po_line_types polt WHERE   1                         =1 AND polt.line...
Post a Comment
Read more

Query to find Operating Unit, Business Group and Legal Entity Information

SELECT   DISTINCT   hrl . country ,                  hroutl_bg . name              bg ,                  hroutl_bg . organization_id ,                  lep . legal_entity_id ,                  lep . name                    legal_entity ,                  hroutl_ou . name              ou_name ,               ...
Post a Comment
Read more

How To Enable / Disable Forms Personalization Option

Forms Personalization gives great flexibility to execute custom business logic without performing so much of technical work. To start forms personalization navigate to Help -> Diagnostics -> Custom Code -> Personalize But many time when we click on personalize it give below error  " Function is not available for this respnosibility. Change responsibilities or contact your System Administrator " To Enable access to forms personalization function we need to set below profile option.  -  Utilities:Diagnostics -> Yes / No It determines the diagnostics option is enabled for a user / responsibility or site, depending on the level profile option is set. Navigate to System Administrator -> Profile -> System Query for your user / responsibility for which you want to provide access. Set the value to 'Yes' , If you want allow access to forms personalization Since we change the profile option please change the respons...
Post a Comment
Read more