I am creating a procedure where the input parameter is an Associative Array (it contains multiple TRANSFERIDs) and this input parameter will be used in the SELECT query's WHERE clause.
With this background, I am trying to write a procedure but getting stuck when it comes to WHERE clause. The other problem is this input parameter will be sent to me by a different program. Anyway here is the code. Hoping someone can look and point me in the right direction.
CREATE OR REPLACE PROCEDURE EXAMPLE_DIRECT_POS_TO_MERCH (p_ChrInTransfer_id an array)
IS
TYPE vsoposdetails_rt IS RECORD (Businessunitid VSO_POS_TRANSFERS.business_unit_id%TYPE, Transfer_id VSO_POS_TRANSFERS.transfer_id, transfer_occurence_type VSO_POS_TRANSFERS.transfer_occurence_type%TYPE, line_no VSO_POS_TRANSFERS.line_no%TYPE, prgid VSO_POS_TRANSFERS.prgid%TYPE, transfer_type VSO_POS_TRANSFERS.transfer_type%TYPE, request_id VSO_POS_TRANSFERS.request_id%TYPE, from_site VSO_POS_TRANSFERS.from_site%TYPE, to_site VSO_POS_TRANSFERS.to_site%TYPE, bar_code_bus_unit_id VSO_POS_TRANSFERS.bar_code_bus_unit_id%TYPE, bar_code_sub_type VSO_POS_TRANSFERS.bar_code_sub_type%TYPE, bar_code_id VSO_POS_TRANSFERS.bar_code_id%TYPE, style_id VSO_POS_TRANSFERS.style_id%TYPE, color_id VSO_POS_TRANSFERS.color_id%TYPE, dimension_id VSO_POS_TRANSFERS.dimension_id%TYPE, valid_dimension_id VSO_POS_TRANSFERS.valid_dimension_id%TYPE, size_id VSO_POS_TRANSFERS.size_id%TYPE, valid_size_id VSO_POS_TRANSFERS.valid_size_id%TYPE, item_qty VSO_POS_TRANSFERS.item_qty%TYPE, valid_ind VSO_POS_TRANSFERS.valid_ind%TYPE, status VSO_POS_TRANSFERS.status%TYPE, site_id VSO_POS_TRANSFERS.site_id%TYPE, valid_header VSO_POS_TRANSFERS.valid_header%TYPE, valid_detail VSO_POS_TRANSFERS.valid_detail%TYPE, reason_id VSO_POS_TRANSFERS.reason_id%TYPE, transfer_origin_name VSO_POS_TRANSFERS.transfer_origin_name%TYPE, from_site_retail_value VSO_POS_TRANSFERS.from_site_retail_value%TYPE, to_site_retail_value VSO_POS_TRANSFERS.to_site_retail_value%TYPE, unit_cost VSO_POS_TRANSFERS.unit_cost%TYPE, expense_transfer_id VSO_POS_TRANSFERS.expense_transfer_id%TYPE, rej_rpt_printed_ind VSO_POS_TRANSFERS.rej_rpt_printed_ind%TYPE, rejected_id VSO_POS_TRANSFERS.rejected_id%TYPE, reason_sub_type VSO_POS_TRANSFERS.reason_sub_type%TYPE, process_status VSO_POS_TRANSFERS.process_status%TYPE, process_date_time VSO_POS_TRANSFERS.process_date_time%TYPE, richter_version_id VSO_POS_TRANSFERS.richter_version_id%TYPE, pos_version_id VSO_POS_TRANSFERS.pos_version_id%TYPE, control_no VSO_POS_TRANSFERS.control_no%TYPE, user_trace_id VSO_POS_TRANSFERS.user_trace_id%TYPE, user_id VSO_POS_TRANSFERS.user_id%TYPE, ride_error_code VSO_POS_TRANSFERS.ride_error_code%TYPE, severity_code VSO_POS_TRANSFERS.severity_code%TYPE, inventory_adjust_id VSO_POS_TRANSFERS.inventory_adjust_id%TYPE, from_business_unit_id VSO_POS_TRANSFERS.from_business_unit_id%TYPE, to_business_unit_id VSO_POS_TRANSFERS.to_business_unit_id%TYPE, cancel_ind VSO_POS_TRANSFERS.cancel_ind%TYPE, pos_transfer_id VSO_POS_TRANSFERS.pos_transfer_id%TYPE, date_created VSO_POS_TRANSFERS.date_created%TYPE, process_date_created VSO_POS_TRANSFERS.process_date_created%TYPE, ride_out_date VSO_POS_TRANSFERS.ride_out_date%TYPE, transfer_date VSO_POS_TRANSFERS.transfer_date%TYPE, user_in_date VSO_POS_TRANSFERS.user_in_date%TYPE, fkdomshipmentid VSO_POS_TRANSFERS.fkdomshipmentid%TYPE, rma_code VSO_POS_TRANSFERS.rma_code%TYPE, ref_dom_order_id VSO_POS_TRANSFERS.ref_dom_order_id%TYPE
);
TYPE vsoposdetails_aa IS TABLE OF vsoposdetails_rt INDEX BY PLS_INTEGER;
CURSOR cur_vso_pos_transfers IS
SELECT V.business_unit_id,
V.transfer_id, V.transfer_occurence_type, V.line_no, V.prgid,
V.transfer_type,
V.request_id,
V.from_site,
V.to_site
V.bar_code_bus_unit_id,
V.bar_code_sub_type,
V.bar_code_id,
V.style_id,
V.color_id,
V.dimension_id,
V.valid_dimension_id,
V.size_id,
V.valid_size_id,
V.item_qty,
V.Valid_ind,
V.status,
V.site_id,
V.valid_header,
V.valid_detail,
V.reason_id,
V.transfer_origin_name,
V.from_site_retail_value,
V.to_site_retail_value,
V.unit_cost,
V.expense_transfer_id,
V.rej_rpt_printed_ind,
V.reason_sub_type,
V.process_status,
V.process_date_time,
V.richter_version_id,
V.pos_version_id,
V.control_no,
V.from_business_unit,
V.to_business_unit,
V.cancel_ind,
V.pos_transfer_id,
V.date_created,
V.process_date_created,
V.ride_out_date,
V.transfer_date,
V.user_in_date,
V.fkdomshipmentid,
V.rma_code,
V.ref_dom_order_id
FROM vso_pos_transfer V
WHERE transfer_id= array() ;
--??? is the above correct or should I use a SELECT BULK COLLECT instead of a cursor
BEGIN
OPEN cur_vso_pos_transfers;
FETCH cur_vso_pos_transfers BULK COLLECT INTO vsoposdetails_aa;
EXIT WHEN vsoposdetails_aa.COUNT=0;
FOR idx IN vsoposdetails_aa.FIRST..LAST
LOOP
DBMS_OUTPUT.put_line(idx||' '||vsoposdetails_aa(idx);
END LOOP;
CLOSE cur_vso_pos_transfers;
END;