[Home] [Help]
266: where list_header_id = g_list_header_id;
267:
268: cursor c_no_of_rows_reqd is
269: select distribution_pct,incl_object_id
270: from ams_list_select_actions
271: where list_select_action_id = p_list_select_action_id;
272:
273: cursor c_no_of_rows_in_list is
274: select no_of_rows_active
574: p_level => 'LOW');
575:
576:
577: FORALL I in p_list_select_action_id.first .. p_list_select_action_id.last
578: UPDATE ams_list_select_actions
579: SET no_of_rows_used = p_no_of_rows_used(i),
580: no_of_rows_available = p_no_of_rows_available(i),
581: no_of_rows_duplicates = p_no_of_rows_duplicates(i)
582: WHERE list_select_action_id = p_list_select_action_id(i);
634: a.distribution_pct,
635: a.rank rank_col,
636: sum(decode(e.marked_as_duplicate_flag,'Y',1,0))
637: FROM ams_list_entries e,
638: ams_list_select_actions a
639: ,ams_list_headers_all b
640: WHERE e.list_header_id = p_list_header_id
641: AND e.list_select_action_id = a.list_select_action_id
642: AND a.arc_action_used_by = 'LIST'
651: a.distribution_pct,
652: a.rank rank_col,
653: sum(decode(e.marked_as_duplicate_flag,'Y',1,0))
654: FROM ams_list_entries e,
655: ams_list_select_actions a
656: WHERE e.list_header_id = p_list_header_id
657: AND e.list_select_action_id = a.list_select_action_id
658: AND a.arc_action_used_by = 'LIST'
659: AND a.action_used_by_id = p_list_header_id
668: a.distribution_pct,
669: a.rank,
670: sum(decode(e.marked_as_duplicate_flag,'Y',1,0))
671: FROM ams_list_entries e,
672: ams_list_select_actions a,
673: ams_act_lists t
674: WHERE e.list_header_id = p_list_header_id
675: AND e.list_select_action_id = t.act_list_header_id
676: and t.list_header_id = a.INCL_OBJECT_ID
1061: -- Added for cancel list gen as it prevents parallel update- Raghu
1062: -- of list headers when cancel button is pressed
1063: commit;
1064:
1065: UPDATE ams_list_select_actions
1066: SET no_of_rows_used = 0
1067: WHERE arc_action_used_by = 'LIST'
1068: and action_used_by_id = p_list_header_id;
1069:
3444: x_include_sql OUT NOCOPY varchar2
3445: ) is
3446:
3447: ------------------------------------------------------------------------------
3448: -- Given the sql id from ams_list_select_actions it will retrieve the
3449: -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
3450: -- workbook_name.
3451: ------------------------------------------------------------------------------
3452: cursor cur_sql is
3590: x_include_sql OUT NOCOPY varchar2
3591: ) is
3592:
3593: ------------------------------------------------------------------------------
3594: -- Given the sql id from ams_list_select_actions it will retrieve the
3595: -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
3596: -- workbook_name.
3597: ------------------------------------------------------------------------------
3598: --bmuthukr 4351740. this cursor should be used to pick the disc sql
3693: x_include_sql OUT NOCOPY varchar2
3694: ) is
3695:
3696: ------------------------------------------------------------------------------
3697: -- Given the sql id from ams_list_select_actions it will retrieve the
3698: -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
3699: -- workbook_name.
3700: ------------------------------------------------------------------------------
3701: l_sql_string sql_string;
3795: x_return_status OUT NOCOPY VARCHAR2,
3796: x_msg_count OUT NOCOPY NUMBER,
3797: x_msg_data OUT NOCOPY VARCHAR2) IS
3798:
3799: -- AMS_LIST_SELECT_ACTIONS Record for init record and complete record
3800: l_tmp_action_rec ams_listaction_pvt.action_rec_type;
3801: p_action_rec ams_listaction_pvt.action_rec_type;
3802: l_list_select_action_id number;
3803: l_return_status VARCHAR2(100) := FND_API.G_FALSE;
3808: ----------------------------------------------------------------------------
3809: /*
3810: CURSOR c_action_dets is
3811: SELECT list_select_action_id
3812: FROM ams_list_select_actions
3813: WHERE action_used_by_id = p_action_used_by_id
3814: AND arc_action_used_by = p_action_used_by
3815: ORDER by rank; -- Raghu Jul 07
3816: -- ORDER by order_number;
3816: -- ORDER by order_number;
3817: */
3818: CURSOR c_action_dets is
3819: SELECT list_select_action_id
3820: FROM ams_list_select_actions
3821: WHERE action_used_by_id = p_action_used_by_id
3822: AND arc_action_used_by = p_action_used_by
3823: ORDER by order_number;
3824:
3896:
3897:
3898: /* CURSOR c_action_dets1 is
3899: SELECT list_select_action_id
3900: FROM ams_list_select_actions
3901: WHERE action_used_by_id = p_action_used_by_id
3902: AND arc_action_used_by = p_action_used_by
3903: AND order_number = l_order_num; -- added for bug fix 4443619
3904: -- AND order_number = l_sorted;-- removed for bug fix 4443619*/
3903: AND order_number = l_order_num; -- added for bug fix 4443619
3904: -- AND order_number = l_sorted;-- removed for bug fix 4443619*/
3905: CURSOR c_action_dets1 is
3906: SELECT list_select_action_id
3907: FROM ams_list_select_actions
3908: WHERE action_used_by_id = p_action_used_by_id
3909: AND arc_action_used_by = p_action_used_by
3910: AND order_number = l_order_num;
3911:
4707: 'N',
4708: 'N',
4709: 'Y',
4710: 1
4711: from ams_list_select_actions s,
4712: ams_list_tmp_entries t,
4713: ams_list_headers_all h
4714: where h.list_header_id = p_list_header_id
4715: and t.list_header_id = h.list_header_id
6121: ail.COL298 ,
6122: ail.COL299 ,
6123: ail.COL300
6124: from ams_list_entries ail,
6125: ams_list_select_actions als
6126: where als.list_select_action_id = ale.list_select_action_id
6127: and als.incl_object_id = ail.list_header_id
6128: and ail.list_entry_source_system_id = ale.list_entry_source_system_id
6129: and ail.enabled_flag = 'Y'
6131: where arc_list_select_action_from = 'LIST'
6132: and list_header_id = p_list_header_id
6133: and exists (select 'x'
6134: from ams_list_entries ail,
6135: ams_list_select_actions als
6136: where als.list_select_action_id = ale.list_select_action_id
6137: and als.incl_object_id = ail.list_header_id
6138: and ail.list_entry_source_system_id = ale.list_entry_source_system_id
6139: and ail.enabled_flag = 'Y' )
6137: and als.incl_object_id = ail.list_header_id
6138: and ail.list_entry_source_system_id = ale.list_entry_source_system_id
6139: and ail.enabled_flag = 'Y' )
6140: and exists (select 'x'
6141: from ams_list_select_actions als1
6142: where als1.list_select_action_id = ale.list_select_action_id
6143: and als1.list_action_type = 'INCLUDE' );
6144: write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
6145: end if;
6147: procedure update_import_list_entries(p_list_header_id in number) is
6148: cursor c1 is
6149: select imp.import_type
6150: from ams_imp_list_headers_all imp,
6151: ams_list_select_actions ail,
6152: ams_list_headers_all alh
6153: where alh.list_header_id = p_list_header_id
6154: and alh.list_header_id = ail.action_used_by_id
6155: and ail.arc_action_used_by = 'LIST'
6262: ail.orig_system_reference,
6263: ail.address3,
6264: ail.address4
6265: from ams_hz_b2b_mapping_v ail,
6266: ams_list_select_actions als
6267: where ail.import_list_header_id = als.incl_object_id
6268: and als.list_select_action_id = ale.list_select_action_id
6269: and ail.party_id = ale.list_entry_source_system_id
6270: and ail.IMPORT_SOURCE_LINE_ID = ale.IMP_SOURCE_LINE_ID)
6270: and ail.IMPORT_SOURCE_LINE_ID = ale.IMP_SOURCE_LINE_ID)
6271: where arc_list_select_action_from = 'IMPH'
6272: and list_header_id = p_list_header_id
6273: and exists (select 'x'
6274: from ams_list_select_actions als1
6275: where als1.list_select_action_id = ale.list_select_action_id
6276: and als1.list_action_type = 'INCLUDE' );
6277: write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
6278: end if;
6355: ail.orig_system_reference,
6356: ail.address3,
6357: ail.address4
6358: from ams_hz_b2c_mapping_v ail,
6359: ams_list_select_actions als
6360: where ail.import_list_header_id = als.incl_object_id
6361: and als.list_select_action_id = ale.list_select_action_id
6362: and ail.party_id = ale.list_entry_source_system_id
6363: and ail.IMPORT_SOURCE_LINE_ID = ale.IMP_SOURCE_LINE_ID)
6363: and ail.IMPORT_SOURCE_LINE_ID = ale.IMP_SOURCE_LINE_ID)
6364: where ale.arc_list_select_action_from = 'IMPH'
6365: and ale.list_header_id = p_list_header_id
6366: and exists (select 'x'
6367: from ams_list_select_actions als1
6368: where als1.list_select_action_id = ale.list_select_action_id
6369: and als1.list_action_type = 'INCLUDE' );
6370: write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
6371: end if;
6830: l_listheader_rec ams_listheader_pvt.list_header_rec_type;
6831: l_tmp_listheader_rec ams_listheader_pvt.list_header_rec_type;
6832:
6833: -- Two records are required for init rec and complete rec
6834: -- Table ams_list_select_actions
6835: l_listaction_rec ams_listaction_pvt.action_rec_type;
6836: l_tmp_listaction_rec ams_listaction_pvt.action_rec_type;
6837: l_no_of_rows_in_list number ;
6838: cursor c_get_rows (c_list_header_id in number ) is
6856: from ams_list_headers_all
6857: where list_header_id = p_list_header_id;
6858:
6859: cursor c_list_selection is
6860: select 'Y' from ams_list_select_actions
6861: where action_used_by_id = p_list_header_id
6862: and arc_action_used_by = 'LIST'
6863: and arc_incl_object_from in ('CELL','DIWB','SQL');
6864:
6862: and arc_action_used_by = 'LIST'
6863: and arc_incl_object_from in ('CELL','DIWB','SQL');
6864:
6865: cursor c_only_list_selection is
6866: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
6867: where act.action_used_by_id = p_list_header_id
6868: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
6869: and act.INCL_OBJECT_ID = head.list_header_id
6870: and head.status_code = 'AVAILABLE'
7028: NO_OF_ROWS_TARGETED,
7029: NO_OF_ROWS_DUPLICATES,
7030: RUNNING_TOTAL,
7031: DELTA
7032: FROM AMS_LIST_SELECT_ACTIONS
7033: WHERE ACTION_USED_BY_ID = P_LIST_HEADER_ID
7034: AND ARC_ACTION_USED_BY = P_ACTION;
7035:
7036: CURSOR C_WAIT_LIST(P_LIST_HEADER_ID NUMBER) IS
7034: AND ARC_ACTION_USED_BY = P_ACTION;
7035:
7036: CURSOR C_WAIT_LIST(P_LIST_HEADER_ID NUMBER) IS
7037: SELECT COUNT(1)
7038: FROM AMS_LIST_SELECT_ACTIONS ACT,
7039: AMS_LIST_HEADERS_ALL HEAD
7040: WHERE ACT.ACTION_USED_BY_ID = P_LIST_HEADER_ID
7041: AND ACT.ARC_INCL_OBJECT_FROM = 'LIST' AND ACT.ARC_ACTION_USED_BY = 'LIST'
7042: AND ACT.INCL_OBJECT_ID = HEAD.LIST_HEADER_ID
7737: write_to_act_log('List selections updated in remote instance.', 'LIST', g_list_header_id,'LOW');
7738: end if;
7739:
7740: /********** added by savio for remote bug 3764343 ******************************************/
7741: execute immediate 'begin Update ams_list_select_actions a1 set no_of_rows_requested =
7742: (select no_of_rows_requested from ams_list_select_actions'||'@'||g_database_link||
7743: ' b1 where b1.list_select_action_id = a1.list_select_action_id)
7744: where action_used_by_id = :1 and arc_action_used_by = :2; end; ' using p_list_header_id, l_action;
7745: write_to_act_log('No_of_rows_requested in list header table updated with values from remote instance.', 'LIST', p_list_header_id,'LOW');
7738: end if;
7739:
7740: /********** added by savio for remote bug 3764343 ******************************************/
7741: execute immediate 'begin Update ams_list_select_actions a1 set no_of_rows_requested =
7742: (select no_of_rows_requested from ams_list_select_actions'||'@'||g_database_link||
7743: ' b1 where b1.list_select_action_id = a1.list_select_action_id)
7744: where action_used_by_id = :1 and arc_action_used_by = :2; end; ' using p_list_header_id, l_action;
7745: write_to_act_log('No_of_rows_requested in list header table updated with values from remote instance.', 'LIST', p_list_header_id,'LOW');
7746:
8708: x_return_status OUT NOCOPY VARCHAR2,
8709: x_msg_count OUT NOCOPY NUMBER,
8710: x_msg_data OUT NOCOPY VARCHAR2) IS
8711:
8712: -- AMS_LIST_SELECT_ACTIONS Record for init record and complete record
8713: l_tmp_action_rec ams_listaction_pvt.action_rec_type;
8714: p_action_rec ams_listaction_pvt.action_rec_type;
8715: l_list_select_action_id number;
8716:
8725: -- a.act_list_header_id,
8726: c.list_select_action_id act_list_header_id,
8727: c.distribution_pct distribution_pct,
8728: c.arc_incl_object_from arc_incl_object_from
8729: FROM ams_act_lists a ,ams_act_lists b, ams_list_select_actions c
8730: WHERE a.list_used_by_id = b.list_used_by_id
8731: AND a.list_used_by = b.list_used_by
8732: AND b.list_header_id = p_action_used_by_id
8733: AND b.list_act_type = 'TARGET'
8806: l_l_sele_action_id number;
8807:
8808: CURSOR c_action_dets1 is
8809: SELECT list_select_action_id
8810: FROM ams_list_select_actions
8811: WHERE action_used_by_id = p_action_used_by_id
8812: AND arc_action_used_by = p_action_used_by
8813: AND order_number = l_order_num;
8814: l_order_number number := 0;
9512: l_listheader_rec ams_listheader_pvt.list_header_rec_type;
9513: l_tmp_listheader_rec ams_listheader_pvt.list_header_rec_type;
9514:
9515: -- Two records are required for init rec and complete rec
9516: -- Table ams_list_select_actions
9517: l_listaction_rec ams_listaction_pvt.action_rec_type;
9518: l_tmp_listaction_rec ams_listaction_pvt.action_rec_type;
9519: l_no_of_rows_in_list number ;
9520: cursor c_get_rows (c_list_header_id in number ) is
9532:
9533: l_list_selection varchar2(1);
9534: l_onlylist_selection varchar2(1);
9535: cursor c_list_selection is
9536: select 'Y' from ams_list_select_actions
9537: where action_used_by_id = p_list_header_id
9538: and arc_action_used_by = 'LIST'
9539: and arc_incl_object_from in ('CELL','DIWB','SQL');
9540:
9538: and arc_action_used_by = 'LIST'
9539: and arc_incl_object_from in ('CELL','DIWB','SQL');
9540:
9541: cursor c_only_list_selection is
9542: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
9543: where act.action_used_by_id = p_list_header_id
9544: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
9545: and act.INCL_OBJECT_ID = head.list_header_id
9546: and head.status_code = 'AVAILABLE'
10511:
10512:
10513: CURSOR c_minimum_rank (my_list_header_id IN NUMBER)
10514: IS
10515: SELECT min(b.rank) FROM ams_list_select_actions b
10516: WHERE b.action_used_by_id = p_list_header_id
10517: and b.arc_action_used_by = 'LIST'
10518: GROUP BY b.rank;
10519:
10598: -- checking to see if there are any List Source Ranks associated
10599: -- with the List.
10600: SELECT COUNT (rank)
10601: INTO l_rank_count
10602: FROM ams_list_select_actions
10603: WHERE action_used_by_id = p_list_header_id
10604: and arc_action_used_by = 'LIST';
10605:
10606: -- Write_To_Act_Log (' # of Ranks for this list = ' ||TO_CHAR (l_rank_count),'LIST',p_list_header_id ,'LOW');
11932: Errbuf OUT NOCOPY VARCHAR2,
11933: Retcode OUT NOCOPY VARCHAR2,
11934: p_action_used_by_id in number ) IS
11935:
11936: -- AMS_LIST_SELECT_ACTIONS Record for init record and complete record
11937: l_tmp_action_rec ams_listaction_pvt.action_rec_type;
11938: p_action_rec ams_listaction_pvt.action_rec_type;
11939: l_list_select_action_id number;
11940: l_running_total number := 0;
11949: -- Process each cursor record according to order specified by the user
11950: ----------------------------------------------------------------------------
11951: CURSOR c_action_dets is
11952: SELECT list_select_action_id
11953: FROM ams_list_select_actions
11954: WHERE action_used_by_id = p_action_used_by_id
11955: AND arc_action_used_by = p_action_used_by
11956: ORDER by order_number;
11957:
11985: l_list_act_id number;
11986: l_ord_num number;
11987: l_r_totals number;
11988: cursor c_delta is
11989: select list_select_action_id,order_number,running_total from ams_list_select_actions where
11990: action_used_by_id = p_action_used_by_id and arc_action_used_by = 'LIST'
11991: and list_action_type = 'INCLUDE' order by order_number;
11992: l_last_generation_success_flag varchar2(1);
11993:
11998:
11999: BEGIN
12000: g_list_header_id := p_action_used_by_id;
12001: Ams_Utility_Pvt.Write_Conc_log('Start calc_running_total : ');
12002: UPDATE ams_list_select_actions
12003: SET RUNNING_TOTAL = null, DELTA = null
12004: WHERE action_used_by_id = p_action_used_by_id
12005: AND arc_action_used_by = 'LIST';
12006:
12180: Ams_Utility_Pvt.Write_Conc_log('*******l_list_select_action(l_sorted) = '||l_list_select_action(l_sorted));
12181: Ams_Utility_Pvt.Write_Conc_log('*******l_selection_results(l_sorted) = '||l_selection_results(l_sorted));
12182: l_running_total := l_running_total + l_selection_results(l_sorted);
12183: Ams_Utility_Pvt.Write_Conc_log('*******l_running_total = '||l_running_total);
12184: Update ams_list_select_actions set RUNNING_TOTAL = l_running_total
12185: Where LIST_SELECT_ACTION_ID = l_list_select_action(l_sorted)
12186: and arc_action_used_by = 'LIST';
12187:
12188: end loop;
12204: if l_ord_num > 1 then
12205: l_delta := l_r_totals - l_previous_incl_total;
12206: Ams_Utility_Pvt.Write_Conc_log(' l_ord_num > 1 *******l_delta = '||l_delta);
12207: end if;
12208: Update ams_list_select_actions set delta = l_delta
12209: Where LIST_SELECT_ACTION_ID = l_list_act_id;
12210: l_delta := 0;
12211: l_previous_incl_total := l_r_totals;
12212: Ams_Utility_Pvt.Write_Conc_log('*******l_previous_incl_total = '||l_previous_incl_total);
12731: x_include_sql OUT NOCOPY varchar2
12732: ) is
12733:
12734: ------------------------------------------------------------------------------
12735: -- Given the sql id from ams_list_select_actions it will retrieve the
12736: -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
12737: -- workbook_name.
12738: ------------------------------------------------------------------------------
12739: cursor cur_sql is
12879: x_include_sql OUT NOCOPY varchar2
12880: ) is
12881:
12882: ------------------------------------------------------------------------------
12883: -- Given the sql id from ams_list_select_actions it will retrieve the
12884: -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
12885: -- workbook_name.
12886: ------------------------------------------------------------------------------
12887: l_sql_string sql_string;
12978: x_include_sql OUT NOCOPY varchar2
12979: ) is
12980:
12981: ------------------------------------------------------------------------------
12982: -- Given the sql id from ams_list_select_actions it will retrieve the
12983: -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
12984: -- workbook_name.
12985: ------------------------------------------------------------------------------
12986: cursor cur_diwb(l_incl_object_id in number ) is
13905: l_list_selection varchar2(1);
13906: l_onlylist_selection varchar2(1);
13907:
13908: cursor c_list_selection is
13909: select 'Y' from ams_list_select_actions
13910: where action_used_by_id = p_list_header_id
13911: and arc_action_used_by = 'LIST'
13912: and arc_incl_object_from in ('CELL','DIWB','SQL');
13913:
13911: and arc_action_used_by = 'LIST'
13912: and arc_incl_object_from in ('CELL','DIWB','SQL');
13913:
13914: cursor c_only_list_selection is
13915: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
13916: where act.action_used_by_id = p_list_header_id
13917: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
13918: and act.INCL_OBJECT_ID = head.list_header_id
13919: and head.status_code = 'AVAILABLE'
14002: and list_act_type = 'TARGET';
14003:
14004: cursor c3 is
14005: select 1
14006: from ams_list_select_actions
14007: where list_header_id = p_list_header_id
14008: and arc_incl_object_from = 'EMPLOYEE';
14009:
14010: cursor c4(l_list_used_by_id number) is