[ Oracle ERP ] AP Invoice PAID BY & PAID_DATE

AP Invoice Actual Payment

FORM: APXIWALL.FMB

VIEW: AP_PAYMENT_SCHEDULES_V

FUNCTION: AP_INVOICE_PAYMENTS_PKG

AP_INVOICE_PAYMENTS_PKG.GET_PAID_BY(AI.INVOICE_ID, APS.PAYMENT_NUM) PAID_BY,

       AP_INVOICE_PAYMENTS_PKG.GET_PAID_DATE(AI.INVOICE_ID, APS.PAYMENT_NUM) PAID_DATE,

       AP_INVOICE_PAYMENTS_PKG.GET_PAYMENT_ID(AI.INVOICE_ID,

                                              APS.PAYMENT_NUM) PAYMENT_ID,

       AP_INVOICE_PAYMENTS_PKG.GET_PAYMENT_TYPE(AI.INVOICE_ID,

                                                APS.PAYMENT_NUM) PAYMENT_TYPE,

[ Oracle ERP ] Upload Excel without WEBADI 複製Excel 到 Oracle ERP FORM

1) Copy Excel File 

2) Past in Form Text ( Multi-Line)

   Result : INTO Table

PROCEDURE UPLOAD( P01_UPLOAD VARCHAR2)
    IS
     V01_SQL VARCHAR2(20000);
     V02_SQL varchar2(4000);
     V03_sql varchar2(4000);
     l_tab   dbms_utility.uncl_array;
     l_tablen   number;
     v_sql_length number;
     
    cursor a_cur is
      select col01
        from jiamin_test2
       where col01 is not null;
        
   BEGIN
    V01_SQL := 'BEGIN insert into jiamin_test2(col01) values(''"' || replace(replace(P01_UPLOAD, chr(9), '","'), chr(10), '''); insert into jiamin_test2(col01) values(''') || '"''); END;';
     
     execute immediate V01_SQL;
     
     commit;
     
     for j in a_cur loop
      dbms_utility.comma_to_table(j.col01 , l_tablen, l_tab);
      dbms_output.put_line('TABLE LENGTH : '|| l_tablen);
      dbms_output.put_line('TABLE COUNT  : '|| l_tab.COUNT);
      for i in 1..l_tablen
      loop
        dbms_output.put_line(l_tab(i));
        V03_sql := V03_sql|| l_tab(i) || ''',''';

      end loop;
          v_sql_length := length(V03_sql);
          V02_SQL := 'begin insert into jiamin_test2(col01, col02, col03) values('''|| substr(V03_sql, 1, v_sql_length-2) || '); end;';
          execute immediate replace(V02_SQL, '"', '');
          
          V01_SQL := '';
          V02_SQL := '';
          V03_sql := '';
    end loop;
     commit;
     
   END UPLOAD;