AR invoices Import to GL interface

Insert  data to GL interface

CREATE OR REPLACE  PROCEDURE ARTRXIMP_TH_1(ERRBUF   OUT VARCHAR2,
                                             RETCODE  OUT VARCHAR2,
                                             p_org_id in number,
                                             p_sob_id in number,
                                             p_trx_date_fr     varchar2,
                                             p_trx_date_to     varchar2) is
  l_group_id         NUMBER;
  l_interface_run_id NUMBER;
  l_req_id           NUMBER;
  l_complete_flag    BOOLEAN;
  l_jv_source_name varchar2(50);
  v_trx_date_fr date;
  v_trx_date_to date;
  --
  l_phase      VARCHAR2(100);
  l_status     VARCHAR2(100);
  l_dev_phase  VARCHAR2(100);
  l_dev_status VARCHAR2(100);
  l_message    VARCHAR2(1000);

  Cursor ar_inv_cur (x_trx_date_fr date , x_trx_date_to date) is
   SELECT distinct trx.customer_trx_id invoice_id
      from RA_CUSTOMER_TRX_ALL TRX
     WHERE 1 = 1
       and trx.org_id =p_org_id
       and trx.set_of_books_id =p_sob_id
       and nvl(trx.attribute1,'N')='N'
       and trx.cust_trx_type_id= 3360--INV(BY OE)-EXTERNAL
       and trx.batch_source_id=2181--Order Entry - Import
       --and trx.trx_number in('110080002','110080001')
       and trunc(trx.trx_date) between nvl(trunc(x_trx_date_fr),trunc(trx.trx_date)) and
       nvl(trunc(x_trx_date_to),trunc(trx.trx_date));--
       
  --Dr records--
  Cursor ar_inv_dr_cur (x_inv_id number) is
    SELECT ar.TRX_NUMBER_DISPLAYED invoice_number,
           ar.CURRENCY_CODE,
           ar.set_of_books_id sob_id,
           ar.trx_date,
           --ar.ENTERED_DR,
           ar.ENTERED_CR ENTERED_DR,
           -- ar.ACCOUNTED_DR,
           ar.ACCOUNTED_CR ACCOUNTED_DR,
           ar.code_combination_id code_id,
           ar.CURRENCY_CONVERSION_DATE,
           -- ar.CURRENCY_CONVERSION_TYPE,
           ar.CURRENCY_USER_CONVERSION_TYPE,
           ar.CURRENCY_CONVERSION_RATE,
           ar.ACCOUNTING_DATE,
           (select p.period_name
              from GL_DATE_PERIOD_MAP p, gl_sets_of_books sob
             where sob.set_of_books_id = p_sob_id
               and sob.period_set_name = p.period_set_name -- 'Belton_13'
               and p.accounting_date = trunc(sysdate)/*ar.accounting_date*/) period_name,
           ar.GL_TRANSFER_STATUS
      from AR_AEL_SL_INV_V AR, RA_CUSTOMER_TRX_ALL TRX
     WHERE 1 = 1
       and ar.TRX_HDR_ID = trx.customer_trx_id
       and trx.org_id = ar.org_id
       and trx.set_of_books_id = ar.set_of_books_id
       and ar.set_of_books_id = p_sob_id
       and ar.org_id = p_org_id
       and ar.TRX_HDR_ID=x_inv_id
       and ar.ENTERED_DR is null;
  --Cr records--   
  Cursor ar_inv_cr_cur (x_inv_id number) is
    SELECT ar.TRX_NUMBER_DISPLAYED invoice_number,
           ar.CURRENCY_CODE,
           ar.set_of_books_id sob_id,
           ar.trx_date,
           ar.ENTERED_DR ENTERED_CR,
           --ar.ENTERED_CR  ,
           ar.ACCOUNTED_DR ACCOUNTED_CR,
           -- ar.ACCOUNTED_CR ,
           ar.code_combination_id code_id,
           ar.CURRENCY_CONVERSION_DATE,
          -- ar.CURRENCY_CONVERSION_TYPE,
           ar.CURRENCY_USER_CONVERSION_TYPE,
           ar.CURRENCY_CONVERSION_RATE,
           ar.ACCOUNTING_DATE,
           (select p.period_name
              from GL_DATE_PERIOD_MAP p, gl_sets_of_books sob
             where sob.set_of_books_id = p_sob_id
               and sob.period_set_name = p.period_set_name -- 'Belton_13'
               and p.accounting_date = trunc(sysdate)/*ar.accounting_date*/) period_name,
           ar.GL_TRANSFER_STATUS
      from AR_AEL_SL_INV_V AR, RA_CUSTOMER_TRX_ALL TRX
     WHERE 1 = 1
       and ar.TRX_HDR_ID = trx.customer_trx_id
       and trx.org_id = ar.org_id
       and trx.set_of_books_id = ar.set_of_books_id
       and ar.set_of_books_id = p_sob_id
       and ar.org_id = p_org_id
       and ar.TRX_HDR_ID=x_inv_id
       and ar.ENTERED_CR is null;

  ar_inv_dr_rec ar_inv_dr_cur%rowtype;
  ar_inv_cr_rec ar_inv_cr_cur%rowtype;
