1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query for Displying full name of the contact at the Bill to site level

Discussion in 'Oracle Apps Technical' started by madhuperam, Feb 19, 2014.

  1. madhuperam

    madhuperam Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Pulivendula
    Hi,

    The full name of the contact at the Bill to site level. If there are multiple contacts then pick the one who has the role of ‘Accounts Payable’. If there are multiple contacts with the same role then pick the one who has the role as Primary. Again if that returns multiple records then pick the one which is alphabetically first.

    Check the below code:

    create or replace Function Contact_Name(P_Cust_Acct_Site_Id In Number )
    RETURN VARCHAR2
    l_contact_name VARCHAR2(240);
    BEGIN
    BEGIN
    select HRP.PERSON_FIRST_NAME
    ||' '||hrp.person_last_name cont_name
    INTO l_contact_name
    FROM hz_relationships hr,
    hz_parties hrp,
    hz_parties hp,
    hz_cust_accounts hca,
    hz_org_contacts org_cont,
    hz_cust_account_roles hcar,
    hz_cust_acct_sites_all hcasa,
    hz_cust_site_uses_all hcsua
    WHERE hp.party_id = hca.party_id
    AND hcar.cust_acct_site_id = hcasa.cust_acct_site_id
    AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
    AND hcsua.site_use_code = 'BILL_TO'
    AND hcar.party_id = hr.party_id
    AND hr.subject_id = hrp.party_id
    AND hr.object_id = hca.party_id
    AND hr.subject_table_name = 'HZ_PARTIES'
    AND hr.object_table_name = 'HZ_PARTIES'
    AND hcar.role_type = 'CONTACT'
    AND hcar.current_role_state = 'A'
    and ORG_CONT.PARTY_RELATIONSHIP_ID = HR.RELATIONSHIP_ID
    AND hcasa.cust_acct_site_id = p_cust_acct_site_id;

    RETURN l_contact_name;
    EXCEPTION
    WHEN TOO_MANY_ROWS THEN
    BEGIN
    select HRP.PERSON_FIRST_NAME
    ||''||hrp.person_last_name cont_name
    INTO l_contact_name
    FROM hz_relationships hr,
    hz_parties hrp,
    hz_parties hp,
    hz_cust_accounts hca,
    hz_org_contacts org_cont,
    hz_cust_account_roles hcar,
    hz_cust_acct_sites_all hcasa,
    hz_cust_site_uses_all hcsua,
    hz_role_responsibility hrr
    where HP.PARTY_ID = HCA.PARTY_ID
    AND hcar.cust_acct_site_id = hcasa.cust_acct_site_id
    AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
    AND hcsua.site_use_code = 'BILL_TO'
    AND hcar.party_id = hr.party_id
    AND hr.subject_id = hrp.party_id
    AND hr.object_id = hca.party_id
    AND hr.subject_table_name = 'HZ_PARTIES'
    AND hr.object_table_name = 'HZ_PARTIES'
    AND hcar.role_type = 'CONTACT'
    AND hcar.current_role_state = 'A'
    AND org_cont.party_relationship_id = hr.relationship_id
    AND hcar.cust_account_role_id = hrr.cust_account_role_id
    AND hrr.responsibility_type ='ACCOUNTS PAYABLE'
    AND hcasa.cust_acct_site_id = p_cust_acct_site_id;

    RETURN l_contact_name;
    EXCEPTION
    WHEN TOO_MANY_ROWS THEN
    BEGIN
    select HRP.PERSON_FIRST_NAME
    ||''||hrp.person_last_name cont_name
    INTO l_contact_name
    FROM hz_relationships hr,
    hz_parties hrp,
    hz_parties hp,
    hz_cust_accounts hca,
    hz_org_contacts org_cont,
    hz_cust_account_roles hcar,
    hz_cust_acct_sites_all hcasa,
    hz_cust_site_uses_all hcsua,
    hz_role_responsibility hrr
    WHERE hp.party_id = hca.party_id
    AND hcar.cust_acct_site_id = hcasa.cust_acct_site_id
    AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
    AND hcsua.site_use_code = 'BILL_TO'
    AND hcar.party_id = hr.party_id
    AND hr.subject_id = hrp.party_id
    AND hr.object_id = hca.party_id
    AND hr.subject_table_name = 'HZ_PARTIES'
    AND hr.object_table_name = 'HZ_PARTIES'
    AND hcar.role_type = 'CONTACT'
    AND hcar.current_role_state = 'A'
    AND org_cont.party_relationship_id = hr.relationship_id
    AND hcar.cust_account_role_id = hrr.cust_account_role_id
    AND hrr.responsibility_type ='ACCOUNTS PAYABLE'
    AND hrr.primary_flag ='Y'
    AND hcasa.cust_acct_site_id = p_cust_acct_site_id;

    RETURN l_contact_name;
    EXCEPTION
    WHEN TOO_MANY_ROWS THEN
    BEGIN
    select CONT_TAB.CONT_NAME
    INTO l_contact_name
    from
    (select HRP.PERSON_FIRST_NAME
    ||' '||HRP.PERSON_LAST_NAME CONT_NAME
    FROM hz_relationships hr,
    hz_parties hrp,
    hz_parties hp,
    hz_cust_accounts hca,
    hz_org_contacts org_cont,
    hz_cust_account_roles hcar,
    HZ_CUST_ACCT_SITES_ALL HCASA,
    hz_cust_site_uses_all hcsua,
    hz_role_responsibility hrr
    WHERE hp.party_id = hca.party_id
    AND hcar.cust_acct_site_id = hcasa.cust_acct_site_id
    AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
    AND hcsua.site_use_code = 'BILL_TO'
    AND hcar.party_id = hr.party_id
    AND hr.subject_id = hrp.party_id
    AND hr.object_id = hca.party_id
    AND hr.subject_table_name = 'HZ_PARTIES'
    AND hr.object_table_name = 'HZ_PARTIES'
    AND hcar.role_type = 'CONTACT'
    AND hcar.current_role_state = 'A'
    AND org_cont.party_relationship_id = hr.relationship_id
    AND hcar.cust_account_role_id = hrr.cust_account_role_id
    AND hrr.responsibility_type ='ACCOUNTS PAYABLE'
    and HRR.PRIMARY_FLAG ='Y'
    AND hcasa.cust_acct_site_id = p_cust_acct_site_id
    order by CONT_NAME) CONT_TAB
    where rownum = 1;
    DBMS_OUTPUT.PUT_LINE ('Entered INto exception1:'||SQLERRM);
    RETURN l_contact_name;
    END;
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_lINe ('Entered INto exception1:'||SQLERRM);
    RETURN NULL;
    END;
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_lINe ('Entered INto Excetion 2:'||SQLERRM);
    RETURN NULL;
    END;
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_lINe ('Entered INto Exception 3:'||SQLERRM);
    RETURN NULL;
    END;
    END contact_name;
     
    jagadekara likes this.