1 PACKAGE INV_UTILITIES AUTHID CURRENT_USER AS
2 /* $Header: INVUTILS.pls 120.4.12020000.2 2013/01/29 20:13:02 avrose ship $ */
3
4 -- this data type is used by the procedure parse_vector
5 TYPE vector_tabtype IS
6 TABLE OF VARCHAR2(60)
7 INDEX BY BINARY_INTEGER;
8
9 /*=====================================================================+
10 | PROCEDURE
11 | DO_SQL
12 |
13 | PURPOSE
14 | Executes a dynamic SQL statement
15 |
16 | ARGUMENTS
17 | p_sql_stmt String holding sql statement. May be up to 8K long.
18 |
19 | EXCEPTIONS
20 |
21 | NOTES
22 |
23 +=====================================================================*/
24 PROCEDURE DO_SQL(p_sql_stmt in varchar2);
25
26
27
28 /*********************************** DEPRECATED *************************************
29 *********************************** DEPRECATED *************************************
30 *********************************** DEPRECATED *************************************
31 * Procedure OBSOLETED. Use INV_PICK_SLIP_REPORT.RUN_DETAIL_ENGINE for Future use. *
32 * THIS PROCEDURE WILL NOT BE SUPPORTED ANY MORE *
33 *********************************** DEPRECATED *************************************
34 *********************************** DEPRECATED *************************************
35 *********************************** DEPRECATED *************************************/
36 PROCEDURE RUN_DETAIL_ENGINE(p_detail_status out NOCOPY varchar2
37 ,p_org_id IN NUMBER DEFAULT NULL
38 ,p_move_order_type NUMBER
39 ,p_transfer_order VARCHAR2
40 ,p_source_subinv VARCHAR2
41 ,p_source_locid NUMBER
42 ,p_dest_subinv VARCHAR2
43 ,p_dest_locid NUMBER
44 ,p_requested_by NUMBER
45 ,p_plan_tasks IN BOOLEAN DEFAULT FALSE
46 ,p_pick_slip_group_rule_id IN NUMBER DEFAULT NULL
47 );
48
49
50 /*
51 -- PROCEDURE : parse_vector
52 --
53 -- This procedure is a work-around the limitation of not being
54 -- able to pass a vector from Java to SQL. Instead of passing a vector,
55 -- a string of elements separated by a delimiter is passed to SQL.
56 --
57 -- This procedure parses a string(varchar2) of elements delimited
58 -- by a delimiter of the user's choice (e.g. comma, semicolon, colon).
59 -- The output is a table of one column of type varchar. Each row of
60 -- this column corresponds to an individual string element. These
61 -- elements can then be manipulated using a loop as shown below.
62 --
63 -- Known limitations:
64 -- The limit for the length of a varchar in SQL is 32676. Please ensure
65 -- the length of the string passed in is below this number.
66 --
67 --
68 -- Example of usage:
69 --
70 -- declare
71 --
72 -- vector_in VARCHAR2(500);
73 -- table_output inv_utilities.vector_tabtype;
74 -- table_row NUMBER;
75 --
76 -- BEGIN
77 --
78 -- parse_vector (vector_in =>'elem0,elem1,elem2',
79 -- delimiter =>':',
80 -- table_of_strings=> table_output);
81 --
82 -- FOR table_row IN 0 .. table_output.COUNT-1
83 -- LOOP
84 -- dbms_output.put_line('value in row'||TO_CHAR(table_row)||':'||table_output(table_row));
85 -- END LOOP;
86 --
87 --*/
88 --Added NOCOPY hint to table_of_strings OUT parameter
89 --to comply with GSCC File.Sql.39 standard. Bug:4410848
90 PROCEDURE parse_vector (vector_in IN VARCHAR2,
91 delimiter IN VARCHAR2,
92 table_of_strings OUT NOCOPY vector_tabtype
93 );
94
95
96 FUNCTION get_conc_segments( X_org_id IN Number,
97 X_loc_id IN Number)
98 Return varchar2;
99
100 /*
101 Added for bug No 7440217
102 PO API for LCM
103 */
104 FUNCTION inv_check_lcm(
105 p_inventory_item_id IN NUMBER,
106 p_ship_to_org_id IN NUMBER,
107 p_consigned_flag IN VARCHAR2,
108 p_outsource_assembly_flag IN VARCHAR2,
109 p_vendor_id IN NUMBER,
110 p_vendor_site_id IN NUMBER,
111 p_po_line_location_id IN NUMBER DEFAULT NULL --Bug#10279800
112 )
113 RETURN VARCHAR2;
114 /*
115 END for bug No 7440217
116 PO API for LCM
117 */
118
119 /*
120 Added for bug No :2326247.
121 Calculates the item cost based on costing.
122 */
123 PROCEDURE GET_ITEM_COST(v_org_id IN NUMBER,
124 v_item_id IN NUMBER,
125 v_locator_id IN NUMBER,
126 v_item_cost OUT NOCOPY NUMBER);
127 PROCEDURE get_sales_order_id
128 /*
129 ||==============================================================================||
130 || Created By : Nalin Kumar ||
131 || Created On : 03-May-2005 ||
132 || Purpose : This procedure will get called from TMO. This procedure will ||
133 || return the Sales Order ID and Concatenated Segments. Created ||
134 || as part of Depot Repair Enh. Bug# 4346443 ||
135 || ||
136 || Known limitations, enhancements or remarks : ||
137 || Change History : ||
138 || Who When What ||
139 || (reverse chronological order - newest change first) ||
140 ||==============================================================================||
141 */
142 (p_sales_order_number NUMBER ,
143 p_sales_order_type VARCHAR2,
144 p_sales_order_source VARCHAR2,
145 p_concatenated_segments OUT NOCOPY VARCHAR2,
146 p_source_id OUT NOCOPY NUMBER);
147
148
149 /*
150 This API was created as a part of MUOM fulfillment ER.
151 This will accept source_line_id as input paramter and will return the fulfillment_base
152 by calling API OE_DUAL_UOM_UTIL.get_fulfillment_base.
153 */
154 PROCEDURE get_inv_fulfillment_base(
155 p_source_line_id IN NUMBER,
156 p_demand_source_type_id IN NUMBER,
157 p_org_id IN NUMBER,
158 x_fulfillment_base OUT NOCOPY VARCHAR2);
159
160
161 END INV_UTILITIES;