1 PACKAGE inv_genealogy_pub AS
2 /* $Header: INVPVCGS.pls 120.4.12000000.1 2007/01/17 16:27:14 appldev ship $ */
3
4 -------------------------------------------------------------------------------
5 -- Global Constants for FND values used in initialization of parameters
6 -------------------------------------------------------------------------------
7
8 gen_fnd_g_false CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
9 gen_fnd_valid_level_full CONSTANT NUMBER := FND_API.G_VALID_LEVEL_FULL;
10
11 -------------------------------------------------------------------------------
12 -- Global Constants: Genealogy Object Types
13 -------------------------------------------------------------------------------
14 GEN_OBJ_TYPE_LOT CONSTANT NUMBER := 1;
15 GEN_OBJ_TYPE_SERIAL CONSTANT NUMBER := 2;
16 GEN_OBJ_TYPE_EXTERNAL CONSTANT NUMBER := 3;
17 GEN_OBJ_TYPE_CONTAINER CONSTANT NUMBER := 4;
18 GEN_OBJ_TYPE_LOTJOB CONSTANT NUMBER := 5;
19
20 -------------------------------------------------------------------------------
21 -- Global Constants: Genealogy Origin
22 -------------------------------------------------------------------------------
23 GEN_ORIGIN_INV CONSTANT NUMBER := 1;
24 GEN_ORIGIN_WIP CONSTANT NUMBER := 2;
25
26 -------------------------------------------------------------------------------
27 -- Global Constants: Genealogy Types
28 -------------------------------------------------------------------------------
29 GEN_TYPE_ASSM_COMP CONSTANT NUMBER := 1;
30 GEN_TYPE_SUBLOT CONSTANT NUMBER := 4;
31 GEN_TYPE_EAM CONSTANT NUMBER := 5;
32
33
34 ----------------------------------
35 -- table and record
36 ----------------------------------
37
38 type object_id_rec_t IS RECORD(object_id NUMBER, start_date_active DATE, end_date_active DATE);
39 type object_id_tbl_t IS TABLE OF object_id_rec_t INDEX BY BINARY_INTEGER;
40
41 -------------------------------------------------------------------------------
42 -- Procedures and Functions
43 -------------------------------------------------------------------------------
44 -- Start of comments
45 -- API name : populate_genealogy
46 -- Type : Public
47 -- Pre-reqs : None.
48 -- Function : Validates and creates a record in MTL_OBJECT_GENEALOGY
49 -- with the parameters passed.
50 -- Parameters :
51 -- IN : p_api_version IN NUMBER Required
52 -- p_init_msg_list IN VARCHAR2
53 -- p_commit IN VARCHAR2
54 -- p_validation_level IN NUMBER
55 -- p_object_type IN NUMBER Required
56 -- Type of the object (1-Lot or 2-Serial)
57 -- p_parent_object_type IN NUMBER Required
58 -- Type of the parent object
59 -- (1-Lot or 2-Serial)
60 -- p_object_id IN NUMBER
61 -- Genealogy object id.
62 -- Either p_object_id or (p_object_number,
63 -- p_inventory_item_id and p_org_id) is mandatory.
64 -- p_object_number IN VARCHAR2
65 -- Lot Number or Serial Number or the child
66 -- p_inventory_item_id IN NUMBER
67 -- Inventory Item id.
68 -- p_org_id IN NUMBER
69 -- Organization id.
70 -- p_parent_object_id IN NUMBER
71 -- Genealogy object id.
72 -- p_parent_object_number IN VARCHAR2
73 -- Parent Lot Number or Parent Serial Number
74 -- p_parent_inventory_item_id IN NUMBER
75 -- Inventory Item id.
76 -- p_parent_org_id IN NUMBER
77 -- Organization id.
78 -- p_genealogy_origin IN NUMBER
79 -- The origin of the genealogy, could be
80 -- WIP or INV Transactions
81 -- p_genealogy_type IN NUMBER
82 -- Could be
83 -- 1- Assembly component
84 -- 2- Lot split
85 -- 3- Lot merge
86 -- p_origin_txn_id IN NUMBER Required
87 -- If origin of the genealogy is passed then
88 -- this should have the id of the WIP or INV
89 -- transaction
90 -- OUT : x_return_status OUT VARCHAR2
91 -- Return Status
92 -- x_msg_count OUT NUMBER
93 -- x_msg_data OUT VARCHAR2
94 --
95 -- Version : Current version 1.0
96 -- Initial version 1.0
97 --
98 -- End of comments
99 PROCEDURE insert_genealogy
100 ( p_api_version IN NUMBER
101 , p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
102 , p_commit IN VARCHAR2 := gen_fnd_g_false
103 , p_validation_level IN NUMBER := gen_fnd_valid_level_full
104 , p_object_type IN NUMBER
105 , p_parent_object_type IN NUMBER := NULL
106 , p_object_id IN NUMBER := NULL
107 , p_object_number IN VARCHAR2 := NULL
108 , p_inventory_item_id IN NUMBER := NULL
109 , p_org_id IN NUMBER := NULL
110 , p_parent_object_id IN NUMBER := NULL
111 , p_parent_object_number IN VARCHAR2 := NULL
112 , p_parent_inventory_item_id IN NUMBER := NULL
113 , p_parent_org_id IN NUMBER := NULL
114 , p_genealogy_origin IN NUMBER := NULL
115 , p_genealogy_type IN NUMBER := NULL
116 , p_start_date_active IN DATE := SYSDATE
117 , p_end_date_active IN DATE := NULL
118 , p_origin_txn_id IN NUMBER := NULL
119 , p_update_txn_id IN NUMBER := NULL
120 , x_return_status OUT NOCOPY VARCHAR2
121 , x_msg_count OUT NOCOPY NUMBER
122 , x_msg_data OUT NOCOPY VARCHAR2
123 , p_object_type2 IN NUMBER := NULL -- R12
124 , p_object_id2 IN NUMBER := NULL -- R12
125 , p_object_number2 IN VARCHAR2 := NULL -- R12
126 , p_parent_object_type2 IN NUMBER := NULL -- R12
127 , p_parent_object_id2 IN NUMBER := NULL -- R12
128 , p_parent_object_number2 IN VARCHAR2 := NULL -- R12
129 , p_child_lot_control_code IN NUMBER := NULL -- R12
130 , p_parent_lot_control_code IN NUMBER := NULL -- R12
131 );
132
133 PROCEDURE update_genealogy
134 ( p_api_version IN NUMBER
135 , p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
136 , p_commit IN VARCHAR2 := gen_fnd_g_false
137 , p_validation_level IN NUMBER := gen_fnd_valid_level_full
138 , p_object_type IN NUMBER
139 , p_object_id IN NUMBER := NULL
140 , p_object_number IN VARCHAR2 := NULL
141 , p_inventory_item_id IN NUMBER := NULL
142 , p_org_id IN NUMBER := NULL
143 , p_genealogy_origin IN NUMBER := NULL
144 , p_genealogy_type IN NUMBER := NULL
145 , p_end_date_active IN DATE := NULL
146 , p_update_txn_id IN NUMBER := NULL
147 , x_return_status OUT NOCOPY VARCHAR2
148 , x_msg_count OUT NOCOPY NUMBER
149 , x_msg_data OUT NOCOPY VARCHAR2
150 );
151
152 PROCEDURE insert_flow_genealogy
153 ( p_api_version IN NUMBER
154 , p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
155 , p_commit IN VARCHAR2 := gen_fnd_g_false
156 , p_validation_level IN NUMBER := gen_fnd_valid_level_full
157 , p_transaction_source_id IN NUMBER
158 , p_completion_transaction_id IN NUMBER
159 , p_parent_object_id IN NUMBER := NULL
160 , p_parent_object_number IN VARCHAR2 := NULL
161 , p_parent_inventory_item_id IN NUMBER := NULL
162 , p_parent_org_id IN NUMBER := NULL
163 , p_genealogy_origin IN NUMBER := NULL
164 , p_genealogy_type IN NUMBER := NULL
165 , p_start_date_active IN DATE := sysdate
166 , p_end_date_active IN DATE := NULL
167 , p_origin_txn_id IN NUMBER := NULL
168 , p_update_txn_id IN NUMBER := NULL
169 , x_return_status OUT NOCOPY VARCHAR2
170 , x_msg_count OUT NOCOPY NUMBER
171 , x_msg_data OUT NOCOPY VARCHAR2
172 );
173
174
175
176 PROCEDURE DELETE_EAM_ROW(
177 P_API_VERSION IN NUMBER,
178 P_INIT_MSG_LIST IN VARCHAR2 := gen_fnd_g_false,
179 P_COMMIT IN VARCHAR2 := gen_fnd_g_false,
180 P_VALIDATION_LEVEL IN NUMBER := gen_fnd_valid_level_full,
181 P_OBJECT_ID IN NUMBER,
182 P_START_DATE_ACTIVE IN DATE,
183 P_END_DATE_ACTIVE IN DATE,
184 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
185 X_MSG_COUNT OUT NOCOPY NUMBER,
186 X_MSG_DATA OUT NOCOPY VARCHAR2);
187
188 PROCEDURE update_genealogy(
189 p_api_version IN NUMBER
190 , p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
191 , p_commit IN VARCHAR2 := gen_fnd_g_false
192 , p_validation_level IN NUMBER := gen_fnd_valid_level_full
193 , p_object_type IN NUMBER
194 , p_parent_object_type IN NUMBER := NULL
195 , p_object_id IN NUMBER := NULL
196 , p_object_number IN VARCHAR2 := NULL
197 , p_inventory_item_id IN NUMBER := NULL
198 , p_organization_id IN NUMBER := NULL
199 , p_parent_object_id IN NUMBER := NULL
200 , p_parent_object_number IN VARCHAR2 := NULL
201 , p_parent_inventory_item_id IN NUMBER := NULL
202 , p_parent_org_id IN NUMBER := NULL
203 , p_genealogy_origin IN NUMBER := NULL
204 , p_genealogy_type IN NUMBER := NULL
205 , p_start_date_active IN DATE := SYSDATE
206 , p_end_date_active IN DATE := NULL
207 , p_origin_txn_id IN NUMBER := NULL
208 , p_update_txn_id IN NUMBER := NULL
209 , p_object_type2 IN NUMBER := NULL
210 , p_object_id2 IN NUMBER := NULL
211 , p_object_number2 IN VARCHAR2 := NULL
212 , p_parent_object_type2 IN NUMBER := NULL
213 , p_parent_object_id2 IN NUMBER := NULL
214 , p_parent_object_number2 IN VARCHAR2 := NULL
215 , p_child_lot_control_code IN NUMBER := NULL
216 , p_parent_lot_control_code IN NUMBER := NULL
217 , p_transaction_type IN VARCHAR2 := NULL -- ASSEMBLY_RETURN, COMP_RETURN, NULL
218 , x_return_status OUT NOCOPY VARCHAR2
219 , x_msg_count OUT NOCOPY NUMBER
220 , x_msg_data OUT NOCOPY VARCHAR2
221 );
222 -- 2/2/06: Bug: 4997221 : Added new parameter p_transaction_type
223
224 END INV_genealogy_PUB;