1 PACKAGE AHL_OSP_PO_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVOPPS.pls 115.1 2002/12/04 19:22:47 jaramana noship $ */
3
4 ---------------------------------------------------------------------
5 -- Define Record Types for record structures needed by the APIs --
6 ---------------------------------------------------------------------
7
8 ----------------------------------------------
9 -- Define Table Type for records structures --
10 ----------------------------------------------
11
12 ------------------------
13 -- Declare Procedures --
14 ------------------------
15
16 -- Start of Comments --
17 -- Procedure name : Create_Purchase_Order
18 -- Type : Private
19 -- Function : Validates OSP Information and inserts records into PO Interface tables
20 -- Launches Concurrent Program to initiate PO creation
21 -- Updates OSP table with request id batch id and interface header id
22 -- Pre-reqs :
23 -- Parameters :
24 --
25 -- Standard IN Parameters :
26 -- p_api_version IN NUMBER Required
27 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
28 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
29 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
30 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
31 -- p_module_type IN VARCHAR2 Default NULL.
32 --
33 -- Standard OUT Parameters :
34 -- x_return_status OUT VARCHAR2 Required
35 -- x_msg_count OUT NUMBER Required
36 -- x_msg_data OUT VARCHAR2 Required
37 --
38 -- Create_Purchase_Order Parameters:
39 -- p_osp_order_id IN NUMBER Required only if Number is null
40 -- The Id of the OSP Order for which to create the Purchase Order
41 -- p_osp_order_number IN NUMBER Required only if Id is null
42 -- The number of the OSP Order for which to create the Purchase Order
43 -- x_batch_id OUT NUMBER Required
44 -- Contains the batch id if the concurrent program was launched successfuly.
45 -- x_request_id OUT NUMBER Required
46 -- Contains the concurrent request id if the concurrent program was launched successfuly.
47 -- x_interface_header_id OUT NUMBER Required
48 -- Contains the interface header id generated for the po_headers_interface table.
49 --
50 --
51 -- Version :
52 -- Initial Version 1.0
53 --
54 -- End of Comments.
55
56 PROCEDURE Create_Purchase_Order
57 (
58 p_api_version IN NUMBER,
59 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
60 p_commit IN VARCHAR2 := FND_API.G_FALSE,
61 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
62 p_default IN VARCHAR2 := FND_API.G_TRUE,
63 p_module_type IN VARCHAR2 := NULL,
64 p_osp_order_id IN NUMBER := NULL, -- Required if Number is not given
65 p_osp_order_number IN NUMBER := NULL, -- Required if Id is not given
66 x_batch_id OUT NOCOPY NUMBER,
67 x_request_id OUT NOCOPY NUMBER,
68 x_interface_header_id OUT NOCOPY NUMBER,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2
72 );
73
74 -- Start of Comments --
75 -- Procedure name : Associate_OSP_With_PO
76 -- Type : Private
77 -- Function : Updates AHL_OSP_ORDERS_B.PO_HEADER_ID and
78 -- AHL_OSP_ORDER_LINES.PO_LINE_ID with PO_HEADER_ID and
79 -- PO_LINE_ID respectively for a single submitted OSP Order.
80 -- Does not give error if the OSP Order is already associated
81 -- or if there is no corresponding PO yet.
82 -- Pre-reqs :
83 -- Parameters :
84 --
85 -- Standard IN Parameters :
86 -- p_api_version IN NUMBER Required
87 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
88 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
89 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
90 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
91 -- p_module_type IN VARCHAR2 Default NULL.
92 --
93 -- Standard OUT Parameters :
94 -- x_return_status OUT VARCHAR2 Required
95 -- x_msg_count OUT NUMBER Required
96 -- x_msg_data OUT VARCHAR2 Required
97 --
98 -- Associate_New_PO_Lines Parameters:
99 -- p_osp_order_id IN NUMBER Required
100 -- Id of the OSP Order containing the PO Lines
101 -- x_po_header_id OUT NUMBER Required
102 -- Id of the associated PO Header if the association succeeded.
103 --
104 --
105 -- Version :
106 -- Initial Version 1.0
107 --
108 -- End of Comments.
109
110 PROCEDURE Associate_OSP_With_PO
111 (
112 p_api_version IN NUMBER,
113 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
114 p_commit IN VARCHAR2 := FND_API.G_FALSE,
115 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
116 p_default IN VARCHAR2 := FND_API.G_TRUE,
117 p_module_type IN VARCHAR2 := NULL,
118 p_osp_order_id IN NUMBER,
119 x_po_header_id OUT NOCOPY NUMBER,
120 x_return_status OUT NOCOPY VARCHAR2,
121 x_msg_count OUT NOCOPY NUMBER,
122 x_msg_data OUT NOCOPY VARCHAR2
123 );
124
125 -- Start of Comments --
126 -- Procedure name : Associate_All_OSP_POs
127 -- Type : Private
128 -- Function : Updates AHL_OSP_ORDERS_B.PO_HEADER_ID and
129 -- AHL_OSP_ORDER_LINES.PO_LINE_ID with PO_HEADER_ID and
130 -- PO_LINE_ID respectively for all submitted OSP Orders.
131 -- Pre-reqs :
132 -- Parameters :
133 --
134 -- Standard IN Parameters :
135 -- p_api_version IN NUMBER Required
136 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
137 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
138 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
139 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
140 -- p_module_type IN VARCHAR2 Default NULL.
141 --
142 -- Standard OUT Parameters :
143 -- x_return_status OUT VARCHAR2 Required
144 -- x_msg_count OUT NUMBER Required
145 -- x_msg_data OUT VARCHAR2 Required
146 --
147 -- Version :
148 -- Initial Version 1.0
149 --
150 -- End of Comments.
151
152 PROCEDURE Associate_All_OSP_POs
153 (
154 p_api_version IN NUMBER,
155 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
156 p_commit IN VARCHAR2 := FND_API.G_FALSE,
157 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
158 p_default IN VARCHAR2 := FND_API.G_TRUE,
159 p_module_type IN VARCHAR2 := NULL,
160 x_return_status OUT NOCOPY VARCHAR2,
161 x_msg_count OUT NOCOPY NUMBER,
162 x_msg_data OUT NOCOPY VARCHAR2
163 );
164
165 -- Start of Comments --
166 -- Procedure name : PO_Synch_All_OSPs
167 -- Type : Private
168 -- Function : Synchronizes all OSPs based on the PO Status
169 -- 1. Handles successfully completed PO Submissions (Updates OSP tables)
170 -- 2. Handles failed PO Submissions (Updates OSP Status)
171 -- 3. Handles cancelled PO Lines (Updates OSP Line status, delete shipments)
172 -- 4. Handles deleted PO Lines (Updates OSP Line status, delete shipments)
173 -- 5. Handles Approved POs (Books Shipment, notifies shipper?)
174 -- Pre-reqs :
175 -- Parameters :
176 --
177 -- Standard IN Parameters :
178 -- p_api_version IN NUMBER Required
179 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
180 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
181 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
182 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
183 -- p_module_type IN VARCHAR2 Default NULL.
184 --
185 -- Standard OUT Parameters :
186 -- x_return_status OUT VARCHAR2 Required
187 -- x_msg_count OUT NUMBER Required
188 -- x_msg_data OUT VARCHAR2 Required
189 -- p_concurrent_flag IN VARCHAR2 Default N.
190 --
191 -- PO_Synch_All_OSPs Parameters:
192 -- p_concurrent_flag IN VARCHAR2 Default N.
193 -- Writes debug Information to Concurrent Program's Log File if set to 'Y'
194 --
195 -- Version :
196 -- Initial Version 1.0
197 --
198 -- End of Comments.
199
200 PROCEDURE PO_Synch_All_OSPs
201 (
202 p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
204 p_commit IN VARCHAR2 := FND_API.G_FALSE,
205 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
206 p_default IN VARCHAR2 := FND_API.G_TRUE,
207 p_module_type IN VARCHAR2 := NULL,
208 p_concurrent_flag IN VARCHAR2 := 'N',
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2
212 );
213
214 -- This function determines if the specified Purchase Order is closed
215 -- Returns 'Y' if the PO is Closed, 'N' if not.
216 -- If the PO Id is invalid, 'N' is returned and the Error Message is set.
217 FUNCTION Is_PO_Closed(p_po_header_id IN NUMBER) RETURN VARCHAR2;
218
219 -- This function determines if the specified OSP Order has any new PO Line
220 -- Returns 'Y' if the present, 'N' if not.
221 FUNCTION Has_New_PO_Line(p_osp_order_id IN NUMBER) RETURN VARCHAR2;
222
223 End AHL_OSP_PO_PVT;