[Home] [Help]
83: TYPE dyna_cur_type IS REF CURSOR;
84:
85: CURSOR c_old_party_id IS -- parties already in the table
86: SELECT party_id
87: FROM ams_party_market_segments
88: WHERE market_segment_id = p_mkt_seg_id
89: AND market_segment_flag = p_mkt_seg_flag
90: AND end_date_active IS NULL
91: ORDER BY party_id;
122: CLOSE c_new_party_id ;
123:
124: IF l_expire_flag = 'Y' THEN -- this party is expired
125:
126: UPDATE AMS_PARTY_MARKET_SEGMENTS
127: SET end_date_active = SYSDATE
128: WHERE market_segment_flag = p_mkt_seg_flag
129: AND market_segment_id = p_mkt_seg_id
130: AND party_id = l_old_party_id;
188: l_api_name CONSTANT VARCHAR2(30) := 'Expire_Changed_Party';
189: TYPE dyna_cur_type IS REF CURSOR;
190:
191: CURSOR c_old_party_rec IS -- party_id and mkt_seg_id of exsiting party
192: SELECT market_segment_id, party_id FROM AMS_PARTY_MARKET_SEGMENTS
193: WHERE market_segment_id <> p_mkt_seg_id
194: AND market_segment_flag = p_mkt_seg_flag
195: AND end_date_active IS NULL
196: ORDER BY party_id;
230: CLOSE c_new_party_id ;
231:
232: IF l_expire_flag = 'Y' THEN
233:
234: UPDATE ams_party_market_segments
235: SET end_date_active = SYSDATE
236: WHERE market_segment_flag = p_mkt_seg_flag
237: AND market_segment_id = l_old_mkt_seg_id
238: AND party_id = l_old_party_id;
296: l_api_name CONSTANT VARCHAR2(30) := 'insert_new_party';
297: TYPE dyna_cur_type IS REF CURSOR;
298:
299: CURSOR c_party_count(id IN NUMBER) IS -- check if party is already in table
300: SELECT count(*) FROM AMS_PARTY_MARKET_SEGMENTS
301: WHERE market_segment_flag = p_mkt_seg_flag
302: AND market_segment_id = p_mkt_seg_id
303: AND party_id = id;
304:
302: AND market_segment_id = p_mkt_seg_id
303: AND party_id = id;
304:
305: CURSOR c_expire_party_count(id IN NUMBER) IS -- check if party expired
306: SELECT count(*) FROM AMS_PARTY_MARKET_SEGMENTS
307: WHERE market_segment_flag = p_mkt_seg_flag
308: AND market_segment_id = p_mkt_seg_id
309: AND party_id = id
310: AND end_date_active IS NOT NULL;
309: AND party_id = id
310: AND end_date_active IS NOT NULL;
311:
312: CURSOR c_party_mkt_seg_seq IS -- generate an ID
313: SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
314: FROM DUAL;
315:
316: CURSOR c_party_mkt_seg_count(party_mkt_seg_id IN NUMBER) IS -- check if ID is unique
317: SELECT count(*)
314: FROM DUAL;
315:
316: CURSOR c_party_mkt_seg_count(party_mkt_seg_id IN NUMBER) IS -- check if ID is unique
317: SELECT count(*)
318: FROM AMS_PARTY_MARKET_SEGMENTS
319: WHERE ams_party_market_segment_id = party_mkt_seg_id;
320:
321: c_party_id dyna_cur_type;
322:
359:
360: END LOOP;
361: AMS_Utility_Pvt.Debug_Message('Insert ');
362: --
363: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
364: (
365: ams_party_market_segment_id
366: , last_update_date
367: , last_updated_by
402: CLOSE c_expire_party_count;
403:
404: IF l_expire_party_count > 0 THEN -- party expired
405:
406: UPDATE AMS_PARTY_MARKET_SEGMENTS SET
407: last_update_date = SYSDATE
408: , last_updated_by = FND_GLOBAL.user_id
409: , last_update_login = FND_GLOBAL.conc_login_id
410: , object_version_number = object_version_number + 1
484: IS
485: l_api_name CONSTANT VARCHAR2(30) := 'expire_inactive_party';
486: l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
487: CURSOR c_old_party_id IS -- parties already in the table
488: SELECT party_id FROM AMS_PARTY_MARKET_SEGMENTS
489: WHERE market_segment_id = p_mkt_seg_id
490: AND market_segment_flag = p_mkt_seg_flag
491: AND end_date_active IS NULL
492: ORDER BY party_id;
517: END LOOP;
518:
519: IF l_expire_flag = 'Y' THEN -- this party is expired
520:
521: UPDATE AMS_PARTY_MARKET_SEGMENTS
522: SET end_date_active = SYSDATE
523: WHERE market_segment_flag = p_mkt_seg_flag
524: AND market_segment_id = p_mkt_seg_id
525: AND party_id = l_old_party_id;
580: l_api_name CONSTANT VARCHAR2(30) := 'expire_changed_party';
581:
582: CURSOR c_old_party_rec IS -- party_id and mkt_seg_id of exsiting party
583: SELECT market_segment_id, party_id
584: FROM AMS_PARTY_MARKET_SEGMENTS
585: WHERE market_segment_id <> p_mkt_seg_id
586: AND market_segment_flag = p_mkt_seg_flag
587: AND end_date_active IS NULL
588: ORDER BY party_id;
611: END LOOP;
612:
613: IF l_expire_flag = 'Y' THEN
614:
615: UPDATE AMS_PARTY_MARKET_SEGMENTS
616: SET end_date_active = SYSDATE
617: WHERE market_segment_flag = p_mkt_seg_flag
618: AND market_segment_id = l_old_mkt_seg_id
619: AND party_id = l_old_party_id;
675: IS
676: l_api_name CONSTANT VARCHAR2(30) := 'insert_new_party';
677:
678: CURSOR c_party_count(id IN NUMBER) IS -- check if party is already in table
679: SELECT count(*) FROM AMS_PARTY_MARKET_SEGMENTS
680: WHERE market_segment_flag = p_mkt_seg_flag
681: AND market_segment_id = p_mkt_seg_id
682: AND party_id = id;
683:
681: AND market_segment_id = p_mkt_seg_id
682: AND party_id = id;
683:
684: CURSOR c_expire_party_count(id IN NUMBER) IS -- check if party expired
685: SELECT count(*) FROM AMS_PARTY_MARKET_SEGMENTS
686: WHERE market_segment_flag = p_mkt_seg_flag
687: AND market_segment_id = p_mkt_seg_id
688: AND party_id = id
689: AND end_date_active IS NOT NULL;
688: AND party_id = id
689: AND end_date_active IS NOT NULL;
690:
691: CURSOR c_party_mkt_seg_seq IS -- generate an ID
692: SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
693: FROM DUAL;
694:
695: CURSOR c_party_mkt_seg_count(party_mkt_seg_id IN NUMBER) IS -- check if ID is unique
696: SELECT count(*)
693: FROM DUAL;
694:
695: CURSOR c_party_mkt_seg_count(party_mkt_seg_id IN NUMBER) IS -- check if ID is unique
696: SELECT count(*)
697: FROM AMS_PARTY_MARKET_SEGMENTS
698: WHERE ams_party_market_segment_id = party_mkt_seg_id;
699:
700:
701: l_party_id NUMBER;
735: END LOOP;
736:
737: AMS_Utility_Pvt.Debug_Message('Insert ');
738: --
739: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
740: (
741: ams_party_market_segment_id
742: , last_update_date
743: , last_updated_by
776: CLOSE c_expire_party_count;
777:
778: IF l_expire_party_count > 0 THEN -- party expired
779:
780: UPDATE AMS_PARTY_MARKET_SEGMENTS SET
781: last_update_date = SYSDATE
782: , last_updated_by = FND_GLOBAL.user_id
783: , last_update_login = FND_GLOBAL.conc_login_id
784: , object_version_number = object_version_number + 1
1298: /*****************************************************************************/
1299: -- Procedure
1300: -- load_party_mkt_seg
1301: -- Purpose
1302: -- load ams_party_market_segments
1303: -- History
1304: -- 01/16/2000 julou created
1305: -- 05/05/2000 ptendulk Modified 1.Added out parameter to capture error
1306: -- 2. Added input parameter to get the Cell id
2551: *
2552: * HISTORY
2553: * 10/14/2001 yzhao created
2554: * 04/09/2003 niprakas Fix for the bug#2833114. The dynamic SQL are
2555: * changed. The insert statement for AMS_PARTY_MARKET_SEGMENTS
2556: * is changed. It now inserts cust_account_id,cust_acct_site_id
2557: * and cust_site_use_code.
2558: ******************************************************************************/
2559: PROCEDURE generate_party_for_territory
2612: l_denorm_csr INTEGER;
2613:
2614: /*
2615: CURSOR c_party_mkt_seg_seq IS -- generate an ID for INSERT
2616: SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
2617: FROM DUAL;
2618: */
2619: BEGIN
2620: p_retcode := 0;
2848: DBMS_OUTPUT.PUT_LINE( substr(l_party_where_sql, l_index, 240));
2849: l_index := l_index + 240;
2850: END LOOP;
2851: */
2852: DELETE FROM AMS_PARTY_MARKET_SEGMENTS
2853: WHERE market_qualifier_type = 'TERRITORY'
2854: AND market_qualifier_reference = p_terr_id;
2855: -- remove 'AND ' at the end of the where clause
2856: -- write_conc_log('Before opening the cursor ');
2858:
2859:
2860: IF l_party_select_sql IS NOT NULL THEN
2861: -- yzhao: 05/08/2003 SQL bind variable project
2862: l_final_sql := 'INSERT INTO AMS_PARTY_MARKET_SEGMENTS(ams_party_market_segment_id, last_update_date, last_updated_by';
2863: l_final_sql := l_final_sql || ', creation_date, created_by, last_update_login, object_version_number, market_segment_id';
2864: l_final_sql := l_final_sql || ', market_segment_flag, party_id, start_date_active, end_date_active, org_id';
2865: l_final_sql := l_final_sql || ', market_qualifier_type, market_qualifier_reference, cust_account_id, cust_acct_site_id, site_use_code)';
2866: l_final_sql := l_final_sql || ' SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL, SYSDATE, FND_GLOBAL.user_id';
2862: l_final_sql := 'INSERT INTO AMS_PARTY_MARKET_SEGMENTS(ams_party_market_segment_id, last_update_date, last_updated_by';
2863: l_final_sql := l_final_sql || ', creation_date, created_by, last_update_login, object_version_number, market_segment_id';
2864: l_final_sql := l_final_sql || ', market_segment_flag, party_id, start_date_active, end_date_active, org_id';
2865: l_final_sql := l_final_sql || ', market_qualifier_type, market_qualifier_reference, cust_account_id, cust_acct_site_id, site_use_code)';
2866: l_final_sql := l_final_sql || ' SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL, SYSDATE, FND_GLOBAL.user_id';
2867: l_final_sql := l_final_sql || ', SYSDATE, FND_GLOBAL.user_id, FND_GLOBAL.conc_login_id, 1, 0';
2868: l_final_sql := l_final_sql || ', ''N'', party_id, SYSDATE, NULL, :org_id org_id';
2869: l_final_sql := l_final_sql || ', ''TERRITORY'', :terr_id market_qualifier_reference, cust_account_id, cust_acct_site_id, site_use_code FROM (';
2870: l_final_sql := l_final_sql || l_party_select_sql || substr(l_party_where_sql, 1, length(l_party_where_sql)-4) || ')';
2901: OPEN c_party_mkt_seg_seq;
2902: FETCH c_party_mkt_seg_seq INTO l_party_mkt_seg_id;
2903: CLOSE c_party_mkt_seg_seq;
2904:
2905: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
2906: (
2907: ams_party_market_segment_id
2908: , last_update_date
2909: , last_updated_by
3034: /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI */
3035: AND directional_flag = NVL(p_direction, directional_flag);
3036:
3037: CURSOR c_party_mkt_seg_seq IS -- generate an ID
3038: SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
3039: FROM DUAL;
3040:
3041: BEGIN
3042: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Start buyinggroup_id=' || p_bg_id);
3043: p_errbuf := null;
3044: p_retcode := 0;
3045:
3046: -- delete all buying group records for this subject_id
3047: DELETE FROM AMS_PARTY_MARKET_SEGMENTS
3048: WHERE market_qualifier_type = 'BG'
3049: AND market_qualifier_reference = p_bg_id;
3050:
3051: l_client_info := TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10));
3053: FETCH c_party_mkt_seg_seq INTO l_index;
3054: CLOSE c_party_mkt_seg_seq;
3055:
3056: -- 03/26/2002 always return the party itself as part of the buying group
3057: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
3058: (
3059: ams_party_market_segment_id
3060: , last_update_date
3061: , last_updated_by
3136: -- DBMS_OUTPUT.PUT_LINE(l_full_name || ': INSERT buying group: buyinggroup_id='
3137: -- || p_bg_id || ' count=' || l_all_obj_list.COUNT);
3138:
3139: FORALL I IN l_all_obj_list.FIRST .. l_all_obj_list.LAST
3140: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
3141: (
3142: ams_party_market_segment_id
3143: , last_update_date
3144: , last_updated_by
3184: CLOSE c_party_mkt_seg_seq;
3185: END LOOP;
3186:
3187: FORALL I IN l_all_obj_list.FIRST .. l_all_obj_list.LAST
3188: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
3189: (
3190: ams_party_market_segment_id
3191: , last_update_date
3192: , last_updated_by
3381: p_obj_list => l_obj_list);
3382: END IF;
3383: ELSE
3384: -- no buying group id parameter means generate party pair list for all buying groups
3385: DELETE FROM AMS_PARTY_MARKET_SEGMENTS
3386: WHERE market_qualifier_type = 'BG';
3387:
3388: IF (l_direction_code = 'N') THEN
3389: /* yzhao: fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
3442: IF p_terr_id IS NOT NULL THEN
3443: generate_party_for_territory(errbuf, retcode, p_terr_id, 'Y', l_bind_vars);
3444: ELSE
3445: -- no territory id parameter means generate party list for all territories
3446: DELETE FROM AMS_PARTY_MARKET_SEGMENTS
3447: WHERE market_qualifier_type = 'TERRITORY';
3448: OPEN c_get_all_territories;
3449: LOOP
3450: FETCH c_get_all_territories INTO l_terr_id;