1 PACKAGE cto_auto_procure_pk AUTHID CURRENT_USER AS
2 /*$Header: CTOPROCS.pls 120.8 2012/01/24 11:47:30 abhissri ship $ */
3 /*============================================================================+
4 | Copyright (c) 1999 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8 | |
9 | FILE NAME : CTOPROCS.pls |
10 | DESCRIPTION: |
11 | Contain all CTO and WF related APIs for AutoCreate Purchase |
12 | Requisitions. This Package creates the following |
13 | Procedures |
14 | 1. AUTO_CREATE_PUR_REQ_CR |
15 | 2. POPULATE_REQ_INTERFACE |
16 | Functions |
17 | 1. GET_RESERVED_QTY |
18 | 2. GET_NEW_ORDER_QTY |
19 | HISTORY : |
20 | 20-Sep-2001 : RaviKumar V Addepalli Initial version |
21 |
22 |
23 |13-AUG-2003 Kiran Konada
24 | for bug# 3063156
25 | Propagate bugfix bugfix 3042904 to main.
26 | Chnaged the signature of populate_req_interface
27 | to pass project_id and task_id for lower-level buy items
28 | Dependent files: CTOWFAPB.pls
29 | CTOPROCB.pls
30 | CTOSUBSB.pls (only for I customers)
31 |
32 |
33 |
34 ||03-NOV-2003 Kiran Konada
35 | Main propagation bug#3140641
36 |
37 | revrting bufix 3042904 (main bug 3063156)with bug#3129117
38 | ie have reverted changes made on |13-AUG-2003
39 | Removed project_id and task_id as parameters
40 | Hence dependency mentioned in 3042904 has been REMOVED
41 | ie following files are not dependent as on 13-AUG-2003
42 | CTOWFAPB.pls
43 | CTOPROCB.pls
44 | CTOSUBSB.pls (only for I customers)
45 |
46 |01-Jun-2005 Renga Kannan
47 | Added NOCOPY hint to all out parameters.
48 |
49 =============================================================================*/
50
51 -- CTO_AUTO_PROCURE_PK
52 -- following parameters are created for
53 g_pkg_name CONSTANT VARCHAR2(30) := 'Test package';
54 gMrpAssignmentSet NUMBER ;
55
56
57 -- rkaza. ireq project. 05/05/2005.
58 -- A new record for passing source type and org into populate_req_interface.
59 TYPE req_interface_input_data IS RECORD(
60 source_type number,
61 sourcing_org number,
62 secondary_qty number, --OPM
63 secondary_uom VARCHAR2 (3) ,--OPM
64 grade VARCHAR2 (25) --OPM
65 );
66
67
68 -- rkaza. 05/06/2005. Introduced this procedure for ireq project.
69 -- Start of comments
70 -- API name : get_need_by_date
71 -- Type : Public
72 -- Pre-reqs : None.
73 -- Function : Given item id, org id, SSD and source type (1 or 3), it returns
74 -- need by date for the item. Used for external and internal reqs
75 -- Parameters:
76 -- IN : p_source_type IN NUMBER Required
77 -- 1 or 3 (external or internal req).
78 -- p_item_id IN NUMBER Required
79 -- p_org_id IN NUMBER Required
80 -- ship from org id
81 -- Version : Current version 115.20
82 -- Added this description
83 -- Initial version 115.17
84 -- End of comments
85
86 PROCEDURE get_need_by_date(p_source_type IN NUMBER,
87 p_item_id IN NUMBER,
88 p_org_id IN NUMBER,
89 p_schedule_ship_date IN DATE,
90 x_need_by_date OUT NOCOPY DATE,
91 x_return_status OUT NOCOPY VARCHAR2);
92
93
94 -- rkaza. ireq project. 05/05/2005.
95 -- Added new conc program parameter p_create_req_type that specifies whether
96 -- to create internal or external reqs or both.
97 -- This parameter will have a default null for backward compatibility in case
98 -- of prescheduled program runs without this parameter.
99
100 /**************************************************************************
101 Procedure: AUTO_CREATE_PUR_REQ_CR
102 Parameters: p_sales_order NUMBER -- Sales Order number.
103 p_dummy_field VARCHAR2 -- Dummy field for the Concurrent Request.
104 p_sales_order_line_id NUMBER -- Sales Order Line number.
105 p_organization_id VARCHAR2 -- Ship From Organization ID.
106 current_organization_id NUMBER -- Current Org ID
107 p_offset_days NUMBER -- Offset days.
108 p_create_req_type NUMBER -- specifies whether to create ext and int reqs or both (1,2,3 respectively).
109
110 Description: This procedure is called from the concurrent progran to run the
111 AutoCreate Purchase Requisitions.
112 *****************************************************************************/
113 PROCEDURE auto_create_pur_req_cr (
114 errbuf OUT NOCOPY VARCHAR2,
115 retcode OUT NOCOPY VARCHAR2,
116 p_sales_order NUMBER,
117 p_dummy_field VARCHAR2,
118 p_sales_order_line_id NUMBER,
119 p_organization_id VARCHAR2,
120 current_organization_id NUMBER, -- VARCHAR2,
121 p_offset_days NUMBER,
122 p_create_req_type NUMBER default null);
123
124
125
126 -- rkaza. 05/05/2005. Added new parameter of type req_interface_input_data
127 -- record. This is for passing source_type and source_org.
128 /**************************************************************************
129 Procedure: POPULATE_REQ_INTERFACE
130 Parameters: p_destination_org_id NUMBER -- PO Destination Org ID
131 p_org_id NUMBER --
132 p_created_by NUMBER -- Created By for preparor ID
133 p_need_by_date DATE -- Need by date
134 p_order_quantity NUMBER -- Order Quantity
135 p_order_uom VARCHAR2 -- Order Unit Of Measure
136 p_item_id NUMBER -- Inventory Item Id on the SO line.
137 p_item_revision VARCHAR2 -- Item Revisionon the SO Line.
138 p_interface_source_line_id NUMBER -- Interface Source Line ID
139 p_unit_price NUMBER -- Unit Price on the SO Line.
140 p_batch_id NUMBER -- Batch ID for the Req-Import
141 p_order_number VARCHAR2 -- Sales Order Number.
142 p_req_interface_input_data req_interface_input_data -- a record structure for any other IN parameters
143 x_return_status OUT VARCHAR2 -- Return Status.
144
145 Description: This procedure is called from the concurrent program
146 and the Workflow to create the records in the
147 req-interface table based on the line ID passed in to these procedures.
148 *****************************************************************************/
149 PROCEDURE populate_req_interface(
150 p_interface_source_code VARCHAR2, --added this parameter for mlsupply enhancemnet , kkonada
151 p_destination_org_id NUMBER,
152 p_org_id NUMBER,
153 p_created_by NUMBER,
154 p_need_by_date DATE,
155 p_order_quantity NUMBER,
156 p_order_uom VARCHAR2,
157 p_item_id NUMBER,
158 p_item_revision VARCHAR2,
159 p_interface_source_line_id NUMBER,
160 p_unit_price NUMBER,
161 p_batch_id NUMBER,
162 p_order_number VARCHAR2,
163 p_req_interface_input_data req_interface_input_data,
164 x_return_status OUT NOCOPY VARCHAR2 );
165
166 /**************************************************************************
167 Function : GET_RESERVED_QTY
168 Parameters : p_line_id NUMBER
169 Return Value : Number
170 Description : This procedure is called from the concurrent program to
171 get the the reserved quantity on the sales Order line.
172 *****************************************************************************/
173 FUNCTION get_reserved_qty (
174 p_line_id NUMBER) RETURN NUMBER;
175
176
177 /**************************************************************************
178 Function : GET_NEW_ORDER_QTY
179 Parameters : p_interface_source_line_id NUMBER -- Sales Order Linae ID.
180 p_order_qty NUMBER -- Sales Order Order_quantity.
181 p_cancelled_qty NUMBER -- Sales Order Cancelled_quantity.
182 p_interface_qty NUMBER DEFAULT NULL
183 -- qty from po_req_interface_all
184 Return Value : Number
185 Description : This procedure is called from the concurrent program to
186 get the the quantity to be reserved for the demand.
187 *****************************************************************************/
188 -- Fix for performance bug 4897231
189 -- Added a new parameter p_item_id to use
190 -- in po_requisitions_interface table where clause
191
192 FUNCTION get_new_order_qty (
193 p_interface_source_line_id NUMBER,
194 p_order_qty NUMBER,
195 p_cancelled_qty NUMBER,
196 p_interface_qty NUMBER DEFAULT NULL, --7559710
197 p_item_id NUMBER)
198 RETURN NUMBER;
199
200
201
202
203 PROCEDURE check_order_line_status (
204 p_line_id NUMBER,
205 p_flow_status OUT NOCOPY VARCHAR2,
206 p_inv_qty OUT NOCOPY NUMBER,
207 p_po_qty OUT NOCOPY NUMBER,
208 p_req_qty OUT NOCOPY NUMBER);
209
210
211
212 -- Added by Renga For purchase doc creation module
213
214
215
216 Type buy_components_rec is record (
217 inventory_item_id bom_cto_order_lines.inventory_item_id%type,
218 line_id bom_cto_order_lines.line_id%type,
219 ato_line_id bom_cto_order_lines.ato_line_id%type,
220 ordered_quantity bom_cto_order_lines.ordered_quantity%type,
221 order_quantity_uom bom_cto_order_lines.order_quantity_uom%type,
222 ship_from_org_id bom_cto_order_lines.ship_from_org_id%type,
223 wip_supply_type bom_cto_order_lines.wip_supply_type%type,
224 bom_item_type bom_cto_order_lines.bom_item_type%type,
225 primary_uom_code mtl_system_items.primary_uom_code%type,
226 list_price_per_unit mtl_system_items.list_price_per_unit%type,
227 config_item_id bom_cto_order_lines.config_item_id%type,
228 qty_per Number,
229 model_line Varchar2(1));
230
231 Type buy_components_tbl is table of buy_components_rec index by binary_integer;
232
233
234 Type Oper_unit_rec is record
235 (Oper_unit Number);
236
237 Type oper_unit_tbl is table of oper_unit_rec index by binary_integer;
238
239
240 G_oper_unit_list oper_unit_tbl;
241 G_oper_unit_list_null oper_unit_tbl;
242
243 Procedure Create_Purchasing_Doc(
244 P_config_item_id IN Number,
245 p_overwrite_list_price IN Varchar2 default 'N',
246 p_called_in_batch IN Varchar2 default 'N',
247 p_batch_number IN OUT NOCOPY Number,
248 p_mode IN Varchar2 Default 'ORDER',
249 p_ato_line_id IN Number Default null,
250 x_oper_unit_list IN OUT NOCOPY cto_auto_procure_pk.oper_unit_tbl,
251 x_return_status OUT NOCOPY Varchar2,
252 x_msg_count OUT NOCOPY Number,
253 x_msg_data OUT NOCOPY Varchar);
254
255
256 Procedure Rollup_list_price (
257 p_config_item_id in Number,
258 p_group_id in Number,
259 p_org_id in Number,
260 x_rolled_price out NOCOPY Number,
261 x_return_status out NOCOPY varchar2,
262 x_msg_count out NOCOPY number,
263 x_msg_data out NOCOPY varchar2);
264
265 Procedure Rollup_purchase_price (
266 p_config_item_id in Number,
267 p_batch_id in out NOCOPY Number,
268 p_group_id in Number,
269 p_mode in Varchar2 Default 'ORDER',
270 p_line_id in Number,
271 x_oper_unit_list in out NOCOPY cto_auto_procure_pk.oper_unit_tbl,
272 x_return_status out NOCOPY varchar2,
273 x_msg_count out NOCOPY number,
274 x_msg_data out NOCOPY varchar2);
275
276 Procedure rollup_blanket_price(
277 p_config_item_id in number,
278 p_doc_header_id in number,
279 p_doc_line_id in number,
280 p_group_id in number,
281 p_po_valid_org in number,
282 p_mode IN Varchar2 Default 'ORDER',
283 x_rolled_price out NOCOPY number,
284 x_return_status out NOCOPY varchar2,
285 x_msg_count out NOCOPY number,
286 x_msg_data out NOCOPY varchar2);
287
288 /** fp-J: Added several new parameters as part of optional processing */
289
290 PROCEDURE Create_purchase_doc_batch (
291 errbuf OUT NOCOPY VARCHAR2,
292 retcode OUT NOCOPY varchar2,
293 p_sales_order NUMBER,
294 p_dummy_field VARCHAR2,
295 p_sales_order_line_id NUMBER,
296 p_organization_id VARCHAR2,
297 p_dummy_field1 VARCHAR2,
298 p_offset_days NUMBER,
299 p_overwrite_list_price varchar2,
300 p_config_id NUMBER DEFAULT NULL,
301 p_dummy_field2 VARCHAR2 DEFAULT NULL,
302 p_base_model_id NUMBER DEFAULT NULL,
303 p_created_days_ago NUMBER DEFAULT NULL,
304 p_load_type NUMBER DEFAULT NULL,
305 p_upgrade NUMBER DEFAULT 2,
306 p_perform_rollup NUMBER DEFAULT 1);
307
308
309 Procedure Submit_pdoi_conc_prog(
310 p_oper_unit_list In cto_auto_procure_pk.oper_unit_tbl,
311 p_batch_id In Number,
312 x_return_status Out NOCOPY Varchar2,
313 x_msg_count Out NOCOPY Number,
314 x_msg_data Out NOCOPY Varchar2);
315
316 --Bugfix 13500057
317 function is_line_eligible(p_line_id number,
318 p_shipped_quantity number,
319 p_ordered_quantity number)
320 return varchar2;
321
322
323 END cto_auto_procure_pk;
[Home] [Help]