BEGIN
  v_trx_date_fr:=to_date(p_trx_date_fr,'YYYY/MM/DD HH24:MI:SS');
  v_trx_date_to:=to_date(p_trx_date_to,'YYYY/MM/DD HH24:MI:SS');
 
  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'), --GL
                             security_group_id => 0);
  select j.je_source_name into l_jv_source_name
  from GL_JE_SOURCES J
  where j.user_je_source_name='Interface'and j.language='US';
                         
  SELECT gl_interface_control_s.NEXTVAL INTO l_interface_run_id
  FROM dual;
                        
  for ar_inv_rec  in ar_inv_cur(v_trx_date_fr,v_trx_date_to) loop
   SELECT gl_journal_import_s.NEXTVAL INTO l_group_id FROM dual; 
  ---insert Accounting Dr records--
  open ar_inv_dr_cur(ar_inv_rec.invoice_id);
  loop
    fetch ar_inv_dr_cur
      into ar_inv_dr_rec;
    exit when ar_inv_dr_cur%NOTFOUND;
    ----
    INSERT INTO gl_interface
      (status,
       set_of_books_id,
       accounting_date,
       period_name,
       currency_code,
       date_created,
       created_by,
       actual_flag,
       user_je_category_name,
       user_je_source_name,
       currency_conversion_date,
       currency_conversion_rate,
       user_currency_conversion_type,
       entered_dr,
       entered_cr,
       ACCOUNTED_DR,
       ACCOUNTED_CR,
       code_combination_id,
       group_id,
       REFERENCE1, --Batch Name
       REFERENCE2, --Batch Description
       REFERENCE4, --Journal Entry Name
       REFERENCE5, --Journal Entry Description
       REFERENCE10 --Journal Entry Line Description
       )
    VALUES
      ('NEW',
       ar_inv_dr_rec.sob_id,
       trunc(sysdate),--ar_inv_dr_rec.accounting_date,
       ar_inv_dr_rec.period_name,
       ar_inv_dr_rec.currency_code,
       SYSDATE,
       fnd_profile.value('USER_ID'), --use id--
       'A',
       'Reverse',
       'Interface',
       ar_inv_dr_rec.CURRENCY_CONVERSION_DATE,
       ar_inv_dr_rec.CURRENCY_CONVERSION_RATE,
       ar_inv_dr_rec.CURRENCY_USER_CONVERSION_TYPE,
       ar_inv_dr_rec.ENTERED_DR, --Entered dr
       NULL, --Entered cr
       ar_inv_dr_rec.ACCOUNTED_DR, --Accounted Dr
       NULL, --Entered cr
       ar_inv_dr_rec.code_id, -- account code id
       l_group_id,
       'VAT Invoice Reversal,Period' || ar_inv_dr_rec.period_name, --Batch Name
       'VAT Invoice Reversal,Period' || ar_inv_dr_rec.period_name, --Batch Description
       'VAT Invoice Reversal' || ar_inv_dr_rec.invoice_number, --Journal Entry Name
       'AR Invoice Number:' || ar_inv_dr_rec.invoice_number, --Journal Entry Description
       'AR Invoice Number:' || ar_inv_dr_rec.invoice_number --Journal Entry Line Description
       );
  end loop;
  close ar_inv_dr_cur;
  --selfname+soure name+request id +Balance type+Group ID
  --'VAT Invoice Reversal,Period'||ar_inv_dr_rec.period_name +Manual+12081659: A 1241248

  /***---insert Accounting Cr records--***/
  open ar_inv_cr_cur(ar_inv_rec.invoice_id);
  loop
    fetch ar_inv_cr_cur
      into ar_inv_cr_rec;
    exit when ar_inv_cr_cur%NOTFOUND;
    --
    INSERT INTO gl_interface
      (status,
       set_of_books_id,
       accounting_date,
       period_name,
       currency_code,
       date_created,
       created_by,
       actual_flag,
       user_je_category_name,
       user_je_source_name,
       currency_conversion_date,
       currency_conversion_rate,
       user_currency_conversion_type,
       entered_dr,
       entered_cr,
       ACCOUNTED_DR,
       ACCOUNTED_CR,
       code_combination_id,
       group_id,
       REFERENCE1, --Batch Name
       REFERENCE2, --Batch Description
       REFERENCE4, --Journal Entry Name
       REFERENCE5, --Journal Entry Description
       REFERENCE10 --Journal Entry Line Description
       )
    VALUES
      ('NEW',
       ar_inv_cr_rec.sob_id,
       trunc(sysdate),--ar_inv_cr_rec.accounting_date,
       ar_inv_cr_rec.period_name,
       ar_inv_cr_rec.currency_code,
       SYSDATE,
       fnd_profile.value('USER_ID'),
       'A',
       'Reverse',
       'Interface',
       ar_inv_cr_rec.CURRENCY_CONVERSION_DATE,
       ar_inv_cr_rec.CURRENCY_CONVERSION_RATE,
       ar_inv_cr_rec.CURRENCY_USER_CONVERSION_TYPE,
       NULL, --entered dr
       ar_inv_cr_rec.ENTERED_CR, --entered cr
       NULL, --accounted dr
       ar_inv_cr_rec.ACCOUNTED_CR, --accounted cr
       ar_inv_cr_rec.code_id,
       l_group_id,
       'VAT Invoice Reversal,Period' || ar_inv_cr_rec.period_name, --Batch Name
       'VAT Invoice Reversal,Period' || ar_inv_cr_rec.period_name, --Batch Description
       'VAT Invoice Reversal' || ar_inv_cr_rec.invoice_number, --Journal Entry Name
       'AR Invoice Number:' || ar_inv_cr_rec.invoice_number, --Journal Entry Description
       'AR Invoice Number:' || ar_inv_cr_rec.invoice_number --Journal Entry Line Description
       );
  end loop;
  close ar_inv_cr_cur;

  INSERT INTO gl_interface_control
    (set_of_books_id,
     interface_run_id,
     je_source_name,
     group_id,
     status,
     packet_id)
  VALUES
    (p_sob_id, l_interface_run_id, l_jv_source_name, l_group_id, 'S', NULL);
 COMMIT;
 
 end loop ;
   
  l_req_id := fnd_request.submit_request(application => 'SQLGL',
                                         program     => 'GLLEZL',
                                         description => 'Journal Import',
                                         start_time  => NULL,
                                         sub_request => NULL,
                                         argument1   => l_interface_run_id,
                                         argument2   => p_sob_id, --sob id
                                         argument3   => 'N', --post_errors_to_suspense
                                         argument4   => NULL,
                                         argument5   => NULL,
                                         argument6   => 'N', --create_summary_journals
                                         argument7   => 'O');

  IF l_req_id <= 0 THEN
    fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
    fnd_message.set_token('PROCEDURE', 'PROCEDURE:BG_JVIMPORT_TH_1');
 
  ELSIF l_req_id > 0 THEN
    fnd_message.set_name('SQLGL', 'GL_CONC_REQUEST_SUBMITTED');
    fnd_message.set_token('REQUEST_ID', l_req_id);
    COMMIT;
    l_complete_flag := fnd_concurrent.wait_for_request(l_req_id,
                                                       1,
                                                       0,
                                                       l_phase,
                                                       l_status,
                                                       l_dev_phase,
                                                       l_dev_status,
                                                       l_message);
  END IF;
if l_complete_flag  then
  for ar_inv_rec  in ar_inv_cur(v_trx_date_fr,v_trx_date_to) loop
  update
  RA_CUSTOMER_TRX_ALL TRX
  set TRX.Attribute1='Y'
     WHERE 1 = 1
       and trx.org_id =p_org_id
       and trx.set_of_books_id =p_sob_id
       and trx.customer_trx_id=ar_inv_rec.invoice_id;--
  end loop;
  commit;
end if;

END;
 

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

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