[Home] [Help]
594: *
595: * HISTORY
596: * 10/14/2001 yzhao created
597: * 04/09/2003 niprakas Fix for the bug#2833114. The dynamic SQL are
598: * changed. The insert statement for AMS_PARTY_MARKET_SEGMENTS
599: * is changed. It now inserts cust_account_id,cust_acct_site_id
600: * and cust_site_use_code.
601: ******************************************************************************/
602: PROCEDURE generate_party_for_territory
669: -- l_hzsaleschannel_sql VARCHAR2(10000) := p_hzsaleschannel_sql;
670: -- TYPE PartyCurTyp IS REF CURSOR; -- define weak REF CURSOR type
671: -- l_party_cv PartyCurTyp; -- declare cursor variable
672: -- CURSOR c_party_mkt_seg_seq IS -- generate an ID for INSERT
673: -- SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
674: -- FROM DUAL;
675:
676:
677: --R12: mkothari -- added cursor to get territory org
1320: AND market_qualifier_reference = p_terr_id;
1321:
1322: /* --this give GSCC error - "select * not allowed"
1323: INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T
1324: SELECT * FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
1325: */
1326: INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T (
1327: AMS_PARTY_MARKET_SEGMENT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1328: CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
1341: PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
1342: MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
1343: CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
1344: ROLLUP_PARTY_ID, SITE_USE_ID
1345: FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
1346: WHERE OLD_TERR.MARKET_QUALIFIER_TYPE='TERRITORY'
1347: AND OLD_TERR.market_qualifier_reference = p_terr_id
1348: AND OLD_TERR.site_use_code = 'SHIP_TO'
1349: AND OLD_TERR.party_id IS NOT NULL
1349: AND OLD_TERR.party_id IS NOT NULL
1350: AND OLD_TERR.site_use_id IS NOT NULL;
1351:
1352:
1353: DELETE FROM AMS_PARTY_MARKET_SEGMENTS
1354: WHERE market_qualifier_type = 'TERRITORY'
1355: AND market_qualifier_reference = p_terr_id;
1356: -- remove 'AND ' at the end of the where clause
1357: -- write_conc_log('Before opening the cursor ');
1360: substr(l_party_where_sql, 1, length(l_party_where_sql)-4));
1361: IF l_party_select_sql IS NOT NULL
1362: THEN
1363: -- yzhao: 05/08/2003 SQL bind variable project
1364: l_final_sql := 'INSERT INTO AMS_PARTY_MARKET_SEGMENTS('||
1365: 'ams_party_market_segment_id, '||
1366: 'last_update_date, '||
1367: 'last_updated_by,';
1368: l_final_sql := l_final_sql ||
1385: 'site_use_id, '||
1386: 'bill_to_site_use_id, '||
1387: 'site_use_code )';
1388: l_final_sql := l_final_sql ||
1389: ' SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL, '||
1390: 'SYSDATE, '||
1391: 'FND_GLOBAL.user_id, ';
1392: l_final_sql := l_final_sql ||
1393: 'SYSDATE, '||
1455: --Needs to be done here so that the copied row is not lost
1456: if l_store_index>0 then
1457:
1458: write_conc_log('############### Copy Store from ozf mapping table to ams table >>> START');
1459: INSERT INTO ams_party_market_segments
1460: (
1461: AMS_PARTY_MARKET_SEGMENT_ID,
1462: LAST_UPDATE_DATE,
1463: LAST_UPDATED_BY,
1479: SITE_USE_ID,
1480: ORG_ID
1481: )
1482: select
1483: AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL,
1484: SYSDATE,
1485: FND_GLOBAL.user_id,
1486: SYSDATE,
1487: FND_GLOBAL.user_id,
1526: -- ' territory_id=' || p_terr_id);
1527: OPEN c_party_mkt_seg_seq;
1528: FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id;
1529: CLOSE c_party_mkt_seg_seq;
1530: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1531: (
1532: ams_party_market_segment_id
1533: , last_update_date
1534: , last_updated_by
1572: END LOOP;
1573: CLOSE l_party_cv;
1574: */
1575: END IF;
1576: UPDATE ams_party_market_segments a
1577: SET a.rollup_party_id = ( SELECT acct.party_id
1578: FROM hz_cust_accounts_all acct,
1579: hz_cust_acct_sites_all acct_site,
1580: hz_cust_site_uses_all site_use
1672: AND object_id = p_bg_id
1673: /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI */
1674: AND directional_flag = NVL(p_direction, directional_flag);
1675: CURSOR c_party_mkt_seg_seq IS -- generate an ID
1676: SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
1677: FROM DUAL;
1678: BEGIN
1679: Ozf_Utility_pvt.write_conc_log(l_full_name || ': Start buyinggroup_id=' || p_bg_id);
1680: p_errbuf := null;
1680: p_errbuf := null;
1681: p_retcode := 0;
1682: -- delete all buying group records for this subject_id
1683:
1684: DELETE FROM AMS_PARTY_MARKET_SEGMENTS
1685: WHERE market_qualifier_type = 'BG'
1686: AND market_qualifier_reference = p_bg_id;
1687:
1688: -- l_client_info := TO_NUMBER(SUBSTRB(userenv('CLIENT
1692: OPEN c_party_mkt_seg_seq;
1693: FETCH c_party_mkt_seg_seq INTO l_index;
1694: CLOSE c_party_mkt_seg_seq;
1695: -- 03/26/2002 always return the party itself as part of the buying group
1696: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1697: (
1698: ams_party_market_segment_id
1699: , last_update_date
1700: , last_updated_by
1768: END LOOP;
1769: -- DBMS_OUTPUT.PUT_LINE(l_full_name || ': INSERT buying group: buyinggroup_id='
1770: -- || p_bg_id || ' count=' || l_all_obj_list.COUNT);
1771: FORALL I IN NVL(l_all_obj_list.FIRST, 1) .. NVL(l_all_obj_list.LAST, 0)
1772: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1773: (
1774: ams_party_market_segment_id
1775: , last_update_date
1776: , last_updated_by
1813: FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id(I);
1814: CLOSE c_party_mkt_seg_seq;
1815: END LOOP;
1816: FORALL I IN NVL(l_all_obj_list.FIRST, 1) .. NVL(l_all_obj_list.LAST, 0)
1817: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
1818: (
1819: ams_party_market_segment_id
1820: , last_update_date
1821: , last_updated_by
2028:
2029: -- Bug 5174046..perf issue. This delete is using lot of time
2030: -- There is a delete from each Buying Group Id inside generate_party_for_buyinggroup
2031:
2032: -- DELETE FROM AMS_PARTY_MARKET_SEGMENTS
2033: -- WHERE market_qualifier_type = 'BG';
2034:
2035: IF (l_direction_code = 'N') THEN
2036: /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
2103: */
2104:
2105: /* --this give GSCC error - "select * not allowed"
2106: INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T
2107: SELECT * FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
2108: */
2109:
2110: /*
2111: INSERT INTO OZF_PARTY_MARKET_SEGMENTS_T (
2126: PROGRAM_ID, PROGRAM_UPDATE_DATE, TERRITORY_ID,
2127: MARKET_QUALIFIER_TYPE, MARKET_QUALIFIER_REFERENCE, CUST_ACCOUNT_ID,
2128: CUST_ACCT_SITE_ID, SITE_USE_CODE, BILL_TO_SITE_USE_ID,
2129: ROLLUP_PARTY_ID, SITE_USE_ID
2130: FROM AMS_PARTY_MARKET_SEGMENTS OLD_TERR
2131: WHERE OLD_TERR.MARKET_QUALIFIER_TYPE='TERRITORY'
2132: AND OLD_TERR.site_use_code = 'SHIP_TO'
2133: AND OLD_TERR.party_id IS NOT NULL
2134: AND OLD_TERR.site_use_id IS NOT NULL;
2135: */
2136:
2137: -- no territory id parameter means generate party list for all territories
2138: ---R12: DO NOT DELETE BEFORE COPYING into TEMP Table
2139: --DELETE FROM AMS_PARTY_MARKET_SEGMENTS
2140: --WHERE market_qualifier_type = 'TERRITORY';
2141: OPEN c_get_all_territories;
2142: LOOP
2143: FETCH c_get_all_territories INTO l_terr_id;