[Home] [Help]
PACKAGE BODY: APPS.MTL_MGD_INV_POSITIONS_PKG
Source
1 PACKAGE BODY MTL_MGD_INV_POSITIONS_PKG AS
2 /* $Header: INVTPOSB.pls 115.1 2002/12/24 23:24:58 vjavli ship $ */
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVUTOSB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Table Handler for table MTL_MGD_INVENTORY_POSITIONS |
13 --| HISTORY |
14 --| 09/01/2000 Paolo Juvara Created |
15 --+======================================================================*/
16
17
18 --===================
19 -- PROCEDURES AND FUNCTIONS
20 --===================
21
22 --========================================================================
23 -- PROCEDURE : Insert_Row PUBLIC
24 -- PARAMETERS: p_????? one parameter per column
25 -- x_rowid rowid of the inserted row
26 -- COMMENT : Inserts a row in MTL_MGD_INVENTORY_POSITIONS; standard who
27 -- value are optional and defaulted from profile options
28 --=======================================================================--
29 PROCEDURE Insert_Row
30 ( p_data_set_name IN VARCHAR2
31 , p_bucket_name IN VARCHAR2
32 , p_organization_code IN VARCHAR2
33 , p_inventory_item_code IN VARCHAR2
34 , p_creation_date IN DATE DEFAULT NULL
35 , p_created_by IN NUMBER DEFAULT NULL
36 , p_last_update_date IN DATE DEFAULT NULL
37 , p_last_updated_by IN NUMBER DEFAULT NULL
38 , p_last_update_login IN NUMBER DEFAULT NULL
39 , p_request_id IN NUMBER DEFAULT NULL
40 , p_program_application_id IN NUMBER DEFAULT NULL
41 , p_program_id IN NUMBER DEFAULT NULL
42 , p_program_update_date IN DATE DEFAULT NULL
43 , p_hierarchy_id IN NUMBER
44 , p_hierarchy_name IN VARCHAR2
45 , p_parent_organization_code IN VARCHAR2
46 , p_parent_organization_id IN VARCHAR2
47 , p_bucket_size_code IN VARCHAR2
48 , p_bucket_start_date IN DATE
49 , p_bucket_end_date IN DATE
50 , p_inventory_item_id IN NUMBER
51 , p_organization_id IN NUMBER
52 , p_hierarchy_delta_qty IN NUMBER
53 , p_hierarchy_end_on_hand_qty IN NUMBER
54 , p_org_received_qty IN NUMBER
55 , p_org_issued_qty IN NUMBER
56 , p_org_delta_qty IN NUMBER
57 , p_org_end_on_hand_qty IN NUMBER
58 )
59 IS
60 BEGIN
61 INSERT INTO mtl_mgd_inventory_positions
62 ( data_set_name
63 , bucket_name
64 , organization_code
65 , inventory_item_code
66 , creation_date
67 , created_by
68 , last_update_date
69 , last_updated_by
70 , last_update_login
71 , request_id
72 , program_application_id
73 , program_id
74 , program_update_date
75 , hierarchy_id
76 , hierarchy_name
77 , parent_organization_code
78 , parent_organization_id
79 , bucket_size_code
80 , bucket_start_date
81 , bucket_end_date
82 , inventory_item_id
83 , organization_id
84 , hierarchy_delta_qty
85 , hierarchy_end_on_hand_qty
86 , org_received_qty
87 , org_issued_qty
88 , org_delta_qty
89 , org_end_on_hand_qty
90 )
91 VALUES
92 ( p_data_set_name
93 , p_bucket_name
94 , p_organization_code
95 , p_inventory_item_code
96 , NVL(p_creation_date, SYSDATE)
97 , NVL(p_created_by, NVL(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')), 0))
98 , NVL(p_last_update_date, SYSDATE)
99 , NVL(p_last_updated_by, NVL(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')), 0))
100 , NVL(p_last_update_login, TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')))
101 , NVL(p_request_id, TO_NUMBER(FND_PROFILE.VALUE('CONC_REQ_ID')))
102 , NVL(p_program_application_id,TO_NUMBER(FND_PROFILE.Value('PROG_APPL_ID')))
103 , NVL(p_program_id, TO_NUMBER(FND_PROFILE.Value('CONC_PROG_ID')))
104 , NVL(p_program_update_date, SYSDATE)
105 , p_hierarchy_id
106 , p_hierarchy_name
107 , p_parent_organization_code
108 , p_parent_organization_id
109 , p_bucket_size_code
110 , p_bucket_start_date
111 , p_bucket_end_date
112 , p_inventory_item_id
113 , p_organization_id
114 , p_hierarchy_delta_qty
115 , p_hierarchy_end_on_hand_qty
116 , p_org_received_qty
117 , p_org_issued_qty
118 , p_org_delta_qty
119 , p_org_end_on_hand_qty
120 );
121 END Insert_Row;
122
123 --========================================================================
124 -- PROCEDURE : Update_Hierarchy_Data PUBLIC
125 -- PARAMETERS: p_data_set_name identifies row (1/4)
126 -- p_bucket_name identfies row (2/4)
127 -- p_organization_id identifies_row (3/4)
128 -- p_inventory_item_id identifies_row (4/4)
129 -- p_????? one parameter per column to update
130 -- COMMENT : Updates the hierarchy data on a row in MTL_MGD_INVENTORY_POSITIONS;
131 -- standard who value are optional and defaulted from profile options
132 --=======================================================================--
133 PROCEDURE Update_Hierarchy_Data
134 ( p_data_set_name IN VARCHAR2
135 , p_bucket_name IN VARCHAR2
136 , p_organization_id IN NUMBER
137 , p_inventory_item_id IN NUMBER
138 , p_last_update_date IN DATE DEFAULT NULL
139 , p_last_updated_by IN NUMBER DEFAULT NULL
140 , p_last_update_login IN NUMBER DEFAULT NULL
141 , p_request_id IN NUMBER DEFAULT NULL
142 , p_program_application_id IN NUMBER DEFAULT NULL
143 , p_program_id IN NUMBER DEFAULT NULL
144 , p_program_update_date IN DATE DEFAULT NULL
145 , p_hierarchy_delta_qty IN NUMBER
146 , p_hierarchy_end_on_hand_qty IN NUMBER
147 )
148 IS
149 BEGIN
150
151 UPDATE mtl_mgd_inventory_positions
152 SET last_update_date = NVL(p_last_update_date, SYSDATE)
153 , last_updated_by = NVL
154 ( p_last_updated_by
155 , NVL(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')), 0)
156 )
157 , last_update_login = NVL
158 ( p_last_update_login
159 , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
160 )
161 , request_id = NVL
162 ( p_request_id
163 , TO_NUMBER(FND_PROFILE.VALUE('CONC_REQ_ID'))
164 )
165 , program_application_id = NVL
166 ( p_program_application_id
167 , TO_NUMBER
168 (FND_PROFILE.Value('PROG_APPL_ID'))
169 )
170 , program_id = NVL
171 ( p_program_id
172 , TO_NUMBER
173 (FND_PROFILE.Value('CONC_PROG_ID'))
174 )
175 , program_update_date = NVL(p_program_update_date, SYSDATE)
176 , hierarchy_delta_qty = p_hierarchy_delta_qty
177 , hierarchy_end_on_hand_qty = p_hierarchy_end_on_hand_qty
178 WHERE data_set_name = p_data_set_name
179 AND bucket_name = p_bucket_name
180 AND organization_id = p_organization_id
181 AND inventory_item_id = p_inventory_item_id;
182
183 END Update_Hierarchy_Data;
184
185 --========================================================================
186 -- PROCEDURE : Delete PUBLIC
187 -- PARAMETERS: p_data_set_name delete specific data set name
188 -- p_created_by delete data set for specific user ID
189 -- p_creation_date delete data set created before date
190 -- COMMENT : Delete rows using one or more specified criteria (each criteria is
191 -- an additional filter
192 --=======================================================================--
193 PROCEDURE Delete
194 ( p_data_set_name IN VARCHAR2 DEFAULT NULL
195 , p_created_by IN NUMBER DEFAULT NULL
196 , p_creation_date IN DATE DEFAULT NULL
197 )
198 IS
199 BEGIN
200 DELETE
201 FROM mtl_mgd_inventory_positions
202 WHERE data_set_name = NVL(p_data_set_name, data_set_name)
203 AND created_by = NVL(p_created_by, created_by)
204 AND creation_date <= NVL(p_creation_date, creation_date);
205 END Delete;
206
207 --========================================================================
208 -- PROCEDURE : Delete_All PUBLIC
209 -- COMMENT : Delete all rows
210 --=======================================================================--
211 PROCEDURE Delete_All
212 IS
213 BEGIN
214 DELETE
215 FROM mtl_mgd_inventory_positions;
216 END Delete_All;
217
218 END MTL_MGD_INV_POSITIONS_PKG;