[Home] [Help]
667:
668: IF p_customer_number <> FND_API.G_MISS_CHAR THEN
669: BEGIN
670: SELECT a.party_id INTO p_customer_id
671: FROM hz_parties a
672: WHERE a.party_number = p_customer_number;
673: EXCEPTION
674: WHEN NO_DATA_FOUND THEN
675: x_return_status := FND_API.G_RET_STS_ERROR;
699: will never execute. hence removing this piece.
700: ELSE
701: BEGIN
702: SELECT a.party_id INTO p_customer_id
703: FROM hz_parties a
704: WHERE UPPER(a.party_name) = UPPER(p_customer_name);
705:
706: EXCEPTION
707: WHEN NO_DATA_FOUND THEN
2099: l_token_v := TO_CHAR(p_customer_id);
2100:
2101: BEGIN
2102: SELECT party_type INTO l_party_type -- Bug 3666089
2103: FROM hz_parties a
2104: WHERE a.party_id = p_customer_id
2105: AND a.status = 'A'
2106: AND a.party_type IN ('ORGANIZATION','PERSON');
2107:
2158: IS
2159: l_dummy VARCHAR2(1);
2160: l_api_name CONSTANT VARCHAR2(30) := 'Validate_Bill_To_Ship_To_Ct';
2161: l_api_name_full CONSTANT VARCHAR2(70) := G_PKG_NAME||'.'||l_api_name;
2162: l_party_type hz_parties.party_type % type;
2163:
2164: CURSOR bill_to_party_type_csr is
2165: SELECT party_type from hz_parties
2166: WHERE party_id = p_bill_to_party_id;
2161: l_api_name_full CONSTANT VARCHAR2(70) := G_PKG_NAME||'.'||l_api_name;
2162: l_party_type hz_parties.party_type % type;
2163:
2164: CURSOR bill_to_party_type_csr is
2165: SELECT party_type from hz_parties
2166: WHERE party_id = p_bill_to_party_id;
2167:
2168: CURSOR validate_bill_to_contact_csr IS
2169: SELECT 'x'
2166: WHERE party_id = p_bill_to_party_id;
2167:
2168: CURSOR validate_bill_to_contact_csr IS
2169: SELECT 'x'
2170: FROM Hz_Parties sub,
2171: Hz_Relationships r
2172: WHERE r.object_id = p_bill_to_party_id
2173: AND r.party_id = p_bill_to_contact_id
2174: AND sub.status = 'A'
2262: l_api_name_full CONSTANT VARCHAR2(70) := G_PKG_NAME||'.'||l_api_name;
2263:
2264: CURSOR validate_bill_to_contact_csr IS
2265: SELECT 'x'
2266: FROM Hz_Parties sub,
2267: Hz_Relationships r
2268: WHERE r.object_id = p_customer_id
2269: AND r.party_id = p_customer_contact_id
2270: AND sub.status = 'A'
2333: l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
2334:
2335: CURSOR party_relationship IS
2336: SELECT 'x'
2337: FROM Hz_Parties sub,
2338: Hz_Relationships r,
2339: Hz_Parties obj
2340: WHERE r.object_id = p_customer_id
2341: AND r.party_id = p_customer_contact_id
2335: CURSOR party_relationship IS
2336: SELECT 'x'
2337: FROM Hz_Parties sub,
2338: Hz_Relationships r,
2339: Hz_Parties obj
2340: WHERE r.object_id = p_customer_id
2341: AND r.party_id = p_customer_contact_id
2342: AND sub.status = 'A'
2343: AND r.status = 'A'
2396: l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
2397:
2398: CURSOR party_relationship_per IS
2399: SELECT 'x'
2400: FROM Hz_Parties sub,
2401: Hz_Relationships r,
2402: Hz_Parties obj
2403: WHERE r.object_id = p_customer_id
2404: AND r.party_id = p_customer_contact_id
2398: CURSOR party_relationship_per IS
2399: SELECT 'x'
2400: FROM Hz_Parties sub,
2401: Hz_Relationships r,
2402: Hz_Parties obj
2403: WHERE r.object_id = p_customer_id
2404: AND r.party_id = p_customer_contact_id
2405: AND sub.status = 'A'
2406: AND r.status = 'A'
2485: AND cip.party_id = p_customer_id
2486: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
2487: AND TRUNC(NVL(a.active_end_date, SYSDATE))
2488: AND a.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
2489: AND a.owner_party_source_table = 'HZ_PARTIES'
2490: AND a.instance_id = cip.instance_id
2491: AND cip.party_source_table = 'HZ_PARTIES'
2492: AND a.inventory_item_id = b.inventory_item_id
2493: AND b.organization_id = p_inventory_org_id
2487: AND TRUNC(NVL(a.active_end_date, SYSDATE))
2488: AND a.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
2489: AND a.owner_party_source_table = 'HZ_PARTIES'
2490: AND a.instance_id = cip.instance_id
2491: AND cip.party_source_table = 'HZ_PARTIES'
2492: AND a.inventory_item_id = b.inventory_item_id
2493: AND b.organization_id = p_inventory_org_id
2494: -- Commented service_item_flag condition as this is not used
2495: -- anymore and added contract_item_type_code condition
2512: -- AND a.owner_party_id = p_customer_id
2513: AND cip.party_id = p_customer_id
2514: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
2515: AND TRUNC(NVL(a.active_end_date, SYSDATE))
2516: AND a.owner_party_source_table = 'HZ_PARTIES'
2517: AND a.instance_id = cip.instance_id
2518: AND cip.party_source_table = 'HZ_PARTIES'
2519: AND a.inventory_item_id = b.inventory_item_id
2520: AND b.organization_id = p_inventory_org_id
2514: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
2515: AND TRUNC(NVL(a.active_end_date, SYSDATE))
2516: AND a.owner_party_source_table = 'HZ_PARTIES'
2517: AND a.instance_id = cip.instance_id
2518: AND cip.party_source_table = 'HZ_PARTIES'
2519: AND a.inventory_item_id = b.inventory_item_id
2520: AND b.organization_id = p_inventory_org_id
2521: -- Commented service_item_flag condition as this is not used
2522: -- anymore and added contract_item_type_code condition
2599: AND a.serial_number = p_current_serial_number
2600: -- AND a.owner_party_account_id = p_customer_id
2601: AND cip.party_id = p_customer_id
2602: AND a.instance_id = cip.instance_id
2603: AND cip.party_source_table = 'HZ_PARTIES'
2604: AND b.serv_req_enabled_code = 'E'
2605: -- Added contract_item_type_code condition
2606: -- For ER 3701924
2607: -- AND contract_item_type_code IS NULL
2621: AND a.serial_number = p_current_serial_number
2622: -- AND a.owner_party_account_id = p_customer_id
2623: AND cip.party_id = p_customer_id
2624: AND a.instance_id = cip.instance_id
2625: AND cip.party_source_table = 'HZ_PARTIES'
2626: AND b.serv_req_enabled_code = 'E'
2627: -- Added contract_item_type_code condition
2628: -- For ER 3701924
2629: -- AND contract_item_type_code IS NULL
3239: WHERE a.instance_id = p_customer_product_id
3240: AND a.external_reference = p_external_reference
3241: AND cip.party_id = p_customer_id
3242: AND a.instance_id = cip.instance_id
3243: AND cip.party_source_table = 'HZ_PARTIES'
3244: AND b.serv_req_enabled_code = 'E'
3245: AND a.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
3246: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
3247: AND TRUNC(NVL(a.active_end_date, SYSDATE))
3257: WHERE a.instance_id = p_customer_product_id
3258: AND a.external_reference = p_external_reference
3259: AND cip.party_id = p_customer_id
3260: AND a.instance_id = cip.instance_id
3261: AND cip.party_source_table = 'HZ_PARTIES'
3262: AND b.serv_req_enabled_code = 'E'
3263: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
3264: AND TRUNC(NVL(a.active_end_date, SYSDATE))
3265: AND b.inventory_item_id = a.inventory_item_id
3433: l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
3434:
3435: CURSOR Bill_To_Party IS
3436: SELECT p.party_type
3437: FROM Hz_parties p ,
3438: Hz_Relationships r
3439: WHERE p.party_id = p_bill_to_party_id
3440: AND p.status = 'A'
3441: AND p.party_type IN ('PERSON','ORGANIZATION')
3588: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(r.START_DATE, SYSDATE)) AND TRUNC(NVL(r.END_DATE, SYSDATE)) )
3589: -- or one of its Related parties
3590: OR s.party_id IN (
3591: SELECT sub.party_id
3592: FROM Hz_Parties p,
3593: Hz_Parties sub,
3594: Hz_Parties obj,
3595: Hz_Relationships r
3596: WHERE obj.party_id = p_customer_id
3589: -- or one of its Related parties
3590: OR s.party_id IN (
3591: SELECT sub.party_id
3592: FROM Hz_Parties p,
3593: Hz_Parties sub,
3594: Hz_Parties obj,
3595: Hz_Relationships r
3596: WHERE obj.party_id = p_customer_id
3597: AND sub.status = 'A'
3590: OR s.party_id IN (
3591: SELECT sub.party_id
3592: FROM Hz_Parties p,
3593: Hz_Parties sub,
3594: Hz_Parties obj,
3595: Hz_Relationships r
3596: WHERE obj.party_id = p_customer_id
3597: AND sub.status = 'A'
3598: AND obj.status = 'A'
5063: FROM HZ_CONTACT_POINTS a
5064: WHERE a.contact_point_type = p_contact_point_type
5065: AND a.contact_point_id = p_contact_point_id
5066: AND a.OWNER_TABLE_ID = p_party_id
5067: AND a.OWNER_TABLE_NAME = 'HZ_PARTIES'
5068: AND a.STATUS = 'A';
5069: EXCEPTION
5070: WHEN NO_DATA_FOUND THEN
5071: x_return_status := FND_API.G_RET_STS_ERROR;
9528: SELECT 1
9529: FROM csi_i_parties
9530: WHERE instance_id = p_customer_product_id
9531: AND party_id = p_customer_id
9532: AND party_source_table = 'HZ_PARTIES';
9533:
9534: l_party_id csi_i_parties.party_id % TYPE;
9535: l_source_table csi_item_instances.owner_party_source_table % TYPE;
9536: l_loc_type csi_item_instances.location_type_code % TYPE;
9592: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9593: RAISE FND_API.G_EXC_ERROR;
9594: END IF;
9595: --
9596: IF NVL(l_source_table,'#') <> 'HZ_PARTIES'
9597: THEN
9598: FND_MESSAGE.set_name('CS','CS_SR_CP_CUST_SOURCE_INVALID'); -- product is not for TCA parties
9599: FND_MESSAGE.set_token('PARTY_SOURCE',l_source_table);
9600: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9859: -- -----------------------------------------------------------------------------
9860: -- Procedure Name : get_party_details
9861: -- Parameter :
9862: -- IN : p_party_id This is a foreign key to table
9863: -- hz_parties
9864: -- OUT : x_party_type it is the party type associated with
9865: -- : x_status represents active or inactive status
9866: -- of party_id
9867: -- party id
9890: INTO
9891: x_party_type
9892: , x_status
9893: FROM
9894: hz_parties a
9895: WHERE a.party_id = p_party_id;
9896:
9897: EXCEPTION
9898: WHEN NO_DATA_FOUND THEN