Monday 31 October 2011

Supplier_Contact

  SELECT   DISTINCT
           b.org_id,
           upper(hr.name) OU_NAME,
           DECODE (b.org_id,
                   '1280', 'APIL-HO-10',
                   '1202', 'APIL - BPU UNIT - 20',
                   '1203', 'APIL - SEWA UNIT - 40',
                   '1204', 'APIL - BWN UNIT- 50',
                   '1205', 'APIL - SGU UNIT - 30',
                   hr.name)
              OU_NAME,
           b.vendor_site_id,
           a.segment1 vendor_code,
           /* Optional for data filter */
           UPPER(DECODE (A.VENDOR_NAME,
                         'Withholding Tax Authority', 'WTA-RETENTION-A/C',
                         A.VENDOR_NAME))
              C_VENDOR_NAME,
           UPPER (B.VENDOR_SITE_CODE) C_SITE_CODE,
           UPPER (C.TITLE) C_CONTACT_TITLE,
           UPPER (C.FIRST_NAME) C_FIRST_NAME,
           UPPER (C.LAST_NAME) C_LAST_NAME,
           UPPER (B.EMAIL_ADDRESS) C_EMAIL,
           NULL URL,
           C.AREA_CODE C_PHONE_AREA_CODE,
           C.PHONE C_PHONE_NUMBER,
           c.AREA_CODE C_PHONE_EXTENSION,
           B.FAX_AREA_CODE C_FAX_AREA_CODE,
           B.FAX C_FAX_NUMBER,
           UPPER (C.ATTRIBUTE1) C_ATTRIBUTE1,
           c.vendor_contact_id old_ref_contact_id
    FROM   apps.PO_VENDORS A,
           apps.PO_VENDOR_SITES_ALL B,
           apps.PO_VENDOR_CONTACTS C,
           apps.hr_operating_units hr
   WHERE       A.VENDOR_ID = B.VENDOR_ID
           AND B.VENDOR_SITE_ID = C.VENDOR_SITE_ID(+)
           AND a.END_DATE_ACTIVE IS NULL
           AND b.INACTIVE_DATE IS NULL
           AND c.INACTIVE_DATE IS NULL
           AND b.org_id = hr.organization_id
           and hr.SET_OF_BOOKS_ID=5
           AND a.vendor_id IN (15685, 18484, 51547, 64242, 66436, 69531)
         
           --AND b.org_id IN (1280, 1202, 1203, 1204, 1205)
           --and b.org_id = :P_org_id
           --           AND hr.set_of_books_id = 5
            --AND A.VENDOR_TYPE_LOOKUP_CODE NOT IN ('Ex-Employee', 'EX-EMPLOYEE','Employee','EMPLOYEE','CONSULTANT','Consultant')
           
--AND a.segment1 IN (  SELECT   VENDOR_code FROM xx_vendor_final_apil)
-- xx_vendor_mst_73)
ORDER BY   TO_NUMBER (a.segment1), b.vendor_site_id

No comments:

Post a Comment