[Home] [Help]
PACKAGE: APPS.AHL_OSP_ORDERS_PVT
Source
1 PACKAGE AHL_OSP_ORDERS_PVT AS
2 /* $Header: AHLVOSPS.pls 120.2 2008/01/30 22:37:08 jaramana 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 -- Define Record Types for record structures needed by the APIs --
46 ---------------------------------------------------------------------
47 TYPE OSP_ORDER_REC_TYPE IS RECORD (
48 OPERATION_FLAG VARCHAR2(1),
49 OSP_ORDER_ID NUMBER,
50 OBJECT_VERSION_NUMBER NUMBER,
51 LAST_UPDATE_DATE DATE,
52 LAST_UPDATED_BY NUMBER,
53 CREATION_DATE DATE,
54 CREATED_BY NUMBER,
55 LAST_UPDATE_LOGIN NUMBER,
56 OSP_ORDER_NUMBER NUMBER,
57 ORDER_TYPE_CODE VARCHAR2(30),
58 STATUS_CODE VARCHAR2(30),
59 ORDER_DATE DATE,
60 DESCRIPTION VARCHAR2(2000),
61 OPERATING_UNIT_ID NUMBER,
62 SINGLE_INSTANCE_FLAG VARCHAR2(1),
63 VENDOR_ID NUMBER,
64 VENDOR_NAME VARCHAR2(240),
65 VENDOR_SITE_ID NUMBER,
66 VENDOR_SITE_CODE VARCHAR2(15),
67 VENDOR_CONTACT_ID NUMBER,
68 VENDOR_CONTACT VARCHAR2(60),
69 PO_SYNCH_FLAG VARCHAR2(1),
70 PO_HEADER_ID NUMBER,
71 PO_BATCH_ID NUMBER,
72 PO_REQUEST_ID NUMBER,
73 PO_AGENT_ID NUMBER,
74 BUYER_NAME VARCHAR2(240),
75 PO_INTERFACE_HEADER_ID NUMBER,
76 OE_HEADER_ID NUMBER,
77 CUSTOMER_ID NUMBER,
78 CUSTOMER_NAME VARCHAR2(360),
79 CONTRACT_ID NUMBER,
80 CONTRACT_NUMBER VARCHAR2(120),
81 CONTRACT_TERMS VARCHAR2(256),
82 ATTRIBUTE_CATEGORY VARCHAR2(30),
83 ATTRIBUTE1 VARCHAR2(150),
84 ATTRIBUTE2 VARCHAR2(150),
85 ATTRIBUTE3 VARCHAR2(150),
86 ATTRIBUTE4 VARCHAR2(150),
87 ATTRIBUTE5 VARCHAR2(150),
88 ATTRIBUTE6 VARCHAR2(150),
89 ATTRIBUTE7 VARCHAR2(150),
90 ATTRIBUTE8 VARCHAR2(150),
91 ATTRIBUTE9 VARCHAR2(150),
92 ATTRIBUTE10 VARCHAR2(150),
93 ATTRIBUTE11 VARCHAR2(150),
94 ATTRIBUTE12 VARCHAR2(150),
95 ATTRIBUTE13 VARCHAR2(150),
96 ATTRIBUTE14 VARCHAR2(150),
97 ATTRIBUTE15 VARCHAR2(150),
98 -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
99 PO_REQ_HEADER_ID NUMBER);
100
101 TYPE OSP_ORDER_LINE_REC_TYPE IS RECORD (
102 OPERATION_FLAG VARCHAR2(1),
103 SHIPMENT_CREATION_FLAG VARCHAR2(1),
104 OSP_ORDER_LINE_ID NUMBER,
105 OBJECT_VERSION_NUMBER NUMBER,
106 LAST_UPDATE_DATE DATE,
107 LAST_UPDATED_BY NUMBER,
108 CREATION_DATE DATE,
109 CREATED_BY NUMBER,
110 LAST_UPDATE_LOGIN NUMBER,
111 OSP_ORDER_ID NUMBER,
112 OSP_LINE_NUMBER NUMBER,
113 STATUS_CODE VARCHAR2(30),
114 NEED_BY_DATE DATE,
115 SHIP_BY_DATE DATE,
116 PO_LINE_TYPE_ID NUMBER,
117 PO_LINE_TYPE VARCHAR2(25),
118 PO_LINE_ID NUMBER,
119 OE_SHIP_LINE_ID NUMBER,
120 OE_RETURN_LINE_ID NUMBER,
121 SERVICE_ITEM_ID NUMBER,
122 SERVICE_ITEM_NUMBER VARCHAR2(40),
123 SERVICE_ITEM_DESCRIPTION VARCHAR2(2000),
124 SERVICE_ITEM_UOM_CODE VARCHAR2(3),
125 QUANTITY NUMBER,
126 WORKORDER_ID NUMBER,
127 JOB_NUMBER VARCHAR2(80),
128 OPERATION_ID NUMBER,
129 INVENTORY_ITEM_ID NUMBER,
130 INVENTORY_ORG_ID NUMBER,
131 ITEM_NUMBER VARCHAR2(40),
132 INVENTORY_ITEM_UOM VARCHAR2(3),
133 INVENTORY_ITEM_QUANTITY NUMBER,
134 SUB_INVENTORY VARCHAR2(10),
135 LOT_NUMBER mtl_lot_numbers.lot_number%TYPE,
136 SERIAL_NUMBER VARCHAR2(30),
137 EXCHANGE_INSTANCE_NUMBER VARCHAR2(30),
138 EXCHANGE_INSTANCE_ID NUMBER,
139 ATTRIBUTE_CATEGORY VARCHAR2(30),
140 ATTRIBUTE1 VARCHAR2(150),
141 ATTRIBUTE2 VARCHAR2(150),
142 ATTRIBUTE3 VARCHAR2(150),
143 ATTRIBUTE4 VARCHAR2(150),
144 ATTRIBUTE5 VARCHAR2(150),
145 ATTRIBUTE6 VARCHAR2(150),
146 ATTRIBUTE7 VARCHAR2(150),
147 ATTRIBUTE8 VARCHAR2(150),
148 ATTRIBUTE9 VARCHAR2(150),
149 ATTRIBUTE10 VARCHAR2(150),
150 ATTRIBUTE11 VARCHAR2(150),
151 ATTRIBUTE12 VARCHAR2(150),
152 ATTRIBUTE13 VARCHAR2(150),
153 ATTRIBUTE14 VARCHAR2(150),
154 ATTRIBUTE15 VARCHAR2(150),
155 -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
156 PO_REQ_LINE_ID NUMBER);
157 ----------------------------------------------
158 -- Define Table Type for records structures --
159 ----------------------------------------------
160 TYPE OSP_ORDER_LINES_TBL_TYPE IS TABLE OF OSP_ORDER_LINE_REC_TYPE INDEX BY BINARY_INTEGER;
161
162 /* for debugging this internal procedure
163 TYPE item_service_rel_rec_type IS RECORD (
164 inv_org_id NUMBER,
165 inv_item_id NUMBER,
166 service_item_id NUMBER);
167 TYPE item_service_rels_tbl_type IS TABLE OF item_service_rel_rec_type INDEX BY BINARY_INTEGER;
168 */
169
170 ------------------------
171 -- Declare Procedures --
172 ------------------------
173 -- Start of Comments --
174 -- Procedure name : process_osp_order
175 -- Type : Private
176 -- Function : For a given set of osp order header and lines, will validate and insert/update/delete
177 -- the osp order information.
178 -- Pre-reqs :
179 -- Parameters :
180 --
181 -- Standard IN Parameters :
182 -- p_api_version IN NUMBER Default 1.0
183 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
184 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
185 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
186 -- Standard OUT Parameters :
187 -- x_return_status OUT VARCHAR2 Required
188 -- x_msg_count OUT NUMBER Required
189 -- x_msg_data OUT VARCHAR2 Required
190 --
191 -- p_module_type IN VARCHAR2 Required.
192 --
193 -- This parameter indicates the front-end form interface. The default value is 'JSP'. If the value
194 -- is JSP, then this API clears out all id columns and validations are done using the values based
195 -- on which the Id's are populated.
196 --
197 -- process_osp_order Parameters:
198 --
199 -- p_x_osp_order_rec IN OUT AHL_OSP_ORDERS_PVT.osp_order_rec_type Required
200 -- OSP Order Header record
201 -- p_x_osp_order_lines_tbl IN OUT AHL_OSP_ORDERS_PVT.osp_order_lines_tbl_type Required
202 -- OSP Order Lines
203 --
204 --
205 -- Version :
206 -- Initial Version 1.0
207 --
208 -- End of Comments.
209 /*
210 PROCEDURE process_osp_order_old(
211 p_api_version IN NUMBER := 1.0,
212 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
213 p_commit IN VARCHAR2 := FND_API.G_FALSE,
214 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
215 p_module_type IN VARCHAR2 := NULL,
216 p_x_osp_order_rec IN OUT NOCOPY OSP_ORDER_REC_TYPE,
217 p_x_osp_order_lines_tbl IN OUT NOCOPY OSP_ORDER_LINES_TBL_TYPE,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2);
221 */
222 --This is the new procedure for Inventory Service Orders including work order based
223 PROCEDURE process_osp_order(
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 /* For debugging purpose
236 PROCEDURE derive_default_vendor(
237 p_item_service_rels_tbl IN item_service_rels_tbl_type,
238 x_vendor_id OUT NOCOPY NUMBER,
239 x_vendor_site_id OUT NOCOPY NUMBER,
240 x_vendor_contact_id OUT NOCOPY NUMBER);
241 */
242
243 End AHL_OSP_ORDERS_PVT;