[Home] [Help]
600: *
601: * HISTORY
602: * 10/14/2001 yzhao created
603: * 04/09/2003 niprakas Fix for the bug#2833114. The dynamic SQL are
604: * changed. The insert statement for AMS_PARTY_MARKET_SEGMENTS
605: * is changed. It now inserts cust_account_id,cust_acct_site_id
606: * and cust_site_use_code.
607: ******************************************************************************/
608: PROCEDURE generate_party_for_territory
675: -- l_hzsaleschannel_sql VARCHAR2(10000) := p_hzsaleschannel_sql;
676: -- TYPE PartyCurTyp IS REF CURSOR; -- define weak REF CURSOR type
677: -- l_party_cv PartyCurTyp; -- declare cursor variable
678: -- CURSOR c_party_mkt_seg_seq IS -- generate an ID for INSERT
679: -- SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
680: -- FROM DUAL;
681:
682:
683: --R12: mkothari -- added cursor to get territory org
1333: AND market_qualifier_reference = p_terr_id;
1334:
1335: /* --this give GSCC error - "select * not allowed"
1336: INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T
1337: SELECT * FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
1338: */
1339: INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T (
1340: AMS_PARTY_MARKET_SEGMENT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1341: CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
1354: PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
1355: MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
1356: CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
1357: ROLLUP_PARTY_ID, SITE_USE_ID
1358: FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
1359: WHERE OLD_TERR.MARKET_QUALIFIER_TYPE='TERRITORY'
1360: AND OLD_TERR.market_qualifier_reference = p_terr_id
1361: AND OLD_TERR.site_use_code = 'SHIP_TO'
1362: AND OLD_TERR.party_id IS NOT NULL
1362: AND OLD_TERR.party_id IS NOT NULL
1363: AND OLD_TERR.site_use_id IS NOT NULL;
1364:
1365:
1366: DELETE FROM AMS_PARTY_MARKET_SEGMENTS
1367: WHERE market_qualifier_type = 'TERRITORY'
1368: AND market_qualifier_reference = p_terr_id;
1369: -- remove 'AND ' at the end of the where clause
1370: -- write_conc_log('Before opening the cursor ');
1373: substr(l_party_where_sql, 1, length(l_party_where_sql)-4));
1374: IF l_party_select_sql IS NOT NULL
1375: THEN
1376: -- yzhao: 05/08/2003 SQL bind variable project
1377: l_final_sql := 'INSERT INTO AMS_PARTY_MARKET_SEGMENTS('||
1378: 'ams_party_market_segment_id, '||
1379: 'last_update_date, '||
1380: 'last_updated_by,';
1381: l_final_sql := l_final_sql ||
1398: 'site_use_id, '||
1399: 'bill_to_site_use_id, '||
1400: 'site_use_code )';
1401: l_final_sql := l_final_sql ||
1402: ' SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL, '||
1403: 'SYSDATE, '||
1404: 'FND_GLOBAL.user_id, ';
1405: l_final_sql := l_final_sql ||
1406: 'SYSDATE, '||
1468: --Needs to be done here so that the copied row is not lost
1469: if l_store_index>0 then
1470:
1471: write_conc_log('############### Copy Store from ozf mapping table to ams table >>> START');
1472: INSERT INTO ams_party_market_segments
1473: (
1474: AMS_PARTY_MARKET_SEGMENT_ID,
1475: LAST_UPDATE_DATE,
1476: LAST_UPDATED_BY,
1492: SITE_USE_ID,
1493: ORG_ID
1494: )
1495: select
1496: AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL,
1497: SYSDATE,
1498: FND_GLOBAL.user_id,
1499: SYSDATE,
1500: FND_GLOBAL.user_id,
1539: -- ' territory_id=' || p_terr_id);
1540: OPEN c_party_mkt_seg_seq;
1541: FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id;
1542: CLOSE c_party_mkt_seg_seq;
1543: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1544: (
1545: ams_party_market_segment_id
1546: , last_update_date
1547: , last_updated_by
1585: END LOOP;
1586: CLOSE l_party_cv;
1587: */
1588: END IF;
1589: UPDATE ams_party_market_segments a
1590: SET a.rollup_party_id = ( SELECT acct.party_id
1591: FROM hz_cust_accounts_all acct,
1592: hz_cust_acct_sites_all acct_site,
1593: hz_cust_site_uses_all site_use
1685: AND object_id = p_bg_id
1686: /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI */
1687: AND directional_flag = NVL(p_direction, directional_flag);
1688: CURSOR c_party_mkt_seg_seq IS -- generate an ID
1689: SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
1690: FROM DUAL;
1691: BEGIN
1692: Ozf_Utility_pvt.write_conc_log(l_full_name || ': Start buyinggroup_id=' || p_bg_id);
1693: p_errbuf := null;
1693: p_errbuf := null;
1694: p_retcode := 0;
1695: -- delete all buying group records for this subject_id
1696:
1697: DELETE FROM AMS_PARTY_MARKET_SEGMENTS
1698: WHERE market_qualifier_type = 'BG'
1699: AND market_qualifier_reference = p_bg_id;
1700:
1701: -- l_client_info := TO_NUMBER(SUBSTRB(userenv('CLIENT
1705: OPEN c_party_mkt_seg_seq;
1706: FETCH c_party_mkt_seg_seq INTO l_index;
1707: CLOSE c_party_mkt_seg_seq;
1708: -- 03/26/2002 always return the party itself as part of the buying group
1709: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1710: (
1711: ams_party_market_segment_id
1712: , last_update_date
1713: , last_updated_by
1781: END LOOP;
1782: -- DBMS_OUTPUT.PUT_LINE(l_full_name || ': INSERT buying group: buyinggroup_id='
1783: -- || p_bg_id || ' count=' || l_all_obj_list.COUNT);
1784: FORALL I IN NVL(l_all_obj_list.FIRST, 1) .. NVL(l_all_obj_list.LAST, 0)
1785: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1786: (
1787: ams_party_market_segment_id
1788: , last_update_date
1789: , last_updated_by
1826: FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id(I);
1827: CLOSE c_party_mkt_seg_seq;
1828: END LOOP;
1829: FORALL I IN NVL(l_all_obj_list.FIRST, 1) .. NVL(l_all_obj_list.LAST, 0)
1830: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1831: (
1832: ams_party_market_segment_id
1833: , last_update_date
1834: , last_updated_by
2041:
2042: -- Bug 5174046..perf issue. This delete is using lot of time
2043: -- There is a delete from each Buying Group Id inside generate_party_for_buyinggroup
2044:
2045: -- DELETE FROM AMS_PARTY_MARKET_SEGMENTS
2046: -- WHERE market_qualifier_type = 'BG';
2047:
2048: IF (l_direction_code = 'N') THEN
2049: /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
2116: */
2117:
2118: /* --this give GSCC error - "select * not allowed"
2119: INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T
2120: SELECT * FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
2121: */
2122:
2123: /*
2124: INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T (
2139: PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
2140: MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
2141: CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
2142: ROLLUP_PARTY_ID, SITE_USE_ID
2143: FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
2144: WHERE OLD_TERR.MARKET_QUALIFIER_TYPE='TERRITORY'
2145: AND OLD_TERR.site_use_code = 'SHIP_TO'
2146: AND OLD_TERR.party_id IS NOT NULL
2147: AND OLD_TERR.site_use_id IS NOT NULL;
2148: */
2149:
2150: -- no territory id parameter means generate party list for all territories
2151: ---R12: DO NOT DELETE BEFORE COPYING into TEMP Table
2152: --DELETE FROM AMS_PARTY_MARKET_SEGMENTS
2153: --WHERE market_qualifier_type = 'TERRITORY';
2154: OPEN c_get_all_territories;
2155: LOOP
2156: FETCH c_get_all_territories INTO l_terr_id;