Using ar_credit_memo_api_pub.create_request created credit transaction

CREATE OR REPLACE  PROCEDURE ARTCRAPI_TH_1(ERRBUF   OUT VARCHAR2,
                                             RETCODE  OUT VARCHAR2,
                                             p_org_id in number,
                                             p_trx_num_fr in varchar2,
                                             p_trx_num_to in varchar2) is
 

  CURSOR c_trx is
    select trx.trx_number,
           trx.customer_trx_id,
           nvl(sum(tl.extended_amount), 0) line_amt,
           (select nvl(sum(l.extended_amount), 0)
              from ra_customer_trx_lines_all l
             where l.customer_trx_id = trx.customer_trx_id
               and l.line_type = 'TAX') tax_amt
      from RA_CUSTOMER_TRX_all       trx,
           ra_customer_trx_lines_all tl,
           ra_cust_trx_types_all     typ,
           ra_batch_sources_all      rs
     where 1 = 1
       and trx.trx_number  between nvl(p_trx_num_fr,trx.trx_number) and nvl(p_trx_num_to,trx.trx_number)
       and trx.customer_trx_id = tl.customer_trx_id
       and rs.batch_source_id = trx.batch_source_id
       and typ.cust_trx_type_id = trx.cust_trx_type_id
       and typ.type != 'CM'
       and typ.name = 'INV(BY OE)-JIT'
       and tl.line_type = 'LINE'
       and trx.org_id = p_org_id
       and nvl(TRX.Attribute1,'N')!='Y' --prevent duplicate import
     group by trx.trx_number, trx.customer_trx_id;

  v_msg_count     number;
  v_msg_data      varchar2(2000);
  v_return_status varchar2(1);
  v_request_id    number;
  v_cm_trx_num    RA_CUSTOMER_TRX_all.Trx_Number%type;
begin

  fnd_global.apps_initialize(user_id=> fnd_profile.value('USER_ID'),
  resp_id           => fnd_profile.value('RESP_ID'),
  resp_appl_id      => fnd_profile.value('RESP_APPL_ID'),
  security_group_id => 0);   
  FOR CURSOR1 IN c_trx LOOP
    begin
      ar_credit_memo_api_pub.create_request(
                                            -- standard api parameters
                                            p_api_version   => 1.0,
                                            p_init_msg_list => FND_API.G_TRUE,
                                            p_commit        => FND_API.G_TRUE,
                                            x_return_status => v_return_status,
                                            x_msg_count     => v_msg_count,
                                            x_msg_data      => v_msg_data,
                                            -- credit memo request parameters
                                            p_customer_trx_id            => CURSOR1.customer_trx_id,
                                            p_line_credit_flag           => 'N',
                                            p_line_amount                => -CURSOR1.line_amt,
                                            p_tax_amount                 => -CURSOR1.tax_amt,
                                            p_cm_reason_code             => 'RETURN',
                                            p_skip_workflow_flag         => 'Y',
                                            p_batch_source_name          => 'EXPORT JIT HUB',
                                            p_credit_method_installments => null,
                                            p_credit_method_rules        => null,
                                            x_request_id                 => v_request_id);
      fnd_file.put_line(fnd_file.output,'API request id:' || v_request_id);
      if v_return_status = fnd_api.G_RET_STS_SUCCESS then
        --Show CM trx number --
        select trx.trx_number
          into v_cm_trx_num
          FROM ra_cm_requests r, RA_CUSTOMER_TRX_all trx
         WHERE r.request_id = v_request_id
           and r.CM_CUSTOMER_TRX_ID = trx.customer_trx_id
           and r.CUSTOMER_TRX_ID = CURSOR1.customer_trx_id;
         fnd_file.put_line(fnd_file.output,'AR invoice :'||CURSOR1.trx_number||' generated Credit Transaction Number :' ||
                             v_cm_trx_num);
         fnd_file.put_line(fnd_file.output,'v_msg_data success:' || v_msg_data);
     
        ---remarked the ar invoice was generated CM--    
        update RA_CUSTOMER_TRX_ALL TRX
           set TRX.Attribute1 = 'Y'
         WHERE 1 = 1
           and trx.org_id = p_org_id
           and trx.customer_trx_id = CURSOR1.customer_trx_id; -- 
        commit;
        ---remarked the ar invoice was generated CM--        
      end if;
   
      if v_return_status = fnd_api.g_ret_sts_unexp_error or
         v_return_status = fnd_api.g_ret_sts_error then
           fnd_file.put_line(fnd_file.output,'Exception or error:' || v_msg_data);
      end if;
   
      if v_msg_count > 1 then
        v_msg_count := 0;
        loop
          v_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
          if v_msg_data is null then
            exit;
          end if;
             fnd_file.put_line(fnd_file.output,'msg_data:' || v_msg_data);
        end loop;
      end if;
   
    exception
      when others then
           fnd_file.put_line(fnd_file.output,'Error in Create Credit Transaction:' ||
                             sqlerrm);
    end;
    ----------
 
  end loop;
end;
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9182041/viewspace-682149/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9182041/viewspace-682149/