Tuesday, 13 January 2015

Supplier and customer active and inactive det

select * from ap_suppliers where VENDOR_NAME like 'Name%'

select * from ap_supplier_sites_all where VENDOR_ID = 69262

select * from ap_supplier_sites_all where VENDOR_ID = 69262 and INACTIVE_DATE is null

select * from ap_suppliers where END_DATE_ACTIVE is not null

select * from ap_supplier_sites_all where INACTIVE_DATE is not null



/* Formatted on 1/13/2015 12:00:34 PM (QP5 v5.115.810.9015) */
SELECT   VENDOR_NAME,
         SEGMENT1 Vendor_number,
         VENDOR_SITE_CODE,
         ADDRESS_LINE1,
         ADDRESS_LINE2,
         CITY
  FROM   ap_suppliers aps, ap_supplier_sites_all apss
 WHERE       1 = 1
         AND aps.VENDOR_ID = apss.VENDOR_ID
         AND aps.END_DATE_ACTIVE IS NULL
         AND apss.INACTIVE_DATE IS NULL
         AND VENDOR_NAME LIKE 'Name%'
==========================================================

/* Formatted on 1/13/2015 12:24:14 PM (QP5 v5.115.810.9015) */
  SELECT   hou.Name OU_NAME,
           -- asu.VENDOR_ID,
           asu.VENDOR_NAME Supplier_Name,
           -- assa.VENDOR_SITE_ID,
           assa.VENDOR_SITE_CODE
    -- assa.org_id,
    -- asu.END_DATE_ACTIVE,
    -- assa.inactive_date
    FROM   ap_suppliers asu, ap_supplier_sites_all assa, hr_operating_units hou
   WHERE       asu.vendor_id = assa.vendor_id
           AND assa.org_id = hou.organization_id
           --and asu.vendor_id=11
           AND (asu.end_date_active IS NULL OR asu.END_DATE_ACTIVE > SYSDATE)
           AND (assa.inactive_date IS NULL OR assa.inactive_date > SYSDATE)
GROUP BY   HOU.NAME, ASU.VENDOR_NAME, ASSA.VENDOR_SITE_CODE


==========================================================

select * from hz_parties  -- STATUS

select distinct STATUS from hz_parties  -- STATUS 

select distinct ORIG_SYSTEM_REFERENCE from hz_parties

select * from hz_party_sites -- STATUS

select distinct STATUS  from hz_party_sites -- STATUS

select * from hz_cust_accounts

select distinct org_id from hz_cust_accounts

select * from HZ_ORG_CONTACTS  -- PARTY_SITE_ID

/* Formatted on 1/13/2015 12:42:45 PM (QP5 v5.115.810.9015) */
  SELECT   lookup_code,
           meaning,
           start_date_active,
           end_date_active,
           description
    FROM   ar_lookups
   WHERE       lookup_type = 'REGISTRY_STATUS'
           AND enabled_flag = 'Y'
           AND lookup_code IN ('A', 'I', 'D', 'M', 'C')
ORDER BY   meaning;
\

LOOKUP_CODE          MEANING   

     A                                    Active   
     D                                   Deleted   
     I                                     Inactive   
     M                                  Merged   



/* Formatted on 1/13/2015 12:50:00 PM (QP5 v5.115.810.9015) */
SELECT   *
  FROM   hz_parties hzp, hz_party_sites hzps
 WHERE       1 = 1
         AND hzp.party_id = hzps.party_id
         AND hzp.STATUS IN ('A', 'M')
         AND hzps.STATUS IN ('A', 'M')

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete