[Home] [Help]
155:
156: -- batoleti coding ends for bug# 6688996
157:
158:
159: l_insert_sql := 'insert into ams_list_entries '||
160: '( LIST_SELECT_ACTION_FROM_NAME, '||
161: ' LIST_ENTRY_SOURCE_SYSTEM_ID , '||
162: ' LIST_ENTRY_SOURCe_SYSTEM_TYPE, '||
163: ' list_select_action_id , '||
188: to_char(sysdate )|| ''''||','||
189: to_char(FND_GLOBAL.login_id )|| ',' ||''''||
190: to_char(sysdate )|| ''''||','||
191: l_created_by|| ',' ||
192: 'ams_list_entries_s.nextval' || ','||
193: 1 || ','||
194: ''''||'NONE' ||'''' || ','||
195: 0 || ',' ||
196: ''''||'NONE' ||'''' || ','||
194: ''''||'NONE' ||'''' || ','||
195: 0 || ',' ||
196: ''''||'NONE' ||'''' || ','||
197: ''''||'NONE' ||'''' || ','||
198: 'ams_list_entries_s.currval'|| ','||
199: 530 || ','||
200: ''''||'Y' ||''''|| ','||
201: ''''||'N' ||''''|| ','||
202: ''''||'N' ||''''|| ','||
264: FORALL i in :1 .. :2 '
265: || ' ' || p_sql_string || ' :tab(i)
266: and
267: not exists (select 1
268: from ams_list_entries
269: where list_entry_source_system_id = :tab(i)
270: and list_header_id = :5 and enabled_flag=''Y'');
271: :6 := SQL%ROWCOUNT;
272: END; '
370: sum(decode(marked_as_duplicate_flag,'Y',1,0)),
371: sum(decode(manually_entered_flag,
372: 'Y',decode(enabled_flag,'Y','1',0),
373: 0))
374: from ams_list_entries
375: where list_header_id = cur_p_list_header_id ;
376: */
377: --- manual entries changes added: musman
378: cursor c_get_max_entries
455: RAISE FND_API.g_exc_unexpected_error;
456: END IF;
457: x_return_status := FND_API.G_RET_STS_SUCCESS;
458:
459: update ams_list_entries
460: set marked_flag = null
461: where list_header_id = p_list_header_id ;
462:
463: --- manual entries changes start: musman
522: FORALL i in :1 .. :2 '
523: || ' ' || p_sql_string || ' :tab(i)
524: and
525: not exists (select 1
526: from ams_list_entries
527: where list_entry_source_system_id = :tab(i)
528: and list_header_id = :5 and enabled_flag=''Y'');
529:
530: END; '
656: */
657:
658: -- rmbhanda bug#5197904 start - Mark duplicate entries as disabled.
659:
660: UPDATE ams_list_entries a
661: SET a.enabled_flag = 'N',
662: a.marked_as_duplicate_flag = 'Y'
663: WHERE a.list_header_id = p_list_header_id
664: and a.enabled_flag = 'Y'
663: WHERE a.list_header_id = p_list_header_id
664: and a.enabled_flag = 'Y'
665: and a.manually_entered_flag ='Y'
666: AND a.rowid > (SELECT min(b.rowid)
667: from ams_list_entries b
668: where b.list_header_id = p_list_header_id
669: and b.party_id = a.party_id
670: and b.enabled_flag = 'Y'
671: and b.manually_entered_flag ='Y'
823: sum(decode(marked_as_duplicate_flag,'Y',1,0)),
824: sum(decode(manually_entered_flag,
825: 'Y',decode(enabled_flag,'Y','1',0),
826: 0))
827: from ams_list_entries
828: where list_header_id = cur_p_list_header_id ;
829:
830: l_master_type_id number;
831: l_source_type_code varchar2(30);
881:
882: -- batoleti coding ends for bug# 6688996
883:
884: FORALL I in p_primary_key_tbl.first .. p_primary_key_tbl.last
885: INSERT INTO ams_List_Entries
886: ( list_entry_id ,
887: last_update_date ,
888: last_updated_by ,
889: creation_date ,
911: first_name,
912: last_name,
913: email_address
914: )
915: ( select ams_list_entries_s.nextval,
916: sysdate,
917: fnd_global.user_id,
918: sysdate,
919: nvl(l_created_by, fnd_global.user_id),
924: 'NONE',
925: 'NONE',
926: 0,
927: p_list_header_id,
928: ams_list_entries_s.currval,
929: p_primary_key_tbl(i) ,
930: p_master_type,
931: 530,
932: 'Y',
941: p_last_name_tbl(i),
942: p_email_tbl(i)
943: from dual
944: where not exists (select 'x'
945: from ams_list_entries
946: where list_entry_source_system_id = p_primary_key_tbl(i)
947: and list_header_id = p_list_header_id));
948:
949: