1 PACKAGE ahl_prd_mtltxn_pvt AUTHID CURRENT_USER AS
2 /*$Header: AHLVMTXS.pls 120.9.12020000.2 2012/12/07 00:50:22 sareepar ship $*/
3 G_AHL_SERVICEABLE_CONDITION CONSTANT NUMBER := FND_PROFILE.VALUE('AHL_MTL_MAT_STATUS_SERVICABLE');
4 G_AHL_UNSERVICEABLE_CONDITION CONSTANT NUMBER := FND_PROFILE.VALUE('AHL_MTL_MAT_STATUS_UNSERVICABLE');
5 G_AHL_MRB_CONDITION CONSTANT NUMBER := FND_PROFILE.VALUE('AHL_MTL_MAT_STATUS_MRB');
6
7 Type Ahl_Mtltxn_Rec_Type Is Record (
8 Ahl_mtltxn_Id NUMBER,
9 Workorder_Id NUMBER,
10 Workorder_Name VARCHAR2(80),
11 Workorder_Status VARCHAR2(80),
12 Workorder_Status_Code VARCHAR2(30),
13 Inventory_Item_Id NUMBER,
14 Inventory_Item_Segments VARCHAR2(240),
15 Inventory_Item_Description VARCHAR2(240),
16 Item_Instance_Number varchar2(80),
17 Item_Instance_ID NUMBER,
18 Revision VARCHAR2(3),
19 Organization_Id NUMBER,
20 Condition NUMBER,
21 Condition_desc VARCHAR2(80),
22 Subinventory_Name VARCHAR2(10),
23 Locator_Id NUMBER,
24 Locator_Segments VARCHAR2(240),
25 Quantity NUMBER,
26 -- JKJAIN FP ER # 6436303 start
27 Net_Total_qty NUMBER,
28 -- JKJAIN FP ER # 6436303 ends
29 Net_Quantity Number,
30 Uom VARCHAR2(3),
31 Uom_Desc VARCHAR2(25),
32 Transaction_Type_Id NUMBER,
33 Transaction_Type_Name varchar2(240),
34 Transaction_Reference VARCHAR2(240),
35 Wip_Entity_Id NUMBER,
36 Operation_Seq_Num NUMBER,
37 Serial_Number VARCHAR2(30),
38 Lot_Number mtl_lot_numbers.lot_number%TYPE,
39 Reason_Id NUMBER,
40 Reason_Name VARCHAR2(240),
41 Problem_Code VARCHAR2(30),
42 Problem_Code_Meaning VARCHAR2(80),
43 Target_Visit_Id NUMBER,
44 Sr_Summary VARCHAR2(80),
45 Qa_Collection_Id NUMBER,
46 Workorder_operation_Id NUMBER,
47 Transaction_Date DATE,
48 recepient_id NUMBER,
49 --Modified for Bug 10084704
50 recepient_name VARCHAR2(240),
51 disposition_id NUMBER,
52 disposition_name VARCHAR2(60),
53 -- added for FP bug# 6032494.
54 move_to_project_flag VARCHAR2(1),
55 visit_locator_flag VARCHAR2(1),
56 -- added for FP bug# 5903318.
57 create_wo_option VARCHAR2(30),
58 --Sareepar FP - PIE
59 service_type VARCHAR(80),
60 service_type_code VARCHAR (30),
61 ATTRIBUTE_CATEGORY VARCHAR2(30),
62 ATTRIBUTE1 VARCHAR2(150),
63 ATTRIBUTE2 VARCHAR2(150),
64 ATTRIBUTE3 VARCHAR2(150),
65 ATTRIBUTE4 VARCHAR2(150),
66 ATTRIBUTE5 VARCHAR2(150),
67 ATTRIBUTE6 VARCHAR2(150),
68 ATTRIBUTE7 VARCHAR2(150),
69 ATTRIBUTE8 VARCHAR2(150),
70 ATTRIBUTE9 VARCHAR2(150),
71 ATTRIBUTE10 VARCHAR2(150),
72 ATTRIBUTE11 VARCHAR2(150),
73 ATTRIBUTE12 VARCHAR2(150),
74 ATTRIBUTE13 VARCHAR2(150),
75 ATTRIBUTE14 VARCHAR2(150),
76 ATTRIBUTE15 VARCHAR2(150)
77 );
78
79 Type Prd_Mtltxn_Criteria_Rec Is Record
80 (
81 JOB_NUMBER ahl_workorders.WORKORDER_NAME%TYPE,
82 PRIORITY varchar2(30),
83 ORGANIZATION_NAME ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE,
84 CONCATENATED_SEGMENTS MTL_SYSTEM_ITEMS_B_KFV.CONCATENATED_SEGMENTS %TYPE,
85 REQUESTED_DATE_FROM DATE,
86 REQUESTED_DATE_TO DATE,
87 INCIDENT_NUMBER CS_INCIDENTS_ALL.INCIDENT_NUMBER%TYPE,
88 VISIT_NUMBER NUMBER,
89 DEPARTMENT_NAME BOM_DEPARTMENTS.DESCRIPTION%TYPE,
90 DISPOSITION_NAME AHL_PRD_DISPOSITIONS_V.IMMEDIATE_TYPE%TYPE,
91 TRANSACTION_TYPE NUMBER
92 );
93
94
95 Type Ahl_Mtltxn_Tbl_Type Is Table of Ahl_Mtltxn_Rec_Type index by BINARY_INTEGER;
96
97
98 Type Ahl_Mtl_Txn_Id_tbl is Table of NUMBER index by BINARY_INTEGER;
99
100
101 PROCEDURE PERFORM_MTL_TXN
102 (
103 p_api_version IN NUMBER := 1.0,
104 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
105 p_commit IN VARCHAR2 := FND_API.G_FALSE,
106 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
107 p_default IN VARCHAR2 := FND_API.G_FALSE,
108 p_module_type IN VARCHAR2 := NULL,
109 p_create_sr IN VARCHAR2,
110 p_x_ahl_mtltxn_tbl IN OUT NOCOPY Ahl_Mtltxn_Tbl_Type,
111 x_return_status OUT NOCOPY VARCHAR2,
112 x_msg_count OUT NOCOPY NUMBER,
113 x_msg_data OUT NOCOPY VARCHAR2
114 );
115
116 PROCEDURE VALIDATE_TXN_REC
117 (
118 p_x_ahl_mtltxn_rec IN OUT NOCOPY Ahl_Mtltxn_Rec_Type,
119 x_item_instance_id OUT NOCOPY NUMBER,
120 x_eam_item_type_id OUT NOCOPY NUMBER,
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_msg_data OUT NOCOPY VARCHAR2
124 );
125
126 /*
127 PROCEDURE INSERT_MTL_TXN_TEMP
128 (
129 p_api_version IN NUMBER := 1.0,
130 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
131 p_commit IN VARCHAR2 := FND_API.G_FALSE,
132 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
133 p_default IN VARCHAR2 := FND_API.G_FALSE,
134 p_module_type IN VARCHAR2 := NULL,
135 p_x_ahl_mtltxn_rec IN OUT NOCOPY Ahl_Mtltxn_Rec_Type,
136 x_txn_Hdr_Id OUT NOCOPY NUMBER,
137 x_txn_Tmp_Id OUT NOCOPY NUMBER,
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_msg_count OUT NOCOPY NUMBER,
140 x_msg_data OUT NOCOPY VARCHAR2
141 );
142 */
143
144 PROCEDURE GET_MTL_TRANS_RETURNS
145 (
146 p_api_version IN NUMBER := 1.0,
147 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
148 p_commit IN VARCHAR2 := FND_API.G_FALSE,
149 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
150 p_default IN VARCHAR2 := FND_API.G_FALSE,
151 p_module_type IN VARCHAR2 := NULL,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2,
155 P_prd_Mtltxn_criteria_rec IN Prd_Mtltxn_criteria_rec,
156 x_ahl_mtltxn_tbl IN OUT NOCOPY Ahl_Mtltxn_Tbl_Type
157 );
158
159
160 /* This funciton will get the issued quantity form the trnasactions table*/
161 function GET_ISSUED_QTY(P_ORG_ID IN NUMBER, P_ITEM_ID IN NUMBER, P_WORKORDER_OP_ID IN NUMBER) RETURN NUMBER;
162
163 /* This function will get the onhand quantity for and item */
164 function GET_ONHAND(P_ORG_ID IN NUMBER, P_ITEM_ID IN NUMBER) RETURN NUMBER;
165
166 /* This function will get the onhand quantity for and item */
167 function GET_WORKORD_LEVEL_QTY(p_wid IN NUMBER,p_item_id IN NUMBER,p_org_id IN NUMBER,p_lotnum IN VARCHAR2,p_rev IN VARCHAR2,p_serial_number IN VARCHAR2) RETURN NUMBER;
168 -- JKJAIN FP ER # 6436303 start
169 -- JKJAIN removed p_lotnum,p_rev,p_serial_number for Bug # 7587902
170 --------------------------------------------------------------------------------------
171 -- Function for returning net quantity of material available with
172 -- a workorder.
173 -- Net Total Quantity = Total Quantity Issued - Total quantity returned
174 -- Balaji added this function for OGMA ER # 5948868.
175 --------------------------------------------------------------------------------------
176 function GET_WORKORD_NET_QTY(
177 p_wid IN NUMBER,
178 p_item_id IN NUMBER,
179 p_org_id IN NUMBER
180 ) RETURN NUMBER;
181 -- JKJAIN FP ER # 6436303 - end
182
183 -- pdoki made this API public so that it can be called from other packages as part of Component Mx Enhancement.
184 -- Added for FP ER 6447935.
185 -- breakup input locator concatenated segments to populate MTI.
186 PROCEDURE Get_MTL_LocatorSegs (p_concat_segs IN VARCHAR2,
187 p_organization_id IN NUMBER,
188 p_x_mti_seglist IN OUT NOCOPY fnd_flex_ext.SegmentArray,
189 x_return_status OUT NOCOPY VARCHAR2);
190
191
192 -- pdoki added for Marshalling Enhancement, Start.
193 ------------------------------------------------------------------------------------
194 -- Start of Comments
195 -- Function name : Check_Rts_Workorder_Exists
196 -- Type : Public
197 -- Function : Function to check, if there exists RTS workorder in the same visit as of the given workorder and for the given instance.
198 -- Returns 'Y' if it has atleast one RTS Workorder, Oherwise 'N'.
199 --
200 -- Pre-reqs :
201 -- Parameters :
202 --
203 -- Check_Rts_Workorder_Exists Parameters:
204 -- p_workorder_id IN Given Workorder Id Required
205 -- p_instance_id IN Given Instance Id Required
206 --
207 -- End of Comments
208 -------------------------------------------------------------------------------------
209 FUNCTION Check_Rts_Workorder_Exists (
210 p_workorder_id IN NUMBER,
211 p_instance_id IN NUMBER
212 ) RETURN VARCHAR2;
213 -- pdoki added for Marshalling Enhancement, End.
214
215 END AHL_PRD_MTLTXN_PVT ;
[Home] [Help]