[Home] [Help]
401: -- *********************************************************************
402: -- ************ End - R12 Notes History ********************************
403: -- *********************************************************************
404:
405: -- call table handler to create dimension assignments and populate cn_rate_tiers
406: IF (p_dims_tbl.COUNT > 0) THEN
407: FOR i IN p_dims_tbl.first..p_dims_tbl.last LOOP
408: l_temp_id := NULL;
409: cn_rate_sch_dims_pkg.insert_row(x_rate_sch_dim_id => l_temp_id,
417: END IF;
418:
419: -- leave table empty and fill in tiers as needed for sparse impl
420: /*
421: -- create records in cn_rate_tiers (product[T_i] tiers for i=1..number of dims)
422: create_rate_tiers(p_rate_schedule_id => x_rate_schedule_id,
423: p_rate_dim_sequence => NULL);
424: */
425:
620: -- dimensions have been changed
621:
622: -- delete all rate tiers and rate_sch_dims
623: delete from cn_rate_sch_dims where rate_schedule_id = p_rate_schedule_id;
624: delete from cn_rate_tiers where rate_schedule_id = p_rate_schedule_id;
625:
626: -- reassign rate schedule dimensions
627: FOR i IN p_dims_tbl.first..p_dims_tbl.last LOOP
628: l_temp_id := NULL;
637: );
638: END LOOP;
639:
640: -- rate table being built up again from scratch - purge existing tiers
641: delete from cn_rate_tiers where rate_schedule_id = p_rate_schedule_id;
642: /*
643: -- create records in cn_rate_tiers (product[T_i] tiers for i=1..number of dims)
644: create_rate_tiers(p_rate_schedule_id => p_rate_schedule_id,
645: p_rate_dim_sequence => NULL);
639:
640: -- rate table being built up again from scratch - purge existing tiers
641: delete from cn_rate_tiers where rate_schedule_id = p_rate_schedule_id;
642: /*
643: -- create records in cn_rate_tiers (product[T_i] tiers for i=1..number of dims)
644: create_rate_tiers(p_rate_schedule_id => p_rate_schedule_id,
645: p_rate_dim_sequence => NULL);
646: */
647: END IF;
795: --
796: -- Notes : Delete rate schedule
797: -- 1) If it is used, it can not be deleted
798: -- 2) If it can be deleted, delete corresponding records in
799: -- cn_rate_sch_dims and cn_rate_tiers
800: --
801: -- End of comments
802: PROCEDURE Delete_Schedule
803: (p_api_version IN NUMBER ,
956: -- Initial version 1.0
957: --
958: -- Notes : Delete schedule dimension
959: -- 1) If the rate schedule is used, its dimensions can not be deleted
960: -- 2) delete the corresponding records in cn_rate_sch_dims and cn_rate_tiers
961: -- 3) update cn_rate_schedules.number_dim if not called from form
962: -- 4) rate_dim_sequence is not adjusted here, users should take care
963: -- of the adjustment by calling
964: -- update_dimension_assign
1022: END IF;
1023: RAISE fnd_api.g_exc_error;
1024: END IF;
1025:
1026: -- delete the records corresponding to this dimension in cn_rate_tiers
1027: BEGIN
1028: SELECT rate_dim_sequence
1029: INTO l_rate_dim_sequence
1030: FROM cn_rate_sch_dims
1164: -- Initial version 1.0
1165: --
1166: -- Notes : Update dimension assignment
1167: -- 1) If the rate table is used, then update is not allowed
1168: -- 2) If it can be updated, update records in cn_rate_sch_dims and cn_rate_tiers
1169: --
1170: -- End of comments
1171: PROCEDURE update_dimension_assign
1172: (p_api_version IN NUMBER ,
1258: OPEN new_dim_info;
1259: FETCH new_dim_info INTO l_number_tier_new;
1260: CLOSE new_dim_info;
1261:
1262: -- if rate dimension is replaced, then adjust cn_rate_tiers also
1263: -- remove the dimension and re-create it
1264: IF (l_rate_dimension_id_old <> p_rate_dimension_id) THEN
1265: -- make sure the dimension hasn't already been assigned
1266: select count(*) into l_count from cn_rate_sch_dims
1290: END IF;
1291:
1292: -- Move Up/Down - Setting Commission rates to 0
1293: if ( (l_rate_dimension_id_old = p_rate_dimension_id) AND (p_rate_dim_sequence <> l_rate_dim_sequence)) THEN
1294: delete from cn_rate_tiers where rate_sequence <> 1 AND rate_schedule_id= p_rate_schedule_id AND org_id = p_org_id;
1295: END IF;
1296:
1297: -- Start - R12 Notes History Query for old dimension id assigned to rate table
1298: select rate_dimension_id into l_rate_dimension_id
1412: -- Initial version 1.0
1413: --
1414: -- Notes : Create dimension assignment
1415: -- 1) If the rate table is used, new assignment can not be created
1416: -- 2) if the rate table is not used, update cn_rate_tiers;
1417: -- and adjust cn_rate_tiers.rate_sequence
1418: -- 3) update cn_rate_schedules.number_dim
1419: -- 4) rate_dim_sequence is not adjusted here, users should do it by
1420: -- calling update_dimension_assign
1413: --
1414: -- Notes : Create dimension assignment
1415: -- 1) If the rate table is used, new assignment can not be created
1416: -- 2) if the rate table is not used, update cn_rate_tiers;
1417: -- and adjust cn_rate_tiers.rate_sequence
1418: -- 3) update cn_rate_schedules.number_dim
1419: -- 4) rate_dim_sequence is not adjusted here, users should do it by
1420: -- calling update_dimension_assign
1421: --
1451: l_note_msg VARCHAR2(240);
1452: l_note_id NUMBER;
1453:
1454: CURSOR tier_exist IS
1455: SELECT count(1) from cn_rate_tiers
1456: WHERE rate_schedule_id = p_rate_schedule_id;
1457: BEGIN
1458: -- Standard Start of API savepoint
1459: SAVEPOINT Create_Dimension_Assign;
1559: UPDATE cn_rate_schedules
1560: SET number_dim = l_num_dims + 1
1561: WHERE rate_schedule_id = p_rate_schedule_id;
1562:
1563: -- insert records into cn_rate_tiers
1564: OPEN tier_exist;
1565: FETCH tier_exist INTO l_dummy;
1566: CLOSE tier_exist;
1567:
1626: -- face problems. The solution is to use the actual number of tiers in the dimension
1627: -- instead of cn_rate_dimensions.number_tier
1628: -- 2. form processing changes in the following order: delete --> update --> insert
1629: PROCEDURE create_rate_tiers
1630: (p_rate_schedule_id CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
1631: p_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE := NULL,
1632: p_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE := NULL,
1633: p_num_tiers NUMBER := 1,
1634: --R12 MOAC Changes--Start
1631: p_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE := NULL,
1632: p_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE := NULL,
1633: p_num_tiers NUMBER := 1,
1634: --R12 MOAC Changes--Start
1635: p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE
1636: --R12 MOAC Changes--End
1637: ) IS
1638:
1639: l_number_dim CN_RATE_SCHEDULES.NUMBER_DIM%TYPE;
1654: ORDER BY rsd.rate_dim_sequence;
1655:
1656: CURSOR get_rate_tiers IS
1657: SELECT rate_tier_id, rate_sequence
1658: FROM cn_rate_tiers
1659: WHERE rate_schedule_id = p_rate_schedule_id;
1660:
1661: BEGIN
1662: SELECT number_dim
1718: end if;
1719: end loop;
1720:
1721: -- update table
1722: update cn_rate_tiers set rate_sequence = l_new_seq
1723: where rate_tier_id = t.rate_tier_id;
1724: end loop;
1725:
1726: -- update cn_srp_rate_assigns.rate_sequence
1725:
1726: -- update cn_srp_rate_assigns.rate_sequence
1727: UPDATE cn_srp_rate_assigns sra
1728: SET rate_sequence = (SELECT rate_sequence
1729: FROM cn_rate_tiers
1730: WHERE rate_schedule_id = p_rate_schedule_id
1731: AND rate_tier_id = sra.rate_tier_id)
1732: WHERE rate_schedule_id = p_rate_schedule_id;
1733:
1733:
1734: END create_rate_tiers;
1735:
1736: PROCEDURE delete_rate_tiers
1737: (p_rate_schedule_id CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
1738: p_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE,
1739: p_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE := NULL,
1740: p_num_tiers NUMBER := 1) IS
1741:
1758: ORDER BY rsd.rate_dim_sequence;
1759:
1760: CURSOR get_rate_tiers IS
1761: SELECT rate_tier_id, rate_sequence
1762: FROM cn_rate_tiers
1763: WHERE rate_schedule_id = p_rate_schedule_id;
1764:
1765: BEGIN
1766: SELECT number_dim
1812: end if;
1813: end loop;
1814:
1815: if delete_flag = true then
1816: delete from cn_rate_tiers
1817: where rate_tier_id = t.rate_tier_id;
1818: delete from cn_srp_rate_assigns
1819: where rate_tier_id = t.rate_tier_id;
1820: else
1830: end if;
1831: end loop;
1832:
1833: -- update table
1834: update cn_rate_tiers set rate_sequence = l_new_seq
1835: where rate_tier_id = t.rate_tier_id;
1836: end if;
1837: end loop;
1838:
1838:
1839: -- update cn_srp_rate_assigns.rate_sequence
1840: UPDATE cn_srp_rate_assigns sra
1841: SET rate_sequence = (SELECT rate_sequence
1842: FROM cn_rate_tiers
1843: WHERE rate_schedule_id = p_rate_schedule_id
1844: AND rate_tier_id = sra.rate_tier_id)
1845: WHERE rate_schedule_id = p_rate_schedule_id;
1846:
1846:
1847: END delete_rate_tiers;
1848:
1849: PROCEDURE update_rate
1850: (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
1851: p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1852: p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1853: --R12 MOAC Changes--Start
1854: p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, --changed
1847: END delete_rate_tiers;
1848:
1849: PROCEDURE update_rate
1850: (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
1851: p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1852: p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1853: --R12 MOAC Changes--Start
1854: p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, --changed
1855: p_org_id CN_RATE_TIERS.ORG_ID%TYPE --new
1848:
1849: PROCEDURE update_rate
1850: (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
1851: p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1852: p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1853: --R12 MOAC Changes--Start
1854: p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, --changed
1855: p_org_id CN_RATE_TIERS.ORG_ID%TYPE --new
1856: --R12 MOAC Changes--End
1850: (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
1851: p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1852: p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1853: --R12 MOAC Changes--Start
1854: p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, --changed
1855: p_org_id CN_RATE_TIERS.ORG_ID%TYPE --new
1856: --R12 MOAC Changes--End
1857: ) IS
1858:
1851: p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1852: p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1853: --R12 MOAC Changes--Start
1854: p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, --changed
1855: p_org_id CN_RATE_TIERS.ORG_ID%TYPE --new
1856: --R12 MOAC Changes--End
1857: ) IS
1858:
1859:
1861: x_msg_count NUMBER;
1862: x_msg_data VARCHAR2(2000);
1863: l_api_name CONSTANT VARCHAR2(30) := 'Update_Rate';
1864:
1865: l_rate_tier_id CN_RATE_TIERS.RATE_TIER_ID%TYPE;
1866: l_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE;
1867:
1868: CURSOR rate_tier_info IS
1869: SELECT rate_tier_id, commission_amount
1862: x_msg_data VARCHAR2(2000);
1863: l_api_name CONSTANT VARCHAR2(30) := 'Update_Rate';
1864:
1865: l_rate_tier_id CN_RATE_TIERS.RATE_TIER_ID%TYPE;
1866: l_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE;
1867:
1868: CURSOR rate_tier_info IS
1869: SELECT rate_tier_id, commission_amount
1870: FROM cn_rate_tiers
1866: l_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE;
1867:
1868: CURSOR rate_tier_info IS
1869: SELECT rate_tier_id, commission_amount
1870: FROM cn_rate_tiers
1871: WHERE rate_schedule_id = p_rate_schedule_id
1872: AND rate_sequence = p_rate_sequence;
1873:
1874: CURSOR get_sqa IS
1891: IF (rate_tier_info%notfound) THEN
1892: -- record may have to be created
1893: CLOSE rate_tier_info;
1894: if p_commission_amount <> 0 then
1895: cn_rate_tiers_pkg.insert_row
1896: (X_RATE_TIER_ID => l_rate_tier_id,
1897: X_RATE_SCHEDULE_ID => p_rate_schedule_id,
1898: X_COMMISSION_AMOUNT => p_commission_amount,
1899: X_RATE_SEQUENCE => p_rate_sequence,
1919: -- see if amt changed
1920: if p_commission_amount <> l_commission_amount then
1921:
1922: -- lock and update the record
1923: cn_rate_tiers_pkg.lock_row
1924: (X_RATE_TIER_ID => l_rate_tier_id,
1925: X_OBJECT_VERSION_NUMBER => p_object_version_number);
1926:
1927: cn_rate_tiers_pkg.update_row
1923: cn_rate_tiers_pkg.lock_row
1924: (X_RATE_TIER_ID => l_rate_tier_id,
1925: X_OBJECT_VERSION_NUMBER => p_object_version_number);
1926:
1927: cn_rate_tiers_pkg.update_row
1928: (X_RATE_TIER_ID => l_rate_tier_id,
1929: X_RATE_SCHEDULE_ID => p_rate_schedule_id,
1930: X_COMMISSION_AMOUNT => p_commission_amount,
1931: X_RATE_SEQUENCE => p_rate_sequence,
1985:
1986: PROCEDURE update_srp_rate
1987: (p_srp_quota_assign_id IN CN_SRP_QUOTA_ASSIGNS.SRP_QUOTA_ASSIGN_ID%TYPE,
1988: p_rt_quota_asgn_id IN CN_SRP_RATE_ASSIGNS.RT_QUOTA_ASGN_ID%TYPE,
1989: p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1990: p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1991: p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, -- changed
1992: --R12 MOAC Changes--Start
1993: p_org_id CN_RATE_TIERS.ORG_ID%TYPE,
1986: PROCEDURE update_srp_rate
1987: (p_srp_quota_assign_id IN CN_SRP_QUOTA_ASSIGNS.SRP_QUOTA_ASSIGN_ID%TYPE,
1988: p_rt_quota_asgn_id IN CN_SRP_RATE_ASSIGNS.RT_QUOTA_ASGN_ID%TYPE,
1989: p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1990: p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1991: p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, -- changed
1992: --R12 MOAC Changes--Start
1993: p_org_id CN_RATE_TIERS.ORG_ID%TYPE,
1994: --R12 MOAC Changes--End
1987: (p_srp_quota_assign_id IN CN_SRP_QUOTA_ASSIGNS.SRP_QUOTA_ASSIGN_ID%TYPE,
1988: p_rt_quota_asgn_id IN CN_SRP_RATE_ASSIGNS.RT_QUOTA_ASGN_ID%TYPE,
1989: p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1990: p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1991: p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, -- changed
1992: --R12 MOAC Changes--Start
1993: p_org_id CN_RATE_TIERS.ORG_ID%TYPE,
1994: --R12 MOAC Changes--End
1995: x_return_status OUT NOCOPY VARCHAR2,
1989: p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
1990: p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
1991: p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, -- changed
1992: --R12 MOAC Changes--Start
1993: p_org_id CN_RATE_TIERS.ORG_ID%TYPE,
1994: --R12 MOAC Changes--End
1995: x_return_status OUT NOCOPY VARCHAR2,
1996: x_loading_status OUT NOCOPY VARCHAR2,
1997: x_msg_count OUT NOCOPY NUMBER,
2017: FOR UPDATE OF srp_rate_assign_id nowait;
2018:
2019: CURSOR get_rate_tier_id IS
2020: SELECT rate_tier_id
2021: from cn_rate_tiers
2022: where rate_schedule_id = l_rate_schedule_id
2023: and rate_sequence = p_rate_sequence;
2024:
2025: CURSOR get_sqa_info IS
2057: FETCH get_rate_tier_id into l_rate_tier_id;
2058: CLOSE get_rate_tier_id;
2059: if l_rate_tier_id is null then
2060: -- insert rate tier into main rate table
2061: cn_rate_tiers_pkg.insert_row
2062: (X_RATE_TIER_ID => l_rate_tier_id,
2063: X_RATE_SCHEDULE_ID => l_rate_schedule_id,
2064: X_COMMISSION_AMOUNT => 0, -- place holder record
2065: X_RATE_SEQUENCE => p_rate_sequence,
2198: END update_srp_rate;
2199:
2200: -- utility function to get the rate_tier_id when given the tier combination
2201: PROCEDURE get_rate_tier_info
2202: (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
2203: p_rate_dim_tier_id_tbl IN NUM_TBL_TYPE ,
2204: x_rate_tier_id OUT NOCOPY CN_RATE_TIERS.RATE_TIER_ID%TYPE,
2205: x_rate_sequence OUT NOCOPY CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
2206: x_commission_amount OUT NOCOPY CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
2200: -- utility function to get the rate_tier_id when given the tier combination
2201: PROCEDURE get_rate_tier_info
2202: (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
2203: p_rate_dim_tier_id_tbl IN NUM_TBL_TYPE ,
2204: x_rate_tier_id OUT NOCOPY CN_RATE_TIERS.RATE_TIER_ID%TYPE,
2205: x_rate_sequence OUT NOCOPY CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
2206: x_commission_amount OUT NOCOPY CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
2207: x_object_version_number OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE) IS
2208:
2201: PROCEDURE get_rate_tier_info
2202: (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
2203: p_rate_dim_tier_id_tbl IN NUM_TBL_TYPE ,
2204: x_rate_tier_id OUT NOCOPY CN_RATE_TIERS.RATE_TIER_ID%TYPE,
2205: x_rate_sequence OUT NOCOPY CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
2206: x_commission_amount OUT NOCOPY CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
2207: x_object_version_number OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE) IS
2208:
2209: l_base NUMBER := 1;
2202: (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
2203: p_rate_dim_tier_id_tbl IN NUM_TBL_TYPE ,
2204: x_rate_tier_id OUT NOCOPY CN_RATE_TIERS.RATE_TIER_ID%TYPE,
2205: x_rate_sequence OUT NOCOPY CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
2206: x_commission_amount OUT NOCOPY CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
2207: x_object_version_number OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE) IS
2208:
2209: l_base NUMBER := 1;
2210: l_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE;
2203: p_rate_dim_tier_id_tbl IN NUM_TBL_TYPE ,
2204: x_rate_tier_id OUT NOCOPY CN_RATE_TIERS.RATE_TIER_ID%TYPE,
2205: x_rate_sequence OUT NOCOPY CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
2206: x_commission_amount OUT NOCOPY CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
2207: x_object_version_number OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE) IS
2208:
2209: l_base NUMBER := 1;
2210: l_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE;
2211: l_rate_dimension_id CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE;
2230: WHERE rate_dim_tier_id = p_rate_dim_tier_id;
2231:
2232: CURSOR get_tier IS
2233: SELECT rate_tier_id, nvl(commission_amount,0), object_version_number
2234: FROM cn_rate_tiers
2235: WHERE rate_schedule_id = p_rate_schedule_id
2236: AND rate_sequence = x_rate_sequence;
2237:
2238: BEGIN
2304: END tokenizer ;
2305:
2306:
2307: Function get_sequence(x_schedule_id
2308: CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,sbuf varchar2) RETURN number
2309: is
2310: sepr varchar2(1);
2311: sres varchar2(200);
2312: pos number;
2311: sres varchar2(200);
2312: pos number;
2313: istart number;
2314:
2315: x_rate_tier_id CN_RATE_TIERS.RATE_TIER_ID%TYPE;
2316: x_rate_sequence CN_RATE_TIERS.RATE_SEQUENCE%TYPE;
2317: x_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE;
2318: x_object_version_number CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE;
2319: l_number_dim NUMBER;
2312: pos number;
2313: istart number;
2314:
2315: x_rate_tier_id CN_RATE_TIERS.RATE_TIER_ID%TYPE;
2316: x_rate_sequence CN_RATE_TIERS.RATE_SEQUENCE%TYPE;
2317: x_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE;
2318: x_object_version_number CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE;
2319: l_number_dim NUMBER;
2320:
2313: istart number;
2314:
2315: x_rate_tier_id CN_RATE_TIERS.RATE_TIER_ID%TYPE;
2316: x_rate_sequence CN_RATE_TIERS.RATE_SEQUENCE%TYPE;
2317: x_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE;
2318: x_object_version_number CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE;
2319: l_number_dim NUMBER;
2320:
2321: --type tbl is table of number INDEX BY BINARY_INTEGER;
2314:
2315: x_rate_tier_id CN_RATE_TIERS.RATE_TIER_ID%TYPE;
2316: x_rate_sequence CN_RATE_TIERS.RATE_SEQUENCE%TYPE;
2317: x_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE;
2318: x_object_version_number CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE;
2319: l_number_dim NUMBER;
2320:
2321: --type tbl is table of number INDEX BY BINARY_INTEGER;
2322: l_tbl APPS.CN_MULTI_RATE_SCHEDULES_PVT.NUM_TBL_TYPE;
2358: );
2359: return x_rate_sequence;
2360: END get_sequence;
2361:
2362: PROCEDURE update_comm_rate(p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
2363: x_result_tbl IN comm_tbl_type,
2364: --R12 MOAC Changes--Start
2365: p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE --new
2366: --R12 MOAC Changes--End
2361:
2362: PROCEDURE update_comm_rate(p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
2363: x_result_tbl IN comm_tbl_type,
2364: --R12 MOAC Changes--Start
2365: p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE --new
2366: --R12 MOAC Changes--End
2367: )
2368: IS
2369: x_ovn number;
2412: and org_id = p_org_id;
2413:
2414:
2415: CURSOR rate_sch_tiers_info(l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE) IS
2416: select * from cn_rate_tiers_all
2417: where rate_schedule_id = l_rate_schedule_id
2418: and org_id = p_org_id;
2419:
2420: