[Home] [Help]
PACKAGE: APPS.AHL_PRD_MTLTXN_PVT
Source
1 PACKAGE ahl_prd_mtltxn_pvt AS
2 /*$Header: AHLVMTXS.pls 120.4.12010000.4 2008/11/26 11:28:43 jkjain 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 recepient_name VARCHAR2(60),
50 disposition_id NUMBER,
51 disposition_name VARCHAR2(60),
52 -- added for FP bug# 6032494.
53 move_to_project_flag VARCHAR2(1),
54 visit_locator_flag VARCHAR2(1),
55 -- added for FP bug# 5903318.
56 create_wo_option VARCHAR2(30),
57 ATTRIBUTE_CATEGORY VARCHAR2(30),
58 ATTRIBUTE1 VARCHAR2(150),
59 ATTRIBUTE2 VARCHAR2(150),
60 ATTRIBUTE3 VARCHAR2(150),
61 ATTRIBUTE4 VARCHAR2(150),
62 ATTRIBUTE5 VARCHAR2(150),
63 ATTRIBUTE6 VARCHAR2(150),
64 ATTRIBUTE7 VARCHAR2(150),
65 ATTRIBUTE8 VARCHAR2(150),
66 ATTRIBUTE9 VARCHAR2(150),
67 ATTRIBUTE10 VARCHAR2(150),
68 ATTRIBUTE11 VARCHAR2(150),
69 ATTRIBUTE12 VARCHAR2(150),
70 ATTRIBUTE13 VARCHAR2(150),
71 ATTRIBUTE14 VARCHAR2(150),
72 ATTRIBUTE15 VARCHAR2(150)
73 );
74
75 Type Prd_Mtltxn_Criteria_Rec Is Record
76 (
77 JOB_NUMBER ahl_workorders.WORKORDER_NAME%TYPE,
78 PRIORITY varchar2(30),
79 ORGANIZATION_NAME ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE,
80 CONCATENATED_SEGMENTS MTL_SYSTEM_ITEMS_B_KFV.CONCATENATED_SEGMENTS %TYPE,
81 REQUESTED_DATE_FROM DATE,
82 REQUESTED_DATE_TO DATE,
83 INCIDENT_NUMBER CS_INCIDENTS_ALL.INCIDENT_NUMBER%TYPE,
84 VISIT_NUMBER NUMBER,
85 DEPARTMENT_NAME BOM_DEPARTMENTS.DESCRIPTION%TYPE,
86 DISPOSITION_NAME AHL_PRD_DISPOSITIONS_V.IMMEDIATE_TYPE%TYPE,
87 TRANSACTION_TYPE NUMBER
88 );
89
90
91 Type Ahl_Mtltxn_Tbl_Type Is Table of Ahl_Mtltxn_Rec_Type index by BINARY_INTEGER;
92
93
94 Type Ahl_Mtl_Txn_Id_tbl is Table of NUMBER index by BINARY_INTEGER;
95
96
97 PROCEDURE PERFORM_MTL_TXN
98 (
99 p_api_version IN NUMBER := 1.0,
100 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
101 p_commit IN VARCHAR2 := FND_API.G_FALSE,
102 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
103 p_default IN VARCHAR2 := FND_API.G_FALSE,
104 p_module_type IN VARCHAR2 := NULL,
105 p_create_sr IN VARCHAR2,
106 p_x_ahl_mtltxn_tbl IN OUT NOCOPY Ahl_Mtltxn_Tbl_Type,
107 x_return_status OUT NOCOPY VARCHAR2,
108 x_msg_count OUT NOCOPY NUMBER,
109 x_msg_data OUT NOCOPY VARCHAR2
110 );
111
112 PROCEDURE VALIDATE_TXN_REC
113 (
114 p_x_ahl_mtltxn_rec IN OUT NOCOPY Ahl_Mtltxn_Rec_Type,
115 x_item_instance_id OUT NOCOPY NUMBER,
116 x_eam_item_type_id OUT NOCOPY NUMBER,
117 x_return_status OUT NOCOPY VARCHAR2,
118 x_msg_count OUT NOCOPY NUMBER,
119 x_msg_data OUT NOCOPY VARCHAR2
120 );
121
122 /*
123 PROCEDURE INSERT_MTL_TXN_TEMP
124 (
125 p_api_version IN NUMBER := 1.0,
126 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
127 p_commit IN VARCHAR2 := FND_API.G_FALSE,
128 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
129 p_default IN VARCHAR2 := FND_API.G_FALSE,
130 p_module_type IN VARCHAR2 := NULL,
131 p_x_ahl_mtltxn_rec IN OUT NOCOPY Ahl_Mtltxn_Rec_Type,
132 x_txn_Hdr_Id OUT NOCOPY NUMBER,
133 x_txn_Tmp_Id OUT NOCOPY NUMBER,
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_msg_count OUT NOCOPY NUMBER,
136 x_msg_data OUT NOCOPY VARCHAR2
137 );
138 */
139
140 PROCEDURE GET_MTL_TRANS_RETURNS
141 (
142 p_api_version IN NUMBER := 1.0,
143 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
144 p_commit IN VARCHAR2 := FND_API.G_FALSE,
145 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
146 p_default IN VARCHAR2 := FND_API.G_FALSE,
147 p_module_type IN VARCHAR2 := NULL,
148 x_return_status OUT NOCOPY VARCHAR2,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_msg_data OUT NOCOPY VARCHAR2,
151 P_prd_Mtltxn_criteria_rec IN Prd_Mtltxn_criteria_rec,
152 x_ahl_mtltxn_tbl IN OUT NOCOPY Ahl_Mtltxn_Tbl_Type
153 );
154
155
156 /* This funciton will get the issued quantity form the trnasactions table*/
157 function GET_ISSUED_QTY(P_ORG_ID IN NUMBER, P_ITEM_ID IN NUMBER, P_WORKORDER_OP_ID IN NUMBER) RETURN NUMBER;
158
159 /* This function will get the onhand quantity for and item */
160 function GET_ONHAND(P_ORG_ID IN NUMBER, P_ITEM_ID IN NUMBER) RETURN NUMBER;
161
162 /* This function will get the onhand quantity for and item */
163 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;
164 -- JKJAIN FP ER # 6436303 start
165 -- JKJAIN removed p_lotnum,p_rev,p_serial_number for Bug # 7587902
166 --------------------------------------------------------------------------------------
167 -- Function for returning net quantity of material available with
168 -- a workorder.
169 -- Net Total Quantity = Total Quantity Issued - Total quantity returned
170 -- Balaji added this function for OGMA ER # 5948868.
171 --------------------------------------------------------------------------------------
172 function GET_WORKORD_NET_QTY(
173 p_wid IN NUMBER,
174 p_item_id IN NUMBER,
175 p_org_id IN NUMBER
176 ) RETURN NUMBER;
177 -- JKJAIN FP ER # 6436303 - end
178
179 END AHL_PRD_MTLTXN_PVT ;