1 PACKAGE BODY Product_Family_PKG AS
2 /* $Header: BOMPFAPB.pls 120.2 2005/06/21 03:32:41 appldev ship $ */
3
4
5 PROCEDURE Update_PF_Item_Id(X_Inventory_Item_Id NUMBER,
6 X_Organization_Id NUMBER,
7 X_PF_Item_Id NUMBER,
8 X_Trans_Type VARCHAR2,
9 X_Error_Msg IN OUT NOCOPY VARCHAR2,
10 X_Error_Code IN OUT NOCOPY NUMBER) IS
11 BEGIN
12
13 UPDATE MTL_SYSTEM_ITEMS
14 SET Product_Family_Item_Id = X_PF_Item_Id
15 WHERE Inventory_Item_Id = X_Inventory_Item_Id
16 and Organization_id = X_Organization_Id;
17
18 -- All exception handling is done by the trigger
19 -- that is fired due to this Update and this
20 -- Package let it pass thru without intervention.
21
22 END Update_PF_Item_Id;
23
24
25
26 /************** Procedure Delete_PF_Memeber *******************/
27
28 PROCEDURE Delete_PF_Member(X_Member_Item_Id NUMBER,
29 X_Organization_Id NUMBER,
30 X_Bill_Sequence_Id NUMBER,
31 X_Error_Msg IN OUT NOCOPY VARCHAR2,
32 X_Error_Code IN OUT NOCOPY NUMBER) IS
33 X_Err_Msg VARCHAR2(2000);
34 X_Err_Code NUMBER; -- These local variables are for the call made to the other func.
35 BEGIN
36
37 DELETE FROM Bom_Inventory_Components
38 WHERE Component_Item_Id = X_Member_Item_Id
39 AND Bill_Sequence_Id = X_Bill_Sequence_Id; -- This will delete all the records under
40 -- that Bill_Sequence_Id.
41
42 --Once the record has been deleted also NULL the Product_Family_Item_Id.
43
44 Update_PF_Item_Id(X_Inventory_Item_Id => X_Member_Item_Id,
45 X_Organization_Id => X_Organization_Id,
46 X_PF_Item_Id => NULL,
47 X_Trans_Type => 'REMOVE',
48 X_Error_Msg => X_Err_Msg,
49 X_Error_Code => X_Err_Code);
50
51 END Delete_PF_Member;
52
53
54
55 /***************** Function Check_Overlap_Dates *********************/
56
57 FUNCTION Check_Overlap_Dates (X_Effectivity_Date DATE,
58 X_Disable_Date DATE,
59 X_Member_Item_Id NUMBER,
60 X_Bill_Sequence_Id NUMBER,
61 X_Rowid VARCHAR2) RETURN BOOLEAN IS
62 X_Count NUMBER := 0;
63 CURSOR X_All_Dates IS
64 SELECT 'X' date_available FROM sys.dual
65 WHERE EXISTS (
66 SELECT 1 from BOM_Inventory_Components
67 WHERE Component_Item_Id = X_Member_Item_Id
68 AND Bill_Sequence_Id = X_Bill_Sequence_Id
69 AND (( RowId <> X_RowID ) or (X_RowId IS NULL))
70 AND ( X_Disable_Date IS NULL
71 OR ( Trunc(X_Disable_Date) >= Trunc(Effectivity_Date)
72 )
73 )
74 AND ( Trunc(X_Effectivity_Date) <= Trunc(Disable_Date)
75 OR Disable_Date IS NULL
76 )
77 );
78 BEGIN
79
80 FOR X_Date IN X_All_Dates LOOP
81 X_Count := X_Count + 1;
82 END LOOP;
83
84 -- If count <> 0 that means the current date is overlapping with some record.
85 IF X_Count <> 0 THEN
86 RETURN TRUE;
87 ELSE
88 RETURN FALSE;
89 END IF;
90
91 END Check_Overlap_Dates;
92
93 /*********************** Procedure Update_Config_Item **********************/
94
95 PROCEDURE Update_Config_Item(X_PF_Item_Id NUMBER,
96 X_Base_Item_Id NUMBER,
97 X_Organization_Id NUMBER,
98 X_Error_Msg IN OUT NOCOPY VARCHAR2,
99 X_Error_Code IN OUT NOCOPY NUMBER) IS
100
101
102 CURSOR X_Config_Items IS
103 SELECT config_item_id
104 FROM BOM_ATO_Configurations BAC
105 WHERE BAC.Base_Model_Id = X_Base_Item_Id
106 AND BAC.Organization_Id = X_Organization_Id;
107 X_TransType Varchar2(15);
108 X_Err_Msg VarChar2(80);
109 X_Err_Code NUMBER;
110
111 BEGIN
112 -- If the user selects or deselects a MODEL item as a PF Member then update the
113 -- Product_Family_Item_Id with the value of PF_Item_id passed here, which can be
114 -- an actuall value or a NULL. A Null indicates that the user has removed a
115 -- memeber from PF which is of type MODEL.
116
117
118 IF X_PF_Item_Id IS NULL THEN
119
120 X_TransType := 'REMOVE';
121 ELSE
122 X_TransType := 'ADD';
123 END IF;
124
125 --First set the Product_Family_Item_Id for the Base Item itself.
126
127 Update_PF_Item_Id(X_Inventory_Item_Id => X_Base_Item_Id,
128 X_Organization_Id => X_Organization_Id,
129 X_PF_Item_Id => X_PF_Item_Id,
130 X_Trans_Type => X_TransType,
131 X_Error_Msg => X_Err_Msg,
132 X_Error_Code => X_Err_Code);
133
134
135 -- After that loop thru the cursor to Update all the Config Item
136 FOR X_Config IN X_Config_Items LOOP
137 Update_PF_Item_Id(X_Inventory_Item_Id => X_Config.Config_Item_Id,
138 X_Organization_Id => X_Organization_Id,
139 X_PF_Item_Id => X_PF_Item_Id,
140 X_Trans_Type => X_TransType,
141 X_Error_Msg => X_Err_Msg,
142 X_Error_Code => X_Err_Code);
143 END LOOP;
144
145 END Update_Config_Item;
146
147 PROCEDURE GetMemberInfo(p_Organization_id IN NUMBER,
148 p_Component_Item_Id IN NUMBER,
149 x_Bom_Item_Type IN OUT NOCOPY VARCHAR2,
150 x_Forecast_Control IN OUT NOCOPY VARCHAR2,
151 x_Planning_Method IN OUT NOCOPY VARCHAR2
152 )
153 IS
154 CURSOR X_MemberDetail IS
155 SELECT ML1.Meaning Bom_Item_Type,
156 ML2.Meaning Forecast_Control,
157 ML3.Meaning Planning_Method
158 FROM MTL_System_Items MSI,
159 MFG_Lookups ML1,
160 MFG_Lookups ML2,
161 MFG_Lookups ML3
162 WHERE MSI.Inventory_Item_Id = p_Component_Item_Id
163 AND MSI.Organization_Id = p_Organization_Id
164 AND ML1.Lookup_Code(+) = MSI.Bom_Item_Type
165 AND ML1.Lookup_Type(+) = 'BOM_ITEM_TYPE'
166 AND ML2.Lookup_Code(+) = MSI.ATO_Forecast_Control
167 AND ML2.Lookup_Type(+) = 'MRP_ATO_FORECAST_CONTROL'
168 AND ML3.Lookup_Code(+) = MSI.MRP_Planning_Code
169 AND ML3.Lookup_Type(+) = 'MRP_PLANNING_CODE';
170 BEGIN
171 FOR X_Member IN X_MemberDetail LOOP
172 x_Bom_Item_Type := X_Member.Bom_Item_Type;
173 x_Forecast_Control := X_Member.Forecast_Control;
174 x_Planning_Method := X_Member.Planning_Method;
175 END LOOP;
176 END GetMemberInfo;
177
178 FUNCTION Check_Unique(X_Assembly_Item_Id NUMBER,
179 X_Organization_Id NUMBER) RETURN BOOLEAN
180 IS
181 dummy NUMBER;
182 BEGIN
183 SELECT 1 INTO dummy FROM dual
184 WHERE not exists
185 (SELECT 1 FROM bom_bill_of_materials
186 WHERE assembly_item_id = X_Assembly_item_id
187 AND organization_id = X_organization_id
188 );
189 RETURN TRUE;
190 EXCEPTION
191 WHEN no_data_found THEN
192 RETURN FALSE;
193 END Check_Unique;
194
195 END Product_Family_PKG;
[Home] [Help]