[Home] [Help]
PACKAGE: APPS.AHL_OSP_ORDERS_PVT
Source
1 PACKAGE AHL_OSP_ORDERS_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVOSPS.pls 120.3.12020000.2 2012/12/07 01:33:56 sareepar ship $ */
3
4 G_OP_CREATE CONSTANT VARCHAR(1) := 'C';
5 G_OP_UPDATE CONSTANT VARCHAR(1) := 'U';
6 G_OP_DELETE CONSTANT VARCHAR(1) := 'D';
7
8 --YES NO FLAGS
9 G_NO_FLAG CONSTANT VARCHAR2(1) := 'N';
10 G_YES_FLAG CONSTANT VARCHAR2(1) := 'Y';
11
12 --OSP Order Statuses
13 G_OSP_ENTERED_STATUS CONSTANT VARCHAR2(30) := 'ENTERED';
14 G_OSP_SUBMITTED_STATUS CONSTANT VARCHAR2(30) := 'SUBMITTED';
15 G_OSP_SUB_FAILED_STATUS CONSTANT VARCHAR2(30) := 'SUBMISSION_FAILED';
16 G_OSP_PO_CREATED_STATUS CONSTANT VARCHAR2(30) := 'PO_CREATED';
17 G_OSP_CLOSED_STATUS CONSTANT VARCHAR2(30) := 'CLOSED';
18 G_OSP_DELETED_STATUS CONSTANT VARCHAR2(30) := 'HEADER_DELETED';
19
20 -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
21 G_OSP_REQ_SUBMITTED_STATUS CONSTANT VARCHAR2(30) := 'REQ_SUBMITTED';
22 G_OSP_REQ_SUB_FAILED_STATUS CONSTANT VARCHAR2(30) := 'REQ_SUBMISSION_FAILED';
23 G_OSP_REQ_CREATED_STATUS CONSTANT VARCHAR2(30) := 'REQ_CREATED';
24 -- jaramana End
25
26 --OSP Order Line Statuses
27 G_OL_PO_CANCELLED_STATUS CONSTANT VARCHAR2(30) := 'PO_CANCELLED';
28 G_OL_PO_DELETED_STATUS CONSTANT VARCHAR2(30) := 'PO_DELETED';
29 -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
30 G_OL_REQ_CANCELLED_STATUS CONSTANT VARCHAR2(30) := 'REQ_CANCELLED';
31 G_OL_REQ_DELETED_STATUS CONSTANT VARCHAR2(30) := 'REQ_DELETED';
32 --jaramana End
33
34 --OSP ORDER Type codes
35 G_OSP_ORDER_TYPE_SERVICE CONSTANT VARCHAR2(30) := 'SERVICE';
36 G_OSP_ORDER_TYPE_EXCHANGE CONSTANT VARCHAR2(30) := 'EXCHANGE'; --item exchange enhancement
37 G_OSP_ORDER_TYPE_LOAN CONSTANT VARCHAR2(30) := 'LOAN';
38 G_OSP_ORDER_TYPE_BORROW CONSTANT VARCHAR2(30) := 'BORROW';
39
40 -- WORKORDER status codes
41 G_OSP_WO_RELEASED CONSTANT VARCHAR2(1) := '3';
42 G_OSP_WO_CANCELLED CONSTANT VARCHAR2(1) := '7';
43 G_OSP_WO_CLOSED CONSTANT VARCHAR2(2) := '12';
44
45 --Added by jrotich of OSP Receiving project
46 -- OSP ACCOMPLISHMENTS status codes
47 G_OSP_AOA_OPEN CONSTANT VARCHAR2(30) := 'OPEN';
48 G_OSP_AOA_ACCOMPLISHED CONSTANT VARCHAR2(30) := 'ACCOMPLISHED';
49 ---------------------------------------------------------------------
50 -- Define Record Types for record structures needed by the APIs --
51 ---------------------------------------------------------------------
52 TYPE OSP_ORDER_REC_TYPE IS RECORD (
53 OPERATION_FLAG VARCHAR2(1),
54 OSP_ORDER_ID NUMBER,
55 OBJECT_VERSION_NUMBER NUMBER,
56 LAST_UPDATE_DATE DATE,
57 LAST_UPDATED_BY NUMBER,
58 CREATION_DATE DATE,
59 CREATED_BY NUMBER,
60 LAST_UPDATE_LOGIN NUMBER,
61 OSP_ORDER_NUMBER NUMBER,
62 ORDER_TYPE_CODE VARCHAR2(30),
63 STATUS_CODE VARCHAR2(30),
64 ORDER_DATE DATE,
65 DESCRIPTION VARCHAR2(2000),
66 OPERATING_UNIT_ID NUMBER,
67 SINGLE_INSTANCE_FLAG VARCHAR2(1),
68 VENDOR_ID NUMBER,
69 VENDOR_NAME VARCHAR2(240),
70 VENDOR_SITE_ID NUMBER,
71 VENDOR_SITE_CODE VARCHAR2(15),
72 VENDOR_CONTACT_ID NUMBER,
73 VENDOR_CONTACT VARCHAR2(60),
74 PO_SYNCH_FLAG VARCHAR2(1),
75 PO_HEADER_ID NUMBER,
76 PO_BATCH_ID NUMBER,
77 PO_REQUEST_ID NUMBER,
78 PO_AGENT_ID NUMBER,
79 BUYER_NAME VARCHAR2(240),
80 PO_INTERFACE_HEADER_ID NUMBER,
81 OE_HEADER_ID NUMBER,
82 CUSTOMER_ID NUMBER,
83 CUSTOMER_NAME VARCHAR2(360),
84 CONTRACT_ID NUMBER,
85 CONTRACT_NUMBER VARCHAR2(120),
86 CONTRACT_TERMS VARCHAR2(256),
87 ATTRIBUTE_CATEGORY VARCHAR2(30),
88 ATTRIBUTE1 VARCHAR2(150),
89 ATTRIBUTE2 VARCHAR2(150),
90 ATTRIBUTE3 VARCHAR2(150),
91 ATTRIBUTE4 VARCHAR2(150),
92 ATTRIBUTE5 VARCHAR2(150),
93 ATTRIBUTE6 VARCHAR2(150),
94 ATTRIBUTE7 VARCHAR2(150),
95 ATTRIBUTE8 VARCHAR2(150),
96 ATTRIBUTE9 VARCHAR2(150),
97 ATTRIBUTE10 VARCHAR2(150),
98 ATTRIBUTE11 VARCHAR2(150),
99 ATTRIBUTE12 VARCHAR2(150),
100 ATTRIBUTE13 VARCHAR2(150),
101 ATTRIBUTE14 VARCHAR2(150),
102 ATTRIBUTE15 VARCHAR2(150),
103 -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
104 PO_REQ_HEADER_ID NUMBER);
105
106 TYPE OSP_ORDER_LINE_REC_TYPE IS RECORD (
107 OPERATION_FLAG VARCHAR2(1),
108 SHIPMENT_CREATION_FLAG VARCHAR2(1),
109 OSP_ORDER_LINE_ID NUMBER,
110 OBJECT_VERSION_NUMBER NUMBER,
111 LAST_UPDATE_DATE DATE,
112 LAST_UPDATED_BY NUMBER,
113 CREATION_DATE DATE,
114 CREATED_BY NUMBER,
115 LAST_UPDATE_LOGIN NUMBER,
116 OSP_ORDER_ID NUMBER,
117 OSP_LINE_NUMBER NUMBER,
118 STATUS_CODE VARCHAR2(30),
119 NEED_BY_DATE DATE,
120 SHIP_BY_DATE DATE,
121 PO_LINE_TYPE_ID NUMBER,
122 PO_LINE_TYPE VARCHAR2(25),
123 PO_LINE_ID NUMBER,
124 OE_SHIP_LINE_ID NUMBER,
125 OE_RETURN_LINE_ID NUMBER,
126 SERVICE_ITEM_ID NUMBER,
127 SERVICE_ITEM_NUMBER VARCHAR2(40),
128 SERVICE_ITEM_DESCRIPTION VARCHAR2(2000),
129 SERVICE_ITEM_UOM_CODE VARCHAR2(3),
130 QUANTITY NUMBER,
131 WORKORDER_ID NUMBER,
132 JOB_NUMBER VARCHAR2(80),
133 OPERATION_ID NUMBER,
134 INVENTORY_ITEM_ID NUMBER,
135 INVENTORY_ORG_ID NUMBER,
136 ITEM_NUMBER VARCHAR2(40),
137 INVENTORY_ITEM_UOM VARCHAR2(3),
138 INVENTORY_ITEM_QUANTITY NUMBER,
139 SUB_INVENTORY VARCHAR2(10),
140 LOT_NUMBER mtl_lot_numbers.lot_number%TYPE,
141 SERIAL_NUMBER VARCHAR2(30),
142 EXCHANGE_INSTANCE_NUMBER VARCHAR2(30),
143 EXCHANGE_INSTANCE_ID NUMBER,
144 ATTRIBUTE_CATEGORY VARCHAR2(30),
145 ATTRIBUTE1 VARCHAR2(150),
146 ATTRIBUTE2 VARCHAR2(150),
147 ATTRIBUTE3 VARCHAR2(150),
148 ATTRIBUTE4 VARCHAR2(150),
149 ATTRIBUTE5 VARCHAR2(150),
150 ATTRIBUTE6 VARCHAR2(150),
151 ATTRIBUTE7 VARCHAR2(150),
152 ATTRIBUTE8 VARCHAR2(150),
153 ATTRIBUTE9 VARCHAR2(150),
154 ATTRIBUTE10 VARCHAR2(150),
155 ATTRIBUTE11 VARCHAR2(150),
156 ATTRIBUTE12 VARCHAR2(150),
157 ATTRIBUTE13 VARCHAR2(150),
158 ATTRIBUTE14 VARCHAR2(150),
159 ATTRIBUTE15 VARCHAR2(150),
160 -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
161 PO_REQ_LINE_ID NUMBER,
162 --salogan added for supplier warranty starts
163 INSTANCE_ID NUMBER := NULL,
164 WARRANTY_ENTITLEMENT_ID NUMBER := NULL,
165 CONTRACT_NUMBER NUMBER := NULL,
166 ENTITLEMENT_STATUS_CODE VARCHAR2(30) := NULL,
167 INSTANCE_WARRANTY VARCHAR2(1) := NULL);
168 --salogan added for supplier warranty ends
169
170 ----------------------------------------------
171 -- Define Table Type for records structures --
172 ----------------------------------------------
173 TYPE OSP_ORDER_LINES_TBL_TYPE IS TABLE OF OSP_ORDER_LINE_REC_TYPE INDEX BY BINARY_INTEGER;
174
175 /* for debugging this internal procedure
176 TYPE item_service_rel_rec_type IS RECORD (
177 inv_org_id NUMBER,
178 inv_item_id NUMBER,
179 service_item_id NUMBER);
180 TYPE item_service_rels_tbl_type IS TABLE OF item_service_rel_rec_type INDEX BY BINARY_INTEGER;
181 */
182
183 ------------------------
184 -- Declare Procedures --
185 ------------------------
186 -- Start of Comments --
187 -- Procedure name : process_osp_order
188 -- Type : Private
189 -- Function : For a given set of osp order header and lines, will validate and insert/update/delete
190 -- the osp order information.
191 -- Pre-reqs :
192 -- Parameters :
193 --
194 -- Standard IN Parameters :
195 -- p_api_version IN NUMBER Default 1.0
196 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
197 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
198 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
199 -- Standard OUT Parameters :
200 -- x_return_status OUT VARCHAR2 Required
201 -- x_msg_count OUT NUMBER Required
202 -- x_msg_data OUT VARCHAR2 Required
203 --
204 -- p_module_type IN VARCHAR2 Required.
205 --
206 -- This parameter indicates the front-end form interface. The default value is 'JSP'. If the value
207 -- is JSP, then this API clears out all id columns and validations are done using the values based
208 -- on which the Id's are populated.
209 --
210 -- process_osp_order Parameters:
211 --
212 -- p_x_osp_order_rec IN OUT AHL_OSP_ORDERS_PVT.osp_order_rec_type Required
213 -- OSP Order Header record
214 -- p_x_osp_order_lines_tbl IN OUT AHL_OSP_ORDERS_PVT.osp_order_lines_tbl_type Required
215 -- OSP Order Lines
216 --
217 --
218 -- Version :
219 -- Initial Version 1.0
220 --
221 -- End of Comments.
222 /*
223 PROCEDURE process_osp_order_old(
224 p_api_version IN NUMBER := 1.0,
225 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
226 p_commit IN VARCHAR2 := FND_API.G_FALSE,
227 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
228 p_module_type IN VARCHAR2 := NULL,
229 p_x_osp_order_rec IN OUT NOCOPY OSP_ORDER_REC_TYPE,
230 p_x_osp_order_lines_tbl IN OUT NOCOPY OSP_ORDER_LINES_TBL_TYPE,
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_msg_count OUT NOCOPY NUMBER,
233 x_msg_data OUT NOCOPY VARCHAR2);
234 */
235 --This is the new procedure for Inventory Service Orders including work order based
236 PROCEDURE process_osp_order(
237 p_api_version IN NUMBER := 1.0,
238 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
239 p_commit IN VARCHAR2 := FND_API.G_FALSE,
240 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
241 p_module_type IN VARCHAR2 := NULL,
242 p_x_osp_order_rec IN OUT NOCOPY osp_order_rec_type,
243 p_x_osp_order_lines_tbl IN OUT NOCOPY osp_order_lines_tbl_type,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2);
247
248 /* For debugging purpose
249 PROCEDURE derive_default_vendor(
250 p_item_service_rels_tbl IN item_service_rels_tbl_type,
251 x_vendor_id OUT NOCOPY NUMBER,
252 x_vendor_site_id OUT NOCOPY NUMBER,
253 x_vendor_contact_id OUT NOCOPY NUMBER);
254 */
255
256 --salogan added the following for supplier warranty starts
257 ------------------------------------------------------------------------------------
258 -- Start of Comments
259 -- Function name : Can_Show_Claim_Details
260 -- Type : Public
261 -- Function : The Warranty Claim and Status fields will only be displayed if:
262 -- There is a Warranty Contract approved for the OSP Line.
263 -- The OSP Claim checkbox is checked in the Warranty Contract.
264 -- The OSP Order has been submitted.
265 -- Pre-reqs :
266 -- Parameters : p_osp_order_line_id IN NUMBER : Required
267 -- : Returns VARCHAR2 ('Y' or 'N')
268 --
269 -- End of Comments
270
271 FUNCTION Can_Show_Claim_Details(
272 p_osp_order_line_id IN NUMBER
273 ) RETURN VARCHAR2;
274 --salogan added the following for supplier warranty ends
275
276 End AHL_OSP_ORDERS_PVT;