Oracle eBS經驗分享

set serveroutput on size 1000000;
declare
   l_msg_data VARCHAR2 (1000);
   l_msg_count NUMBER;
   l_return_status VARCHAR2 (100);
   l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
   cursor a is
       SELECT EXT_BANK_ACCOUNT_ID,
       START_DATE,
       end_date,
       object_version_number
       FROM IBY_EXT_BANK_ACCOUNTS
       WHERE end_date is null
       and ext_bank_account_id IN (
           SELECT DISTINCT a.instrument_id
           FROM iby_pmt_instr_uses_all a
           WHERE a.payment_flow    = 'DISBURSEMENTS'
           AND a.instrument_type   = 'BANKACCOUNT'
           AND a.EXT_PMT_PARTY_ID IN (
               SELECT EXT_PAYEE_ID
               FROM IBY_EXTERNAL_PAYEES_ALL
               WHERE (payee_party_id = (
                   SELECT a.party_id
                   FROM ap_suppliers a
                   WHERE a.vendor_id = &vendor_id)
               AND (party_site_id IS NULL
               OR party_site_id IN (
                   SELECT b.party_site_id
                   FROM ap_supplier_sites_all b
                   WHERE b.vendor_id         = &vendor_id
                   AND (b. vendor_site_id    = NVL( 0, vendor_site_id) OR 0 = 0 ))))))
       ORDER BY ext_bank_account_id ;
BEGIN
   FND_MSG_PUB.initialize;
   for x in a loop
       dbms_output.put('processing account id: ' || x.EXT_BANK_ACCOUNT_ID);
       iby_ext_bankacct_pub.set_ext_bank_acct_dates (
             p_api_version => 1.0
           , p_init_msg_list => FND_API.G_TRUE
           , p_acct_id        => x.EXT_BANK_ACCOUNT_ID
           , p_start_date => nvl(x.start_date,sysdate)
           , p_end_date     => sysdate
           , p_object_version_number  => x.object_version_number
           , x_return_status => l_return_status
           , x_msg_count => l_msg_count
           , x_msg_data => l_msg_data
           , x_response => l_response );
     
      dbms_output.put_line(' --- ' || l_return_status || ' --- ' || l_msg_count || ' --- ' || l_msg_data);
       IF l_msg_count >1 THEN
           FOR I IN 1..l_msg_count LOOP
               dbms_output.put_line(' ' || I || '. ' || SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
           END LOOP;
       END IF;
       if (l_return_status = 'S') then
           dbms_output.put_line('Success');
           commit;
       else
           rollback;
       end if;
   end loop;
end;
/

, p_end_date => sysdate 

, p_end_date => TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss');