[Home] [Help]
4783: SELECT accts.party_id,
4784: sites.cust_account_id
4785: FROM hz_cust_site_uses_all site_uses,
4786: hz_cust_acct_sites_all sites,
4787: hz_cust_accounts accts
4788: WHERE site_uses.site_use_id = p_site_use_id
4789: AND site_uses.site_use_code = p_site_use_code
4790: AND site_uses.cust_acct_site_id = sites.cust_acct_site_id
4791: AND sites.cust_account_id = accts.cust_account_id;
6396: l_rel_cust_flag VARCHAR2 (5) := 'Y';
6397:
6398: CURSOR get_cust_id(l_cust_acct_id NUMBER) IS
6399: SELECT P.PARTY_NAME name, CA.PARTY_ID party_id
6400: FROM HZ_CUST_ACCOUNTS CA, HZ_PARTIES P
6401: WHERE CA.PARTY_ID = P.PARTY_ID AND
6402: CA.CUST_ACCOUNT_ID = l_cust_acct_id;
6403:
6404: -- This cursor will return the party id of the customer or third party
6426:
6427: -- If the party id does not belong to the customer or third party then
6428: -- it might belong to the related customer account. If not give an error.
6429: -- replaced OKX_CUST_ACCT_RELATE_ALL_V with HZ_CUST_ACCT_RELATE_ALL
6430: -- replaced OKX_CUSTOMER_ACCOUNTS_V with HZ_CUST_ACCOUNTS
6431: CURSOR get_related_cust_acct_id(orgId NUMBER, relatedCustAccId NUMBER,
6432: custPartyId NUMBER) IS
6433: SELECT A.CUST_ACCOUNT_ID, A.RELATED_CUST_ACCOUNT_ID
6434: FROM HZ_CUST_ACCT_RELATE_ALL A,
6431: CURSOR get_related_cust_acct_id(orgId NUMBER, relatedCustAccId NUMBER,
6432: custPartyId NUMBER) IS
6433: SELECT A.CUST_ACCOUNT_ID, A.RELATED_CUST_ACCOUNT_ID
6434: FROM HZ_CUST_ACCT_RELATE_ALL A,
6435: HZ_CUST_ACCOUNTS CA
6436: WHERE CA.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
6437: AND CA.PARTY_ID = custPartyId
6438: AND A.RELATED_CUST_ACCOUNT_ID = relatedCustAccId
6439: AND CA.STATUS = 'A'
6566: AND CS.cust_acct_site_id = CA.cust_acct_site_id;
6567:
6568: CURSOR is_cust_active(l_cust_acct_id NUMBER) IS
6569: SELECT status
6570: FROM hz_cust_accounts
6571: WHERE cust_account_id = l_cust_acct_id
6572: AND status = 'A';
6573:
6574: CURSOR get_contr_cust IS
6581: CURSOR is_cust_hdr_active(l_party_id NUMBER) IS
6582: SELECT CA.CUST_ACCOUNT_ID,
6583: ca.status,
6584: decode(CA.ACCOUNT_NAME, NULL, P.PARTY_NAME, CA.Account_NAME) NAME
6585: FROM HZ_CUST_ACCOUNTS CA, HZ_PARTIES P
6586: WHERE CA.PARTY_ID = P.PARTY_ID
6587: AND P.PARTY_ID = l_party_id;
6588:
6589: -- GCHADHA --
6604: c.cust_account_id CUST_ID1,
6605: p.party_id party_id
6606:
6607: FROM OKX_CUST_SITE_USES_V a,
6608: hz_cust_accounts c,
6609: hz_parties p
6610: WHERE a.id1 = p_bill_to_site_use_id
6611: AND c.cust_account_id = a.cust_account_id
6612: AND a.site_use_code = 'BILL_TO'
6616: SELECT
6617: c.cust_account_id CUST_ID1,
6618: p.party_id party_id
6619: FROM OKX_CUST_SITE_USES_V a,
6620: hz_cust_accounts c,
6621: hz_parties p
6622: WHERE a.id1 = p_ship_to_site_use_id
6623: AND c.cust_account_id = a.cust_account_id
6624: AND a.site_use_code = 'SHIP_TO'
6626:
6627: CURSOR Get_Status_Party (l_cust_acct_id IN NUMBER, l_party_id IN NUMBER) IS
6628: SELECT CA.CUST_ACCOUNT_ID, ca.status,
6629: decode(CA.ACCOUNT_NAME, NULL, P.PARTY_NAME, CA.Account_NAME) NAME
6630: FROM HZ_CUST_ACCOUNTS CA, HZ_PARTIES P
6631: WHERE CA.PARTY_ID = P.PARTY_ID
6632: AND P.PARTY_ID = l_party_id
6633: AND CA.CUST_ACCOUNT_ID = l_cust_acct_id;
6634:
6647: -- Customer_account which is inactive
6648: CURSOR get_parent_party (p_org_id IN NUMBER, p_id IN NUMBER) IS
6649: SELECT rle_code, B.Status, B.PARTY_STATUS FROM okc_k_party_roles_v A,
6650: (SELECT CA.PARTY_ID, A.Status, CA.STATUS PARTY_STATUS FROM HZ_CUST_ACCT_RELATE_ALL A,
6651: HZ_CUST_ACCOUNTS CA WHERE CA.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
6652: AND A.RELATED_CUST_ACCOUNT_ID = p_id
6653: AND A.org_id = p_org_id) B
6654: WHERE A.rle_code NOT IN ('VENDOR', 'MERCHANT')
6655: AND A.dnz_chr_id = p_chr_id
6660: -- Check whether the customer/third party is active or not.
6661: CURSOR Get_Status_Party_Main (l_party_id IN NUMBER) IS
6662: SELECT CA.CUST_ACCOUNT_ID, ca.status,
6663: decode(CA.ACCOUNT_NAME, NULL, P.PARTY_NAME, CA.Account_NAME) NAME
6664: FROM HZ_CUST_ACCOUNTS CA, HZ_PARTIES P
6665: WHERE CA.PARTY_ID = P.PARTY_ID
6666: AND P.PARTY_ID = l_party_id
6667: AND CA.STATUS = 'A';
6668: