[Home] [Help]
1666: p_org_id IN NUMBER)
1667: IS
1668: SELECT DISTINCT MAP.department_id, DEPT.description
1669: FROM BOM_DEPARTMENT_RESOURCES BD, AHL_RESOURCE_MAPPINGS MAP,
1670: BOM_RESOURCES BR, BOM_DEPARTMENTS DEPT
1671: WHERE BD.resource_id = MAP.bom_resource_id
1672: AND BR.resource_id = BD.resource_id
1673: AND BR.organization_id = p_org_id
1674: AND MAP.aso_resource_id = p_aso_resource_id
1900: BR.resource_type,
1901: BR.description,
1902: BR.unit_of_measure
1903: FROM BOM_DEPARTMENT_RESOURCES BDR,
1904: BOM_RESOURCES BR,
1905: AHL_RESOURCE_MAPPINGS MAP
1906: WHERE BDR.department_id=c_dept_id
1907: AND BDR.resource_id=BR.resource_id
1908: AND BR.organization_id=c_org_id
1909: AND BR.resource_id=MAP.bom_resource_id
1910: AND MAP.aso_resource_id=c_aso_resource_id;*/
1911:
1912: --Pekambar Changed the Query for ER # 9014609
1913: --For Reopened issue to add all BOM resources (statement no longer applicable)
1914: /* MANESING::ER 11062672, 03-Jan-2011, in case there is more than 1 BOM resource i.e.
1915: * 1) BOM resource with department same as that of visit task and
1916: * 2) BOM resource with department as null,
1917: * then preference is given to 1st BOM resource
1925: BR.resource_type,
1926: BR.description,
1927: BR.unit_of_measure
1928: FROM BOM_DEPARTMENT_RESOURCES BDR,
1929: BOM_RESOURCES BR,
1930: AHL_RESOURCE_MAPPINGS MAP
1931: WHERE BDR.resource_id = BR.resource_id
1932: AND BR.resource_id = MAP.bom_resource_id
1933: AND MAP.aso_resource_id= c_aso_resource_id
2113: BR.resource_type,
2114: BR.description,
2115: BR.unit_of_measure
2116: FROM BOM_DEPARTMENT_RESOURCES BDR,
2117: BOM_RESOURCES BR,
2118: AHL_RESOURCE_MAPPINGS MAP
2119: WHERE BDR.department_id=c_dept_id
2120: AND BDR.resource_id=BR.resource_id
2121: AND BR.organization_id=c_org_id
2122: AND BR.resource_id=MAP.bom_resource_id
2123: AND MAP.aso_resource_id=c_aso_resource_id;*/
2124:
2125: --Pekambar Changed the Query for ER # 9014609
2126: --For Reopened issue to add all BOM resources (statement no longer applicable)
2127: /* MANESING::ER 11062672, 03-Jan-2011, in case there is more than 1 BOM resource i.e.
2128: * 1) BOM resource with department same as that of visit task and
2129: * 2) BOM resource with department as null,
2130: * then preference is given to 1st BOM resource
2138: BR.resource_type,
2139: BR.description,
2140: BR.unit_of_measure
2141: FROM BOM_DEPARTMENT_RESOURCES BDR,
2142: BOM_RESOURCES BR,
2143: AHL_RESOURCE_MAPPINGS MAP
2144: WHERE BDR.resource_id = BR.resource_id
2145: AND BR.resource_id = MAP.bom_resource_id
2146: AND MAP.aso_resource_id= c_aso_resource_id
2385: -- Start of Comments
2386: -- Procedure name : aggregate_stg_resource_req
2387: -- Type : Private
2388: -- Description : This procedure gets the CMRO Resource Requirements for the Stage Workorder, find the corr.
2389: -- BOM resources, and then depending on whether it's the case of MR creation or deletion,
2390: -- aggregate the existing BOM resources for the Stage Workorder with the new requirements obtained.
2391: --
2392: -- Pre-reqs :
2393: -- Parameters : p_workorder_rec IN PRD_WORKORDER_REC Required
2386: -- Procedure name : aggregate_stg_resource_req
2387: -- Type : Private
2388: -- Description : This procedure gets the CMRO Resource Requirements for the Stage Workorder, find the corr.
2389: -- BOM resources, and then depending on whether it's the case of MR creation or deletion,
2390: -- aggregate the existing BOM resources for the Stage Workorder with the new requirements obtained.
2391: --
2392: -- Pre-reqs :
2393: -- Parameters : p_workorder_rec IN PRD_WORKORDER_REC Required
2394: -- p_nrp_res_req_tbl IN NUMBER_TBL_TYPE Required
2422: RESTYPE.meaning resource_type_name,
2423: c_aso_resource_dur duration
2424:
2425: FROM BOM_DEPARTMENT_RESOURCES BDR,
2426: BOM_RESOURCES BR,
2427: AHL_RESOURCE_MAPPINGS MAP,
2428: MFG_LOOKUPS RESTYPE
2429:
2430: WHERE BDR.resource_id = BR.resource_id
2479: SELECT max(resource_sequence_num)
2480: FROM AHL_OPERATION_RESOURCES
2481: WHERE workorder_operation_id = c_wo_oper_id;
2482:
2483: /* An Associative array for storing BOM resources corresponding to the CMRO resources.
2484: * Basic use is to aggregate the BOM resource durations in case more than 1 CMRO resource gives the same BOM resource.
2485: * Each record in this table contains a BOM resource given by the respective cursor indexed by BOM resource id.
2486: */
2487: TYPE BOM_RESOURCE_TBL_TYPE IS TABLE OF get_bom_resource%ROWTYPE INDEX BY BINARY_INTEGER;
2518: p_workorder_rec.organization_id,
2519: p_workorder_rec.department_id);
2520: FETCH get_bom_resource INTO l_bom_resource_rec;
2521:
2522: /* If same BOM resources are obtained across different CMRO resources, then aggregate their durations.
2523: * This will ensure proper updation of BOM resources in case of MR(s) deletion.
2524: */
2525: IF ( get_bom_resource%FOUND ) THEN
2526:
2519: p_workorder_rec.department_id);
2520: FETCH get_bom_resource INTO l_bom_resource_rec;
2521:
2522: /* If same BOM resources are obtained across different CMRO resources, then aggregate their durations.
2523: * This will ensure proper updation of BOM resources in case of MR(s) deletion.
2524: */
2525: IF ( get_bom_resource%FOUND ) THEN
2526:
2527: l_bom_res_id_indx := l_bom_resource_rec.resource_id;
2757: -- Clear values of previous iteration
2758: l_res_ctr := 0;
2759: l_resource_tbl.DELETE;
2760:
2761: -- Fetch and populate BOM resources in a table
2762: FOR bom_res_rec IN get_oper_bom_res_csr (l_wo_oper_id) LOOP
2763: l_res_ctr := l_res_ctr + 1;
2764: l_resource_tbl(l_res_ctr).operation_resource_id := bom_res_rec.operation_resource_id;
2765: l_resource_tbl(l_res_ctr).object_version_number := bom_res_rec.object_version_number;
2776: FND_MSG_PUB.add;
2777: END IF;
2778: CLOSE check_wo_status_cur;
2779:
2780: -- Delete the BOM resources obtained above
2781: IF ( l_resource_tbl.COUNT > 0 ) THEN
2782:
2783: IF ( G_DEBUG = 'Y' ) THEN
2784: AHL_DEBUG_PUB.debug( l_full_name || ' - Invoking Process_resrc_require API for Removing all the Resource Requirements for Workorder id: ' || p_workorder_tbl(i).workorder_id );