[Home] [Help]
2: /* $Header: csusrb.pls 120.41.12020000.4 2013/05/30 22:08:36 siahmed 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;
4317: l_api_name CONSTANT VARCHAR2(30) := 'Validate_product_Version';
4318: l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
4319: l_dummy NUMBER;
4320: l_rev_control_code NUMBER;
4321: l_product_version cs_incidents_all_b.component_version % type;
4322:
4323: CURSOR c_inv_item
4324: IS SELECT inventory_item_id, inventory_revision
4325: FROM CSI_ITEM_INSTANCES
7032: lx_site_use_id NUMBER; -- used in validate_bill_to_ship_to_site
7033:
7034: -- 3224828 contracts
7035: l_contract_id NUMBER; -- contract id
7036: l_con_number cs_incidents_all_b.contract_number % type;
7037:
7038: -- 3340433
7039: l_inv_change_by_cp VARCHAR2(1):= 'N'; --set when inv_item_id is overwritten by cp
7040: l_inv_id_from_cp NUMBER;
7041: -- 3360274
7042: l_resource_type VARCHAR2(240); -- used in validate_resource_type
7043:
7044: l_inv_org_master_org_flag VARCHAR2(1);
7045: l_maint_organization_id cs_incidents_all_b.maint_organization_id % TYPE;
7046: l_owning_dept_id cs_incidents_all_b.owning_department_id % TYPE;
7047:
7048: BEGIN
7049: -- Initialize return status to success
7042: l_resource_type VARCHAR2(240); -- used in validate_resource_type
7043:
7044: l_inv_org_master_org_flag VARCHAR2(1);
7045: l_maint_organization_id cs_incidents_all_b.maint_organization_id % TYPE;
7046: l_owning_dept_id cs_incidents_all_b.owning_department_id % TYPE;
7047:
7048: BEGIN
7049: -- Initialize return status to success
7050: x_return_status := FND_API.G_RET_STS_SUCCESS;
9192: SLA_DURATION_1 SLA_DURATION_1,
9193: 'N' CHANGE_SLA_DURATION_2_FLAG,
9194: SLA_DURATION_2 OLD_SLA_DURATION_2,
9195: SLA_DURATION_2 SLA_DURATION_2
9196: FROM cs_incidents_all_b
9197: WHERE incident_id = p_request_id;
9198:
9199: l_audit_vals_rec CS_ServiceRequest_PVT.SR_AUDIT_REC_TYPE;
9200: l_api_name CONSTANT VARCHAR2(30) := 'Prepare_Audit_Record';
9213: CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg
9214: ( p_token_an => l_api_name,
9215: p_token_v => TO_CHAR(p_request_id),
9216: p_token_p => 'p_request_id',
9217: p_table_name => 'CS_INCIDENTS_ALL_B',
9218: p_column_name => 'REQUEST_ID' );
9219: CLOSE l_ServiceRequest_csr;
9220: ELSE
9221: CLOSE l_ServiceRequest_csr;
9629: THEN
9630: FND_MESSAGE.set_name('CS','CS_SR_CP_INACTIVE');
9631: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9632: FND_MESSAGE.set_token('API_NAME' , l_proc_name);
9633: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9634: RAISE FND_API.G_EXC_ERROR;
9635: END IF;
9636: --
9637: IF CS_SERVICEREQUEST_PVT.g_restrict_ib = 'YES' AND
9640: FND_MESSAGE.set_name('CS','CS_SR_CP_LOCATION_INVALID'); -- Customer porduct installed location is invalid
9641: FND_MESSAGE.set_token('LOC',l_loc_type);
9642: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9643: FND_MESSAGE.set_token('API_NAME' , l_proc_name);
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));
9653: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9654: RAISE FND_API.G_EXC_ERROR;
9655: END IF;
9656: --
9657: IF p_customer_id <> NVL(l_party_id,-1)
9662: THEN
9663: FND_MESSAGE.set_name('CS','CS_SR_CP_CUST_INVALID'); -- cust not related to product
9664: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9665: FND_MESSAGE.set_token('CUST_ID',TO_CHAR(p_customer_id));
9666: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9667: CLOSE c_prod_rel;
9668: RAISE FND_API.G_EXC_ERROR;
9669: ELSE
9670: CLOSE c_prod_rel;
9691: THEN
9692: FND_MESSAGE.set_name('CS','CS_SR_CP_ITEM_ID'); -- item associated with CP does not exists
9693: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9694: FND_MESSAGE.set_token('INV_ITEM_ID',TO_CHAR(l_inv_item_id));
9695: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9696: RAISE FND_API.G_EXC_ERROR;
9697: END IF;
9698: IF l_enabled_flag <> 'Y'
9699: THEN
9700: FND_MESSAGE.set_name('CS','CS_SR_CP_ITEM_DISABLED'); -- item associated with CP is not enabled
9701: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9702: FND_MESSAGE.set_token('INV_ITEM_ID',TO_CHAR(l_inv_item_id));
9703: FND_MESSAGE.set_token('API_NAME' , l_proc_name);
9704: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9705: RAISE FND_API.G_EXC_ERROR;
9706: END IF;
9707: IF l_serv_req_enabled <> 'E'
9708: THEN
9709: FND_MESSAGE.set_name('CS','CS_SR_CP_ITEM_NOT_SERV'); -- item associated with CP is not serviceable
9710: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9711: FND_MESSAGE.set_token('INV',TO_CHAR(l_inv_item_id));
9712: FND_MESSAGE.set_token('API_NAME' , l_proc_name);
9713: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9714: RAISE FND_API.G_EXC_ERROR;
9715: END IF;
9716: IF l_sys_dt < NVL(l_start_dt,l_sys_dt) OR
9717: l_sys_dt > NVL(l_end_dt ,l_sys_dt)
9718: THEN
9719: FND_MESSAGE.set_name('CS','CS_SR_CP_ITEM_INACTIVE'); --Item associated with CP is inactive
9720: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9721: FND_MESSAGE.set_token('INV_ITEM_ID',TO_CHAR(l_inv_item_id));
9722: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9723: RAISE FND_API.G_EXC_ERROR;
9724: END IF;
9725: -- All validations succeeded, so assign item for CP to p_inventory_item_id
9726: p_inventory_item_id := l_inv_item_id;
9739: THEN
9740: FND_MESSAGE.set_name('CS','CS_SR_CP_LAST_VLD_ORG'); -- Last validation org does not exists
9741: FND_MESSAGE.set_token('CP_ID',TO_CHAR(p_customer_product_id));
9742: FND_MESSAGE.set_token('MAINT_ORG_ID',TO_CHAR(l_last_vld_org_id));
9743: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9744: RAISE FND_API.G_EXC_ERROR;
9745: END IF;
9746: /* last vlg org may not be eam enabled but it might be maintained bu maint org
9747: so no need for this check
9748: IF l_eam_enabled_flag <> 'Y'
9749: THEN
9750: FND_MESSAGE.set_name('CS','CS_SR_CP_MAINT_EAM'); -- Maintenance Organization is NOT Eam Enabled
9751: FND_MESSAGE.set_token('MAINT_ORG_ID',TO_CHAR(p_maint_organization_id));
9752: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9753: RAISE FND_API.G_EXC_ERROR;
9754: END IF;
9755: */
9756: IF l_maint_org_id <> p_maint_organization_id
9758: FND_MESSAGE.set_name('CS','CS_SR_LVD_ORG_NOT_MAINT_BY'); -- CP is not maintained by Maintenance Organization
9759: FND_MESSAGE.set_token('API_NAME' , l_proc_name);
9760: FND_MESSAGE.set_token('MAINT_ORG_ID',TO_CHAR(p_maint_organization_id));
9761: FND_MESSAGE.set_token('VAL_ORG_ID',TO_CHAR(l_maint_org_id));
9762: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9763: RAISE FND_API.G_EXC_ERROR;
9764: END IF;
9765: IF p_inv_org_master_org_flag = 'Y' AND
9766: l_master_org_id <> p_inventory_org_id
9766: l_master_org_id <> p_inventory_org_id
9767: THEN
9768: FND_MESSAGE.set_name('CS','CS_SR_VLD_INV_ORG_MISMATCH');
9769: FND_MESSAGE.set_token('VAL_ORG',TO_CHAR(l_maint_org_id));
9770: FND_MSG_PUB.add_detail(p_associated_column1=> 'CS_INCIDENTS_ALL_B.CUSTOMER_PRODUCT_ID');
9771: RAISE FND_API.G_EXC_ERROR;
9772: END IF;
9773: END IF; -- p_maint_organization check
9774: --
9974: -- -----------------------------------------------------------------------------
9975: -- Procedure Name : update_task_address
9976: -- Parameter :
9977: -- IN : p_incident_id This is a foreign key to table
9978: -- cs_incidents_all_b
9979: -- OUT : p_location_type This is location type of service
9980: -- request
9981: -- : p_location_id This is incident location id of
9982: -- service request
10023: AND NVL(stat.completed_flag, 'N') <> 'Y'
10024: AND NVL(stat.closed_flag , 'N') <> 'Y'
10025: AND NVL(stat.cancelled_flag, 'N') <> 'Y'
10026: ;
10027: l_site_id cs_incidents_all_b.incident_location_id % TYPE;
10028: l_location_id cs_incidents_all_b.incident_location_id % TYPE;
10029: l_msg_count NUMBER;
10030: l_msg_data VARCHAR2(4000);
10031:
10024: AND NVL(stat.closed_flag , 'N') <> 'Y'
10025: AND NVL(stat.cancelled_flag, 'N') <> 'Y'
10026: ;
10027: l_site_id cs_incidents_all_b.incident_location_id % TYPE;
10028: l_location_id cs_incidents_all_b.incident_location_id % TYPE;
10029: l_msg_count NUMBER;
10030: l_msg_data VARCHAR2(4000);
10031:
10032: l_upd_task Varchar2(2) := 'N'; -- Bug 8947959
10064: ( 'API_NAME'
10065: , 'CS_SERVICEREQUEST_UTIL.update_task_address'
10066: );
10067: FND_MSG_PUB.ADD_DETAIL
10068: ( p_associated_column1 => 'CS_INCIDENTS_ALL_B.incident_location_id'
10069: );
10070: RAISE FND_API.G_EXC_ERROR;
10071: END IF;
10072: END IF;