1 PACKAGE INV_LOT_SEL_ATTR AS
2 /* $Header: INVLSDFS.pls 120.1 2005/06/05 22:49:53 appldev $ */
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
123
124 /* New Procedure to get the Inventory Attributes */
125
126 procedure get_inv_lot_attributes( x_return_status OUT NOCOPY VARCHAR2
127 ,x_msg_count OUT NOCOPY NUMBER
128 ,x_msg_data OUT NOCOPY VARCHAR2
129 ,x_inv_lot_attributes OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
130 ,P_inventory_item_id IN NUMBER
131 ,P_LOT_NUMBER IN VARCHAR2
132 ,p_organization_id IN NUMBER
133 ,p_attribute_category IN VARCHAR2 );
134
135 /* ----------------------------------------------------------
136 * Procedure to fetch descriptive flexfield context category
137 * from MSN for a given item and organization.
138 * Currently, made changes only for fetching from MSN.
139 * Need to add code to fetch from MLN if the flex_name
140 * is Lot Attributes --Added for 2756040
141 *----------------------------------------------------------*/
142
143 PROCEDURE get_lot_serial_context(context_value OUT NOCOPY VARCHAR2,
144 org_id IN NUMBER,
145 item_id IN NUMBER,
146 p_lot_serial IN VARCHAR2,
147 flex_name IN VARCHAR2);
148
149 PROCEDURE get_dflex_context(
150 x_context OUT NOCOPY t_genref,
151 p_application_id IN NUMBER,
152 p_flex_name IN VARCHAR2);
153
154 PROCEDURE get_dflex_segment(
155 x_segment OUT NOCOPY t_genref,
156 p_application_id IN NUMBER,
157 p_flex_name IN VARCHAR2,
158 p_flex_context_code IN VARCHAR2);
159
160
161 procedure get_inv_serial_attributes( x_return_status OUT NOCOPY VARCHAR2
162 ,x_msg_count OUT NOCOPY NUMBER
163 ,x_msg_data OUT NOCOPY VARCHAR2
164 ,x_inv_serial_attributes OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
165 ,x_concatenated_values OUT NOCOPY VARCHAR2
166 ,P_inventory_item_id IN NUMBER
167 ,P_Serial_Number IN VARCHAR2
168 ,p_attribute_category IN VARCHAR2
169 ,p_transaction_temp_id IN NUMBER DEFAULT NULL
170 ,p_transaction_source IN VARCHAR2 DEFAULT NULL);
171
172 END INV_LOT_SEL_ATTR;