1 PACKAGE INV_GLOBALS AUTHID CURRENT_USER AS
2 /* $Header: INVSGLBS.pls 120.1 2005/07/15 05:05:15 ramarava noship $ */
3
4 -- Procedure GET_ENTITIES_TBL;
5 --
6 -- Used by generator to avoid overriding or duplicating existing
7 -- entity constants.
8 --
9 PROCEDURE GET_ENTITIES_TBL;
10
11 -- Product entity constants.
12 G_ORG_ID NUMBER := NULL;
13 G_MAX_LINE_NUM NUMBER := NULL;
14 G_CALL_MODE VARCHAR2(10) := NULL;
15
16 -- Transaction Types
17 G_TYPE_CYCLE_COUNT_ADJ CONSTANT NUMBER := 4;
18 G_TYPE_CYCLE_COUNT_SUBXFR CONSTANT NUMBER := 5;
19 G_TYPE_PHYSICAL_COUNT_ADJ CONSTANT NUMBER := 8;
20 G_TYPE_PHYSICAL_COUNT_SUBXFR CONSTANT NUMBER := 9;
21 G_TYPE_LOGL_IC_SHIP_RECEIPT CONSTANT NUMBER := 10;
22 G_TYPE_LOGL_IC_SALES_ISSUE CONSTANT NUMBER := 11;
23 G_TYPE_LOGL_IC_RECEIPT_RETURN CONSTANT NUMBER := 13;
24 G_TYPE_LOGL_IC_SALES_RETURN CONSTANT NUMBER := 14;
25 G_TYPE_LOGL_RMA_RECEIPT CONSTANT NUMBER := 16;
26 G_TYPE_PO_RECEIPT CONSTANT NUMBER := 18;
27 G_TYPE_LOGL_PO_RECEIPT CONSTANT NUMBER := 19;
28 G_TYPE_RETRO_PRICE_UPDATE CONSTANT NUMBER := 20;
29 G_TYPE_LOGL_IC_PROC_RECEIPT CONSTANT NUMBER := 22;
30 G_TYPE_LOGL_IC_PROC_RETURN CONSTANT NUMBER := 23;
31 G_TYPE_LOGL_EXP_REQ_RECEIPT CONSTANT NUMBER := 27;
32 G_TYPE_LOGL_SALES_ORDER_ISSUE CONSTANT NUMBER := 30;
33 G_TYPE_XFER_ORDER_WIP_ISSUE CONSTANT NUMBER := 35;
34 G_TYPE_LOGL_RETURN_TO_VENDOR CONSTANT NUMBER := 39;
35 G_TYPE_XFER_ORDER_REPL_SUBXFR CONSTANT NUMBER := 51;
36 G_TYPE_TRANSFER_ORDER_STGXFR CONSTANT NUMBER := 52;
37 G_TYPE_INTERNAL_ORDER_STGXFR CONSTANT NUMBER := 53;
38 G_TYPE_TRANSFER_ORDER_ISSUE CONSTANT NUMBER := 63;
39 G_TYPE_TRANSFER_ORDER_SUBXFR CONSTANT NUMBER := 64;
40 G_TYPE_LOGL_PO_RECEIPT_ADJ CONSTANT NUMBER := 69;
41 G_TYPE_INV_LOT_SPLIT CONSTANT NUMBER := 82;
42 G_TYPE_INV_LOT_MERGE CONSTANT NUMBER := 83;
43 G_TYPE_INV_LOT_TRANSLATE CONSTANT NUMBER := 84;
44 G_TYPE_CONTAINER_PACK CONSTANT NUMBER := 87;
45 G_TYPE_CONTAINER_UNPACK CONSTANT NUMBER := 88;
46 G_TYPE_CONTAINER_SPLIT CONSTANT NUMBER := 89;
47 G_TYPE_PO_RCPT_ADJ CONSTANT NUMBER := 71;
48 G_TYPE_RETURN_TO_VENDOR CONSTANT NUMBER := 36;
49
50 -- Transaction Source Types
51 G_SOURCETYPE_PURCHASEORDER CONSTANT NUMBER := 1;
52 G_SOURCETYPE_SALESORDER CONSTANT NUMBER := 2;
53 G_SOURCETYPE_ACCOUNT CONSTANT NUMBER := 3;
54 G_SOURCETYPE_MOVEORDER CONSTANT NUMBER := 4;
55 G_SOURCETYPE_WIP CONSTANT NUMBER := 5;
56 G_SOURCETYPE_ACCOUNTALIAS CONSTANT NUMBER := 6;
57 G_SOURCETYPE_INTREQ CONSTANT NUMBER := 7;
58 G_SOURCETYPE_INTORDER CONSTANT NUMBER := 8;
59 G_SOURCETYPE_CYCLECOUNT CONSTANT NUMBER := 9;
60 G_SOURCETYPE_PHYSICALCOUNT CONSTANT NUMBER := 10;
61 G_SOURCETYPE_STDCOSTUPDATE CONSTANT NUMBER := 11;
62 G_SOURCETYPE_RMA CONSTANT NUMBER := 12;
63 G_SOURCETYPE_INVENTORY CONSTANT NUMBER := 13;
64 G_SOURCETYPE_PERCOSTUPDATE CONSTANT NUMBER := 14;
65 G_SOURCETYPE_LAYERCOSTUPDATE CONSTANT NUMBER := 15;
66 G_SOURCETYPE_PRJCONTRACTS CONSTANT NUMBER := 16;
67 G_SOURCETYPE_EXTREQ CONSTANT NUMBER := 17;
68
69 -- Transaction Actions
70 G_ACTION_ISSUE CONSTANT NUMBER := 1;
71 G_ACTION_SUBXFR CONSTANT NUMBER := 2;
72 G_ACTION_ORGXFR CONSTANT NUMBER := 3;
73 G_ACTION_CYCLECOUNTADJ CONSTANT NUMBER := 4;
74 G_ACTION_PLANXFR CONSTANT NUMBER := 5;
75 G_ACTION_OWNXFR CONSTANT NUMBER := 6;
76 G_ACTION_LOGICALISSUE CONSTANT NUMBER := 7;
77 G_ACTION_PHYSICALCOUNTADJ CONSTANT NUMBER := 8;
78 G_ACTION_LOGICALICSALES CONSTANT NUMBER := 9;
79 G_ACTION_LOGICALICRECEIPT CONSTANT NUMBER := 10;
80 G_ACTION_LOGICALDELADJ CONSTANT NUMBER := 11;
81 G_ACTION_INTRANSITRECEIPT CONSTANT NUMBER := 12;
82 G_ACTION_LOGICALICRCPTRETURN CONSTANT NUMBER := 13;
83 G_ACTION_LOGICALICSALESRETURN CONSTANT NUMBER := 14;
84 G_ACTION_LOGICALEXPREQRECEIPT CONSTANT NUMBER := 17;
85 G_ACTION_INTRANSITSHIPMENT CONSTANT NUMBER := 21;
86 G_ACTION_COSTUPDATE CONSTANT NUMBER := 24;
87 G_ACTION_RETROPRICEUPDATE CONSTANT NUMBER := 25;
88 G_ACTION_LOGICALRECEIPT CONSTANT NUMBER := 26;
89 G_ACTION_RECEIPT CONSTANT NUMBER := 27;
90 G_ACTION_STGXFR CONSTANT NUMBER := 28;
91 G_ACTION_DELIVERYADJ CONSTANT NUMBER := 29;
92 G_ACTION_WIPSCRAP CONSTANT NUMBER := 30;
93 G_ACTION_ASSYCOMPLETE CONSTANT NUMBER := 31;
94 G_ACTION_ASSYRETURN CONSTANT NUMBER := 32;
95 G_ACTION_NEGCOMPISSUE CONSTANT NUMBER := 33;
96 G_ACTION_NEGCOMPRETURN CONSTANT NUMBER := 34;
97 G_ACTION_INV_LOT_SPLIT CONSTANT NUMBER := 40;
98 G_ACTION_INV_LOT_MERGE CONSTANT NUMBER := 41;
99 G_ACTION_INV_LOT_TRANSLATE CONSTANT NUMBER := 42;
100 G_ACTION_CONTAINERPACK CONSTANT NUMBER := 50;
101 G_ACTION_CONTAINERUNPACK CONSTANT NUMBER := 51;
102 G_ACTION_CONTAINERSPLIT CONSTANT NUMBER := 52;
103 G_ACTION_COSTGROUPXFR CONSTANT NUMBER := 55;
104
105 -- GEN entities
106 G_ENTITY_ALL CONSTANT VARCHAR2(30) := 'ALL';
107 G_ENTITY_TROHDR CONSTANT VARCHAR2(30) := 'TROHDR';
108 G_ENTITY_TROLIN CONSTANT VARCHAR2(30) := 'TROLIN';
109
110 G_MATERIAL_STATUS_ACTIVE CONSTANT NUMBER := 1;
111
112 -- Move order types
113 G_MOVE_ORDER_REQUISITION CONSTANT NUMBER := 1;
114 G_MOVE_ORDER_REPLENISHMENT CONSTANT NUMBER := 2;
115 G_MOVE_ORDER_PICK_WAVE CONSTANT NUMBER := 3;
116 G_MOVE_ORDER_RECEIPT CONSTANT NUMBER := 4;
117 G_MOVE_ORDER_MFG_PICK CONSTANT NUMBER := 5;
118 G_MOVE_ORDER_WIP_ISSUE CONSTANT NUMBER := 5;
119 G_MOVE_ORDER_PUT_AWAY CONSTANT NUMBER := 6;
120 G_MOVE_ORDER_BACKFLUSH CONSTANT NUMBER := 7;
121 G_MOVE_ORDER_SYSTEM CONSTANT NUMBER := 8;
122
123 -- Move Order Status
124 G_TO_STATUS_INCOMPLETE CONSTANT NUMBER := 1;
125 G_TO_STATUS_PENDING_APPROVAL CONSTANT NUMBER := 2;
126 G_TO_STATUS_APPROVED CONSTANT NUMBER := 3;
127 G_TO_STATUS_REJECTED CONSTANT NUMBER := 4;
128 G_TO_STATUS_CLOSED CONSTANT NUMBER := 5;
129 G_TO_STATUS_CANCELLED CONSTANT NUMBER := 6;
130 G_TO_STATUS_PREAPPROVED CONSTANT NUMBER := 7;
131 G_TO_STATUS_PART_APPROVED CONSTANT NUMBER := 8;
132 G_TO_STATUS_CANCEL_BY_SOURCE CONSTANT NUMBER := 9;
133
134 -- SubInventory Flags
135 G_SUBINVENTORY_RESERVABLE CONSTANT NUMBER := 1;
136 G_SUBINVENTORY_NON_RESERVABLE CONSTANT NUMBER := 2;
137 G_SUBINVENTORY_LPN_CONTROLLED CONSTANT NUMBER := 1;
138
139 -- Operations.
140 G_OPR_CREATE CONSTANT VARCHAR2(30) := 'CREATE';
141 G_OPR_UPDATE CONSTANT VARCHAR2(30) := 'UPDATE';
142 G_OPR_DELETE CONSTANT VARCHAR2(30) := 'DELETE';
143 G_OPR_LOCK CONSTANT VARCHAR2(30) := 'LOCK';
144 G_OPR_NONE CONSTANT VARCHAR2(30) := chr(0);
145
146 -- Locator Types
147 G_LOC_TYPE_DOCK_DOOR CONSTANT NUMBER := 1;
148 G_LOC_TYPE_STAGING_LANE CONSTANT NUMBER := 2;
149 G_LOC_TYPE_STORAGE_LOC CONSTANT NUMBER := 3;
150 G_LOC_TYPE_CONSOLIDATION CONSTANT NUMBER := 4;
151 G_LOC_TYPE_PACKING_STATION CONSTANT NUMBER := 5;
152
153 -- Max number of defaulting tterations.
154 G_MAX_DEF_ITERATIONS CONSTANT NUMBER := 5;
155
156 -- DONT USE THIS CONTANT. Its already defined in Source Types section.
157 G_SOURCE_TYPE_TRANSFER_ORDERS CONSTANT NUMBER := 4;
158
159 -- Index table type used by JVC controllers.
160 TYPE INDEX_TBL_TYPE IS TABLE OF BINARY_INTEGER
161 INDEX BY BINARY_INTEGER;
162
163 -- API Operation control flags.
164 TYPE CONTROL_REC_TYPE IS RECORD(
165 CONTROLLED_OPERATION BOOLEAN := FALSE
166 , DEFAULT_ATTRIBUTES BOOLEAN := TRUE
167 , CHANGE_ATTRIBUTES BOOLEAN := TRUE
168 , VALIDATE_ENTITY BOOLEAN := TRUE
169 , WRITE_TO_DB BOOLEAN := TRUE
170 , PROCESS BOOLEAN := TRUE
171 , PROCESS_ENTITY VARCHAR2(30) := G_ENTITY_ALL
172 , CLEAR_API_CACHE BOOLEAN := TRUE
173 , CLEAR_API_REQUESTS BOOLEAN := TRUE
174 , REQUEST_CATEGORY VARCHAR2(30) := NULL
175 , REQUEST_NAME VARCHAR2(30) := NULL);
176
177 -- Variable representing missing control record.
178 G_MISS_CONTROL_REC CONTROL_REC_TYPE;
179
180 -- API request record type.
181 TYPE REQUEST_REC_TYPE IS RECORD(
182 ENTITY VARCHAR2(30) := NULL
183 , STEP VARCHAR2(30) := NULL
184 , NAME VARCHAR2(30) := NULL
185 , CATEGORY VARCHAR2(30) := NULL
186 , PROCESSED BOOLEAN := FALSE
187 , ATTRIBUTE1 VARCHAR2(240) := NULL
188 , ATTRIBUTE2 VARCHAR2(240) := NULL
189 , ATTRIBUTE3 VARCHAR2(240) := NULL
190 , ATTRIBUTE4 VARCHAR2(240) := NULL
191 , ATTRIBUTE5 VARCHAR2(240) := NULL);
192
193 -- API Request table type.
194 TYPE REQUEST_TBL_TYPE IS TABLE OF REQUEST_REC_TYPE
195 INDEX BY BINARY_INTEGER;
196
197 -- Generic table types
198 TYPE BOOLEAN_TBL_TYPE IS TABLE OF BOOLEAN
199 INDEX BY BINARY_INTEGER;
200
201 TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER
202 INDEX BY BINARY_INTEGER;
203
204 TYPE VARCHAR_TBL_TYPE IS TABLE OF VARCHAR2(30)
205 INDEX BY BINARY_INTEGER;
206
207 -- Variable representing a missing table.
208 G_MISS_BOOLEAN_TBL BOOLEAN_TBL_TYPE;
209 G_MISS_NUMBER_TBL NUMBER_TBL_TYPE;
210 G_MISS_VARCHAR_TBL VARCHAR_TBL_TYPE;
211
212 FUNCTION IS_ISSUE_XFR_TRANSACTION(P_TRANSACTION_ACTION_ID IN NUMBER)
213 RETURN BOOLEAN;
214
215 -- Initialize control record.
216
217 FUNCTION INIT_CONTROL_REC(P_OPERATION IN VARCHAR2, P_CONTROL_REC IN CONTROL_REC_TYPE)
218 RETURN CONTROL_REC_TYPE;
219
220 -- Function Equal
221 -- Number comparison.
222
223 FUNCTION EQUAL(P_ATTRIBUTE1 IN NUMBER, P_ATTRIBUTE2 IN NUMBER)
224 RETURN BOOLEAN;
225
226 -- Varchar2 comparison.
227
228 FUNCTION EQUAL(P_ATTRIBUTE1 IN VARCHAR2, P_ATTRIBUTE2 IN VARCHAR2)
229 RETURN BOOLEAN;
230
231 -- Date comparison.
232
233 FUNCTION EQUAL(P_ATTRIBUTE1 IN DATE, P_ATTRIBUTE2 IN DATE)
234 RETURN BOOLEAN;
235
236 PROCEDURE SET_ORG_ID(P_ORG_ID IN NUMBER);
237
238 -- It sets the organization id in the server profile to this
239 -- new value so that PJM can use it appropriately to get the
240 -- projects segments of the locator.
241 PROCEDURE SET_PJM_ORG_ID(ORGANIZATION_ID IN NUMBER);
242
243 FUNCTION NO_NEG_ALLOWED(P_RESTRICT_FLAG IN NUMBER, P_NEG_FLAG IN NUMBER, P_ACTION IN NUMBER)
244 RETURN BOOLEAN;
245
246 FUNCTION LOCATOR_CONTROL(
247 X_RETURN_STATUS OUT NOCOPY VARCHAR2
248 , X_MSG_COUNT OUT NOCOPY NUMBER
249 , X_MSG_DATA OUT NOCOPY VARCHAR2
250 , P_ORG_CONTROL IN NUMBER
251 , P_SUB_CONTROL IN NUMBER
252 , P_ITEM_CONTROL IN NUMBER
253 , P_ITEM_LOC_RESTRICT IN NUMBER
254 , P_ORG_NEG_ALLOWED IN NUMBER
255 , P_ACTION IN NUMBER
256 )
257 RETURN NUMBER;
258
259 --
260 -- PURPOSE
261 -- In R12 the LE - OU link will be broken
262 -- There are some places where the SOB is derived using this link. In all these places the SOB needs
263 -- to be derived using the direct linkage of Inventory Org and Ledger if Inventory Organization ID is
264 -- known, otherwise if Operating Unit is knowng the linkage of Operating Unit and Leger is to be used.
265 -- For this purpose following two procedures are added to this package
266 -- 1. GET_COA_LEDGER_ID
267 -- 2. GET_LEDGER_INFO
268 --
269 -- These program is a New Supporting routine that returns the Chart of Accounts or Set of Books for a
270 -- given Legal Entity or or Operating Unit or an Inventory Org.
271 --
275 -- ARGUMENTS
272 --
273 -- PRIVATE PROCEDURE
274 --
276 --
277 -- X_return_Status Returns 1 if the procedure is successful otherwise returns 0
278 --
279 -- X_msg_data Returns the error message if the "X_return_Status" is 0
280 --
281 -- P_Account_Info_Context This parameter indicates if for a given context Chart of
282 -- Accounts is required or Set of books is required or both
283 -- of them is required. Valid values for this parameter are:
284 -- SOB -> To get the Set of Books ID/Ledger ID
285 -- COA -> To get the Chart of Accounts ID
286 -- BOTH -> To get both SOB and COA ids
287 --
288 -- P_ENTITY_CONTEXT This parameter determines the Accounting Context stored
289 -- for Legal Entities, Operating Units and Inventory
290 -- Organizations.Valid Values are:
291 -- Legal Entity Accounting -> For LE
292 -- Operating Unit Information -> For OU
293 -- Accounting Information -> For Inv Org
294 --
295 -- P_Org_Id This parameter will be the organization id of legal entity
296 -- or operating unit or inventory org depending on the value
297 -- passed in P_Context_Type parameter
298 --
299 -- X_ACCOUNT_INFO1 This parameter returns the Org_Information1 of
300 -- Hr_Organizaiton_Informaiton for the
301 -- Organizaiton ID passed.
302 --
303 -- X_ACCOUNT_INFO3 This parameter returns the Org_Information3 of
304 -- Hr_Organizaiton_Informaiton for the
305 -- Organizaiton ID passed.
306 --
307 -- X_COA_ID This Parameter will contain the Chart of Accounts for an
308 -- operating unit or Legal Entity or Inventory Org depding
309 -- on the value passed in P_Context_Type parameter
310 --
311 -- HISTORY
312 --
313 -- Created by: Karthik Sambasivam Date : 22-Apr-2005
314 --
315 -- CHANGES
316 -- WHO WHEN WHAT
317 --
318 /*PROCEDURE GET_COA_LEDGER_ID
319 (X_return_status OUT NOCOPY VARCHAR2,
320 X_msg_data OUT NOCOPY VARCHAR2,
321 P_Account_Info_Context IN VARCHAR2,
322 P_ENTITY_CONTEXT IN VARCHAR2,
323 P_Org_Id IN NUMBER,
324 X_ACCOUNT_INFO1 OUT NOCOPY NUMBER,
325 X_ACCOUNT_INFO3 OUT NOCOPY NUMBER,
326 X_COA_ID OUT NOCOPY NUMBER
327 );*/
328 --
329 -- PUBLIC PROCEDURE
330 --
331 -- ARGUMENTS
332 --
333 -- X_return_Status Returns 1 if the procedure is successful otherwise returns 0
334 --
335 -- X_msg_data Returns the error message if the "X_return_Status" is 0
336 --
337 -- P_Context_Type This parameter specifies the type of Org_Id passed. The valid
338 -- values for this parameter are:
339 -- LE -> Legal Entity
340 -- OU -> Operating Unit
341 -- IO -> Inventory Organization
342 --
343 -- P_Org_Id This Parameter will contain the Ledger ID for an operating
344 -- unit or Legal Entity or Inventory Org depding on the value
345 -- passed in P_Context_Type parameter
346 --
347 -- X_SOB_ID This Parameter will contain the Ledger ID for an operating
348 -- unit or Legal Entity or Inventory Org depding on the value
349 -- passed in P_Context_Type parameter
350 --
351 -- X_COA_ID This Parameter will contain the Chart of Accounts for an
352 -- operating unit or Legal Entity or Inventory Org depding on
353 -- the value passed in P_Context_Type parameter
354 --
355 -- P_Account_Info_Context This parameter indicates if for a given context Chart of
356 -- Accounts is required or Set of books is required or both
357 -- of them is required. Valid values for this parameter are:
358 -- SOB -> To get the Set of Books ID/Ledger ID
359 -- COA -> To get the Chart of Accounts ID
360 -- BOTH -> To get both SOB and COA ids.
361 --
362 -- HISTORY
363 --
364 -- Created by: Karthik Sambasivam Date : 22-Apr-2005
365 --
366 -- CHANGES
370 (X_return_status OUT NOCOPY VARCHAR2,
367 -- WHO WHEN WHAT
368 --
369 PROCEDURE GET_LEDGER_INFO
371 X_msg_data OUT NOCOPY VARCHAR2,
372 P_Context_Type IN VARCHAR2,
373 P_Org_Id IN NUMBER,
374 X_SOB_ID OUT NOCOPY NUMBER,
375 X_COA_ID OUT NOCOPY NUMBER,
376 P_Account_Info_Context IN VARCHAR2 DEFAULT 'SOB'
377 );
378
379 END INV_GLOBALS;