[Home] [Help]
285: ,p_inst_hist_tbl IN OUT NOCOPY csi_diagnostics_pkg.T_NUM
286: ) IS
287: BEGIN
288: FOR i in p_inst_tbl.FIRST .. p_inst_tbl.LAST LOOP
289: select CSI_ITEM_INSTANCES_H_S.nextval
290: into p_inst_hist_tbl(i) from dual;
291: --
292: p_inst_rec_tab.INSTANCE_ID(i) := p_inst_tbl(i).INSTANCE_ID;
293: p_inst_rec_tab.INSTANCE_NUMBER(i) := p_inst_tbl(i).INSTANCE_NUMBER;
1173: INTO g_inst_rec.customer_view_flag,
1174: g_inst_rec.merchant_view_flag,
1175: g_inst_rec.quantity,
1176: g_inst_rec.object_version_number
1177: FROM csi_item_instances
1178: WHERE instance_id = g_inst_rec.instance_id;
1179:
1180: g_vl_qry_rec.instance_id := g_inst_rec.instance_id;
1181:
1509: quantity
1510: INTO p_instance_rec.customer_view_flag,
1511: p_instance_rec.merchant_view_flag,
1512: p_instance_rec.quantity
1513: FROM csi_item_instances
1514: WHERE instance_id = p_instance_rec.instance_id;
1515: --
1516: l_ctr := l_instance_tbl.count;
1517: l_ctr := l_ctr + 1;
1842: l_ctr := l_inst_rec_tab.instance_id.count;
1843: -- Insert into History
1844: log('Before Inserting into Instances history..');
1845: FORALL i in 1 .. l_inst_rec_tab.instance_id.count
1846: INSERT INTO CSI_ITEM_INSTANCES_H(
1847: INSTANCE_HISTORY_ID,
1848: INSTANCE_ID,
1849: TRANSACTION_ID,
1850: NEW_INSTANCE_NUMBER,
3904: last_vld_organization_id
3905: INTO l_inst_serial_number,
3906: l_inst_item_id,
3907: l_inst_vld_org_id
3908: FROM csi_item_instances
3909: WHERE instance_id = tld_inst_rec.instance_id;
3910: END IF;
3911:
3912: IF l_inst_serial_number is not null THEN
4178: l_inst_org_id,
4179: l_inst_subinv_name,
4180: l_inst_rev_num,
4181: l_inst_mig_flag
4182: FROM csi_item_instances
4183: WHERE inventory_item_id = srl_rec.item_id
4184: AND serial_number = srl_rec.serial_number;
4185:
4186: l_create_flag := 'N';
5772: l_inv_locator_id,
5773: l_location_type_code,
5774: l_instance_usage_code,
5775: l_location_id
5776: FROM csi_item_instances
5777: WHERE instance_id = p_instance_rec.instance_id;
5778:
5779: out(' Instance: '||
5780: fill(l_location_type_code, 15)||
5852: l_pa_project_task_id,
5853: l_accounting_class_code,
5854: l_wip_job_id,
5855: l_in_transit_order_line_id
5856: FROM csi_item_instances
5857: WHERE instance_id = p_instance_rec.instance_id;
5858:
5859: IF p_instance_rec.location_type_code = 'INVENTORY' THEN
5860:
6258: IF l_return_status <> fnd_api.g_ret_sts_success THEN
6259: raise fnd_api.g_exc_error;
6260: END IF;
6261:
6262: UPDATE csi_item_instances
6263: SET last_vld_organization_id = l_instance_rec.vld_organization_id,
6264: active_end_date = null
6265: WHERE instance_id = l_instance_rec.instance_id;
6266:
6293: END IF;
6294:
6295: SELECT object_version_number
6296: INTO l_instance_rec.object_version_number
6297: FROM csi_item_instances
6298: WHERE instance_id = l_instance_rec.instance_id;
6299:
6300: csi_item_instance_pub.update_item_instance(
6301: p_api_version => 1.0,
6332:
6333: ELSE
6334: log(' the same');
6335: IF nvl(l_instance_rec.active_end_date, fnd_api.g_miss_date) <> fnd_api.g_miss_date THEN
6336: UPDATE csi_item_instances
6337: SET active_end_date = l_instance_rec.active_end_date
6338: WHERE instance_id = l_instance_rec.instance_id;
6339: END IF;
6340:
6338: WHERE instance_id = l_instance_rec.instance_id;
6339: END IF;
6340:
6341: IF nvl(l_instance_rec.active_end_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
6342: UPDATE csi_item_instances
6343: SET active_end_date = null
6344: WHERE instance_id = l_instance_rec.instance_id;
6345: END IF;
6346: END IF;
6631: object_version_number
6632: INTO l_instance_id,
6633: l_quantity,
6634: l_object_version_number
6635: FROM csi_item_instances
6636: WHERE location_type_code = 'INVENTORY'
6637: AND instance_usage_code = 'IN_INVENTORY'
6638: AND inventory_item_id = stage_rec.item_id
6639: AND inv_organization_id = stage_rec.organization_id
6951: ELSE
6952:
6953: SELECT location_type_code
6954: INTO l_location_type_code
6955: FROM csi_item_instances
6956: WHERE instance_id = p_diag_txn_rec.inst_id;
6957:
6958: IF l_location_type_code IN ('INVENTORY', 'WIP') THEN
6959:
7345: IF p_diag_txn_rec.inst_id is not null THEN
7346:
7347: SELECT location_type_code
7348: INTO l_location_type_code
7349: FROM csi_item_instances
7350: WHERE instance_id = p_diag_txn_rec.inst_id;
7351:
7352: FOR inv_rec IN inv_cur (p_diag_txn_rec.mtl_txn_id)
7353: LOOP
7717: quantity
7718: INTO l_instance_rec.instance_id,
7719: l_instance_rec.object_version_number,
7720: l_instance_quantity
7721: FROM csi_item_instances
7722: WHERE inventory_item_id = inv_rec.item_id
7723: AND nvl(inventory_revision,'$$##$$') = nvl(inv_rec.revision, '$$##$$')
7724: AND nvl(lot_number,'$$##$$') = nvl(l_lot_number, '$$##$$')
7725: AND location_type_code = 'INVENTORY'
8020: quantity
8021: INTO l_instance_rec.instance_id,
8022: l_instance_rec.object_version_number,
8023: l_instance_quantity
8024: FROM csi_item_instances
8025: WHERE inventory_item_id = inv_rec.item_id
8026: AND nvl(inventory_revision,'$$##$$') = nvl(inv_rec.revision, '$$##$$')
8027: AND nvl(lot_number,'$$##$$') = nvl(l_lot_number, '$$##$$')
8028: AND location_type_code = 'IN_TRANSIT'
8310: object_version_number
8311: INTO l_instance_id,
8312: l_quantity,
8313: l_object_version_number
8314: FROM csi_item_instances
8315: WHERE location_type_code = 'INVENTORY'
8316: AND instance_usage_code = 'IN_INVENTORY'
8317: AND inventory_item_id = stage_rec.item_id
8318: AND inv_organization_id = stage_rec.organization_id
8990: SELECT instance_id ,
8991: object_version_number
8992: INTO l_instance_rec.instance_id,
8993: l_instance_rec.object_version_number
8994: FROM csi_item_instances
8995: WHERE inventory_item_id = srl_rec.inventory_item_id
8996: AND serial_number = srl_rec.serial_number;
8997:
8998: l_instance := to_char(l_instance_rec.instance_id);
9195: x_return_status => l_return_status);
9196:
9197: SELECT object_version_number
9198: INTO l_instance_rec.object_version_number
9199: FROM csi_item_instances
9200: WHERE instance_id = l_instance_rec.instance_id;
9201:
9202: log(' csi_item_instance_pub.update_item_instance');
9203:
9298: where cir.object_id = p_object_id
9299: and cir.relationship_type_code = l_rel_type_code
9300: and ((cir.active_end_date is null) or (cir.active_end_date > sysdate))
9301: and EXISTS (select 'x'
9302: from CSI_ITEM_INSTANCES cii
9303: where cii.instance_id = cir.subject_id
9304: and ((active_end_date is null) or (active_end_date > sysdate)));
9305: --
9306: l_ctr NUMBER := 0;
9428: --
9429: PROCEDURE Delete_Dup_Srl_Inv_Instance IS
9430: CURSOR CHECK_CUR IS
9431: select count(*)
9432: from CSI_ITEM_INSTANCES
9433: where location_type_code = 'INVENTORY'
9434: and instance_usage_code = 'IN_INVENTORY'
9435: and creation_date = last_update_date
9436: and serial_number is not null
9446: inv_subinventory_name,
9447: inv_locator_id,
9448: serial_number,
9449: last_update_date
9450: from csi_item_instances
9451: where instance_id between p_min and p_max
9452: and serial_number is not null
9453: and lot_number is null
9454: and location_type_code = 'INVENTORY'
9458: and last_update_date = creation_date;
9459: --
9460: CURSOR MIN_CSI IS
9461: select min(instance_id)
9462: from csi_item_instances
9463: where serial_number is not null
9464: and lot_number is null
9465: and location_type_code = 'INVENTORY'
9466: and instance_usage_code = 'IN_INVENTORY'
9469: and last_update_date = creation_date;
9470: --
9471: CURSOR MAX_CSI IS
9472: select max(instance_id)
9473: from csi_item_instances
9474: where serial_number is not null
9475: and lot_number is null
9476: and location_type_code = 'INVENTORY'
9477: and instance_usage_code = 'IN_INVENTORY'
9595: --
9596: IF l_instance_tbl.count > 0 THEN
9597: BEGIN
9598: FORALL j in l_instance_tbl.FIRST .. l_instance_tbl.LAST
9599: DELETE FROM CSI_ITEM_INSTANCES WHERE instance_id = l_instance_tbl(j);
9600: FORALL j in l_instance_tbl.FIRST .. l_instance_tbl.LAST
9601: DELETE FROM CSI_I_PARTIES WHERE instance_id = l_instance_tbl(j);
9602: FORALL j in l_instance_tbl.FIRST .. l_instance_tbl.LAST
9603: DELETE FROM CSI_I_VERSION_LABELS WHERE instance_id = l_instance_tbl(j);
9601: DELETE FROM CSI_I_PARTIES WHERE instance_id = l_instance_tbl(j);
9602: FORALL j in l_instance_tbl.FIRST .. l_instance_tbl.LAST
9603: DELETE FROM CSI_I_VERSION_LABELS WHERE instance_id = l_instance_tbl(j);
9604: FORALL j in l_txn_tbl.FIRST .. l_txn_tbl.LAST
9605: DELETE FROM CSI_ITEM_INSTANCES_H WHERE transaction_id = l_txn_tbl(j);
9606: FORALL j in l_txn_tbl.FIRST .. l_txn_tbl.LAST
9607: DELETE FROM CSI_I_PARTIES_H WHERE transaction_id = l_txn_tbl(j);
9608: FORALL j in l_txn_tbl.FIRST .. l_txn_tbl.LAST
9609: DELETE FROM CSI_I_VERSION_LABELS_H WHERE transaction_id = l_txn_tbl(j);
9621: PROCEDURE Update_Instance_Usage IS
9622: CURSOR CSI_CUR IS
9623: Select instance_id,location_type_code
9624: ,serial_number_control_code,serial_number,null usage_code
9625: from CSI_ITEM_INSTANCES cii
9626: ,MTL_SYSTEM_ITEMS_B msi
9627: Where cii.instance_usage_code is NULL
9628: and msi.inventory_item_id = cii.inventory_item_id
9629: and msi.organization_id = cii.last_vld_organization_id;
9680: End;
9681: --
9682: END LOOP;
9683: FORALL j in 1 .. instance_id_mig.count
9684: UPDATE CSI_ITEM_INSTANCES
9685: set instance_usage_code = usage_code_mig(j)
9686: ,last_update_date = sysdate
9687: where instance_id = instance_id_mig(j);
9688: commit;
9749: --
9750: PROCEDURE Del_API_Dup_Srl_Instance IS
9751: cursor check_count is
9752: select count(*)
9753: from csi_item_instances
9754: where serial_number is not null
9755: group by serial_number, inventory_item_id
9756: having count(*) > 1;
9757: --
9756: having count(*) > 1;
9757: --
9758: cursor c1 is
9759: select count(*), serial_number, inventory_item_id
9760: from csi_item_instances
9761: where serial_number is not null
9762: group by serial_number, inventory_item_id
9763: having count(*) > 1;
9764: --
9763: having count(*) > 1;
9764: --
9765: cursor c4(p_serial_number varchar, p_inventory_item_id number) is
9766: select instance_id,instance_status_id
9767: from csi_item_instances
9768: where serial_number = p_serial_number
9769: and inventory_item_id = p_inventory_item_id
9770: order by instance_id desc;
9771: --
9810: --
9811: Begin
9812: select max(instance_id)
9813: into v_instance_id
9814: from csi_item_instances
9815: where inventory_item_id = i.inventory_item_id
9816: and serial_number = i.serial_number;
9817: End;
9818: --
9820: loop
9821: if v_instance_id <> j.instance_id then
9822: v_duplicate_count := v_duplicate_count + 1;
9823:
9824: update csi_item_instances_h
9825: set old_serial_number = i.serial_number
9826: ,new_serial_number = new_serial_number||'-DUP'||to_char(v_duplicate_count)
9827: ,last_updated_by = fnd_global.user_id
9828: ,last_update_date = sysdate
9828: ,last_update_date = sysdate
9829: ,old_instance_status_id = j.instance_status_id
9830: ,new_instance_status_id = v_instance_status_id
9831: where instance_history_id = (select max(instance_history_id)
9832: from csi_item_instances_h
9833: where instance_id = j.instance_id);
9834: --
9835: update csi_item_instances
9836: set serial_number = serial_number||'-DUP'||to_char(v_duplicate_count)
9831: where instance_history_id = (select max(instance_history_id)
9832: from csi_item_instances_h
9833: where instance_id = j.instance_id);
9834: --
9835: update csi_item_instances
9836: set serial_number = serial_number||'-DUP'||to_char(v_duplicate_count)
9837: ,active_end_date = sysdate
9838: ,instance_status_id = v_instance_status_id
9839: ,last_updated_by = fnd_global.user_id
9860: ,serial_number,lot_number
9861: ,mfg_serial_number_flag,creation_complete_flag
9862: ,inventory_revision
9863: ,instance_usage_code
9864: FROM CSI_ITEM_INSTANCES
9865: WHERE location_type_code = 'INVENTORY'
9866: AND inv_organization_id is not null
9867: AND nvl(last_vld_organization_id,-999) <> inv_organization_id;
9868: --
9937: END IF;
9938: --
9939: END LOOP;
9940: FORALL j in 1 .. instance_id_mig.count
9941: Update CSI_ITEM_INSTANCES
9942: set last_vld_organization_id = vld_org_id_mig(j)
9943: where instance_id = instance_id_mig(j);
9944: --
9945: commit;
9944: --
9945: commit;
9946: --
9947: FORALL j in 1 .. instance_id_mig.count
9948: Update CSI_ITEM_INSTANCES_H
9949: set new_last_vld_organization_id = vld_org_id_mig(j)
9950: where instance_history_id = (select max(instance_history_id) from CSI_ITEM_INSTANCES_H
9951: where instance_id = instance_id_mig(j));
9952: --
9946: --
9947: FORALL j in 1 .. instance_id_mig.count
9948: Update CSI_ITEM_INSTANCES_H
9949: set new_last_vld_organization_id = vld_org_id_mig(j)
9950: where instance_history_id = (select max(instance_history_id) from CSI_ITEM_INSTANCES_H
9951: where instance_id = instance_id_mig(j));
9952: --
9953: commit;
9954: --
9965: ,a.last_vld_organization_id
9966: ,a.last_oe_order_line_id
9967: ,a.serial_number
9968: ,a.inventory_revision
9969: FROM csi_item_instances a
9970: ,mtl_system_items_b b
9971: WHERE a.inventory_item_id = b.inventory_item_id
9972: AND a.last_vld_organization_id = b.organization_id
9973: AND a.creation_complete_flag = 'Y'
10062: END IF;
10063: END LOOP;
10064: --
10065: FORALL j in 1 .. instance_id_mig.count
10066: UPDATE csi_item_instances
10067: SET inventory_revision = revision_mig(j)
10068: WHERE instance_id = instance_id_mig(j);
10069: commit;
10070: --
10068: WHERE instance_id = instance_id_mig(j);
10069: commit;
10070: --
10071: FORALL j in 1 .. instance_id_mig.count
10072: Update CSI_ITEM_INSTANCES_H
10073: set new_inventory_revision = revision_mig(j)
10074: where instance_history_id = (select max(instance_history_id) from CSI_ITEM_INSTANCES_H
10075: where instance_id = instance_id_mig(j));
10076: --
10070: --
10071: FORALL j in 1 .. instance_id_mig.count
10072: Update CSI_ITEM_INSTANCES_H
10073: set new_inventory_revision = revision_mig(j)
10074: where instance_history_id = (select max(instance_history_id) from CSI_ITEM_INSTANCES_H
10075: where instance_id = instance_id_mig(j));
10076: --
10077: commit;
10078: --
10084: --
10085: PROCEDURE Update_Dup_Srl_Instance IS
10086: CURSOR CSI_CUR IS -- Cursor to delete the Inv instances emerged from wrong WHERE clause in Mig
10087: select cii.instance_id
10088: from CSI_ITEM_INSTANCES cii
10089: where cii.location_type_code = 'INVENTORY'
10090: and cii.instance_usage_code = 'IN_INVENTORY'
10091: and mfg_serial_number_flag = 'Y'
10092: and cii.serial_number is not null
10115: l_del_txn_tbl NUMLIST;
10116: --
10117: cursor c1 is
10118: select count(*), serial_number, inventory_item_id
10119: from csi_item_instances
10120: where migrated_flag = 'Y'
10121: and serial_number is not null
10122: group by serial_number, inventory_item_id
10123: having count(*) > 1;
10123: having count(*) > 1;
10124:
10125: cursor c2(p_serial_number varchar, p_inventory_item_id number) is
10126: select max(instance_id)
10127: from csi_item_instances
10128: where serial_number = p_serial_number
10129: and inventory_item_id = p_inventory_item_id
10130: and location_type_code = 'INVENTORY'
10131: and instance_usage_code = 'IN_INVENTORY'
10133: and inv_organization_id IS NOT NULL;
10134:
10135: cursor c3(p_serial_number varchar, p_inventory_item_id number) is
10136: select max(instance_id)
10137: from csi_item_instances
10138: where serial_number = p_serial_number
10139: and inventory_item_id = p_inventory_item_id;
10140:
10141: cursor c5(p_serial_number varchar, p_inventory_item_id number) is
10139: and inventory_item_id = p_inventory_item_id;
10140:
10141: cursor c5(p_serial_number varchar, p_inventory_item_id number) is
10142: select max(instance_id)
10143: from csi_item_instances
10144: where serial_number = p_serial_number
10145: and inventory_item_id = p_inventory_item_id
10146: and last_oe_rma_line_id is not null
10147: and migrated_flag = 'Y';
10147: and migrated_flag = 'Y';
10148:
10149: cursor c4(p_serial_number varchar, p_inventory_item_id number) is
10150: select instance_id,instance_status_id
10151: from csi_item_instances
10152: where serial_number = p_serial_number
10153: and inventory_item_id = p_inventory_item_id
10154: order by instance_id desc;
10155: --
10154: order by instance_id desc;
10155: --
10156: cursor c6(p_inst_id1 number,p_inst_id2 number) is
10157: select transaction_id
10158: from csi_item_instances_h
10159: where instance_id = p_inst_id1
10160: intersect
10161: select transaction_id
10162: from csi_item_instances_h
10158: from csi_item_instances_h
10159: where instance_id = p_inst_id1
10160: intersect
10161: select transaction_id
10162: from csi_item_instances_h
10163: where instance_id = p_inst_id2;
10164:
10165: v_commit_count NUMBER := 0;
10166: v_instance_status_id NUMBER;
10222: --
10223: IF l_del_inst_tbl.count > 0 THEN
10224: BEGIN
10225: FORALL j in l_del_inst_tbl.FIRST .. l_del_inst_tbl.LAST
10226: DELETE FROM CSI_ITEM_INSTANCES WHERE instance_id = l_del_inst_tbl(j);
10227: FORALL j in l_del_inst_tbl.FIRST .. l_del_inst_tbl.LAST
10228: DELETE FROM CSI_I_PARTIES WHERE instance_id = l_del_inst_tbl(j);
10229: FORALL j in l_del_inst_tbl.FIRST .. l_del_inst_tbl.LAST
10230: DELETE FROM CSI_I_VERSION_LABELS WHERE instance_id = l_del_inst_tbl(j);
10228: DELETE FROM CSI_I_PARTIES WHERE instance_id = l_del_inst_tbl(j);
10229: FORALL j in l_del_inst_tbl.FIRST .. l_del_inst_tbl.LAST
10230: DELETE FROM CSI_I_VERSION_LABELS WHERE instance_id = l_del_inst_tbl(j);
10231: FORALL j in l_del_txn_tbl.FIRST .. l_del_txn_tbl.LAST
10232: DELETE FROM CSI_ITEM_INSTANCES_H WHERE transaction_id = l_del_txn_tbl(j);
10233: FORALL j in l_del_txn_tbl.FIRST .. l_del_txn_tbl.LAST
10234: DELETE FROM CSI_I_PARTIES_H WHERE transaction_id = l_del_txn_tbl(j);
10235: FORALL j in l_del_txn_tbl.FIRST .. l_del_txn_tbl.LAST
10236: DELETE FROM CSI_I_VERSION_LABELS_H WHERE transaction_id = l_del_txn_tbl(j);
10309: Begin
10310: select location_id,inv_organization_id
10311: ,inv_subinventory_name,inv_locator_id,inventory_revision
10312: into v_loc_id,v_org_id,v_subinv,v_locator,v_rev
10313: from CSI_ITEM_INSTANCES
10314: where instance_id = v_inv_instance_id;
10315: --
10316: Exception
10317: when no_data_found then
10343: from CSI_INSTANCE_STATUSES
10344: where name = v_status;
10345: end if;
10346:
10347: update CSI_ITEM_INSTANCES
10348: set location_type_code = 'INVENTORY'
10349: ,location_id = v_loc_id
10350: ,accounting_class_code = 'CUST_PROD'
10351: ,instance_usage_code = v_inst_usage_code
10375: close c6;
10376: --
10377: -- While suffixing with DUP, we use the instance_id in serial_number column to avoid serial #
10378: -- becoming more than 30 chars. The old serial_number is stored in the external_reference column.
10379: update csi_item_instances
10380: set external_reference = serial_number
10381: , serial_number = to_char(j.instance_id)||'-DUP'||to_char(v_duplicate_count)
10382: ,active_end_date = sysdate
10383: ,instance_status_id = v_instance_status_id
10385: ,last_update_date = sysdate
10386: where instance_id = j.instance_id;
10387: --
10388: if v_txn_id is null then
10389: update csi_item_instances_h
10390: set instance_id = v_instance_id
10391: ,new_external_reference = i.serial_number
10392: ,old_serial_number = nvl(new_serial_number,i.serial_number)
10393: ,new_serial_number = to_char(j.instance_id)||'-DUP'||to_char(v_duplicate_count)
10394: ,last_updated_by = fnd_global.user_id
10395: ,last_update_date = sysdate
10396: where instance_id = j.instance_id;
10397: else -- common txn found
10398: update csi_item_instances_h
10399: set new_external_reference = i.serial_number
10400: ,old_serial_number = nvl(new_serial_number,i.serial_number)
10401: ,new_serial_number = to_char(j.instance_id)||'-DUP'||to_char(v_duplicate_count)
10402: ,last_updated_by = fnd_global.user_id
10401: ,new_serial_number = to_char(j.instance_id)||'-DUP'||to_char(v_duplicate_count)
10402: ,last_updated_by = fnd_global.user_id
10403: ,last_update_date = sysdate
10404: where instance_history_id = (select max(instance_history_id)
10405: from csi_item_instances_h
10406: where instance_id = j.instance_id
10407: and creation_date < v_freeze_date);
10408: end if;
10409: end if;
10528: --
10529: PROCEDURE Update_Instance_Party_Source IS
10530: CURSOR CSI_INS_CUR IS
10531: SELECT instance_id,null
10532: FROM csi_item_instances
10533: WHERE (owner_party_source_table is null
10534: OR owner_party_source_table not in ('HZ_PARTIES','PO_VENDORS','EMPLOYEE','TEAM','GROUP'));
10535: --
10536: Type NumTabType is VARRAY(10000) of NUMBER;
10565: --
10566: END LOOP;
10567: --
10568: FORALL j in 1 .. instance_id_mig.count
10569: UPDATE CSI_ITEM_INSTANCES
10570: SET OWNER_PARTY_SOURCE_TABLE = party_src_table_mig(j)
10571: WHERE instance_id = instance_id_mig(j);
10572: commit;
10573: EXIT WHEN CSI_INS_CUR%NOTFOUND;
10840: ,-1
10841: ,1
10842: );
10843: --
10844: Update CSI_ITEM_INSTANCES
10845: set owner_party_id = l_internal_party_id,
10846: owner_party_account_id = null
10847: where instance_id = l_inst_tbl(j);
10848: --
10847: where instance_id = l_inst_tbl(j);
10848: --
10849: select last_vld_organization_id
10850: into l_vld_org_id
10851: from CSI_ITEM_INSTANCES
10852: where instance_id = l_inst_tbl(j);
10853: --
10854: CSI_Item_Instance_Pvt.Call_to_Contracts
10855: ( p_transaction_type => 'TRM'
10908: PROCEDURE Update_Master_Organization_ID IS
10909: CURSOR CSI_CUR IS
10910: select cii.instance_id,cii.inv_master_organization_id,
10911: cii.last_vld_organization_id
10912: from CSI_ITEM_INSTANCES cii
10913: where not exists (select 'x'
10914: from MTL_PARAMETERS msi
10915: where organization_id = inv_master_organization_id
10916: and master_organization_id = inv_master_organization_id);
10942: End;
10943: END LOOP;
10944: --
10945: FORALL i in 1..instance_id_mig.count
10946: UPDATE CSI_ITEM_INSTANCES
10947: set inv_master_organization_id = inv_master_org_id_mig(i)
10948: where instance_id = instance_id_mig(i);
10949: commit;
10950: --
10948: where instance_id = instance_id_mig(i);
10949: commit;
10950: --
10951: FORALL i in 1..instance_id_mig.count
10952: UPDATE CSI_ITEM_INSTANCES_H
10953: set old_inv_master_organization_id = decode(old_inv_master_organization_id,null,null,inv_master_org_id_mig(i)),
10954: new_inv_master_organization_id = decode(new_inv_master_organization_id,null,null,inv_master_org_id_mig(i))
10955: where instance_id = instance_id_mig(i);
10956: commit;
11164: BEGIN
11165: Begin
11166: select last_vld_organization_id
11167: into l_vld_org
11168: from CSI_ITEM_INSTANCES
11169: where instance_id = exp.instance_id;
11170: Exception
11171: when no_data_found then
11172: Raise Process_next;
11229: --
11230: PROCEDURE Expire_Non_Trackable_Instance IS
11231: CURSOR CSI_CUR IS
11232: select cii.instance_id,cii.inventory_item_id,cii.inv_master_organization_id
11233: from CSI_ITEM_INSTANCES cii
11234: where nvl(cii.active_end_date,(sysdate+1)) > sysdate;
11235: --
11236: CURSOR REL_CUR IS
11237: select cii.instance_id,cii.inventory_item_id,cii.inv_master_organization_id,
11235: --
11236: CURSOR REL_CUR IS
11237: select cii.instance_id,cii.inventory_item_id,cii.inv_master_organization_id,
11238: cir.relationship_id
11239: from CSI_ITEM_INSTANCES cii,
11240: CSI_II_RELATIONSHIPS cir
11241: where nvl(cii.active_end_date,(sysdate+1)) > sysdate
11242: and cir.subject_id = cii.instance_id
11243: and cir.relationship_type_code = 'COMPONENT-OF'
11400: --
11401: l_ctr := l_ctr + 1;
11402: l_inst_tbl(l_ctr) := instance_id_mig(i);
11403: --
11404: select CSI_ITEM_INSTANCES_H_S.nextval
11405: into l_inst_hist_tbl(l_ctr) from dual;
11406: --
11407: -- For this instance_id get the children
11408: l_child_tbl.DELETE;
11416: FOR rel_count in l_child_tbl.FIRST .. l_child_tbl.LAST LOOP
11417: l_ctr := l_ctr + 1;
11418: l_inst_tbl(l_ctr) := l_child_tbl(rel_count).subject_id;
11419: --
11420: select CSI_ITEM_INSTANCES_H_S.nextval
11421: into l_inst_hist_tbl(l_ctr) from dual;
11422: END LOOP;
11423: END IF;
11424: Exception
11440: 'EXPNL'
11441: );
11442: -- Bulk Update Instances
11443: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
11444: UPDATE CSI_ITEM_INSTANCES
11445: set active_end_date = sysdate,
11446: instance_status_id = 1,
11447: last_update_date = sysdate,
11448: last_updated_by = v_user_id
11449: where instance_id = l_inst_tbl(j);
11450: --
11451: -- Tie the Transaction to the history
11452: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
11453: INSERT INTO CSI_ITEM_INSTANCES_H
11454: (
11455: INSTANCE_HISTORY_ID
11456: ,TRANSACTION_ID
11457: ,INSTANCE_ID
11666: which are already shipped to customers */
11667: /* CURSOR CSI_SRL_CUR IS
11668: select cii.instance_id,cii.serial_number,
11669: cii.last_vld_organization_id,cii.inventory_item_id
11670: from CSI_ITEM_INSTANCES cii
11671: where cii.serial_number is not null
11672: and cii.last_vld_organization_id is not null
11673: and cii.inv_organization_id is null
11674: and cii.inv_subinventory_name is null; -- to filter Inventory instances
11675: --
11676: CURSOR CSI_LOT_CUR IS
11677: select cii.instance_id,cii.lot_number,
11678: cii.last_vld_organization_id,cii.inventory_item_id
11679: from CSI_ITEM_INSTANCES cii
11680: where cii.lot_number is not null
11681: and cii.last_vld_organization_id is not null
11682: and cii.inv_organization_id is null
11683: and cii.inv_subinventory_name is null; -- to filter Inventory instances
11685: */
11686: CURSOR INV_CSI_SRL_CUR IS
11687: select cii.instance_id,cii.serial_number,
11688: cii.last_vld_organization_id,cii.inventory_item_id
11689: from CSI_ITEM_INSTANCES cii
11690: where cii.serial_number is not null
11691: and cii.last_vld_organization_id is not null
11692: and cii.location_type_code = 'INVENTORY'
11693: and cii.instance_usage_code = 'IN_INVENTORY'
11698: -- Commented for bug#14835893
11699: /* CURSOR INV_CSI_LOT_CUR IS
11700: select cii.instance_id,cii.lot_number,
11701: cii.last_vld_organization_id,cii.inventory_item_id
11702: from CSI_ITEM_INSTANCES cii
11703: where cii.lot_number is not null
11704: and cii.last_vld_organization_id is not null
11705: and cii.inv_organization_id is not null
11706: and cii.location_type_code = 'INVENTORY'
11800: l_ctr := l_ctr + 1;
11801: l_inst_tbl(l_ctr) := instance_id_mig(i);
11802: l_srl_tbl(l_ctr) := serial_number_mig(i);
11803: --
11804: select CSI_ITEM_INSTANCES_H_S.nextval
11805: into l_inst_hist_tbl(l_ctr) from dual;
11806: END IF;
11807: --
11808: END LOOP;
11809: IF l_inst_tbl.count > 0 THEN
11810: -- Bulk Update Instances
11811: l_srl_ins_flag := 'Y';
11812: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
11813: UPDATE CSI_ITEM_INSTANCES
11814: set serial_number = null,
11815: mfg_serial_number_flag = 'N',
11816: last_update_date = sysdate,
11817: last_updated_by = v_user_id
11818: where instance_id = l_inst_tbl(j);
11819: --
11820: -- Tie the Transaction to the history
11821: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
11822: INSERT INTO CSI_ITEM_INSTANCES_H
11823: (
11824: INSTANCE_HISTORY_ID
11825: ,TRANSACTION_ID
11826: ,INSTANCE_ID
11884: l_ctr := l_ctr + 1;
11885: l_inst_tbl(l_ctr) := instance_id_mig(i);
11886: l_lot_tbl(l_ctr) := lot_number_mig(i);
11887: --
11888: select CSI_ITEM_INSTANCES_H_S.nextval
11889: into l_inst_hist_tbl(l_ctr) from dual;
11890: END IF;
11891: END LOOP;
11892: --
11893: IF l_inst_tbl.count > 0 THEN
11894: l_lot_ins_flag := 'Y';
11895: -- Bulk Update Instances
11896: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
11897: UPDATE CSI_ITEM_INSTANCES
11898: set lot_number = null,
11899: last_update_date = sysdate,
11900: last_updated_by = v_user_id
11901: where instance_id = l_inst_tbl(j);
11901: where instance_id = l_inst_tbl(j);
11902: --
11903: -- Tie the Transaction to the history
11904: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
11905: INSERT INTO CSI_ITEM_INSTANCES_H
11906: (
11907: INSTANCE_HISTORY_ID
11908: ,TRANSACTION_ID
11909: ,INSTANCE_ID
11965: IF nvl(v_srl_ctl,1) = 1 THEN
11966: l_ctr := l_ctr + 1;
11967: l_inst_tbl(l_ctr) := instance_id_mig(i);
11968: --
11969: select CSI_ITEM_INSTANCES_H_S.nextval
11970: into l_inst_hist_tbl(l_ctr) from dual;
11971: END IF;
11972: END LOOP;
11973: IF l_inst_tbl.count > 0 THEN
11973: IF l_inst_tbl.count > 0 THEN
11974: -- Bulk Update Instances
11975: l_srl_ins_flag := 'Y';
11976: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
11977: UPDATE CSI_ITEM_INSTANCES
11978: set active_end_date = sysdate,
11979: instance_status_id = 1,
11980: last_update_date = sysdate,
11981: last_updated_by = v_user_id
11982: where instance_id = l_inst_tbl(j);
11983: --
11984: -- Tie the Transaction to the history
11985: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
11986: INSERT INTO CSI_ITEM_INSTANCES_H
11987: (
11988: INSTANCE_HISTORY_ID
11989: ,TRANSACTION_ID
11990: ,INSTANCE_ID
12043: IF nvl(v_lot_ctl,1) = 1 THEN
12044: l_ctr := l_ctr + 1;
12045: l_inst_tbl(l_ctr) := instance_id_mig(i);
12046: --
12047: select CSI_ITEM_INSTANCES_H_S.nextval
12048: into l_inst_hist_tbl(l_ctr) from dual;
12049: END IF;
12050: END LOOP;
12051: --
12052: IF l_inst_tbl.count > 0 THEN
12053: l_lot_ins_flag := 'Y';
12054: -- Bulk Update Instances
12055: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
12056: UPDATE CSI_ITEM_INSTANCES
12057: set active_end_date = sysdate,
12058: instance_status_id = 1,
12059: last_update_date = sysdate,
12060: last_updated_by = v_user_id
12061: where instance_id = l_inst_tbl(j);
12062: --
12063: -- Tie the Transaction to the history
12064: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
12065: INSERT INTO CSI_ITEM_INSTANCES_H
12066: (
12067: INSTANCE_HISTORY_ID
12068: ,TRANSACTION_ID
12069: ,INSTANCE_ID
12355: v_exists := 'N';
12356: Begin
12357: select quantity,instance_id
12358: into v_ins_qty,v_instance_id
12359: from CSI_ITEM_INSTANCES
12360: where inventory_item_id = inventory_item_id_mig(i)
12361: and inv_organization_id = organization_id_mig(i)
12362: and serial_number is null
12363: and lot_number is null
12414: Raise;
12415: End;
12416: --
12417: IF v_exists = 'Y' THEN
12418: UPDATE CSI_ITEM_INSTANCES
12419: set quantity = quantity + quantity_mig(i),
12420: active_end_date = null,
12421: instance_status_id = v_ins_status_id,
12422: last_update_date = sysdate,
12424: last_vld_organization_id = organization_id_mig(i)
12425: where instance_id = v_instance_id;
12426: --
12427: -- Tie the Transaction to the history
12428: INSERT INTO CSI_ITEM_INSTANCES_H
12429: (
12430: INSTANCE_HISTORY_ID
12431: ,TRANSACTION_ID
12432: ,INSTANCE_ID
12438: ,OBJECT_VERSION_NUMBER
12439: )
12440: VALUES
12441: (
12442: CSI_ITEM_INSTANCES_H_S.nextval
12443: ,v_txn_id
12444: ,v_instance_id
12445: ,SYSDATE
12446: ,SYSDATE
12483: End;
12484: END IF;
12485: --
12486: Begin
12487: select csi_item_instances_s.nextval
12488: into v_instance_id
12489: from DUAL;
12490: End;
12491: --
12488: into v_instance_id
12489: from DUAL;
12490: End;
12491: --
12492: -- Insert into CSI_ITEM_INSTANCES
12493: Begin
12494: INSERT INTO CSI_ITEM_INSTANCES(
12495: INSTANCE_ID
12496: ,INSTANCE_NUMBER
12490: End;
12491: --
12492: -- Insert into CSI_ITEM_INSTANCES
12493: Begin
12494: INSERT INTO CSI_ITEM_INSTANCES(
12495: INSTANCE_ID
12496: ,INSTANCE_NUMBER
12497: ,EXTERNAL_REFERENCE
12498: ,INVENTORY_ITEM_ID
12571: );
12572: Exception
12573: when others then
12574: v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
12575: ||' into CSI_ITEM_INSTANCES '||SUBSTR(sqlerrm,1,1000);
12576: raise_application_error(-20000, v_err_msg );
12577: Raise;
12578: End;
12579: -- Use the same instance record to create the history
12577: Raise;
12578: End;
12579: -- Use the same instance record to create the history
12580: Begin
12581: select CSI_ITEM_INSTANCES_H_S.nextval
12582: into v_ins_history_id
12583: from DUAL;
12584: End;
12585: --
12583: from DUAL;
12584: End;
12585: --
12586: Begin
12587: INSERT INTO CSI_ITEM_INSTANCES_H(
12588: INSTANCE_HISTORY_ID
12589: ,INSTANCE_ID
12590: ,TRANSACTION_ID
12591: ,OLD_INSTANCE_NUMBER
12708: );
12709: Exception
12710: when others then
12711: v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
12712: ||' into CSI_ITEM_INSTANCES_H Using the Same Instance '||SUBSTR(sqlerrm,1,1000);
12713: raise_application_error(-20000, v_err_msg );
12714: Raise;
12715: End;
12716: --
12837: v_exists := 'N';
12838: Begin
12839: select quantity,instance_id
12840: into v_ins_qty,v_instance_id
12841: from CSI_ITEM_INSTANCES
12842: where inventory_item_id = inventory_item_id_mig(i)
12843: and inv_organization_id = organization_id_mig(i)
12844: and serial_number is null
12845: and location_type_code = 'INVENTORY'
12896: Raise;
12897: End;
12898: --
12899: IF v_exists = 'Y' THEN
12900: UPDATE CSI_ITEM_INSTANCES
12901: set quantity = quantity + lot_rec.transaction_quantity,
12902: active_end_date = null,
12903: instance_status_id = v_ins_status_id,
12904: last_update_date = sysdate,
12906: last_vld_organization_id = organization_id_mig(i)
12907: where instance_id = v_instance_id;
12908: --
12909: -- Tie the Transaction to the history
12910: INSERT INTO CSI_ITEM_INSTANCES_H
12911: (
12912: INSTANCE_HISTORY_ID
12913: ,TRANSACTION_ID
12914: ,INSTANCE_ID
12920: ,OBJECT_VERSION_NUMBER
12921: )
12922: VALUES
12923: (
12924: CSI_ITEM_INSTANCES_H_S.nextval
12925: ,v_txn_id
12926: ,v_instance_id
12927: ,SYSDATE
12928: ,SYSDATE
12965: End;
12966: END IF;
12967: --
12968: Begin
12969: select csi_item_instances_s.nextval
12970: into v_instance_id
12971: from DUAL;
12972: End;
12973: --
12970: into v_instance_id
12971: from DUAL;
12972: End;
12973: --
12974: -- Insert into CSI_ITEM_INSTANCES
12975: Begin
12976: INSERT INTO CSI_ITEM_INSTANCES(
12977: INSTANCE_ID
12978: ,INSTANCE_NUMBER
12972: End;
12973: --
12974: -- Insert into CSI_ITEM_INSTANCES
12975: Begin
12976: INSERT INTO CSI_ITEM_INSTANCES(
12977: INSTANCE_ID
12978: ,INSTANCE_NUMBER
12979: ,EXTERNAL_REFERENCE
12980: ,INVENTORY_ITEM_ID
13057: );
13058: Exception
13059: when others then
13060: v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
13061: ||' into CSI_ITEM_INSTANCES '||SUBSTR(sqlerrm,1,1000);
13062: raise_application_error(-20000, v_err_msg );
13063: Raise;
13064: End;
13065: -- Use the same instance record to create the history
13063: Raise;
13064: End;
13065: -- Use the same instance record to create the history
13066: Begin
13067: select CSI_ITEM_INSTANCES_H_S.nextval
13068: into v_ins_history_id
13069: from DUAL;
13070: End;
13071: --
13069: from DUAL;
13070: End;
13071: --
13072: Begin
13073: INSERT INTO CSI_ITEM_INSTANCES_H(
13074: INSTANCE_HISTORY_ID
13075: ,INSTANCE_ID
13076: ,TRANSACTION_ID
13077: ,OLD_INSTANCE_NUMBER
13202: );
13203: Exception
13204: when others then
13205: v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
13206: ||' into CSI_ITEM_INSTANCES_H Using the Same Instance '||SUBSTR(sqlerrm,1,1000);
13207: raise_application_error(-20000, v_err_msg );
13208: Raise;
13209: End;
13210: --
13461: INTO l_instance_rec.instance_id,
13462: l_instance_rec.object_version_number,
13463: l_instance_rec.location_type_code,
13464: l_instance_rec.instance_usage_code
13465: FROM csi_item_instances
13466: WHERE inventory_item_id = l_item_id_tab(l_ind)
13467: AND serial_number = l_serial_number_tab(l_ind);
13468:
13469: IF l_instance_rec.instance_usage_code <> 'RETURNED' OR
13488:
13489: -- build instance rec
13490: SELECT object_version_number
13491: INTO l_instance_rec.object_version_number
13492: FROM csi_item_instances
13493: WHERE instance_id = l_instance_rec.instance_id;
13494:
13495: l_instance_rec.location_type_code := 'INVENTORY';
13496: l_instance_rec.instance_usage_code := 'RETURNED';
13812: v_end_date := NULL;
13813: Begin
13814: select quantity,instance_id,active_end_date
13815: into v_ins_qty,v_instance_id,v_end_date
13816: from CSI_ITEM_INSTANCES
13817: where inventory_item_id = inventory_item_id_mig(i)
13818: and inv_organization_id = organization_id_mig(i)
13819: and serial_number is null
13820: and location_type_code = 'INVENTORY'
13833: --
13834: IF v_exists = 'Y' THEN
13835: IF v_ins_qty <> quantity_mig(i) OR
13836: NVL(v_end_date,(sysdate+1)) <= sysdate THEN
13837: UPDATE CSI_ITEM_INSTANCES
13838: set quantity = quantity_mig(i)
13839: ,active_end_date = decode(quantity_mig(i),0,sysdate,null)
13840: ,instance_status_id = decode(quantity_mig(i),0,1,v_ins_status_id)
13841: ,last_update_date = sysdate
13882: raise_application_error(-20000, v_err_msg );
13883: Raise;
13884: End;
13885: -- Tie the Transaction with the instance history
13886: INSERT INTO CSI_ITEM_INSTANCES_H
13887: (
13888: INSTANCE_HISTORY_ID
13889: ,TRANSACTION_ID
13890: ,INSTANCE_ID
13898: ,OBJECT_VERSION_NUMBER
13899: )
13900: VALUES
13901: (
13902: CSI_ITEM_INSTANCES_H_S.nextval
13903: ,v_txn_id
13904: ,v_instance_id
13905: ,v_ins_qty
13906: ,quantity_mig(i)
13958: Raise;
13959: End;
13960: --
13961: Begin
13962: select csi_item_instances_s.nextval
13963: into v_instance_id
13964: from DUAL;
13965: End;
13966: --
13963: into v_instance_id
13964: from DUAL;
13965: End;
13966: --
13967: -- Insert into CSI_ITEM_INSTANCES
13968: Begin
13969: INSERT INTO CSI_ITEM_INSTANCES(
13970: INSTANCE_ID
13971: ,INSTANCE_NUMBER
13965: End;
13966: --
13967: -- Insert into CSI_ITEM_INSTANCES
13968: Begin
13969: INSERT INTO CSI_ITEM_INSTANCES(
13970: INSTANCE_ID
13971: ,INSTANCE_NUMBER
13972: ,EXTERNAL_REFERENCE
13973: ,INVENTORY_ITEM_ID
14082: );
14083: Exception
14084: when others then
14085: v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
14086: ||' into CSI_ITEM_INSTANCES '||SUBSTR(sqlerrm,1,1000);
14087: raise_application_error(-20000, v_err_msg );
14088: Raise;
14089: End;
14090: -- Use the same instance record to create the history
14088: Raise;
14089: End;
14090: -- Use the same instance record to create the history
14091: Begin
14092: select CSI_ITEM_INSTANCES_H_S.nextval
14093: into v_ins_history_id
14094: from DUAL;
14095: End;
14096: --
14094: from DUAL;
14095: End;
14096: --
14097: Begin
14098: INSERT INTO CSI_ITEM_INSTANCES_H(
14099: INSTANCE_HISTORY_ID
14100: ,INSTANCE_ID
14101: ,TRANSACTION_ID
14102: ,OLD_INSTANCE_NUMBER
14251: );
14252: Exception
14253: when others then
14254: v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
14255: ||' into CSI_ITEM_INSTANCES_H Using the Same Instance '||SUBSTR(sqlerrm,1,1000);
14256: raise_application_error(-20000, v_err_msg );
14257: Raise;
14258: End;
14259: --
14546: CURSOR CSI_CUR IS
14547: select cii.instance_id,cii.inventory_item_id,cii.inv_organization_id,
14548: cii.inv_subinventory_name,cii.inv_locator_id,
14549: cii.inventory_revision,cii.lot_number,cii.quantity
14550: from CSI_ITEM_INSTANCES cii,
14551: MTL_SYSTEM_ITEMS msi
14552: where cii.location_type_code = 'INVENTORY'
14553: and cii.instance_usage_code = 'IN_INVENTORY'
14554: and cii.serial_number is NULL
14659: End;
14660: --
14661: IF v_qty <> quantity_mig(i) THEN
14662: l_ins_flag := 'Y';
14663: UPDATE CSI_ITEM_INSTANCES
14664: set quantity = v_qty,
14665: active_end_date = decode(v_qty,0,sysdate,active_end_date),
14666: instance_status_id = decode(v_qty,0,1,instance_status_id),
14667: last_update_date = sysdate,
14668: last_updated_by = l_user_id
14669: where instance_id = instance_id_mig(i);
14670: --
14671: -- Tie this instance with the transaction
14672: INSERT INTO CSI_ITEM_INSTANCES_H
14673: (
14674: INSTANCE_HISTORY_ID
14675: ,TRANSACTION_ID
14676: ,INSTANCE_ID
14682: ,OBJECT_VERSION_NUMBER
14683: )
14684: VALUES
14685: (
14686: CSI_ITEM_INSTANCES_H_S.nextval
14687: ,l_txn_id
14688: ,instance_id_mig(i)
14689: ,SYSDATE
14690: ,SYSDATE
14837: a.inventory_revision,
14838: a.inv_locator_id,
14839: a.lot_number,
14840: a.owner_party_id
14841: FROM csi_item_instances a,
14842: mtl_system_items_b c
14843: WHERE a.ROWID > (SELECT MIN(b.ROWID)
14844: FROM csi_item_instances b
14845: WHERE b.inventory_item_id = a.inventory_item_id
14840: a.owner_party_id
14841: FROM csi_item_instances a,
14842: mtl_system_items_b c
14843: WHERE a.ROWID > (SELECT MIN(b.ROWID)
14844: FROM csi_item_instances b
14845: WHERE b.inventory_item_id = a.inventory_item_id
14846: AND b.location_type_code = a.location_type_code
14847: -- AND b.location_id = a.location_id
14848: AND b.serial_number is null
14875: a.inventory_revision,
14876: a.inv_locator_id,
14877: a.lot_number,
14878: a.owner_party_id
14879: FROM csi_item_instances a,
14880: mtl_system_items_b c
14881: WHERE a.ROWID = (SELECT MIN(b.ROWID)
14882: FROM csi_item_instances b
14883: WHERE b.inventory_item_id = a.inventory_item_id
14878: a.owner_party_id
14879: FROM csi_item_instances a,
14880: mtl_system_items_b c
14881: WHERE a.ROWID = (SELECT MIN(b.ROWID)
14882: FROM csi_item_instances b
14883: WHERE b.inventory_item_id = a.inventory_item_id
14884: AND b.location_type_code = a.location_type_code
14885: -- AND b.location_id = a.location_id
14886: AND b.serial_number is null
14919: p_instance_tbl(m).inv_locator_id:=i.inv_locator_id;
14920: p_instance_tbl(m).lot_number:=i.lot_number;
14921: p_instance_tbl(m).attribute1:=i.owner_party_id;
14922:
14923: DELETE FROM csi_item_instances_h
14924: WHERE instance_id=i.instance_id;
14925: --
14926: DELETE FROM csi_i_parties_h
14927: WHERE instance_party_id in (select instance_party_id from csi_i_parties
14929: --
14930: DELETE FROM csi_i_parties
14931: WHERE instance_id=i.instance_id;
14932: --
14933: DELETE FROM csi_item_instances
14934: WHERE instance_id=i.instance_id;
14935:
14936: m := m+1;
14937:
14965: l_status_id := 510;
14966: l_active_end_date := null;
14967: END IF;
14968: --
14969: UPDATE csi_item_instances
14970: SET quantity=j.quantity+temp_quantity,
14971: instance_status_id = l_status_id,
14972: active_end_date = l_active_end_date
14973: WHERE instance_id=j.instance_id;
15030: lot_number,
15031: inventory_revision ,
15032: owner_party_account_id account_id,
15033: owner_party_id party_id
15034: FROM csi_item_instances,
15035: hz_parties
15036: WHERE inventory_item_id = p_item_id
15037: AND owner_party_account_id = decode(p_chg_owner,'Y',owner_party_account_id,p_customer_id)
15038: AND instance_usage_code = 'OUT_OF_ENTERPRISE'
15292: a.manually_created_flag,
15293: a.instance_usage_code,
15294: a.location_type_code,
15295: a.active_end_date
15296: FROM csi_item_instances a,
15297: mtl_serial_numbers d,
15298: mtl_parameters e
15299: WHERE a.serial_number is not null
15300: AND d.serial_number = a.serial_number
15411: IS
15412: SELECT cii.instance_id instance_id,
15413: cii.quantity quantity,
15414: cii.active_start_date active_start_date
15415: FROM csi_item_instances cii
15416: WHERE cii.inventory_item_id = p_item_id
15417: AND cii.accounting_class_code = 'CUST_PROD'
15418: AND cii.instance_usage_code = 'OUT_OF_ENTERPRISE'
15419: AND cii.owner_party_id = p_owner_party_id
15746: MAX_BUFFER_SIZE number := 1000;
15747:
15748: CURSOR wip_cur IS
15749: SELECT cii.instance_id
15750: FROM csi_item_instances cii
15751: WHERE cii.location_type_code = 'WIP'
15752: AND cii.instance_usage_code <> 'IN_RELATIONSHIP'
15753: AND exists (
15754: SELECT 'X' FROM mtl_system_items msi
15766: LIMIT MAX_BUFFER_SIZE;
15767:
15768: FOR ind IN 1 .. l_instance_id_tab.COUNT
15769: LOOP
15770: UPDATE csi_item_instances
15771: SET instance_usage_code = 'IN_WIP'
15772: WHERE instance_id = l_instance_id_tab(ind);
15773: END LOOP;
15774: commit;
15800: SELECT cii.inventory_item_id,
15801: cii.inventory_revision,
15802: cii.lot_number,
15803: cii.wip_job_id
15804: FROM csi_item_instances cii,
15805: mtl_system_items msi
15806: WHERE cii.location_type_code = 'WIP'
15807: AND cii.instance_usage_code = 'IN_WIP'
15808: AND msi.inventory_item_id = cii.inventory_item_id
15821: p_wip_job_id IN number)
15822: IS
15823: SELECT cii.instance_id,
15824: cii.quantity
15825: FROM csi_item_instances cii
15826: WHERE cii.location_type_code = 'WIP'
15827: AND cii.instance_usage_code = 'IN_WIP'
15828: AND cii.inventory_item_id = p_item_id
15829: AND nvl(cii.inventory_revision, '#*#*#') = nvl(p_revision, '#*#*#')
15863: l_keep_instance_id := dup_inst_rec.instance_id;
15864: ELSE
15865:
15866: -- preserve one instance for the wip job component and cumulate quantity
15867: UPDATE csi_item_instances
15868: SET quantity = quantity + dup_inst_rec.quantity
15869: WHERE instance_id = l_keep_instance_id;
15870:
15871: -- delete the rest of the instances (party and accounts)
15883:
15884: DELETE FROM csi_i_parties
15885: WHERE instance_id = dup_inst_rec.instance_id;
15886:
15887: DELETE FROM csi_item_instances
15888: WHERE instance_id = dup_inst_rec.instance_id;
15889:
15890: DELETE FROM csi_item_instances_h
15891: WHERE instance_id = dup_inst_rec.instance_id;
15886:
15887: DELETE FROM csi_item_instances
15888: WHERE instance_id = dup_inst_rec.instance_id;
15889:
15890: DELETE FROM csi_item_instances_h
15891: WHERE instance_id = dup_inst_rec.instance_id;
15892:
15893: END IF;
15894: END LOOP;
16898: l_batch_size NUMBER := 1000;
16899: l_worker_id NUMBER:=5;
16900: l_num_workers NUMBER := 5;
16901: l_any_rows_to_process BOOLEAN;
16902: l_table_name VARCHAR2(30) := 'CSI_ITEM_INSTANCES';
16903: l_update_name VARCHAR2(30) := 'csidiagb.pls';
16904: l_start_rowid rowid;
16905: l_end_rowid rowid;
16906: l_rows_processed number;
16933:
16934: WHILE(l_any_rows_to_process = TRUE)
16935: LOOP
16936:
16937: UPDATE /*+ rowid(cii) */ csi_item_instances cii
16938: SET operational_status_code =
16939: DECODE(instance_usage_code,'IN_SERVICE','IN_SERVICE','OUT_OF_SERVICE',
16940: 'OUT_OF_SERVICE', 'INSTALLED','INSTALLED','NOT_USED')
16941: ,LAST_UPDATE_DATE = sysdate