[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
2101: l_token_v := TO_CHAR(p_customer_id);
2102:
2103: BEGIN
2104: SELECT party_type INTO l_party_type -- Bug 3666089
2105: FROM hz_parties a
2106: WHERE a.party_id = p_customer_id
2107: AND a.status = 'A'
2108: AND a.party_type IN ('ORGANIZATION','PERSON');
2109:
2160: IS
2161: l_dummy VARCHAR2(1);
2162: l_api_name CONSTANT VARCHAR2(30) := 'Validate_Bill_To_Ship_To_Ct';
2163: l_api_name_full CONSTANT VARCHAR2(70) := G_PKG_NAME||'.'||l_api_name;
2164: l_party_type hz_parties.party_type % type;
2165:
2166: CURSOR bill_to_party_type_csr is
2167: SELECT party_type from hz_parties
2168: WHERE party_id = p_bill_to_party_id;
2163: l_api_name_full CONSTANT VARCHAR2(70) := G_PKG_NAME||'.'||l_api_name;
2164: l_party_type hz_parties.party_type % type;
2165:
2166: CURSOR bill_to_party_type_csr is
2167: SELECT party_type from hz_parties
2168: WHERE party_id = p_bill_to_party_id;
2169:
2170: CURSOR validate_bill_to_contact_csr IS
2171: SELECT 'x'
2168: WHERE party_id = p_bill_to_party_id;
2169:
2170: CURSOR validate_bill_to_contact_csr IS
2171: SELECT 'x'
2172: FROM Hz_Parties sub,
2173: Hz_Relationships r
2174: WHERE r.object_id = p_bill_to_party_id
2175: AND r.party_id = p_bill_to_contact_id
2176: AND sub.status = 'A'
2264: l_api_name_full CONSTANT VARCHAR2(70) := G_PKG_NAME||'.'||l_api_name;
2265:
2266: CURSOR validate_bill_to_contact_csr IS
2267: SELECT 'x'
2268: FROM Hz_Parties sub,
2269: Hz_Relationships r
2270: WHERE r.object_id = p_customer_id
2271: AND r.party_id = p_customer_contact_id
2272: AND sub.status = 'A'
2335: l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
2336:
2337: CURSOR party_relationship IS
2338: SELECT 'x'
2339: FROM Hz_Parties sub,
2340: Hz_Relationships r,
2341: Hz_Parties obj
2342: WHERE r.object_id = p_customer_id
2343: AND r.party_id = p_customer_contact_id
2337: CURSOR party_relationship IS
2338: SELECT 'x'
2339: FROM Hz_Parties sub,
2340: Hz_Relationships r,
2341: Hz_Parties obj
2342: WHERE r.object_id = p_customer_id
2343: AND r.party_id = p_customer_contact_id
2344: AND sub.status = 'A'
2345: AND r.status = 'A'
2398: l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
2399:
2400: CURSOR party_relationship_per IS
2401: SELECT 'x'
2402: FROM Hz_Parties sub,
2403: Hz_Relationships r,
2404: Hz_Parties obj
2405: WHERE r.object_id = p_customer_id
2406: AND r.party_id = p_customer_contact_id
2400: CURSOR party_relationship_per IS
2401: SELECT 'x'
2402: FROM Hz_Parties sub,
2403: Hz_Relationships r,
2404: Hz_Parties obj
2405: WHERE r.object_id = p_customer_id
2406: AND r.party_id = p_customer_contact_id
2407: AND sub.status = 'A'
2408: AND r.status = 'A'
2487: AND cip.party_id = p_customer_id
2488: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
2489: AND TRUNC(NVL(a.active_end_date, SYSDATE))
2490: AND a.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
2491: AND a.owner_party_source_table = 'HZ_PARTIES'
2492: AND a.instance_id = cip.instance_id
2493: AND cip.party_source_table = 'HZ_PARTIES'
2494: AND a.inventory_item_id = b.inventory_item_id
2495: AND b.organization_id = p_inventory_org_id
2489: AND TRUNC(NVL(a.active_end_date, SYSDATE))
2490: AND a.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
2491: AND a.owner_party_source_table = 'HZ_PARTIES'
2492: AND a.instance_id = cip.instance_id
2493: AND cip.party_source_table = 'HZ_PARTIES'
2494: AND a.inventory_item_id = b.inventory_item_id
2495: AND b.organization_id = p_inventory_org_id
2496: -- Commented service_item_flag condition as this is not used
2497: -- anymore and added contract_item_type_code condition
2514: -- AND a.owner_party_id = p_customer_id
2515: AND cip.party_id = p_customer_id
2516: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
2517: AND TRUNC(NVL(a.active_end_date, SYSDATE))
2518: AND a.owner_party_source_table = 'HZ_PARTIES'
2519: AND a.instance_id = cip.instance_id
2520: AND cip.party_source_table = 'HZ_PARTIES'
2521: AND a.inventory_item_id = b.inventory_item_id
2522: AND b.organization_id = p_inventory_org_id
2516: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
2517: AND TRUNC(NVL(a.active_end_date, SYSDATE))
2518: AND a.owner_party_source_table = 'HZ_PARTIES'
2519: AND a.instance_id = cip.instance_id
2520: AND cip.party_source_table = 'HZ_PARTIES'
2521: AND a.inventory_item_id = b.inventory_item_id
2522: AND b.organization_id = p_inventory_org_id
2523: -- Commented service_item_flag condition as this is not used
2524: -- anymore and added contract_item_type_code condition
2601: AND a.serial_number = p_current_serial_number
2602: -- AND a.owner_party_account_id = p_customer_id
2603: AND cip.party_id = p_customer_id
2604: AND a.instance_id = cip.instance_id
2605: AND cip.party_source_table = 'HZ_PARTIES'
2606: AND b.serv_req_enabled_code = 'E'
2607: -- Added contract_item_type_code condition
2608: -- For ER 3701924
2609: -- AND contract_item_type_code IS NULL
2623: AND a.serial_number = p_current_serial_number
2624: -- AND a.owner_party_account_id = p_customer_id
2625: AND cip.party_id = p_customer_id
2626: AND a.instance_id = cip.instance_id
2627: AND cip.party_source_table = 'HZ_PARTIES'
2628: AND b.serv_req_enabled_code = 'E'
2629: -- Added contract_item_type_code condition
2630: -- For ER 3701924
2631: -- AND contract_item_type_code IS NULL
3241: WHERE a.instance_id = p_customer_product_id
3242: AND a.external_reference = p_external_reference
3243: AND cip.party_id = p_customer_id
3244: AND a.instance_id = cip.instance_id
3245: AND cip.party_source_table = 'HZ_PARTIES'
3246: AND b.serv_req_enabled_code = 'E'
3247: AND a.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
3248: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
3249: AND TRUNC(NVL(a.active_end_date, SYSDATE))
3259: WHERE a.instance_id = p_customer_product_id
3260: AND a.external_reference = p_external_reference
3261: AND cip.party_id = p_customer_id
3262: AND a.instance_id = cip.instance_id
3263: AND cip.party_source_table = 'HZ_PARTIES'
3264: AND b.serv_req_enabled_code = 'E'
3265: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
3266: AND TRUNC(NVL(a.active_end_date, SYSDATE))
3267: AND b.inventory_item_id = a.inventory_item_id
3435: l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
3436:
3437: CURSOR Bill_To_Party IS
3438: SELECT p.party_type
3439: FROM Hz_parties p ,
3440: Hz_Relationships r
3441: WHERE p.party_id = p_bill_to_party_id
3442: AND p.status = 'A'
3443: AND p.party_type IN ('PERSON','ORGANIZATION')
3590: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(r.START_DATE, SYSDATE)) AND TRUNC(NVL(r.END_DATE, SYSDATE)) )
3591: -- or one of its Related parties
3592: OR s.party_id IN (
3593: SELECT sub.party_id
3594: FROM Hz_Parties p,
3595: Hz_Parties sub,
3596: Hz_Parties obj,
3597: Hz_Relationships r
3598: WHERE obj.party_id = p_customer_id
3591: -- or one of its Related parties
3592: OR s.party_id IN (
3593: SELECT sub.party_id
3594: FROM Hz_Parties p,
3595: Hz_Parties sub,
3596: Hz_Parties obj,
3597: Hz_Relationships r
3598: WHERE obj.party_id = p_customer_id
3599: AND sub.status = 'A'
3592: OR s.party_id IN (
3593: SELECT sub.party_id
3594: FROM Hz_Parties p,
3595: Hz_Parties sub,
3596: Hz_Parties obj,
3597: Hz_Relationships r
3598: WHERE obj.party_id = p_customer_id
3599: AND sub.status = 'A'
3600: AND obj.status = 'A'
5065: FROM HZ_CONTACT_POINTS a
5066: WHERE a.contact_point_type = p_contact_point_type
5067: AND a.contact_point_id = p_contact_point_id
5068: AND a.OWNER_TABLE_ID = p_party_id
5069: AND a.OWNER_TABLE_NAME = 'HZ_PARTIES'
5070: AND a.STATUS = 'A';
5071: EXCEPTION
5072: WHEN NO_DATA_FOUND THEN
5073: x_return_status := FND_API.G_RET_STS_ERROR;
9580: SELECT 1
9581: FROM csi_i_parties
9582: WHERE instance_id = p_customer_product_id
9583: AND party_id = p_customer_id
9584: AND party_source_table = 'HZ_PARTIES';
9585:
9586: l_party_id csi_i_parties.party_id % TYPE;
9587: l_source_table csi_item_instances.owner_party_source_table % TYPE;
9588: l_loc_type csi_item_instances.location_type_code % TYPE;
9644: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9645: RAISE FND_API.G_EXC_ERROR;
9646: END IF;
9647: --
9648: IF NVL(l_source_table,'#') <> 'HZ_PARTIES'
9649: THEN
9650: FND_MESSAGE.set_name('CS','CS_SR_CP_CUST_SOURCE_INVALID'); -- product is not for TCA parties
9651: FND_MESSAGE.set_token('PARTY_SOURCE',l_source_table);
9652: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9922: -- -----------------------------------------------------------------------------
9923: -- Procedure Name : get_party_details
9924: -- Parameter :
9925: -- IN : p_party_id This is a foreign key to table
9926: -- hz_parties
9927: -- OUT : x_party_type it is the party type associated with
9928: -- : x_status represents active or inactive status
9929: -- of party_id
9930: -- party id
9953: INTO
9954: x_party_type
9955: , x_status
9956: FROM
9957: hz_parties a
9958: WHERE a.party_id = p_party_id;
9959:
9960: EXCEPTION
9961: WHEN NO_DATA_FOUND THEN