hi all,
i had a small issue in fetching the information with the order by condition.when the columns in the order by is an input variable
the order by condition is not working as expected.
create table dummy_table
(
javasession_id VARCHAR2(255),
search_criteria VARCHAR2(4000),
executive_uid NUMBER(38),
plans_uid NUMBER(38),
participant_uid NUMBER(38),
transaction_date DATE,
transaction_type VARCHAR2(50),
event_name VARCHAR2(50),
event_code VARCHAR2(255),
reallocation_period_uid NUMBER(38),
distribution_code VARCHAR2(255),
award_code VARCHAR2(50),
sai_trade_event_code VARCHAR2(50),
request_date DATE,
account_name VARCHAR2(255),
offering_uid VARCHAR2(38),
offering_name VARCHAR2(120),
offering_currency_iso_code CHAR(3),
number_shares NUMBER(24,8),
share_price_scr_curr NUMBER(24,8),
share_price_off_curr NUMBER(24,8),
tx_amount_scr_curr NUMBER(24,8),
tx_amount_off_cur NUMBER(24,8),
tx_est_amt_scr_curr NUMBER(24,8),
tx_est_amt_off_curr NUMBER(24,8),
from_currency_iso_code CHAR(3),
to_currency_iso_code CHAR(3),
rate NUMBER(24,8),
from_amount NUMBER(24,8),
to_amount NUMBER(24,8),
status VARCHAR2(50),
grouping_code VARCHAR2(255),
row_number NUMBER(38),
reference_number VARCHAR2(50),
award_name VARCHAR2(256),
show_sai_hist_flag CHAR(1) default 'Y' not null,
eqty_dist_req_hdr_uid NUMBER(38),
event_action_type VARCHAR2(225)
);
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'Health Care Select Sector SPDR', 'USD', -1.73000000, 66.30000000, null, null, -114.64000000, null, null, null, null, null, null, '1899', null, null, null, null, null, null, 'NO GROUP', 14, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'iShares Dow Jones US Technology', 'USD', -1.13000000, 106.85000000, null, null, -120.64000000, null, null, null, null, null, null, '1902', null, null, null, null, null, null, 'NO GROUP', 17, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'MSIF Growth - I', 'USD', -4.02000000, 37.01000000, null, null, -148.94000000, null, null, null, null, null, null, '255', null, null, null, null, null, null, 'NO GROUP', 16, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('21-03-2016', 'dd-mm-yyyy'), 'S', 'FEE', 'Fee_Sweep', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', -788.56000000, 1.00000000, null, null, -788.56000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 7, 8360, 'Y', null, null, null, 'Fee_Sweep');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('20-04-2016', 'dd-mm-yyyy'), 'S', 'FEE', 'Fee_Sweep', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', -4.34000000, 1.00000000, null, null, -4.34000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 4, 8360, 'Y', null, null, null, 'Fee_Sweep');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-05-2016', 'dd-mm-yyyy'), 'B', 'TRADE', 'trade', 'SETTLED', 'SECAP US', 'iShares Dow Jones US Telecommunications', 'USD', 1263.79000000, 30.33000000, null, null, 38330.85000000, null, null, 5578, null, null, null, '1905', to_date('04-05-2016 15:59:33', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, 'NO GROUP', 2, 8360, 'Y', null, null, null, 'REALLOCATION');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-05-2016', 'dd-mm-yyyy'), 'B', 'TRADE', 'trade', 'SETTLED', 'SECAP US', 'Consumer Staples Select Sector SPDR', 'USD', 359.40000000, 52.53000000, null, null, 18879.37000000, null, null, 5578, null, null, null, '1879', to_date('04-05-2016 15:59:33', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, 'NO GROUP', 1, 8360, 'Y', null, null, null, 'REALLOCATION');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'MSIF Global Franchise', 'USD', -7.79000000, 20.94000000, null, null, -163.03000000, null, null, null, null, null, null, '251', null, null, null, null, null, null, 'NO GROUP', 15, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('22-01-2016', 'dd-mm-yyyy'), 'S', 'FEE', 'Fee_Sweep', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', -0.09000000, 1.00000000, null, null, -0.09000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 18, 8360, 'Y', null, null, null, 'Fee_Sweep');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'Consumer Discretionary Select Sector SPDR', 'USD', -2.03000000, 78.31000000, null, null, -159.32000000, null, null, null, null, null, null, '1878', null, null, null, null, null, null, 'NO GROUP', 12, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-05-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'trade', 'SETTLED', 'SECAP US', 'iShares Dow Jones US Technology', 'USD', -558.20000000, 102.49000000, null, null, -57210.22000000, null, null, 5578, null, null, null, '1902', to_date('04-05-2016 15:59:33', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, 'NO GROUP', 3, 8360, 'Y', null, null, null, 'REALLOCATION');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('23-03-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'iShares Dow Jones US Technology', 'USD', 2.09000000, 106.35000000, null, null, 221.98000000, null, null, null, null, null, null, '1902', null, null, null, null, null, null, 'NO GROUP', 6, 8360, 'Y', null, null, null, 'dividend');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('24-12-2015', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'iShares Dow Jones US Technology', 'USD', 1.78000000, 107.89000000, null, null, 192.34000000, null, null, null, null, null, null, '1902', null, null, null, null, null, null, 'NO GROUP', 20, 8360, 'Y', null, null, null, 'dividend');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('18-03-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'Consumer Staples Select Sector SPDR', 'USD', 4.13000000, 52.65000000, null, null, 217.36000000, null, null, null, null, null, null, '1879', null, null, null, null, null, null, 'NO GROUP', 10, 8360, 'Y', null, null, null, 'dividend');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('18-03-2016', 'dd-mm-yyyy'), 'B', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', 792.87000000, 1.00000000, null, null, 792.87000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 8, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('18-03-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'Consumer Discretionary Select Sector SPDR', 'USD', 4.12000000, 78.09000000, null, null, 321.70000000, null, null, null, null, null, null, '1878', null, null, null, null, null, null, 'NO GROUP', 9, 8360, 'Y', null, null, null, 'dividend');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'Consumer Staples Select Sector SPDR', 'USD', -1.63000000, 52.99000000, null, null, -86.31000000, null, null, null, null, null, null, '1879', null, null, null, null, null, null, 'NO GROUP', 13, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('18-03-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'Health Care Select Sector SPDR', 'USD', 3.02000000, 66.84000000, null, null, 201.58000000, null, null, null, null, null, null, '1899', null, null, null, null, null, null, 'NO GROUP', 11, 8360, 'Y', null, null, null, 'dividend');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('01-04-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', 0.03000000, 1.00000000, null, null, 0.03000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 5, 8360, 'Y', null, null, null, 'dividend');
insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('01-01-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', 0.09000000, 1.00000000, null, null, 0.09000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 19, 8360, 'Y', null, null, null, 'dividend');
consider p_sort_order :='transaction_date DESC,transaction_type'
create or replace procedure Get_Sort_Action(p_sort_order in varchar2
,p_compliance_actions_cur OUT SYS_REFCURSOR) IS
begin
OPEN p_compliance_actions_cur FOR
select DISTINCT tth.transaction_date
,tth.transaction_type
,tth.event_name
,tth.event_code
,tth.status
,tth.account_name
,tth.offering_name
,tth.offering_currency_iso_code
,tth.number_shares
,tth.share_price_off_curr
,tth.share_price_scr_curr
,tth.tx_amount_scr_curr
,tth.tx_amount_off_cur
,tth.tx_est_amt_off_curr
,tth.tx_est_amt_scr_curr
,tth.reallocation_period_uid
,tth.distribution_code
,tth.award_code
,tth.sai_trade_event_code
,tth.offering_uid
,tth.request_date
,tth.from_currency_iso_code
,tth.to_currency_iso_code
,tth.rate
,tth.from_amount
,tth.to_amount
,tth.grouping_code
,tth.row_number
,tth.participant_uid
,tth.show_sai_hist_flag
,tth.eqty_dist_req_hdr_uid
,tth.reference_number
,tth.award_name
,tth.event_action_type
FROM DUMMY_TABLE tth
ORDER BY p_sort_order;
end;
i am fetching the information out of refcursor but the order by condition is not working .
could anyone suggest me how to handle this senoria.
|