[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:
3895:
3896:
3897: /* CURSOR c_action_dets1 is
3898: SELECT list_select_action_id
3899: FROM ams_list_select_actions
3900: WHERE action_used_by_id = p_action_used_by_id
3901: AND arc_action_used_by = p_action_used_by
3902: AND order_number = l_order_num; -- added for bug fix 4443619
3903: -- AND order_number = l_sorted;-- removed for bug fix 4443619*/
3902: AND order_number = l_order_num; -- added for bug fix 4443619
3903: -- AND order_number = l_sorted;-- removed for bug fix 4443619*/
3904: CURSOR c_action_dets1 is
3905: SELECT list_select_action_id,order_number
3906: FROM ams_list_select_actions
3907: WHERE action_used_by_id = p_action_used_by_id
3908: AND arc_action_used_by = p_action_used_by
3909: AND order_number = l_order_num;
3910:
4700: 'N',
4701: 'N',
4702: 'Y',
4703: 1
4704: from ams_list_select_actions s,
4705: ams_list_tmp_entries t,
4706: ams_list_headers_all h
4707: where h.list_header_id = p_list_header_id
4708: and t.list_header_id = h.list_header_id
6114: ail.COL298 ,
6115: ail.COL299 ,
6116: ail.COL300
6117: from ams_list_entries ail,
6118: ams_list_select_actions als
6119: where als.list_select_action_id = ale.list_select_action_id
6120: and als.incl_object_id = ail.list_header_id
6121: and ail.list_entry_source_system_id = ale.list_entry_source_system_id
6122: and ail.enabled_flag = 'Y'
6124: where arc_list_select_action_from = 'LIST'
6125: and list_header_id = p_list_header_id
6126: and exists (select 'x'
6127: from ams_list_entries ail,
6128: ams_list_select_actions als
6129: where als.list_select_action_id = ale.list_select_action_id
6130: and als.incl_object_id = ail.list_header_id
6131: and ail.list_entry_source_system_id = ale.list_entry_source_system_id
6132: and ail.enabled_flag = 'Y' )
6130: and als.incl_object_id = ail.list_header_id
6131: and ail.list_entry_source_system_id = ale.list_entry_source_system_id
6132: and ail.enabled_flag = 'Y' )
6133: and exists (select 'x'
6134: from ams_list_select_actions als1
6135: where als1.list_select_action_id = ale.list_select_action_id
6136: and als1.list_action_type = 'INCLUDE' );
6137: write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
6138: end if;
6140: procedure update_import_list_entries(p_list_header_id in number) is
6141: cursor c1 is
6142: select imp.import_type
6143: from ams_imp_list_headers_all imp,
6144: ams_list_select_actions ail,
6145: ams_list_headers_all alh
6146: where alh.list_header_id = p_list_header_id
6147: and alh.list_header_id = ail.action_used_by_id
6148: and ail.arc_action_used_by = 'LIST'
6255: ail.orig_system_reference,
6256: ail.address3,
6257: ail.address4
6258: from ams_hz_b2b_mapping_v ail,
6259: ams_list_select_actions als
6260: where ail.import_list_header_id = als.incl_object_id
6261: and als.list_select_action_id = ale.list_select_action_id
6262: and ail.party_id = ale.list_entry_source_system_id
6263: and ail.IMPORT_SOURCE_LINE_ID = ale.IMP_SOURCE_LINE_ID)
6263: and ail.IMPORT_SOURCE_LINE_ID = ale.IMP_SOURCE_LINE_ID)
6264: where arc_list_select_action_from = 'IMPH'
6265: and list_header_id = p_list_header_id
6266: and exists (select 'x'
6267: from ams_list_select_actions als1
6268: where als1.list_select_action_id = ale.list_select_action_id
6269: and als1.list_action_type = 'INCLUDE' );
6270: write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
6271: end if;
6348: ail.orig_system_reference,
6349: ail.address3,
6350: ail.address4
6351: from ams_hz_b2c_mapping_v ail,
6352: ams_list_select_actions als
6353: where ail.import_list_header_id = als.incl_object_id
6354: and als.list_select_action_id = ale.list_select_action_id
6355: and ail.party_id = ale.list_entry_source_system_id
6356: and ail.IMPORT_SOURCE_LINE_ID = ale.IMP_SOURCE_LINE_ID)
6356: and ail.IMPORT_SOURCE_LINE_ID = ale.IMP_SOURCE_LINE_ID)
6357: where ale.arc_list_select_action_from = 'IMPH'
6358: and ale.list_header_id = p_list_header_id
6359: and exists (select 'x'
6360: from ams_list_select_actions als1
6361: where als1.list_select_action_id = ale.list_select_action_id
6362: and als1.list_action_type = 'INCLUDE' );
6363: write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
6364: end if;
6773: l_listheader_rec ams_listheader_pvt.list_header_rec_type;
6774: l_tmp_listheader_rec ams_listheader_pvt.list_header_rec_type;
6775:
6776: -- Two records are required for init rec and complete rec
6777: -- Table ams_list_select_actions
6778: l_listaction_rec ams_listaction_pvt.action_rec_type;
6779: l_tmp_listaction_rec ams_listaction_pvt.action_rec_type;
6780: l_no_of_rows_in_list number ;
6781: cursor c_get_rows (c_list_header_id in number ) is
6799: from ams_list_headers_all
6800: where list_header_id = p_list_header_id;
6801:
6802: cursor c_list_selection is
6803: select 'Y' from ams_list_select_actions
6804: where action_used_by_id = p_list_header_id
6805: and arc_action_used_by = 'LIST'
6806: and arc_incl_object_from in ('CELL','DIWB','SQL');
6807:
6805: and arc_action_used_by = 'LIST'
6806: and arc_incl_object_from in ('CELL','DIWB','SQL');
6807:
6808: cursor c_only_list_selection is
6809: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
6810: where act.action_used_by_id = p_list_header_id
6811: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
6812: and act.INCL_OBJECT_ID = head.list_header_id
6813: and head.status_code = 'AVAILABLE'
6971: NO_OF_ROWS_TARGETED,
6972: NO_OF_ROWS_DUPLICATES,
6973: RUNNING_TOTAL,
6974: DELTA
6975: FROM AMS_LIST_SELECT_ACTIONS
6976: WHERE ACTION_USED_BY_ID = P_LIST_HEADER_ID
6977: AND ARC_ACTION_USED_BY = P_ACTION;
6978:
6979: c11_rec c11%rowtype;
7649: write_to_act_log('List selections updated in remote instance.', 'LIST', g_list_header_id,'LOW');
7650: end if;
7651:
7652: /********** added by savio for remote bug 3764343 ******************************************/
7653: execute immediate 'begin Update ams_list_select_actions a1 set no_of_rows_requested =
7654: (select no_of_rows_requested from ams_list_select_actions'||'@'||g_database_link||
7655: ' b1 where b1.list_select_action_id = a1.list_select_action_id)
7656: where action_used_by_id = :1 and arc_action_used_by = :2; end; ' using p_list_header_id, l_action;
7657: write_to_act_log('No_of_rows_requested in list header table updated with values from remote instance.', 'LIST', p_list_header_id,'LOW');
7650: end if;
7651:
7652: /********** added by savio for remote bug 3764343 ******************************************/
7653: execute immediate 'begin Update ams_list_select_actions a1 set no_of_rows_requested =
7654: (select no_of_rows_requested from ams_list_select_actions'||'@'||g_database_link||
7655: ' b1 where b1.list_select_action_id = a1.list_select_action_id)
7656: where action_used_by_id = :1 and arc_action_used_by = :2; end; ' using p_list_header_id, l_action;
7657: write_to_act_log('No_of_rows_requested in list header table updated with values from remote instance.', 'LIST', p_list_header_id,'LOW');
7658:
8620: x_return_status OUT NOCOPY VARCHAR2,
8621: x_msg_count OUT NOCOPY NUMBER,
8622: x_msg_data OUT NOCOPY VARCHAR2) IS
8623:
8624: -- AMS_LIST_SELECT_ACTIONS Record for init record and complete record
8625: l_tmp_action_rec ams_listaction_pvt.action_rec_type;
8626: p_action_rec ams_listaction_pvt.action_rec_type;
8627: l_list_select_action_id number;
8628:
8637: -- a.act_list_header_id,
8638: c.list_select_action_id act_list_header_id,
8639: c.distribution_pct distribution_pct,
8640: c.arc_incl_object_from arc_incl_object_from
8641: FROM ams_act_lists a ,ams_act_lists b, ams_list_select_actions c
8642: WHERE a.list_used_by_id = b.list_used_by_id
8643: AND a.list_used_by = b.list_used_by
8644: AND b.list_header_id = p_action_used_by_id
8645: AND b.list_act_type = 'TARGET'
8711: l_l_sele_action_id number;
8712:
8713: CURSOR c_action_dets1 is
8714: SELECT list_select_action_id,order_number
8715: FROM ams_list_select_actions
8716: WHERE action_used_by_id = p_action_used_by_id
8717: AND arc_action_used_by = p_action_used_by
8718: AND order_number = l_sorted;
8719: l_order_number number := 0;
9411: l_listheader_rec ams_listheader_pvt.list_header_rec_type;
9412: l_tmp_listheader_rec ams_listheader_pvt.list_header_rec_type;
9413:
9414: -- Two records are required for init rec and complete rec
9415: -- Table ams_list_select_actions
9416: l_listaction_rec ams_listaction_pvt.action_rec_type;
9417: l_tmp_listaction_rec ams_listaction_pvt.action_rec_type;
9418: l_no_of_rows_in_list number ;
9419: cursor c_get_rows (c_list_header_id in number ) is
9431:
9432: l_list_selection varchar2(1);
9433: l_onlylist_selection varchar2(1);
9434: cursor c_list_selection is
9435: select 'Y' from ams_list_select_actions
9436: where action_used_by_id = p_list_header_id
9437: and arc_action_used_by = 'LIST'
9438: and arc_incl_object_from in ('CELL','DIWB','SQL');
9439:
9437: and arc_action_used_by = 'LIST'
9438: and arc_incl_object_from in ('CELL','DIWB','SQL');
9439:
9440: cursor c_only_list_selection is
9441: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
9442: where act.action_used_by_id = p_list_header_id
9443: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
9444: and act.INCL_OBJECT_ID = head.list_header_id
9445: and head.status_code = 'AVAILABLE'
10410:
10411:
10412: CURSOR c_minimum_rank (my_list_header_id IN NUMBER)
10413: IS
10414: SELECT min(b.rank) FROM ams_list_select_actions b
10415: WHERE b.action_used_by_id = p_list_header_id
10416: and b.arc_action_used_by = 'LIST'
10417: GROUP BY b.rank;
10418:
10497: -- checking to see if there are any List Source Ranks associated
10498: -- with the List.
10499: SELECT COUNT (rank)
10500: INTO l_rank_count
10501: FROM ams_list_select_actions
10502: WHERE action_used_by_id = p_list_header_id
10503: and arc_action_used_by = 'LIST';
10504:
10505: -- Write_To_Act_Log (' # of Ranks for this list = ' ||TO_CHAR (l_rank_count),'LIST',p_list_header_id ,'LOW');
11831: Errbuf OUT NOCOPY VARCHAR2,
11832: Retcode OUT NOCOPY VARCHAR2,
11833: p_action_used_by_id in number ) IS
11834:
11835: -- AMS_LIST_SELECT_ACTIONS Record for init record and complete record
11836: l_tmp_action_rec ams_listaction_pvt.action_rec_type;
11837: p_action_rec ams_listaction_pvt.action_rec_type;
11838: l_list_select_action_id number;
11839: l_running_total number := 0;
11848: -- Process each cursor record according to order specified by the user
11849: ----------------------------------------------------------------------------
11850: CURSOR c_action_dets is
11851: SELECT list_select_action_id
11852: FROM ams_list_select_actions
11853: WHERE action_used_by_id = p_action_used_by_id
11854: AND arc_action_used_by = p_action_used_by
11855: ORDER by order_number;
11856:
11884: l_list_act_id number;
11885: l_ord_num number;
11886: l_r_totals number;
11887: cursor c_delta is
11888: select list_select_action_id,order_number,running_total from ams_list_select_actions where
11889: action_used_by_id = p_action_used_by_id and arc_action_used_by = 'LIST'
11890: and list_action_type = 'INCLUDE' order by order_number;
11891: l_last_generation_success_flag varchar2(1);
11892:
11897:
11898: BEGIN
11899: g_list_header_id := p_action_used_by_id;
11900: Ams_Utility_Pvt.Write_Conc_log('Start calc_running_total : ');
11901: UPDATE ams_list_select_actions
11902: SET RUNNING_TOTAL = null, DELTA = null
11903: WHERE action_used_by_id = p_action_used_by_id
11904: AND arc_action_used_by = 'LIST';
11905:
12079: Ams_Utility_Pvt.Write_Conc_log('*******l_list_select_action(l_sorted) = '||l_list_select_action(l_sorted));
12080: Ams_Utility_Pvt.Write_Conc_log('*******l_selection_results(l_sorted) = '||l_selection_results(l_sorted));
12081: l_running_total := l_running_total + l_selection_results(l_sorted);
12082: Ams_Utility_Pvt.Write_Conc_log('*******l_running_total = '||l_running_total);
12083: Update ams_list_select_actions set RUNNING_TOTAL = l_running_total
12084: Where LIST_SELECT_ACTION_ID = l_list_select_action(l_sorted)
12085: and arc_action_used_by = 'LIST';
12086:
12087: end loop;
12103: if l_ord_num > 1 then
12104: l_delta := l_r_totals - l_previous_incl_total;
12105: Ams_Utility_Pvt.Write_Conc_log(' l_ord_num > 1 *******l_delta = '||l_delta);
12106: end if;
12107: Update ams_list_select_actions set delta = l_delta
12108: Where LIST_SELECT_ACTION_ID = l_list_act_id;
12109: l_delta := 0;
12110: l_previous_incl_total := l_r_totals;
12111: Ams_Utility_Pvt.Write_Conc_log('*******l_previous_incl_total = '||l_previous_incl_total);
12630: x_include_sql OUT NOCOPY varchar2
12631: ) is
12632:
12633: ------------------------------------------------------------------------------
12634: -- Given the sql id from ams_list_select_actions it will retrieve the
12635: -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
12636: -- workbook_name.
12637: ------------------------------------------------------------------------------
12638: cursor cur_sql is
12778: x_include_sql OUT NOCOPY varchar2
12779: ) is
12780:
12781: ------------------------------------------------------------------------------
12782: -- Given the sql id from ams_list_select_actions it will retrieve the
12783: -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
12784: -- workbook_name.
12785: ------------------------------------------------------------------------------
12786: l_sql_string sql_string;
12877: x_include_sql OUT NOCOPY varchar2
12878: ) is
12879:
12880: ------------------------------------------------------------------------------
12881: -- Given the sql id from ams_list_select_actions it will retrieve the
12882: -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
12883: -- workbook_name.
12884: ------------------------------------------------------------------------------
12885: cursor cur_diwb(l_incl_object_id in number ) is
13804: l_list_selection varchar2(1);
13805: l_onlylist_selection varchar2(1);
13806:
13807: cursor c_list_selection is
13808: select 'Y' from ams_list_select_actions
13809: where action_used_by_id = p_list_header_id
13810: and arc_action_used_by = 'LIST'
13811: and arc_incl_object_from in ('CELL','DIWB','SQL');
13812:
13810: and arc_action_used_by = 'LIST'
13811: and arc_incl_object_from in ('CELL','DIWB','SQL');
13812:
13813: cursor c_only_list_selection is
13814: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
13815: where act.action_used_by_id = p_list_header_id
13816: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
13817: and act.INCL_OBJECT_ID = head.list_header_id
13818: and head.status_code = 'AVAILABLE'
13901: and list_act_type = 'TARGET';
13902:
13903: cursor c3 is
13904: select 1
13905: from ams_list_select_actions
13906: where list_header_id = p_list_header_id
13907: and arc_incl_object_from = 'EMPLOYEE';
13908:
13909: cursor c4(l_list_used_by_id number) is