[Home] [Help]
472: x_return_status := FND_API.G_RET_STS_SUCCESS;
473:
474: /* Added condition for date through bug 3238782 */
475:
476: SELECT count(*) INTO l_total FROM bom_inventory_components WHERE
477: bill_sequence_id = p_bom_comp_unexp_rec.bill_sequence_id
478: and sysdate between effectivity_date and
479: nvl(disable_date,sysdate + 1);
480:
746: X_Count NUMBER := 0;
747: CURSOR X_All_Dates IS
748: SELECT 'X' date_available FROM sys.dual
749: WHERE EXISTS (
750: SELECT 1 from BOM_Inventory_Components
751: WHERE Component_Item_Id = X_Member_Item_Id
752: AND Bill_Sequence_Id = X_Bill_Sequence_Id
753: AND Operation_Seq_Num = X_Operation_Seq_Num
754: AND Component_Sequence_Id <> X_Comp_Seq_Id
807: X_Count NUMBER := 0;
808: CURSOR X_All_Numbers IS
809: SELECT 'X' unit_available FROM sys.dual
810: WHERE EXISTS (
811: SELECT 1 from BOM_Inventory_Components
812: WHERE Component_Item_Id = X_Member_Item_Id
813: AND Bill_Sequence_Id = X_Bill_Sequence_Id
814: AND Operation_Seq_Num = X_Operation_Seq_Num
815: AND DISABLE_DATE IS NULL --bug:5347036 Consider only enabled components
2105:
2106: CURSOR c_Op_Seq_Date_Used IS
2107: /* Check same component is not already effective */
2108: SELECT 'Already Used' op_seq_used
2109: FROM bom_inventory_components bic
2110: WHERE bic.bill_sequence_id =
2111: g_rev_comp_Unexp_rec.bill_sequence_id
2112: AND bic.component_item_id =
2113: g_rev_comp_Unexp_rec.component_item_id
2130:
2131: CURSOR c_Op_Seq_Unit_Used IS
2132: /* Check same component is not already effective */
2133: SELECT 'Already Used' op_seq_used
2134: FROM bom_inventory_components bic
2135: WHERE bic.bill_sequence_id =
2136: g_rev_comp_Unexp_rec.bill_sequence_id
2137: AND bic.component_item_id =
2138: g_rev_comp_Unexp_rec.component_item_id
2241: IS
2242: CURSOR c_Unit_Num_Used IS
2243: /* Check same component is not already effective */
2244: SELECT 'Already Used' unit_num_used
2245: FROM bom_inventory_components bic
2246: WHERE bic.bill_sequence_id =
2247: g_rev_comp_Unexp_rec.bill_sequence_id
2248: AND bic.component_item_id =
2249: g_rev_comp_Unexp_rec.component_item_id
3074:
3075: item_num_for_bill number := 0;
3076: CURSOR c_ItemDateNum IS
3077: SELECT 'Valid'
3078: FROM BOM_inventory_components
3079: WHERE item_num = p_rev_component_rec.item_sequence_number
3080: AND component_item_id <> p_rev_comp_unexp_rec.component_item_id
3081: AND trunc(effectivity_date) <=
3082: trunc(p_rev_component_rec.start_effective_date)
3086: AND bill_sequence_id = p_Rev_Comp_Unexp_rec.bill_sequence_id;
3087:
3088: CURSOR c_ItemUnitNum IS
3089: SELECT 'Valid'
3090: FROM BOM_inventory_components
3091: WHERE item_num = p_rev_component_rec.item_sequence_number
3092: AND component_item_id <> p_rev_comp_unexp_rec.component_item_id
3093: AND disable_date is NULL
3094: AND from_end_item_unit_number <=
3136:
3137: l_bill_to_end_number VARCHAR2(30);
3138: CURSOR c_To_End_Item_Number IS
3139: SELECT To_End_Item_Unit_Number
3140: FROM BOM_Inventory_Components
3141: WHERE component_sequence_id =
3142: g_rev_comp_unexp_rec.old_component_sequence_id;
3143:
3144:
3151: IS
3152: SELECT 'Old Comp is invalid'
3153: FROM SYS.DUAL
3154: WHERE NOT EXISTS ( SELECT NULL
3155: FROM BOM_INVENTORY_COMPONENTS ic
3156: WHERE TRUNC(ic.effectivity_date) <=
3157: TRUNC(p_rev_component_rec.start_effective_date)
3158: AND NVL(ic.disable_date,
3159: TRUNC(p_rev_component_rec.start_effective_date)+1)
3161: AND NVL(ic.disable_date , SYSDATE + 1) > SYSDATE
3162: AND NVL(ic.revised_item_sequence_id, -999)
3163: <> NVL(p_rev_comp_unexp_rec.revised_item_sequence_id, -100)
3164: AND NOT EXISTS (SELECT NULL
3165: FROM bom_inventory_components ic2
3166: WHERE ic2.revised_item_sequence_id
3167: = NVL(p_rev_comp_unexp_rec.revised_item_sequence_id,
3168: -888)
3169: AND decode(ic2.implementation_date,
4258: IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Old sequence: ' || to_char(p_rev_comp_Unexp_rec.old_component_sequence_id)); END IF;
4259: BEGIN
4260: SELECT 'Component Implemented'
4261: INTO l_dummy
4262: FROM bom_inventory_components
4263: WHERE component_sequence_id =
4264: p_rev_comp_Unexp_rec.old_component_sequence_id
4265: AND implementation_date IS NOT NULL;
4266:
4510: THEN
4511: BEGIN
4512: SELECT operation_seq_num
4513: INTO l_result
4514: FROM bom_inventory_components
4515: WHERE component_sequence_id =
4516: p_rev_comp_unexp_rec.old_component_sequence_id
4517: AND operation_seq_num =
4518: p_Rev_Component_rec.old_operation_sequence_number;
6538: SELECT 'Component cancelled'
6539: INTO l_dummy
6540: FROM sys.dual
6541: WHERE NOT EXISTS
6542: (SELECT 1 from bom_inventory_components
6543: WHERE component_sequence_id =
6544: p_rev_comp_Unexp_rec.component_sequence_id
6545: )
6546: AND EXISTS (SELECT 1 from eng_revised_components
6715: )
6716: IS
6717: CURSOR c_GetComponent IS
6718: SELECT revised_item_sequence_id
6719: FROM bom_inventory_components
6720: WHERE component_item_id = p_rev_comp_unexp_rec.component_item_id
6721: AND bill_sequence_id = p_rev_comp_unexp_rec.bill_sequence_id
6722: AND operation_seq_num =
6723: p_rev_component_rec.operation_sequence_number
6820: SELECT 1
6821: FROM sys.dual
6822: WHERE NOT EXISTS
6823: ( SELECT component_sequence_id
6824: FROM bom_inventory_components
6825: WHERE component_item_id = p_component_item_id
6826: AND bill_sequence_id = p_bill_sequence_id
6827: AND effectivity_date = p_effectivity_date
6828: AND operation_seq_num = p_operation_seq_num
6837: );
6838:
6839: CURSOR c_CheckDisabled IS
6840: SELECT component_item_id
6841: FROM bom_inventory_components
6842: WHERE component_item_id = p_component_item_id
6843: AND bill_sequence_id = p_bill_sequence_id
6844: AND effectivity_date = p_effectivity_date
6845: AND operation_seq_num = p_operation_seq_num