1 package AHL_PRD_PARTS_CHANGE_PVT AS
2 /* $Header: AHLVPPCS.pls 120.2 2008/02/01 03:30:15 sikumar ship $ */
3 --
4 --
5 -- To modify this template, edit file PKGSPEC.TXT in TEMPLATE
6 -- directory of SQL Navigator
7 --
8 -- Purpose: This package will do parts change processing
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- Sangita Gupta 08/23/02
13 -- --------- ------ ------------------------------------------
14
15
16 Type Ahl_Parts_Rec_Type is Record (
17 Workorder_Id NUMBER
18 ,Operation_Sequence_Num NUMBER
19 ,workorder_operation_id NUMBER
20 ,Unit_Config_Header_Id NUMBER
21 ,Unit_Config_Name VARCHAR2(80)
22 ,Unit_config_obj_ver_num Number --UC_HEADER_OBJ_VER_NUM
23 ,Mc_Relationship_Id NUMBER
24 ,Installed_Instance_Id NUMBER
25 ,Installed_Instance_Num varchar2(30)
26 ,Installed_Quantity NUMBER
27 ,Installation_date DATE
28 ,Parent_Installed_Instance_Id NUMBER
29 ,Parent_Installed_Instance_Num varchar2(30)
30 ,Removed_Instance_Id NUMBER
31 ,Removed_Instance_Num varchar2(30)--see in csi_item_instances
32 ,Removed_Quantity NUMBER
33 ,Removal_Code Varchar2(30)
34 ,Removal_Meaning Varchar2(80)
35 ,Removal_Reason_Id Number
36 ,Removal_Reason_Name varchar2(30)
37 ,Removal_Date Date
38 --,Condition_Id Number
39 --,Condition Varchar2(80)
40 --,Locator_id NUMBER
41 --,Locator_code varchar2(240)
42 --,Subinventory_code VarChar2(10)
43 --,Severity_id Number
44 --,Severity_name Varchar2(30)
45 ,Csi_II_Relationship_Id NUMBER
46 ,CSI_II_OBJECT_VERSION_NUM NUMBER --Rel_Object_Version_Num
47 --,Target_Visit_Num NUMBER
48 --,Target_Visit_Id NUMBER
49 --,Problem_Code varchar2(30)
50 --,Problem_Meaning VARCHAR2(80)
51 ,Operation_Type VARCHAR2(1)
52 --,Summary VarChar2(80)
53 --,estimated_duration number
54 ,Installed_Instance_Obj_Ver_Num NUMBER
55 ,Removed_INSTANCE_OBJ_VER_NUM Number -- not sureif reqd
56 ,Last_update_date DATE
57 ,Last_Update_by NUMBER
58 ,Creation_date DATE
59 ,Created_by NUMBER
60 ,Last_update_login NUMBER
61 --,collection_id NUMBER
62 --,NonRoutine_WO_ID NUMBER
63 --, Material_txn_id Number
64 ,Part_Change_Txn_Id NUMBER
65 ,path_position_id NUMBER
66
67 );
68
69 Type Ahl_Parts_tbl_type is Table of Ahl_Parts_Rec_Type
70 index by Binary_Integer;
71
72 ------------------------
73 -- Declare Procedures --
74 ------------------------
75
76 -- Start of Comments --
77 -- Procedure name : Process_part
78 -- Type : Private
79 -- Function : Manages Parts change operations such as install, swap and return.
80 -- Pre-reqs :
81 -- Parameters :
82 --
83 -- Standard IN Parameters :
84 -- p_api_version IN NUMBER Required
85 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
86 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
87 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
88 -- Standard OUT Parameters :
89 -- x_return_status OUT VARCHAR2 Required
90 -- x_msg_count OUT NUMBER Required
91 -- x_msg_data OUT VARCHAR2 Required
92 --
93 -- Process_Parts Parameters :
94 --
95 -- p_x_parts_rec_tbl IN OUT Ahl_Parts_rec_Type Required
96 -- Unit_Config_Header_Id Optional- Decides whether a part is UC or IB config.
97 -- Unit_Config_Header_Name Optional - Decides whether a part is UC or IB config.
98 -- Target_Visit_Number Optional. Needed when an SR is created.
99 -- Target_Visit_Id Optional. Either number or ID should be sent for an SR creation.
100 -- Problem_Code Required when returning an unserviceable item. If problem meaning exists then derived.
101 -- Problem_Meaning Required when returning an unserviceable item.
102 -- Removed_Instance_Number Required when returning a part.
103 -- Removed_Instance_Id Derivable. Either number or ID should be sent if returning a part.
104 -- MC_Relationship_id Required when removing, installing or swapping a UC part.(Same as position)
105 -- Removal_Code Required when removing a part.
106 -- Removal_Reason_Id Required when removing a part.
107 -- Condition Required when returning.
108 -- Parent_Installed_Instance_Id Required when performing an installation
109 -- Installed_Instance_Id Required when performing an installation
110 -- Relationship_Id Required when performing an installation
111 -- Operation_Sequence_Num Required
112 -- Ahl_Wo_Id Required
113 -- Rel_Object_Version_Number Required for swap or return
114 -- Locator Required if Subinventory is selected
115 -- Subinventory Optional
116 -- Operation_Type C - for Create, S - for Swap and D - for delete.
117 -- Summary Required if creating an SR
118 -- Installed_Instance_Obj_Ver_Num Required if installing a part
119 -- SWAPPED_INSTANCE_OBJ_VER_NUM Required only for swapping. This is the object version number of the replaced item.
120 -- UC_HEADER_OBJ_VER_NUM Required. This is the object version number of the UC header record. This is neded for UC status updates.
121 -- Csi_II_Relationship_Id Required for part return. Must be null for install.
122 -- Last_update_date Required (SYSDATE)
123 -- Last_Update_by Required
124 -- Creation_date Required
125 -- Created_by Required
126 -- Last_update_login Required
127
128 -- NonRoutine_WO_ID Out parameter from SR
129 --
130 -- Material_Txn_id Out parameter from Material Transaction.
131 -- Part_Change_Txn_Id Generated
132
133 --
134 --
135 --
136 -- Version :
137 -- Initial Version 1.0
138 --
139 -- End of Comments.
140
141 Procedure process_part (
142 P_API_Version In Number Default 1.0,
143 P_Init_Msg_List In Varchar2 Default Fnd_API.G_False,
144 P_Commit In Varchar2 Default Fnd_API.G_False,
145 P_Validation_Level In Number Default Fnd_API.G_Valid_Level_Full,
146 p_module_type In VarChar2 Default NULL,
147 p_default In Varchar2 Default FND_API.G_TRUE,
148 p_x_parts_rec_tbl In Out nocopy Ahl_Parts_tbl_type,
149 X_Error_Code Out NOCOPY Varchar2,
150 X_Return_Status Out NOCOPY Varchar2,
151 X_Msg_Count Out NOCOPY Number,
152 X_Msg_Data Out NOCOPY Varchar2,
153 x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type );
154
155 -- Obtain Config header id given the item_instance_id
156 -- Fix for bug# 5564026. Added workorder_id.
157 -- Er# 5660658: Added p_validation_mode parameter. This takes 2 values
158 -- PARTS_CHG and UPDATE_UC.
159 procedure get_unit_config_information(
160 p_item_instance_id In number,
161 p_workorder_id In Number,
162 p_validation_mode In varchar2 := 'PARTS_CHG',
163 x_unit_config_id Out NOCOPY number,
164 x_unit_config_name Out NOCOPY Varchar2,
165 --x_unit_config_obj_version_num Out NOCOPY number,
166 x_return_status Out NOCOPY Varchar2);
167
168
169
170
171 -- Update Material Return txn if item returned using Material Transactions.
172 PROCEDURE Update_Material_Return (p_return_mtl_txn_id IN NUMBER,
173 p_workorder_id IN NUMBER,
174 p_Item_Instance_Id IN NUMBER,
175 x_return_status OUT NOCOPY VARCHAR2);
176
177
178 -- Added for ER 5854712.
179 -- Procedure will return removed instance to Visit-Workorder locator.
180 PROCEDURE ReturnTo_Workorder_Locator( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
181 p_commit IN VARCHAR2 := FND_API.G_FALSE,
182 p_part_change_id IN NUMBER,
183 p_disposition_id IN NUMBER,
184 x_return_status OUT NOCOPY VARCHAR2,
185 x_msg_data OUT NOCOPY VARCHAR2,
186 x_msg_count OUT NOCOPY NUMBER,
187 x_ahl_mtltxn_rec OUT NOCOPY AHL_PRD_MTLTXN_PVT.Ahl_Mtltxn_Rec_Type);
188
189 TYPE move_item_instance_rec_type IS RECORD(
190 instance_id Number,
191 instance_number Varchar2(30),
192 quantity Number,
193 from_workorder_id Number,
194 from_workorder_number Varchar2(80),
195 to_workorder_id Number,
196 to_workorder_number Varchar2(80)
197 );
198
199 TYPE move_item_instance_tbl_type is Table of move_item_instance_rec_type
200 index by Binary_Integer;
201
202 -- Procedure to move item instances between work orders.
203 PROCEDURE move_instance_location(
204 P_API_Version In Number Default 1.0,
205 P_Init_Msg_List In Varchar2 Default Fnd_API.G_False,
206 P_Commit In Varchar2 Default Fnd_API.G_False,
207 P_Validation_Level In Number Default Fnd_API.G_Valid_Level_Full,
208 p_module_type In Varchar2 Default NULL,
209 p_default In Varchar2 Default FND_API.G_TRUE,
210 p_move_item_instance_tbl In AHL_PRD_PARTS_CHANGE_PVT.move_item_instance_tbl_type,
211 X_Return_Status Out NOCOPY Varchar2,
212 X_Msg_Count Out NOCOPY Number,
213 X_Msg_Data Out NOCOPY Varchar2);
214
215 FUNCTION Get_UnitConfig_ID(p_workorder_id IN NUMBER) RETURN NUMBER;
216
217
218 end;---- Package Specification AHL_PRD_PARTS_CHANGE_PVT