1 Package CTO_Workbench_Util_pk AUTHID CURRENT_USER as
2 /* $Header: CTOWBUTS.pls 120.6 2006/07/28 00:52:07 rekannan noship $ */
3 /********************************************************************************************************
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved, |
6 | Oracle Manufacturing |
7 | File Name : CTOWBUTS.pls |
8 | |
9 | Description : This is the Utility pkg for CTO workbench. CTO work bench is the |
10 | Self service application and we need lot of funtions to call in the sql |
11 | This file is not having any other product dependency. |
12 | History : Created on 11-NOV-2002 by Renga Kannan |
13 ********************************************************************************************************/
14
15
16 -- rkaza. ireq project. 06/30/2005. Used to cache config_line_ids given
17 -- ato_line_id.
18 TYPE config_line_id_tbl_type is TABLE OF number INDEX BY Binary_integer;
19 config_line_id_tbl config_line_id_tbl_type;
20
21
22 /* This function will get the order line number as displayed in the OM form
23 */
24
25 FUNCTION get_line_number
26 (
27 p_ato_line_id IN Number,
28 p_line_id IN NUMBER,
29 p_item_type_code IN VARCHAR2,
30 p_Line_Number IN NUMBER,
31 p_Shipment_Number IN NUMBER,
32 p_Option_Number IN NUMBER,
33 p_Component_Number IN NUMBER DEFAULT NULL,
34 p_Service_Number IN NUMBER DEFAULT NULL
35 ) Return varchar2;
36
37 /* This function will get the line status for the given line, If the line passed is Model then it will
38 get the config line's line status */
39
40 FUNCTION get_line_status (
41 p_Line_Id IN NUMBER,
42 p_Ato_Line_Id IN NUMBER,
43 p_Item_Type_code IN Varchar2,
44 p_flow_status IN Varchar2) Return Varchar2;
45
46 /* This function will get the supply type based on the reservation/Link to the config/ato/std line */
47
48 Function Get_supply_type (P_line_id IN Number,
49 p_ato_line_id IN Number,
50 p_item_type IN Varchar2,
51 p_source_type IN Varchar2) return Varchar2;
52
53 /* This function is used to get the config line id based on model line */
54
55 Function Get_config_line_id (P_ato_line_id IN Number,
56 p_line_id IN Number,
57 p_item_type IN Varchar2) return Number;
58
59
60 /* This function is used to get the Item name for config based on Model Line */
61
62 Function Get_Item_Name (P_ato_line_id IN Number,
63 p_line_id IN Number,
64 p_item_type IN Varchar2,
65 p_item_name IN Varchar2,
66 p_config_item IN Number,
67 p_ship_org_id IN Number) return Varchar2;
68
69
70 /* This function is used to get the item description for the config based on model line */
71
72 Function Get_Item_Desc (P_ato_line_id IN Number,
73 p_line_id IN Number,
74 p_item_type IN Varchar2,
75 p_item_desc IN Varchar2,
76 p_config_item IN Number,
77 p_ship_org_id IN Number) return Varchar2;
78
79
80 /* This function is used to get the document source id for sales order line */
81
82 FUNCTION get_source_document_id (pLineId in number)
83 RETURN NUMBER;
84
85
86 /* This function is used to convert UOM from and to primary UOM */
87
88 FUNCTION convert_uom(from_uom IN VARCHAR2,
89 to_uom IN VARCHAR2,
90 quantity IN NUMBER,
91 item_id IN NUMBER )
92 RETURN NUMBER;
93
94 /* This function gets the suggested buyer name for reqs */
95 FUNCTION Get_Buyer_Name (P_suggested_buyer_id IN Varchar2)
96 RETURN Varchar2;
97
98
99 /* fp-J project: Added a new function Get_Workbench_Item_Type */
100 FUNCTION Get_WorkBench_Item_Type
101 ( p_header_id IN NUMBER
102 ,p_top_model_line_id IN NUMBER
103 ,p_ato_line_id IN NUMBER
104 ,p_line_id IN NUMBER
105 ,p_item_type_code IN VARCHAR2
106 ) RETURN varchar2;
107
108 -- Fixed bug 5199341
109 -- Added two more parameters to derive the config line id inisde API
110
111
112 FUNCTION Get_Rsvd_on_hand_qty(
113 p_line_id IN Number,
114 p_ato_line_id IN Number,
115 p_item_type_code IN varchar2) RETURN Number;
116
117
118 -- rkaza. 05/19/2005. ireq project.
119 -- Start of comments
120 -- API name : get_last_available_date
121 -- Type : Public
122 -- Pre-reqs : None.
123 -- Function : Given ato line id, order line id and item type code, it returns
124 -- the date when the last supply becomes available for the top
125 -- level config/ato/std item.
126 -- Parameters:
127 -- IN : p_ato_line_id IN NUMBER Required
128 -- order line id
129 -- p_line_id
130 -- p_item_type_code
131 -- Version : Current version 115.9
132 -- Modified signature. Added ato_line_id and item_type_code
133 -- Previous version 115.7
134 -- Added this description
135 -- Initial version 115.6
136 -- End of comments
137 FUNCTION get_last_available_date(p_ato_line_id IN number, p_line_id IN Number, p_item_type IN varchar2) RETURN date;
138
139
140
141 /*******************************************************************************************
142 -- API name : get_rsvd_inrcv_qty
143 -- Type : Public
144 -- Pre-reqs : INVRSVGS.pls
145 -- Function : Given config/ato item line id it returns
146 -- the qty reserved to in receiving supply
147 -- Parameters:
148 -- IN : p_line_id Expects the config/ato item order line id Required
149 --
150 -- Version :
151 --
152 --
153 ******************************************************************************************/
154
155
156 -- Fixed bug 5199341
157 -- Added two more parameters to derive the config line id inisde API
158 FUNCTION Get_Rsvd_inrcv_qty(
159 p_line_id IN Number,
160 p_ato_line_id IN Number,
161 p_item_type_code IN varchar2
162 ) RETURN Number;
163
164
165
166 -- rkaza. 06/30/2005. ireq project.
167 -- Start of comments
168 -- API name : find_config_line_and_level
169 -- Type : Public
170 -- Pre-reqs : None.
171 -- Function : Given ato line id, order line id and item type code, it finds
172 -- config/ato item/std line id for the top level. For lower level,
173 -- it will find line_id itself. Also, along with config line id,
174 -- it will give corresponding line level.
175 -- top level - 'Top'
176 -- lower level - 'Lower'
177 -- Ato item - 'Ato'
178 -- Std item - 'Std'
179 -- Parameters:
180 -- IN : p_ato_line_id IN NUMBER Required
181 -- order line id
182 -- p_line_id IN number Required
183 -- p_item_type_code IN varchar2 Required
184 -- x_config_line_id OUT number
185 -- x_line_level OUT varchar2
186 -- x_return_status OUT varchar2
187 -- Version :
188 -- End of comments
189 Procedure find_config_line_and_level(p_ato_line_id IN number,
190 p_line_id IN Number,
191 p_item_type IN varchar2,
192 x_config_line_id OUT NOCOPY Number,
193 x_line_level OUT NOCOPY varchar2,
194 x_return_status OUT NOCOPY varchar2);
195 /* Added by renga Kannan for bug 5348842 */
196
197 Function get_order_line_number(p_line_number Number,
198 p_service_number Number,
199 p_option_number Number,
200 p_component_number Number,
201 p_shipment_number Number) return varchar2;
202
203
204
205 end cto_workbench_util_pk;
206
[Home] [Help]