[Home] [Help]
172: l_exp_party_tbl num_tab;
173:
174: --Cursor to select expired parties which are active in segments table
175: CURSOR C_expired_party_ids IS
176: SELECT party_id FROM AMS_PARTY_MARKET_SEGMENTS
177: WHERE market_segment_id = p_mkt_seg_id
178: AND end_date_active IS NULL
179: MINUS
180: SELECT column_value party_id FROM TABLE(CAST(p_party_tbl AS jtf_number_table));
199:
200: IF l_exp_party_tbl.count > 0 THEN
201:
202: FORALL i in l_exp_party_tbl.FIRST..l_exp_party_tbl.LAST
203: UPDATE AMS_PARTY_MARKET_SEGMENTS
204: SET end_date_active = SYSDATE
205: , last_update_date = SYSDATE
206: , last_updated_by = FND_GLOBAL.user_id
207: , last_update_login = FND_GLOBAL.conc_login_id
267: l_api_name CONSTANT VARCHAR2(30) := 'expire_changed_party_dbms';
268:
269: CURSOR c_old_party_rec IS -- party_id and mkt_seg_id of exsiting party
270: SELECT market_segment_id, party_id
271: FROM AMS_PARTY_MARKET_SEGMENTS
272: WHERE market_segment_id <> p_mkt_seg_id
273: AND end_date_active IS NULL
274: ORDER BY party_id;
275:
312: AMS_Utility_PVT.Debug_Message(l_api_name||' expire changed parties ');
313: END IF;
314: IF l_iterator > 1 THEN
315: FORALL i in l_sql_tbl.first .. l_sql_tbl.last
316: UPDATE AMS_PARTY_MARKET_SEGMENTS
317: SET end_date_active = SYSDATE
318: , last_update_date = SYSDATE
319: , last_updated_by = FND_GLOBAL.user_id
320: , last_update_login = FND_GLOBAL.conc_login_id
382: --Cursor to select new parties which only available in with the seg query, not in segments table
383: CURSOR C_new_parties IS
384: SELECT column_value party_id FROM TABLE(CAST(p_party_tbl AS jtf_number_table))
385: MINUS
386: SELECT party_id FROM AMS_PARTY_MARKET_SEGMENTS
387: WHERE market_segment_id = p_mkt_seg_id;
388:
389: --Cursor to select inactive parties from segments table which are also returned by seg query
390: CURSOR C_activate_parties IS
387: WHERE market_segment_id = p_mkt_seg_id;
388:
389: --Cursor to select inactive parties from segments table which are also returned by seg query
390: CURSOR C_activate_parties IS
391: SELECT seg.party_id FROM AMS_PARTY_MARKET_SEGMENTS seg,
392: (SELECT column_value party_id FROM TABLE(CAST(p_party_tbl AS jtf_number_table))) tbl
393: WHERE seg.party_id = tbl.party_id
394: AND seg.end_date_active IS NOT NULL; --Inactive parties
395:
411:
412: IF l_party_tbl.count > 0 then
413:
414: FORALL i IN l_party_tbl.FIRST..l_party_tbl.LAST
415: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
416: (
417: ams_party_market_segment_id
418: , last_update_date
419: , last_updated_by
428: , end_date_active
429: , org_id
430: )
431: select
432: AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
433: , SYSDATE
434: , FND_GLOBAL.user_id
435: , SYSDATE
436: , FND_GLOBAL.user_id
458: END IF;
459:
460: IF l_party_tbl.count > 0 THEN
461: FORALL i IN l_party_tbl.FIRST..l_party_tbl.LAST
462: UPDATE AMS_PARTY_MARKET_SEGMENTS SET
463: last_update_date = SYSDATE
464: , last_updated_by = FND_GLOBAL.user_id
465: , last_update_login = FND_GLOBAL.conc_login_id
466: , object_version_number = object_version_number + 1
508: /*****************************************************************************/
509: -- Procedure
510: -- load_party_seg_one
511: -- Purpose
512: -- load ams_party_market_segments for one segment
513: --
514: -- Note
515: -- 1. The process will execute the ams_cell_pvt.get_comp_sql for a given
516: -- cell_id to get its sql and its ancestors, then use DBMS SQL to excute
709: /*****************************************************************************/
710: -- Procedure
711: -- load_party_seg
712: -- Purpose
713: -- load ams_party_market_segments
714: --
715: -- Note
716: -- 1. If cell_id is passed into, then only that cell will be refreshed or
717: -- else all the cells will be refreshed.