1--update_material_txn 2--created by jenrry 3DECLARE 4 p_mmti_rec mtl_transactions_interface%ROWTYPE; 5 p_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl; 6 x_return_status VARCHAR2 (2000); 7 p_validation_level NUMBER; 8 p_init_msg_list VARCHAR2 (2000); 9 p_commit VARCHAR2 (2000); 10 x_message_count NUMBER; 11 x_message_list VARCHAR2 (2000); 12 l_msg_count NUMBER; 13 l_msg_data VARCHAR2 (2000); 14 x_mmt_rec mtl_material_transactions%ROWTYPE; 15 x_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl; 16 l_txn_count NUMBER; 17 l_count_t NUMBER; 18 l_count_i NUMBER; 19 setup_failure EXCEPTION; 20 p_org_code VARCHAR2 (3) := 'PR1'; 21 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL'); 22 l_user_name VARCHAR2 (80); 23 l_user_id NUMBER; 24 25 CURSOR get_user_id (v_user_name IN VARCHAR2) 26 IS 27 SELECT user_id 28 FROM fnd_user 29 WHERE user_name = v_user_name; 30 31 PROCEDURE display_messages (p_msg_count IN NUMBER) 32 IS 33 MESSAGE VARCHAR2 (2000); 34 dummy NUMBER; 35 l_api_name CONSTANT VARCHAR2 (30) := 'DISPLAY_MESSAGES'; 36 BEGIN 37 FOR i IN 1 .. p_msg_count 38 LOOP 39 fnd_msg_pub.get (p_msg_index => i, 40 p_data => MESSAGE, 41 p_encoded => 'F', 42 p_msg_index_out => dummy 43 ); 44 DBMS_OUTPUT.put_line ('Message ' || TO_CHAR (i) || ' ' || MESSAGE); 45 END LOOP; 46 EXCEPTION 47 WHEN OTHERS 48 THEN 49 fnd_msg_pub.add_exc_msg ('wrapper for Update_Material_Txn', l_api_name); 50 END display_messages; 51BEGIN 52 DBMS_OUTPUT.ENABLE (20000); 53 l_user_name := 'PROCESS_OPS'; 54 55 OPEN get_user_id (l_user_name); 56 57 FETCH get_user_id 58 INTO l_user_id; 59 60 IF get_user_id%NOTFOUND 61 THEN 62 DBMS_OUTPUT.put_line ('Invalid User ' || l_user_name); 63 64 CLOSE get_user_id; 65 66 RAISE NO_DATA_FOUND; 67 END IF; 68 69 CLOSE get_user_id; 70 71 fnd_profile.initialize (l_user_id); 72 fnd_global.apps_initialize (user_id => l_user_id, 73 resp_id => NULL, 74 resp_appl_id => NULL 75 ); 76 x_return_status := fnd_api.g_ret_sts_success; 77 fnd_msg_pub.initialize; 78 gme_common_pvt.g_error_count := 0; 79 gme_common_pvt.set_timestamp; 80 gme_common_pvt.g_move_to_temp := fnd_api.g_false; 81 p_mmti_rec.source_code := 'OPM'; 82 p_mmti_rec.source_header_id := 19492; 83 p_mmti_rec.transaction_source_id := 186706; 84 p_mmti_rec.trx_source_line_id := 19492; 85--p_mmti_rec.last_updated_by := gme_common_pvt.g_user_ident; 86--p_mmti_rec.last_update_login := gme_common_pvt.g_user_ident ; 87--p_mmti_rec.last_update_date := gme_common_pvt.g_timestamp ; 88--p_mmti_rec.creation_date := gme_common_pvt.g_timestamp ; 89--p_mmti_rec.created_by := gme_common_pvt.g_user_ident; 90 p_mmti_rec.inventory_item_id := 178859; 91 p_mmti_rec.revision := NULL; 92 p_mmti_rec.organization_id := 1381; 93 p_mmti_rec.transaction_date := SYSDATE; 94 p_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue; 95 p_mmti_rec.transaction_action_id := gme_common_pvt.g_ing_issue_txn_action; 96 p_mmti_rec.transaction_quantity := -8; 97--p_mmti_rec.primary_quantity := 98 p_mmti_rec.transaction_uom := 'LB'; 99 p_mmti_rec.subinventory_code := 'PR0'; 100 p_mmti_rec.locator_id := 1355; 101 p_mmti_rec.transaction_source_type_id := 5; 102--p_mmti_rec.transaction_source_name := 103 p_mmti_rec.wip_entity_type := 10; 104--p_mmti_rec.reason_id := 105 106 /* p_mmli_tbl(1). last_update_date := gme_common_pvt.g_timestamp ; 107 p_mmli_tbl(1). last_updated_by := gme_common_pvt.g_user_ident ; 108 p_mmli_tbl(1). creation_date := gme_common_pvt.g_timestamp ; 109 p_mmli_tbl(1). created_by := gme_common_pvt.g_user_ident ; 110 p_mmli_tbl(1). lot_number := 'TCS' ; 111 p_mmli_tbl(1). transaction_quantity := ; */ 112 gme_api_pub.update_material_txn 113 (p_api_version => 2.0, 114 p_validation_level => gme_common_pvt.g_max_errors, 115 p_init_msg_list => fnd_api.g_false, 116 p_commit => fnd_api.g_true, 117 x_message_count => x_message_count, 118 x_message_list => x_message_list, 119 x_return_status => x_return_status, 120 p_transaction_id => 12137851, 121 p_mmti_rec => p_mmti_rec, 122 p_mmli_tbl => p_mmli_tbl, 123 p_create_lot => NULL, 124 p_generate_lot => NULL, 125 p_generate_parent_lot => NULL, 126 x_mmt_rec => x_mmt_rec, 127 x_mmln_tbl => x_mmln_tbl 128 ); 129 DBMS_OUTPUT.put_line ( 'msg_count from process trxns=' 130 || TO_CHAR (l_msg_count) 131 ); 132 133 IF l_msg_count > 1 134 THEN 135 display_messages (l_msg_count); 136 END IF; 137 138 DBMS_OUTPUT.put_line ( 'after process transactions x_return_status=' 139 || TO_CHAR (x_return_status) 140 ); 141-- Output the results 142 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.transaction_id = ' 143 || TO_CHAR (x_mmt_rec.transaction_id), 144 1, 145 255 146 ) 147 ); 148 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.transaction_type_id = ' 149 || TO_CHAR (x_mmt_rec.transaction_type_id), 150 1, 151 255 152 ) 153 ); 154 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.transaction_action_id = ' 155 || TO_CHAR (x_mmt_rec.transaction_action_id), 156 1, 157 255 158 ) 159 ); 160 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.transaction_source_id = ' 161 || TO_CHAR (x_mmt_rec.transaction_source_id), 162 1, 163 255 164 ) 165 ); 166 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.trx_source_line_id = ' 167 || TO_CHAR (x_mmt_rec.trx_source_line_id), 168 1, 169 255 170 ) 171 ); 172 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.source_line_id = ' 173 || TO_CHAR (x_mmt_rec.source_line_id), 174 1, 175 255 176 ) 177 ); 178 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.transaction_quantity = ' 179 || TO_CHAR (x_mmt_rec.transaction_quantity), 180 1, 181 255 182 ) 183 ); 184 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.transaction_uom = ' 185 || TO_CHAR (x_mmt_rec.transaction_uom), 186 1, 187 255 188 ) 189 ); 190 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.transaction_date = ' 191 || TO_CHAR (x_mmt_rec.transaction_date), 192 1, 193 255 194 ) 195 ); 196 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmln_tbl.count = ' 197 || TO_CHAR (x_mmln_tbl.COUNT), 198 1, 199 255 200 ) 201 ); 202 203 IF (x_mmln_tbl.COUNT > 0) 204 THEN 205 FOR i IN 1 .. x_mmln_tbl.COUNT 206 LOOP 207 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmln_tbl((i).lot_number = ' 208 || TO_CHAR (x_mmln_tbl.COUNT), 209 1, 210 255 211 ) 212 ); 213 END LOOP; 214 END IF; 215 216 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.LAST_UPDATE_DATE = ' 217 || TO_CHAR (x_mmt_rec.last_update_date), 218 1, 219 255 220 ) 221 ); 222 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.LAST_UPDATED_BY = ' 223 || TO_CHAR (x_mmt_rec.last_updated_by), 224 1, 225 255 226 ) 227 ); 228 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.CREATION_DATE = ' 229 || TO_CHAR (x_mmt_rec.creation_date), 230 1, 231 255 232 ) 233 ); 234 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.CREATED_BY = ' 235 || TO_CHAR (x_mmt_rec.created_by), 236 1, 237 255 238 ) 239 ); 240 DBMS_OUTPUT.put_line (SUBSTR ( 'x_mmt_rec.LAST_UPDATE_LOGIN = ' 241 || TO_CHAR (x_mmt_rec.last_update_login), 242 1, 243 255 244 ) 245 ); 246 DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status, 247 1, 248 255 249 ) 250 ); 251 DBMS_OUTPUT.put_line ('x_message_count = ' || TO_CHAR (x_message_count)); 252 DBMS_OUTPUT.put_line (SUBSTR ('x_message_list = ' || x_message_list, 1, 253 255) 254 ); 255 gme_common_pvt.count_and_get (x_count => x_message_count, 256 p_encoded => fnd_api.g_false, 257 x_data => x_message_list 258 ); 259 260 IF x_message_count > 1 261 THEN 262 display_messages (x_message_count); 263 END IF; 264EXCEPTION 265 WHEN setup_failure 266 THEN 267 DBMS_OUTPUT.put_line (' in setup failure'); 268 WHEN OTHERS 269 THEN 270 DBMS_OUTPUT.put_line (SUBSTR ( 'Error ' 271 || TO_CHAR (SQLCODE) 272 || ': ' 273 || SQLERRM, 274 1, 275 255 276 ) 277 ); 278 RAISE; 279END; 280/
Oracle EBS OPM update material txn
Wesley13
2021-10-11
1366 0 0
点赞
收藏
评论区
加载中...