INV*账户别名接收发放

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199

DECLARE     --p_old_new_flag  OLD 为导出 NEW 为导入     l_iface_rec         inv.mtl_transactions_interface%ROWTYPE;     l_iface_lot_rec     inv.mtl_transaction_lots_interface%ROWTYPE;     l_timeout           NUMBER;     l_outcome           BOOLEAN;     l_error_code        VARCHAR2(100);     l_error_explanation VARCHAR2(100);     l_request_id        NUMBER;     l_sqlerrm           VARCHAR2(1000);       l_inventory_item_id NUMBER;     l_organization_id   NUMBER;     l_uom               VARCHAR2(240);     l_disposition_id    NUMBER;     l_subinventory_code VARCHAR2(240);     l_count             NUMBER;     l_locator_id        NUMBER;     l_error_msg         VARCHAR2(32767);       CURSOR cur IS         SELECT t.import_id,                      t.organization_code,                      t.item_number,                      t.subinventory_code,                      t.transaction_type transaction_type1,                      decode(t.transaction_type,                                     '帐户别名接收',                                     41,                                     '帐户别名发放',                                     31,                                     NULL) transaction_type,                      t.account_alias,                      t.transaction_uom,                      t.transaction_date,                      t.lot_number,                      decode(t.transaction_type,                                     '帐户别名接收',                                     t.transaction_quantity,                                     '帐户别名发放',                                     t.transaction_quantity * (-1),                                     NULL) transaction_quantity,                      t.attribute1,                      t.attribute2,                      t.attribute3,                      t.attribute4,                      t.attribute5         FROM   cux_inv_mmt_account_temp t         WHERE  1 = 1         --AND    t.item_number = '1515453000051'     ; BEGIN       mo_global.init('INV');     fnd_global.apps_initialize(user_id      => 1318,                                                          resp_id      => 50559,                                                          resp_appl_id => 222);     mo_global.set_policy_context('S',                                                              83);       l_count := 0;     FOR rec IN cur     LOOP               l_error_msg                       := NULL;         l_iface_rec.last_update_date      := SYSDATE;         l_iface_rec.last_updated_by       := -1;         l_iface_rec.creation_date         := SYSDATE;         l_iface_rec.created_by            := -1;         l_iface_rec.last_update_login     := -1;         l_iface_lot_rec.last_update_date  := SYSDATE;         l_iface_lot_rec.last_updated_by   := -1;         l_iface_lot_rec.creation_date     := SYSDATE;         l_iface_lot_rec.created_by        := -1;         l_iface_lot_rec.last_update_login := -1;               SELECT mtl_material_transactions_s.nextval         INTO   l_iface_rec.transaction_interface_id         FROM   dual;               BEGIN             SELECT msi.inventory_item_id,                          msi.organization_id,                          msi.primary_uom_code             INTO   l_inventory_item_id, l_organization_id, l_uom             FROM   mtl_system_items_b msi, org_organization_definitions ood             WHERE  1 = 1             AND    msi.organization_id = ood.organization_id             AND    ood.organization_code = rec.organization_code             AND    msi.segment1 = rec.item_number;         EXCEPTION             WHEN OTHERS THEN                 l_error_msg := l_error_msg || '物料' || rec.item_number || '不存在';         END;               BEGIN             SELECT mgd.disposition_id             INTO   l_disposition_id             FROM   mtl_generic_dispositions mgd             WHERE  1 = 1             AND    mgd.organization_id = l_organization_id             AND    mgd.segment1 = rec.account_alias;         EXCEPTION             WHEN OTHERS THEN                 l_error_msg := l_error_msg || '账户别名不存在';         END;               BEGIN             SELECT msi.secondary_inventory_name             INTO   l_subinventory_code             FROM   mtl_secondary_inventories msi             WHERE  1 = 1             AND    msi.organization_id = l_organization_id             AND    msi.secondary_inventory_name = rec.subinventory_code;         EXCEPTION             WHEN OTHERS THEN                 l_error_msg := l_error_msg || '子库存不存在';         END;               IF (rec.attribute1 IS NOT NULL) THEN             BEGIN                 SELECT mik.inventory_location_id                 INTO   l_locator_id                 FROM   mtl_item_locations_kfv mik                 WHERE  mik.concatenated_segments = rec.attribute1                 AND    mik.organization_id = l_organization_id                 AND    mik.subinventory_code = rec.subinventory_code;             EXCEPTION                 WHEN no_data_found THEN                     l_error_msg := l_error_msg || '货位不存在';             END;         END IF;               --dbms_output.put_line('1!');               l_iface_rec.transaction_header_id := l_iface_rec.transaction_interface_id;         l_iface_rec.transaction_mode      := 3;         l_iface_rec.process_flag          := 1;         l_iface_rec.transaction_type_id   := rec.transaction_type; --41 帐户别名接收  31 帐户别名发放         l_iface_rec.transaction_source_id := l_disposition_id; --来源         l_iface_rec.organization_id       := l_organization_id;         l_iface_rec.inventory_item_id     := l_inventory_item_id;         l_iface_rec.subinventory_code     := rec.subinventory_code;         l_iface_rec.locator_id            := l_locator_id;         l_iface_rec.transaction_quantity  := rec.transaction_quantity; --出库数量为负数 入库数量为正数         l_iface_rec.primary_quantity      := rec.transaction_quantity;         l_iface_rec.transaction_uom       := l_uom;         l_iface_rec.transaction_date      := rec.transaction_date;         l_iface_rec.source_code           := '账户别名接收/发放';         l_iface_rec.source_header_id      := to_number(to_char(SYSDATE,                                                                                                                      'YYYYMMDDHH24MISS'));         l_iface_rec.source_line_id        := to_number(to_char(SYSDATE,                                                                                                                      'YYYYMMDDHH24MISS'));               IF (l_error_msg IS NULL) THEN             INSERT INTO inv.mtl_transactions_interface VALUES l_iface_rec;         END IF;               IF (rec.lot_number IS NOT NULL) THEN             l_iface_lot_rec.transaction_interface_id := l_iface_rec.transaction_interface_id;             l_iface_lot_rec.lot_number               := rec.lot_number;             l_iface_lot_rec.transaction_quantity     := l_iface_rec.transaction_quantity;             l_iface_lot_rec.source_code              := l_iface_rec.source_code;             l_iface_lot_rec.source_line_id           := l_iface_rec.source_line_id;                       IF (l_error_msg IS NULL) THEN                 INSERT INTO inv.mtl_transaction_lots_interface                 VALUES l_iface_lot_rec;             END IF;         END IF;               IF (l_error_msg IS NULL) THEN             l_timeout := 10;             l_outcome := mtl_online_transaction_pub.process_online(p_transaction_header_id => l_iface_rec.transaction_header_id,                                                                                                                          p_timeout               => l_timeout,                                                                                                                          p_error_code            => l_error_code,                                                                                                                          p_error_explanation     => l_error_explanation);                       IF (l_outcome = FALSE) THEN                 dbms_output.put_line('Failed*******Import_id:' || rec.import_id);                 dbms_output.put_line('Failed!');                 dbms_output.put_line('l_error_code:' || l_error_code);                 dbms_output.put_line('l_error_explanation:' || l_error_explanation);             ELSIF (l_outcome = TRUE) THEN                 l_count := l_count + 1;             END IF;         END IF;               IF (l_error_msg IS NOT NULL) THEN             dbms_output.put_line('l_error_msg:' || l_error_msg);         END IF;           END LOOP;     dbms_output.put_line('SuccessFul:' || l_count);   EXCEPTION     WHEN OTHERS THEN         dbms_output.put_line('Exception!' || SQLERRM); END;

　　

 --期初数据导入，创建表存储

create table cux_inv_mmt_account_temp(
IMPORT_ID                 NUMBER,
ORGANIZATION_CODE         VARCHAR2(240),
ITEM_NUMBER               VARCHAR2(240),
SUBINVENTORY_CODE         VARCHAR2(240),
TRANSACTION_TYPE          VARCHAR2(240),
ACCOUNT_ALIAS             VARCHAR2(240),
TRANSACTION_UOM           VARCHAR2(240),
TRANSACTION_DATE          DATE,
LOT_NUMBER                VARCHAR2(240),
TRANSACTION_QUANTITY      NUMBER,
ATTRIBUTE1                VARCHAR2(240),
ATTRIBUTE2                VARCHAR2(240),
ATTRIBUTE3                VARCHAR2(240),
ATTRIBUTE4                VARCHAR2(240),
ATTRIBUTE5                VARCHAR2(240)
)