1 PACKAGE INV_TRX_MGR AUTHID CURRENT_USER AS
2 /* $Header: INVJTRXS.pls 120.1 2005/06/21 19:32:26 appldev ship $ */
3
4
5 --
6 -- Name: PROCESS_TRX_BATCH
7 --
8 -- Input parameters:
9 -- p_header_id TRANSACTION_HEADER_ID column value of the records
10 -- that should be processed
11 -- p_commit 1 - Commit after processing
12 -- 0 - No commit after processing
13 -- p_atomic 1 - Treat batch as an atomic group. Rollback all
14 -- changes if any one record fails.
15 -- 0 - Treat each record seperately. If error, rollback
16 -- only changes related to that row.
17 -- p_business_flow_code : Code that determines which label to be printed
18 --
19 -- Output parameters:
20 -- x_proc_msg Message from the Process-Manager
21 -- return_status 0 on Success, 1 on Error
22 --
23 -- Description: This is the API to the INV Transaction Manager for
24 -- processing a batch of transaction record in the table
25 -- MTL_MATERIAL_TRANSACTIONS_TEMP grouped by TRANSACTION_HEADER_ID.
26 -- This function implements the logic which was previously in
27 -- inltpu.ppc .
28 --
29 -- Example :
30 -- The following code inserts a transaction for a serial and lot
31 -- controlled item.
32 --
33 -- String lnins = "{?=call INV_TRX_UTIL_PUB.INSERT_LINE_TRX(?,?,..?)}";
34 -- String lotins = "{?=call INV_TRX_UTIL_PUB.INSERT_LOT_TRX(?,?,..?)}";
35 -- String serins = "{?=call INV_TRX_UTIL_PUB.INSERT_SER_TRX(?,?,..?)}";
36 -- String proctrx = "{?=call INV_TRX_MGR.PROCESS_TRX_BATCH(?,?)}";
37 -- long TrxTmpId, SerTrxTmpId;
38 -- try{
39 -- // First insert the Transaction Line and retrieve TrxTempId
40 -- CallableStatement cs = conn.createCall(lnins);
41 -- CallableStatement csser = conn.createCall(serins);
42 -- csser.registerOutParameter(1, java.sql.Types.NUMERIC);
43 -- cs.registerOutParameter(1, java.sql.Types.NUMERIC);
44 --
45 -- cs.registerOutParameter(18, java.sql.Types.NUMERIC);
46 -- cs.setInt(2, itemId);
47 --
48 -- cs.executeQuery();
49 -- TrxTmpId = cs.getLong(18);
50 --
51 -- // If item is lot controlled, insert Lot-Transaction records
52 -- cs = conn.createCall(lotins);
53 -- cs.registerOutParameter(1, java.sql.Types.NUMERIC);
54 -- for (int k=0; k < lot_entered; k++){
55 -- cs.setLong(2, TrxTmpId);
56 -- cs.setLong(3, user_id);
57 -- cs.setString(4, LotVec.elementAt(k));
58 --
59 -- cs.registerOutParameter(8, java.sql.Types.NUMERIC);
60 -- cs.executeQuery();
61 -- SerTrxTmpId = cs.getLong(8);
62 --
63 -- // If item is also Serial-controlled, insert Serial Trx records
64 -- // If the item is also lot-controlled, provide the SerTrxTmpId
65 -- // returned by the INSERT_LOT_TRX call.
66 -- for (int k=0; k < ser_entered; k++){
67 -- csser.setLong(2, SerTrxTmpId);
68 -- csser.setLong(3, user_id);
69 -- csser.setString(4, SerVec.elementAt(k));
70 --
71 -- csser.executeQuery();
72 -- }
73 -- }
74 --
75 -- // Finally, execute this transaciton
76 -- cs = conn.createCall(proctrx);
77 -- cs.setLong(2, TrxTmpId);
78 -- cs.executeQuery();
79 -- // Check status of process
80 -- int retstatus = cs.getInt(1);
81 --
82 -- }catch(Exception e){
83 -- System.out.println("Error:"+e);
84 -- }
85 --
86 --
87 FUNCTION PROCESS_TRX_BATCH(p_header_id IN NUMBER,
88 p_commit IN NUMBER,
89 p_atomic IN NUMBER,
90 p_business_flow_code IN NUMBER,
91 x_proc_msg OUT NOCOPY VARCHAR2 ) RETURN NUMBER;
92
93
94 -- Name: PROCESS_TRX_BATCH
95 -- Overload function, for backwards compatability
96 FUNCTION PROCESS_TRX_BATCH(p_header_id IN NUMBER,
97 p_commit IN NUMBER,
98 p_atomic IN NUMBER,
99 x_proc_msg OUT NOCOPY VARCHAR2 ) RETURN NUMBER;
100
101 -- This is created to fix the G-I merge Issue, We dont have to port this to main line
102 --
103 -- Name: GENERATE_SERIALS (Over Loaded. Version 1)
104 --
105 -- Input parameters:
106 -- p_org_id Organization ID
107 -- p_item_id Item ID
108 -- p_qty Count of Serial Numbers
109 -- p_wip_id Wip Entity ID
110 -- p_rev Revision
111 -- p_lot Lot Number
112 --
113 -- Output parameters:
114 -- x_start_serial Starting Serial Number
115 -- x_end_serial Ending Serial Number
116 -- x_proc_msg Message from the Process-Manager
117 -- return_status 0 on Success, 1 on Error
118 --
119 -- Functions: This API generates a batch of Serial Numbers
120 -- in MTL_SERIAL_NUMBERS and sets their status as
121 -- 'DEFINED_BUT_NOT_USED'. Before inserting into the table
122 -- it ensures that there is no clash with existing Serial Numbers
123 -- as per the configured Serial-Number-Uniqueness attribute.
124 -- Note: This API works in an autonomous transaction
125 --
126 FUNCTION GENERATE_SERIALS( p_org_id IN NUMBER ,
127 p_item_id IN NUMBER ,
128 p_qty IN NUMBER ,
129 p_wip_id IN NUMBER ,
130 p_rev IN VARCHAR2,
131 p_lot IN VARCHAR2,
132 x_start_ser OUT NOCOPY VARCHAR2,
133 x_end_ser OUT NOCOPY VARCHAR2,
134 x_proc_msg OUT NOCOPY VARCHAR2 ) RETURN NUMBER;
135
136 FUNCTION VALIDATE_SERIALS( p_org_id IN NUMBER ,
137 p_item_id IN NUMBER ,
138 p_qty IN NUMBER ,
139 p_rev IN VARCHAR2 ,
140 p_lot IN VARCHAR2,
141 p_start_ser IN VARCHAR2,
142 p_trx_src_id IN NUMBER,
143 p_trx_action_id IN NUMBER,
144 x_end_ser IN OUT NOCOPY VARCHAR2,
145 x_proc_msg OUT NOCOPY VARCHAR2 ) RETURN NUMBER;
146
147 END INV_TRX_MGR;