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;
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,
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);
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)
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: --
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: --
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;
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);
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;
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;
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
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
11799: l_ctr := l_ctr + 1;
11800: l_inst_tbl(l_ctr) := instance_id_mig(i);
11801: l_srl_tbl(l_ctr) := serial_number_mig(i);
11802: --
11803: select CSI_ITEM_INSTANCES_H_S.nextval
11804: into l_inst_hist_tbl(l_ctr) from dual;
11805: END IF;
11806: --
11807: END LOOP;
11817: where instance_id = l_inst_tbl(j);
11818: --
11819: -- Tie the Transaction to the history
11820: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
11821: INSERT INTO CSI_ITEM_INSTANCES_H
11822: (
11823: INSTANCE_HISTORY_ID
11824: ,TRANSACTION_ID
11825: ,INSTANCE_ID
11883: l_ctr := l_ctr + 1;
11884: l_inst_tbl(l_ctr) := instance_id_mig(i);
11885: l_lot_tbl(l_ctr) := lot_number_mig(i);
11886: --
11887: select CSI_ITEM_INSTANCES_H_S.nextval
11888: into l_inst_hist_tbl(l_ctr) from dual;
11889: END IF;
11890: END LOOP;
11891: --
11900: where instance_id = l_inst_tbl(j);
11901: --
11902: -- Tie the Transaction to the history
11903: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
11904: INSERT INTO CSI_ITEM_INSTANCES_H
11905: (
11906: INSTANCE_HISTORY_ID
11907: ,TRANSACTION_ID
11908: ,INSTANCE_ID
11964: IF nvl(v_srl_ctl,1) = 1 THEN
11965: l_ctr := l_ctr + 1;
11966: l_inst_tbl(l_ctr) := instance_id_mig(i);
11967: --
11968: select CSI_ITEM_INSTANCES_H_S.nextval
11969: into l_inst_hist_tbl(l_ctr) from dual;
11970: END IF;
11971: END LOOP;
11972: IF l_inst_tbl.count > 0 THEN
11981: where instance_id = l_inst_tbl(j);
11982: --
11983: -- Tie the Transaction to the history
11984: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
11985: INSERT INTO CSI_ITEM_INSTANCES_H
11986: (
11987: INSTANCE_HISTORY_ID
11988: ,TRANSACTION_ID
11989: ,INSTANCE_ID
12041: IF nvl(v_lot_ctl,1) = 1 THEN
12042: l_ctr := l_ctr + 1;
12043: l_inst_tbl(l_ctr) := instance_id_mig(i);
12044: --
12045: select CSI_ITEM_INSTANCES_H_S.nextval
12046: into l_inst_hist_tbl(l_ctr) from dual;
12047: END IF;
12048: END LOOP;
12049: --
12059: where instance_id = l_inst_tbl(j);
12060: --
12061: -- Tie the Transaction to the history
12062: FORALL j in l_inst_tbl.FIRST .. l_inst_tbl.LAST
12063: INSERT INTO CSI_ITEM_INSTANCES_H
12064: (
12065: INSTANCE_HISTORY_ID
12066: ,TRANSACTION_ID
12067: ,INSTANCE_ID
12419: last_vld_organization_id = organization_id_mig(i)
12420: where instance_id = v_instance_id;
12421: --
12422: -- Tie the Transaction to the history
12423: INSERT INTO CSI_ITEM_INSTANCES_H
12424: (
12425: INSTANCE_HISTORY_ID
12426: ,TRANSACTION_ID
12427: ,INSTANCE_ID
12433: ,OBJECT_VERSION_NUMBER
12434: )
12435: VALUES
12436: (
12437: CSI_ITEM_INSTANCES_H_S.nextval
12438: ,v_txn_id
12439: ,v_instance_id
12440: ,SYSDATE
12441: ,SYSDATE
12572: Raise;
12573: End;
12574: -- Use the same instance record to create the history
12575: Begin
12576: select CSI_ITEM_INSTANCES_H_S.nextval
12577: into v_ins_history_id
12578: from DUAL;
12579: End;
12580: --
12578: from DUAL;
12579: End;
12580: --
12581: Begin
12582: INSERT INTO CSI_ITEM_INSTANCES_H(
12583: INSTANCE_HISTORY_ID
12584: ,INSTANCE_ID
12585: ,TRANSACTION_ID
12586: ,OLD_INSTANCE_NUMBER
12703: );
12704: Exception
12705: when others then
12706: v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
12707: ||' into CSI_ITEM_INSTANCES_H Using the Same Instance '||SUBSTR(sqlerrm,1,1000);
12708: raise_application_error(-20000, v_err_msg );
12709: Raise;
12710: End;
12711: --
12901: last_vld_organization_id = organization_id_mig(i)
12902: where instance_id = v_instance_id;
12903: --
12904: -- Tie the Transaction to the history
12905: INSERT INTO CSI_ITEM_INSTANCES_H
12906: (
12907: INSTANCE_HISTORY_ID
12908: ,TRANSACTION_ID
12909: ,INSTANCE_ID
12915: ,OBJECT_VERSION_NUMBER
12916: )
12917: VALUES
12918: (
12919: CSI_ITEM_INSTANCES_H_S.nextval
12920: ,v_txn_id
12921: ,v_instance_id
12922: ,SYSDATE
12923: ,SYSDATE
13058: Raise;
13059: End;
13060: -- Use the same instance record to create the history
13061: Begin
13062: select CSI_ITEM_INSTANCES_H_S.nextval
13063: into v_ins_history_id
13064: from DUAL;
13065: End;
13066: --
13064: from DUAL;
13065: End;
13066: --
13067: Begin
13068: INSERT INTO CSI_ITEM_INSTANCES_H(
13069: INSTANCE_HISTORY_ID
13070: ,INSTANCE_ID
13071: ,TRANSACTION_ID
13072: ,OLD_INSTANCE_NUMBER
13197: );
13198: Exception
13199: when others then
13200: v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
13201: ||' into CSI_ITEM_INSTANCES_H Using the Same Instance '||SUBSTR(sqlerrm,1,1000);
13202: raise_application_error(-20000, v_err_msg );
13203: Raise;
13204: End;
13205: --
13877: raise_application_error(-20000, v_err_msg );
13878: Raise;
13879: End;
13880: -- Tie the Transaction with the instance history
13881: INSERT INTO CSI_ITEM_INSTANCES_H
13882: (
13883: INSTANCE_HISTORY_ID
13884: ,TRANSACTION_ID
13885: ,INSTANCE_ID
13893: ,OBJECT_VERSION_NUMBER
13894: )
13895: VALUES
13896: (
13897: CSI_ITEM_INSTANCES_H_S.nextval
13898: ,v_txn_id
13899: ,v_instance_id
13900: ,v_ins_qty
13901: ,quantity_mig(i)
14083: Raise;
14084: End;
14085: -- Use the same instance record to create the history
14086: Begin
14087: select CSI_ITEM_INSTANCES_H_S.nextval
14088: into v_ins_history_id
14089: from DUAL;
14090: End;
14091: --
14089: from DUAL;
14090: End;
14091: --
14092: Begin
14093: INSERT INTO CSI_ITEM_INSTANCES_H(
14094: INSTANCE_HISTORY_ID
14095: ,INSTANCE_ID
14096: ,TRANSACTION_ID
14097: ,OLD_INSTANCE_NUMBER
14246: );
14247: Exception
14248: when others then
14249: v_err_msg := 'Unable to Insert Item ID '||to_char(inventory_item_id_mig(i))
14250: ||' into CSI_ITEM_INSTANCES_H Using the Same Instance '||SUBSTR(sqlerrm,1,1000);
14251: raise_application_error(-20000, v_err_msg );
14252: Raise;
14253: End;
14254: --
14657: last_updated_by = l_user_id
14658: where instance_id = instance_id_mig(i);
14659: --
14660: -- Tie this instance with the transaction
14661: INSERT INTO CSI_ITEM_INSTANCES_H
14662: (
14663: INSTANCE_HISTORY_ID
14664: ,TRANSACTION_ID
14665: ,INSTANCE_ID
14671: ,OBJECT_VERSION_NUMBER
14672: )
14673: VALUES
14674: (
14675: CSI_ITEM_INSTANCES_H_S.nextval
14676: ,l_txn_id
14677: ,instance_id_mig(i)
14678: ,SYSDATE
14679: ,SYSDATE
14908: p_instance_tbl(m).inv_locator_id:=i.inv_locator_id;
14909: p_instance_tbl(m).lot_number:=i.lot_number;
14910: p_instance_tbl(m).attribute1:=i.owner_party_id;
14911:
14912: DELETE FROM csi_item_instances_h
14913: WHERE instance_id=i.instance_id;
14914: --
14915: DELETE FROM csi_i_parties_h
14916: WHERE instance_party_id in (select instance_party_id from csi_i_parties
15875:
15876: DELETE FROM csi_item_instances
15877: WHERE instance_id = dup_inst_rec.instance_id;
15878:
15879: DELETE FROM csi_item_instances_h
15880: WHERE instance_id = dup_inst_rec.instance_id;
15881:
15882: END IF;
15883: END LOOP;