CREATE OR REPLACE PACKAGE BODY OPT_OWNER.test_pkg AS PROCEDURE get_payment_details(p_get_pay_typ IN opt_get_pay_type) IS V_PAYMENT_ID VARCHAR2(2500); --l_payment_id opp_payment.payment_id%TYPE; BEGIN FOR i IN p_get_pay_typ.FIRST..p_get_pay_typ.LAST LOOP /* Loop for iteration of payment ids in the given list */ V_PAYMENT_ID := V_PAYMENT_ID || ',' ||p_get_pay_typ(i); END LOOP; V_PAYMENT_ID := SUBSTR(V_PAYMENT_ID, 2, LENGTH(V_PAYMENT_ID)); EXECUTE IMMEDIATE 'INSERT INTO opt_payment_global SELECT pmt.payment_id , pmt.PAYMENT_TYPE , pmt.mutation_id , pmt.payment_status , pmt.payment_details , pmt.payment_reference , pmt.customer_reference , pmt.ben_account_number , pmt.ben_account_number_type , pmt.transfer_ccy , pmt.transfer_amount , pmt.transfer_date , pmt.charges_for , pmt.model_ind , pmt.set_id , pmt.internal_transfer , pmt.domestic , pmt.intra_company , pmt.bbi , pmt.fx_contract_nr , pmt.fx_contract_date , pmt.fx_rate , pmt.import_status , pmt.payment_template , pmt.model_reference , pmt.payment_category , pmt.value_added_tax_currency , pmt.value_added_tax_amount , pmt.message_details , pmt.message_type , pmt.accgiro_reference , pmt.tax_input_type , pmt.tax_decl_code , pmt.tax_amount , pmt.tax_file_name , pmt.tax_file , pmt.dirdeb_id , pmt.nr_of_notes , pmt.send_cheque_to , pmt.cheque_printing_branch , pmt.cheque_cruzado_type , pmt.cheque_transfer_date_type , pmt.cheque_receive_mode , pmt.cheque_collection_branch , pmt.sal_ind , pmt.contract_no , pmt.batch_id , pmt.cheque_type_mx , pmt.cheque_nr , cacu.external_decimal_number , cacu.internal_decimal_number , uabh.account_id , uabh.account_code , uabh.account_currency , uabh.account_number , uabh.account_alias_id , ps.mutation_id payment_set_mutation_id , ps.payment_set_name , ps.payment_set_status , uabh.account_resident , pp.party_type , pp.party_code , pp.party_name , pp.party_address_1 , pp.party_address_2 , pp.party_city , pp.party_postal_code , pp.party_country , pp.party_swift_id , pp.party_clearing_system , pp.party_bank_sorting_code , pp.party_id , pp.party_resident_ind , pp.party_status , pp.party_own_account_ind , pp.party_mutation_id , pp.party_tax_id , pp.party_id_type , pp.party_id_number , pp.party_party_id , pp.party_province , invd.invoice_detail , invd.ben_notification_method , invd.ben_email1 , invd.ben_email2 , invd.ben_fax1 , invd.ben_fax2 , invd.ben_contact_name , invd.document_detail , addi.add_detail1 , addi.add_detail2 , addi.add_detail3 , addi.add_detail4 , rete.retention_detail , pmt.sender_charges_ccy , pmt.sender_charges , pmt.receiver_charges_ccy , pmt.receiver_charges , pmt.ordered_amount_ccy , pmt.ordered_amount , pmt.bank_operation_code , pmt.transaction_type_code , pmt.priority , pmt.mt103_plus_ind , pmt.value_date , pmt.related_reference , pmt.isp_ind , pmt.payment_location , pmt.payment_date , pmt.form_type , pmt.payment_id_form , pp.party_id_type_number , pp.party_account_number_type , pp.party_account_number , pp.party_bank_name , pp.party_bank_address , pp.party_bank_city , pmt.reference_indicator , pmt.reference_number , pmt.reference_text , pmt.reference_number_text , pmt.ben_ref_num , pmt.ben_ref , pmt.ben_ref_num_text , pmt.ben_ref_text , pp.party_layout , pp.party_post_number FROM opp_payment pmt , opp_nua_cache_currency cacu ,(SELECT DISTINCT account_id , account_code , account_number , account_alias_id , account_currency , account_resident , account_domain_id FROM opp_user_acc_bnk_hld ) uabh , opp_payment_set ps , opp_payment_party pp , opp_invoice_detail invd , opp_additional_detail addi , opp_retention_detail rete WHERE pmt.payment_id IN ('||V_PAYMENT_ID||') AND pmt.account_id = uabh.account_id AND pmt.domain_id = uabh.account_domain_id AND pmt.set_id = ps.payment_set_id(+) AND pmt.transfer_ccy = cacu.iso_currency_code (+) AND pmt.payment_id = pp.payment_id AND pmt.payment_id = invd.payment_id (+) AND pmt.payment_id = addi.payment_id (+) AND pmt.payment_id = rete.payment_id (+)'; EXCEPTION WHEN OTHERS THEN RAISE; END get_payment_details; END test_pkg; /