[Home] [Help]
73: ---Start: Bug fix 3051653
74: CURSOR c_get_revisions(cp_item_number VARCHAR2,cp_revision VARCHAR2,
75: cp_organization_id NUMBER) IS
76: SELECT organization_id,item_number
77: FROM mtl_item_revisions_interface
78: WHERE inventory_item_id IS NULL
79: AND item_number = cp_item_number
80: AND organization_id = cp_organization_id
81: AND revision = cp_revision
639: INVPUTLI.info('INVPAGI2: About to enter header cursorloop');
640: END IF;
641:
642:
643: update MTL_ITEM_REVISIONS_INTERFACE i
644: set i.organization_id = (select o.organization_id
645: from MTL_PARAMETERS o
646: where o.organization_code = i.organization_code)
647: where i.organization_id is NULL
1656: -- Store sysdate minus 1 sec when inserting default revision data
1657: l_curr_sysdate := sysdate - 1/86400;
1658:
1659: --default rev should be sysdate
1660: update mtl_item_revisions_interface
1661: set effectivity_date = sysdate
1662: where set_process_id = xset_id
1663: and process_flag = 1
1664: and revision = cr.revision
1679: END IF;
1680:
1681:
1682: IF dumm_status = 0 THEN
1683: update mtl_item_revisions_interface
1684: set inventory_item_id = flex_id
1685: where item_number = c_revision_record.item_number
1686: and set_process_id = xset_id
1687: and organization_id = c_revision_record.organization_id
1691: --2885843:Effectivity date to sysdate if passed date is > sysdate
1692: IF (l_effectivity_date IS NULL
1693: OR TRUNC(l_effectivity_date) > TRUNC(SYSDATE)) THEN
1694:
1695: update mtl_item_revisions_interface
1696: set effectivity_date = sysdate
1697: where rowid = l_rowid;
1698:
1699: END IF;
1702:
1703: --End 2861248 :Populate Item Id for default revision only
1704:
1705: SELECT count(*) INTO revs
1706: FROM mtl_item_revisions_interface
1707: WHERE ((organization_id = cr.organization_id
1708: AND inventory_item_id = cr.inventory_item_id)
1709: OR (organization_id = cr.organization_id
1710: AND item_number = cr.item_number))
1721: if (revs = 0) then
1722: if (cr.revision = default_rev) then -- Bug 4539703 for default revision create entry with sysdate - 1 sec
1723: --Bug 4626774 added request_id in both inserts
1724: --Adding Source System Id and Source System Reference to ensure Sequence Generated Item Number propogation
1725: insert into mtl_item_revisions_interface
1726: (organization_id, inventory_item_id, revision,
1727: process_flag, transaction_type, set_process_id, implementation_date,
1728: effectivity_date, creation_date, last_update_date
1729: ,request_id, source_system_id, source_system_reference)
1732: l_curr_sysdate, l_curr_sysdate, l_curr_sysdate
1733: ,request_id, cr.source_system_id, cr.source_system_reference);
1734: else
1735: --Adding Source System Id and Source System Reference to ensure Sequence Generated Item Number propogation
1736: insert into mtl_item_revisions_interface
1737: (organization_id, inventory_item_id, revision,
1738: process_flag, transaction_type, set_process_id
1739: ,request_id, source_system_id, source_system_reference)
1740: values (cr.organization_id, cr.inventory_item_id, cr.revision,
1749: if (cr.revision <> default_rev) then
1750: revs := 0;
1751:
1752: select count(revision) into revs
1753: from mtl_item_revisions_interface
1754: where set_process_id = xset_id
1755: and revision = default_rev
1756: AND process_flag = 1--Bug No: 3344480
1757: and ((organization_id = cr.organization_id
1768: if (revs = 0) then
1769: -- bug 4539703
1770: --Bug 4626774 added request_id in the insert
1771: --Adding Source System Id and Source System Reference to ensure Sequence Generated Item Number propogation
1772: insert into mtl_item_revisions_interface -- create a default revision with sysdate - 1 sec
1773: (organization_id, inventory_item_id, revision,
1774: process_flag, transaction_type, set_process_id,implementation_date,
1775: effectivity_date,creation_date,last_update_date
1776: ,request_id, source_system_id, source_system_reference)