[Home] [Help]
2: /* $Header: csusrb.pls 120.30 2008/01/09 11:58:04 nveerara ship $ */
3:
4: G_ACTION_SUBTYPE CONSTANT VARCHAR2(5) := 'ACT';
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_ServiceRequest_UTIL';
6: G_TABLE_NAME CONSTANT VARCHAR2(40) := 'CS_INCIDENTS_ALL_B';
7: G_TL_TABLE_NAME CONSTANT VARCHAR2(40) := 'CS_INCIDENTS_ALL_TL';
8:
9: -- -----------------------------------------------------------------------------
10: -- Procedure Name : get_org_details
375: -- Initialize Return Status to SUCCESS
376: x_return_status := FND_API.G_RET_STS_SUCCESS;
377: BEGIN
378: SELECT incident_id INTO p_request_id
379: FROM cs_incidents_all_b
380: WHERE incident_number = p_request_number ;
381: EXCEPTION
382: WHEN NO_DATA_FOUND THEN
383: x_return_status := FND_API.G_RET_STS_ERROR;
4315: l_api_name CONSTANT VARCHAR2(30) := 'Validate_product_Version';
4316: l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
4317: l_dummy NUMBER;
4318: l_rev_control_code NUMBER;
4319: l_product_version cs_incidents_all_b.component_version % type;
4320:
4321: CURSOR c_inv_item
4322: IS SELECT inventory_item_id, inventory_revision
4323: FROM CSI_ITEM_INSTANCES
7016: lx_site_use_id NUMBER; -- used in validate_bill_to_ship_to_site
7017:
7018: -- 3224828 contracts
7019: l_contract_id NUMBER; -- contract id
7020: l_con_number cs_incidents_all_b.contract_number % type;
7021:
7022: -- 3340433
7023: l_inv_change_by_cp VARCHAR2(1):= 'N'; --set when inv_item_id is overwritten by cp
7024: l_inv_id_from_cp NUMBER;
7025: -- 3360274
7026: l_resource_type VARCHAR2(240); -- used in validate_resource_type
7027:
7028: l_inv_org_master_org_flag VARCHAR2(1);
7029: l_maint_organization_id cs_incidents_all_b.maint_organization_id % TYPE;
7030: l_owning_dept_id cs_incidents_all_b.owning_department_id % TYPE;
7031:
7032: BEGIN
7033: -- Initialize return status to success
7026: l_resource_type VARCHAR2(240); -- used in validate_resource_type
7027:
7028: l_inv_org_master_org_flag VARCHAR2(1);
7029: l_maint_organization_id cs_incidents_all_b.maint_organization_id % TYPE;
7030: l_owning_dept_id cs_incidents_all_b.owning_department_id % TYPE;
7031:
7032: BEGIN
7033: -- Initialize return status to success
7034: x_return_status := FND_API.G_RET_STS_SUCCESS;
9140: UNASSIGNED_INDICATOR OLD_UNASSIGNED_INDICATOR,
9141: UNASSIGNED_INDICATOR UNASSIGNED_INDICATOR,
9142: MAINT_ORGANIZATION_ID OLD_MAINT_ORGANIZATION_ID,
9143: MAINT_ORGANIZATION_ID MAINT_ORGANIZATION_ID
9144: FROM cs_incidents_all_b
9145: WHERE incident_id = p_request_id;
9146:
9147: l_audit_vals_rec CS_ServiceRequest_PVT.SR_AUDIT_REC_TYPE;
9148: l_api_name CONSTANT VARCHAR2(30) := 'Prepare_Audit_Record';
9161: CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg
9162: ( p_token_an => l_api_name,
9163: p_token_v => TO_CHAR(p_request_id),
9164: p_token_p => 'p_request_id',
9165: p_table_name => 'CS_INCIDENTS_ALL_B',
9166: p_column_name => 'REQUEST_ID' );
9167: CLOSE l_ServiceRequest_csr;
9168: ELSE
9169: CLOSE l_ServiceRequest_csr;
9577: THEN
9578: FND_MESSAGE.set_name('CS','CS_SR_CP_INACTIVE');
9579: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9580: FND_MESSAGE.set_token('API_NAME' , l_proc_name);
9581: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9582: RAISE FND_API.G_EXC_ERROR;
9583: END IF;
9584: --
9585: IF CS_SERVICEREQUEST_PVT.g_restrict_ib = 'YES' AND
9588: FND_MESSAGE.set_name('CS','CS_SR_CP_LOCATION_INVALID'); -- Customer porduct installed location is invalid
9589: FND_MESSAGE.set_token('LOC',l_loc_type);
9590: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9591: FND_MESSAGE.set_token('API_NAME' , l_proc_name);
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));
9601: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9602: RAISE FND_API.G_EXC_ERROR;
9603: END IF;
9604: --
9605: IF p_customer_id <> NVL(l_party_id,-1)
9610: THEN
9611: FND_MESSAGE.set_name('CS','CS_SR_CP_CUST_INVALID'); -- cust not related to product
9612: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9613: FND_MESSAGE.set_token('CUST_ID',TO_CHAR(p_customer_id));
9614: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9615: CLOSE c_prod_rel;
9616: RAISE FND_API.G_EXC_ERROR;
9617: ELSE
9618: CLOSE c_prod_rel;
9639: THEN
9640: FND_MESSAGE.set_name('CS','CS_SR_CP_ITEM_ID'); -- item associated with CP does not exists
9641: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9642: FND_MESSAGE.set_token('INV_ITEM_ID',TO_CHAR(l_inv_item_id));
9643: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9644: RAISE FND_API.G_EXC_ERROR;
9645: END IF;
9646: IF l_enabled_flag <> 'Y'
9647: THEN
9648: FND_MESSAGE.set_name('CS','CS_SR_CP_ITEM_DISABLED'); -- item associated with CP is not enabled
9649: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9650: FND_MESSAGE.set_token('INV_ITEM_ID',TO_CHAR(l_inv_item_id));
9651: FND_MESSAGE.set_token('API_NAME' , l_proc_name);
9652: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9653: RAISE FND_API.G_EXC_ERROR;
9654: END IF;
9655: IF l_serv_req_enabled <> 'E'
9656: THEN
9657: FND_MESSAGE.set_name('CS','CS_SR_CP_ITEM_NOT_SERV'); -- item associated with CP is not serviceable
9658: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9659: FND_MESSAGE.set_token('INV',TO_CHAR(l_inv_item_id));
9660: FND_MESSAGE.set_token('API_NAME' , l_proc_name);
9661: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9662: RAISE FND_API.G_EXC_ERROR;
9663: END IF;
9664: IF l_sys_dt < NVL(l_start_dt,l_sys_dt) OR
9665: l_sys_dt > NVL(l_end_dt ,l_sys_dt)
9666: THEN
9667: FND_MESSAGE.set_name('CS','CS_SR_CP_ITEM_INACTIVE'); --Item associated with CP is inactive
9668: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9669: FND_MESSAGE.set_token('INV_ITEM_ID',TO_CHAR(l_inv_item_id));
9670: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9671: RAISE FND_API.G_EXC_ERROR;
9672: END IF;
9673: -- All validations succeeded, so assign item for CP to p_inventory_item_id
9674: p_inventory_item_id := l_inv_item_id;
9687: THEN
9688: FND_MESSAGE.set_name('CS','CS_SR_CP_LAST_VLD_ORG'); -- Last validation org does not exists
9689: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9690: FND_MESSAGE.set_token('MAINT_ORG_ID',TO_CHAR(l_last_vld_org_id));
9691: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9692: RAISE FND_API.G_EXC_ERROR;
9693: END IF;
9694: /* last vlg org may not be eam enabled but it might be maintained bu maint org
9695: so no need for this check
9696: IF l_eam_enabled_flag <> 'Y'
9697: THEN
9698: FND_MESSAGE.set_name('CS','CS_SR_CP_MAINT_EAM'); -- Maintenance Organization is NOT Eam Enabled
9699: FND_MESSAGE.set_token('MAINT_ORG_ID',TO_CHAR(p_maint_organization_id));
9700: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9701: RAISE FND_API.G_EXC_ERROR;
9702: END IF;
9703: */
9704: IF l_maint_org_id <> p_maint_organization_id
9706: FND_MESSAGE.set_name('CS','CS_SR_LVD_ORG_NOT_MAINT_BY'); -- CP is not maintained by Maintenance Organization
9707: FND_MESSAGE.set_token('API_NAME' , l_proc_name);
9708: FND_MESSAGE.set_token('MAINT_ORG_ID',TO_CHAR(p_maint_organization_id));
9709: FND_MESSAGE.set_token('VAL_ORG_ID',TO_CHAR(l_maint_org_id));
9710: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9711: RAISE FND_API.G_EXC_ERROR;
9712: END IF;
9713: IF p_inv_org_master_org_flag = 'Y' AND
9714: l_master_org_id <> p_inventory_org_id
9714: l_master_org_id <> p_inventory_org_id
9715: THEN
9716: FND_MESSAGE.set_name('CS','CS_SR_VLD_INV_ORG_MISMATCH');
9717: FND_MESSAGE.set_token('VAL_ORG',TO_CHAR(l_maint_org_id));
9718: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9719: RAISE FND_API.G_EXC_ERROR;
9720: END IF;
9721: END IF; -- p_maint_organization check
9722: --
9911: -- -----------------------------------------------------------------------------
9912: -- Procedure Name : update_task_address
9913: -- Parameter :
9914: -- IN : p_incident_id This is a foreign key to table
9915: -- cs_incidents_all_b
9916: -- OUT : p_location_type This is location type of service
9917: -- request
9918: -- : p_location_id This is incident location id of
9919: -- service request
9957: AND NVL(stat.completed_flag, 'N') <> 'Y'
9958: AND NVL(stat.closed_flag , 'N') <> 'Y'
9959: AND NVL(stat.cancelled_flag, 'N') <> 'Y'
9960: ;
9961: l_site_id cs_incidents_all_b.incident_location_id % TYPE;
9962: l_location_id cs_incidents_all_b.incident_location_id % TYPE;
9963: l_msg_count NUMBER;
9964: l_msg_data VARCHAR2(4000);
9965: --
9958: AND NVL(stat.closed_flag , 'N') <> 'Y'
9959: AND NVL(stat.cancelled_flag, 'N') <> 'Y'
9960: ;
9961: l_site_id cs_incidents_all_b.incident_location_id % TYPE;
9962: l_location_id cs_incidents_all_b.incident_location_id % TYPE;
9963: l_msg_count NUMBER;
9964: l_msg_data VARCHAR2(4000);
9965: --
9966: BEGIN
9986: ( 'API_NAME'
9987: , 'CS_SERVICEREQUEST_UTIL.update_task_address'
9988: );
9989: FND_MSG_PUB.ADD_DETAIL
9990: ( p_associated_column1 => 'CS_INCIDENTS_ALL_B.incident_location_id'
9991: );
9992: RAISE FND_API.G_EXC_ERROR;
9993: END IF;
9994: END IF;