1 PACKAGE INV_LOT_SEL_ATTR AUTHID CURRENT_USER AS
2 /* $Header: INVLSDFS.pls 120.4 2010/12/20 10:21:19 ksaripal ship $ */
3
4 -- Record Type for the lot number attributes columns
5 TYPE lot_sel_attributes_rec_type IS RECORD
6 (
7 COLUMN_NAME VARCHAR2(50) :=NULL
8 , COLUMN_TYPE VARCHAR2(20) :=NULL
9 , COLUMN_VALUE fnd_descr_flex_col_usage_vl.default_value%TYPE :=NULL
10 , REQUIRED VARCHAR2(10) :='NULL'
11 , PROMPT VARCHAR2(100) :=NULL
12 , COLUMN_LENGTH NUMBER :=NULL
13 );
14
15 -- Table type definition for an array of cb_chart_status_rec_type records.
16 TYPE lot_sel_attributes_tbl_type is TABLE OF lot_sel_attributes_rec_type
17 INDEX BY BINARY_INTEGER;
18
19 TYPE n_attribute_table_type IS TABLE OF mtl_lot_numbers.n_attribute1%TYPE INDEX BY BINARY_INTEGER;
20 TYPE d_attribute_table_type IS TABLE OF mtl_lot_numbers.d_attribute1%TYPE INDEX BY BINARY_INTEGER;
21 TYPE c_attribute_table_type IS TABLE OF mtl_lot_numbers.c_attribute1%TYPE INDEX BY BINARY_INTEGER;
22
23 TYPE t_genref IS REF CURSOR;
24
25 /* procedure definition to fetch the attribute category
26 * for a given item and organization */
27 PROCEDURE get_context_code(
28 context_value OUT NOCOPY VARCHAR2
29 , org_id IN NUMBER
30 , item_id IN NUMBER
31 , flex_name IN VARCHAR2
32 , p_lot_serial_number IN VARCHAR2);
33
34 PROCEDURE get_context_code(
35 context_value OUT NOCOPY VARCHAR2
36 , org_id IN NUMBER
37 , item_id IN NUMBER
38 , flex_name IN VARCHAR2);
39
40 /*-------------------------------------------------
41 * Check whether a descriptive flexfield has enabled(required)
42 segements. The return value can be:
43 0 - no enabled segments
44 1 - has enabled segments but are not required
45 2 - had enabled and required segments
46 -------------------------------------------------*/
47 FUNCTION is_enabled(p_flex_name IN VARCHAR2,
48 p_organization_id IN NUMBER,
49 p_inventory_item_id IN NUMBER) RETURN NUMBER ;
50
51
52 /*----------------------------------------------------
53 * Check Whether the DFF has any required Context or Global Segment
54 1 - Has required Context or Global Data segment
55 0 - Otherwise
56 ----------------------------------------------------*/
57
58 FUNCTION is_dff_required(p_flex_name IN VARCHAR2,
59 p_application_short_name IN VARCHAR2,
60 p_organization_id IN NUMBER,
61 p_inventory_item_id IN NUMBER) RETURN NUMBER;
62
63
64 /*--------------------------------------------------------------
65 * Returns 1 if the context_override_flag is set, 0 otherwise
66 --------------------------------------------------------------*/
67
68 FUNCTION is_context_displayed(p_flex_name IN VARCHAR2,
69 p_application_short_name IN VARCHAR2) RETURN NUMBER;
70
71
72 /*-------------------------------------------------
73 * Returns the delimiter for the given flex field
74 -------------------------------------------------*/
75
76 FUNCTION get_delimiter(p_flex_name IN VARCHAR2,
77 p_application_short_name IN VARCHAR2) RETURN VARCHAR2;
78
79
80 /*-------------------------------------------------
81 * Check whether a segment of a descriptive flexfield is enabled(required)
82 The return value can be:
83 0 - no enabled segment
84 1 - enabled segment
85 2 - enabled and required segment
86 -------------------------------------------------*/
87 FUNCTION is_enabled_segment(p_flex_name IN VARCHAR2,
88 p_segment_name IN VARCHAR2,
89 p_organization_id IN NUMBER,
90 p_inventory_item_id IN NUMBER) RETURN NUMBER ;
91
92 -- procedure definition for get lot number attributes defaults
93 PROCEDURE get_default(
94 x_attributes_default OUT NOCOPY lot_sel_attributes_tbl_type
95 , x_attributes_default_count OUT NOCOPY NUMBER
96 , x_return_status OUT NOCOPY VARCHAR2
97 , x_msg_count OUT NOCOPY NUMBER
98 , x_msg_data OUT NOCOPY VARCHAR2
99 , p_table_name IN VARCHAR2
100 , p_attributes_name IN VARCHAR2
101 , p_inventory_item_id IN NUMBER
102 , p_organization_id IN NUMBER
103 , p_lot_serial_number IN VARCHAR2
104 , p_attributes IN lot_sel_attributes_tbl_type);
105
106 /*Bug 2756040: Added extra IN parameter p_issue_receipt at the end to determine
107 if the transaction is of type issue or receipt. This has a default value
108 of NULL */
109 /*Bug 4328865: Replaced the default value of IN parameter p_issue_receipt with '@@@' */
110 PROCEDURE get_attribute_values
111 ( x_lot_serial_attributes OUT NOCOPY lot_sel_attributes_tbl_type
112 , x_lot_serial_attributes_count OUT NOCOPY NUMBER
113 , x_return_status OUT NOCOPY VARCHAR2
114 , x_msg_count OUT NOCOPY NUMBER
115 , x_msg_data OUT NOCOPY VARCHAR2
116 , p_table_name IN VARCHAR2
117 , p_attributes_name IN VARCHAR2
118 , p_inventory_item_id IN NUMBER
119 , p_organization_id IN NUMBER
120 , p_lot_serial_number IN VARCHAR2
121 , p_issue_receipt IN VARCHAR2 DEFAULT '@@@'
122 , p_where_called IN VARCHAR2 DEFAULT '@@@' );
123 -- Bug 9828930
124
125
126 /* New Procedure to get the Inventory Attributes */
127
128 procedure get_inv_lot_attributes( x_return_status OUT NOCOPY VARCHAR2
129 ,x_msg_count OUT NOCOPY NUMBER
130 ,x_msg_data OUT NOCOPY VARCHAR2
131 ,x_inv_lot_attributes OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
132 ,P_inventory_item_id IN NUMBER
133 ,P_LOT_NUMBER IN VARCHAR2
134 ,p_organization_id IN NUMBER
135 ,p_attribute_category IN VARCHAR2 );
136
137 /* ----------------------------------------------------------
138 * Procedure to fetch descriptive flexfield context category
139 * from MSN for a given item and organization.
140 * Currently, made changes only for fetching from MSN.
141 * Need to add code to fetch from MLN if the flex_name
142 * is Lot Attributes --Added for 2756040
143 *----------------------------------------------------------*/
144
145 PROCEDURE get_lot_serial_context(context_value OUT NOCOPY VARCHAR2,
146 org_id IN NUMBER,
147 item_id IN NUMBER,
148 p_lot_serial IN VARCHAR2,
149 flex_name IN VARCHAR2);
150
151 PROCEDURE get_dflex_context(
152 x_context OUT NOCOPY t_genref,
153 p_application_id IN NUMBER,
154 p_flex_name IN VARCHAR2);
155
156 PROCEDURE get_dflex_segment(
157 x_segment OUT NOCOPY t_genref,
158 p_application_id IN NUMBER,
159 p_flex_name IN VARCHAR2,
160 p_flex_context_code IN VARCHAR2);
161
162
163 procedure get_inv_serial_attributes( x_return_status OUT NOCOPY VARCHAR2
164 ,x_msg_count OUT NOCOPY NUMBER
165 ,x_msg_data OUT NOCOPY VARCHAR2
166 ,x_inv_serial_attributes OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
167 ,x_concatenated_values OUT NOCOPY VARCHAR2
168 ,P_inventory_item_id IN NUMBER
169 ,P_Serial_Number IN VARCHAR2
170 ,p_attribute_category IN VARCHAR2
171 ,p_transaction_temp_id IN NUMBER DEFAULT NULL
172 ,p_transaction_source IN VARCHAR2 DEFAULT NULL);
173
174 -- Bug 7249316
175 FUNCTION is_lot_attributes_required( p_flex_name IN VARCHAR2,
176 p_organization_id IN NUMBER,
177 p_inventory_item_id IN NUMBER,
178 p_lot_number IN VARCHAR2) RETURN BOOLEAN;
179
180 /* Added for bug 7632531 */
181
182 FUNCTION lock_lot_records( p_org_id IN NUMBER
183 , p_inventory_item_id IN NUMBER
184 , p_lot_uniqueness IN NUMBER DEFAULT NULL
185 , p_lot_generation IN NUMBER DEFAULT NULL
186 , p_lot_prefix IN VARCHAR2
187 , x_return_status OUT NOCOPY VARCHAR2
188 ) RETURN BOOLEAN;
189
190 /* End of changes for bug 7632531 */
191
192 END INV_LOT_SEL_ATTR;
[Home] [Help]