[Home] [Help]
1: PACKAGE BODY CS_SR_PROB_CODE_MAPPING_PKG AS
2: /* $Header: csxpbcdb.pls 120.0 2005/12/12 16:13:25 smisra noship $ */
3:
4: PROCEDURE VALIDATE_PROBLEM_CODE
5: ( p_api_version IN NUMBER,
3:
4: PROCEDURE VALIDATE_PROBLEM_CODE
5: ( p_api_version IN NUMBER,
6: p_init_msg_list IN VARCHAR2,
7: p_probcode_criteria_rec IN CS_SR_PROB_CODE_MAPPING_PKG.probcode_search_rec,
8: p_problem_code IN VARCHAR2,
9: x_return_status OUT NOCOPY VARCHAR2,
10: x_msg_count OUT NOCOPY NUMBER,
11: x_msg_data OUT NOCOPY VARCHAR2
54: cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
55: cslkup.lookup_code = l_problem_code and
56: not exists
57: ( select 'X' from
58: cs_sr_prob_code_mapping_detail cstl
59: where
60: cstl.problem_code = cslkup.lookup_code and
61: (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
62: (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
87: cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
88: cslkup.lookup_code = l_problem_code and
89: exists
90: ( select 'X' from
91: cs_sr_prob_code_mapping_detail cstl
92: where
93: cstl.problem_code = cslkup.lookup_code and
94: (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
95: (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
128: cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
129: cslkup.lookup_code = l_problem_code and
130: exists
131: ( select 'X' from
132: cs_sr_prob_code_mapping_detail cstl
133: where
134: cstl.problem_code = cslkup.lookup_code and
135: (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
136: (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
178: cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
179: cslkup.lookup_code = l_problem_code and
180: exists
181: ( select 'X' from
182: cs_sr_prob_code_mapping_detail cstl
183: where
184: cstl.problem_code = cslkup.lookup_code and
185: (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
186: (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
212: cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
213: cslkup.lookup_code = l_problem_code and
214: exists
215: ( select 'X' from
216: cs_sr_prob_code_mapping_detail cstl
217: where
218: cstl.problem_code = cslkup.lookup_code and
219: (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
220: (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
238: cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
239: cslkup.lookup_code = l_problem_code and
240: exists
241: ( select 'X' from
242: cs_sr_prob_code_mapping_detail cstl
243: where
244: cstl.problem_code = cslkup.lookup_code and
245: (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
246: (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
478: CLOSE cs_sr_pc_catset_with_prod;
479: END IF;
480: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
481: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
482: FND_MSG_PUB.Add_Exc_Msg('CS_SR_PROB_CODE_MAPPING_PKG', l_api_name);
483: END IF;
484: FND_MSG_PUB.Count_And_Get
485: ( p_count => x_msg_count,
486: p_data => x_msg_data
531:
532: l_prob_code_index BINARY_INTEGER;
533:
534: CURSOR c_sr_criteria_exists_csr IS
535: SELECT problem_map_id,start_date_active, end_date_active from cs_sr_prob_code_mapping
536: WHERE incident_type_id = l_service_request_type_id
537: AND category_id = l_product_category_id
538: AND inventory_item_id = l_inventory_item_id
539: AND organization_id = l_organization_id
555: (end_date_active is null or
556: trunc(end_date_active) >= trunc(sysdate));
557:
558: CURSOR c_sr_prob_code_map_exists_csr IS
559: SELECT problem_map_detail_id,start_date_active, end_date_active from cs_sr_prob_code_mapping_detail
560: WHERE problem_map_id = l_problem_map_id
561: AND problem_code = l_problem_code
562: AND (start_date_active is null or trunc(start_date_active) <= trunc(sysdate))
563: AND (end_date_active is null or trunc(end_date_active) >= trunc(sysdate));
578: /* The following validation checks will be executed on the search criteria:
579: #1) the product categroy and product cannot be part of the same search criteria
580: #2) all search criteria attributes should NOT be null in the search criteria
581: #3) Organization Id should not be null IF inventory item is not null
582: #4) the search criteria should not already be present in the CS_SR_PROB_CODE_MAPPING table
583: #5) the product category of the new search criteria, if passed, should belong to the default category set
584: whose value is held in the profile option - CS_SR_DEFAULT_CATEGORY_SET
585: #6) the start date of the new search criteria should NOT be greater than the end date
586: */
641: RAISE fnd_api.g_exc_unexpected_error;
642: END IF;
643:
644: /* All validations have passed for the search criteria. Hence we can create a search criteria record in
645: CS_SR_PROB_CODE_MAPPING table */
646: CS_SR_PROBLEM_CODE_MAPPING_PKG.INSERT_ROW (
647: PX_PROBLEM_MAP_ID => l_problem_map_id,
648: P_INCIDENT_TYPE_ID => l_service_request_type_id,
649: P_INVENTORY_ITEM_ID => l_inventory_item_id,
682: RAISE fnd_api.g_exc_unexpected_error;
683: END IF;
684:
685: /* Now, we need to create the actual problem code -> search criteria mapping details in
686: CS_SR_PROB_CODE_MAPPING_DETAIL table */
687:
688: /* First, the following validation checks :
689: #1) the problem code should be a CS lookup code with lookup type = 'REQUEST_PROBLEM_CODE'
690: #2) the problem code mapping should not already be present. Though this is creation API, it is
725: l_start_date_active >= l_end_date_active) THEN
726: RAISE fnd_api.g_exc_unexpected_error;
727: END IF;
728:
729: /* We can create the problem code mapping in CS_SR_PROB_CODE_MAPPING_DETAIL table now */
730: CS_SR_PROB_CODE_MAP_DETAIL_PKG.INSERT_ROW (
731: PX_PROBLEM_MAP_DETAIL_ID => l_problem_map_detail_id,
732: P_PROBLEM_MAP_ID => l_problem_map_id,
733: P_INCIDENT_TYPE_ID => l_service_request_type_id,
842: END IF;
843: ROLLBACK TO create_mapping_rules;
844: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
845: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
846: FND_MSG_PUB.Add_Exc_Msg('CS_SR_PROB_CODE_MAPPING_PKG', l_api_name);
847: END IF;
848: FND_MSG_PUB.Count_And_Get
849: ( p_count => x_msg_count,
850: p_data => x_msg_data
891: l_prob_code_index BINARY_INTEGER;
892:
893: CURSOR cs_sr_probmapid_exists_csr IS
894: SELECT problem_map_id,incident_type_id,category_id,inventory_item_id,organization_id
895: FROM CS_SR_PROB_CODE_MAPPING
896: WHERE problem_map_id <> l_problem_map_id AND
897: incident_type_id = l_service_request_type_id AND
898: category_id = l_product_category_id AND
899: inventory_item_id = l_inventory_item_id AND
913: (end_date_active is null or
914: trunc(end_date_active) >= trunc(sysdate));
915:
916: CURSOR cs_sr_probcode_mapped_csr IS
917: SELECT problem_code,start_date_active,end_date_active from CS_SR_PROB_CODE_MAPPING_DETAIL
918: WHERE problem_map_id = l_problem_map_id and
919: problem_map_detail_id <> nvl(l_problem_map_detail_id,0) and
920: problem_code = l_problem_code and
921: (start_date_active is null or
1178: l_res_code_index BINARY_INTEGER;
1179:
1180: CURSOR cs_sr_probmapid_crit_csr IS
1181: SELECT problem_map_id, start_date_active, end_date_active
1182: FROM CS_SR_PROB_CODE_MAPPING;
1183: cs_sr_probmapid_crit_rec cs_sr_probmapid_crit_csr%ROWTYPE;
1184:
1185:
1186: CURSOR cs_sr_probmapid_rules_csr IS
1188: map_start_date_active, map_end_date_active,
1189: start_date_active, end_date_active,
1190: incident_type_id, inventory_item_id, organization_id,
1191: category_id, problem_code
1192: FROM CS_SR_PROB_CODE_MAPPING_DETAIL
1193: WHERE
1194: problem_map_id = l_problem_map_id;
1195: cs_sr_probmapid_rules_rec cs_sr_probmapid_rules_csr%ROWTYPE;
1196:
1216:
1217: IF(cs_sr_probmapid_crit_rec.start_date_active is not null AND
1218: nvl(cs_sr_probmapid_rules_rec.map_start_date_active, cs_sr_probmapid_crit_rec.start_date_active+1) <> cs_sr_probmapid_crit_rec.start_date_active) THEN
1219:
1220: update CS_SR_PROB_CODE_MAPPING_DETAIL
1221: set
1222: map_start_date_active = cs_sr_probmapid_crit_rec.start_date_active,
1223: map_end_date_active = cs_sr_probmapid_crit_rec.end_date_active
1224: where
1226: ELSIF (cs_sr_probmapid_crit_rec.start_date_active is null AND
1227: cs_sr_probmapid_crit_rec.end_date_active is not null AND
1228: nvl(cs_sr_probmapid_rules_rec.map_end_date_active, cs_sr_probmapid_crit_rec.end_date_active+1) <> cs_sr_probmapid_crit_rec.end_date_active) THEN
1229:
1230: update CS_SR_PROB_CODE_MAPPING_DETAIL
1231: set
1232: map_start_date_active = cs_sr_probmapid_crit_rec.start_date_active,
1233: map_end_date_active = cs_sr_probmapid_crit_rec.end_date_active
1234: where
1314:
1315:
1316:
1317: -- Enter further code below as specified in the Package spec.
1318: END CS_SR_PROB_CODE_MAPPING_PKG; -- Package Body CS_SR_PROB_CODE_MAPPING_PKG