利用請求状态来提示信息

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 512
DECLARE

P_BATCH_NO   number:='&1';   -- get it from sequence .

V_BATCH_NO   NUMBER;

LV_FUNC_STATUS boolean;
x_error_cnt number:=0;

cursor c_e
is
select v_batch_no, parent_table, parent_id, reject_lookup_code, creation_date  from ap_interface_rejections
where parent_id in
(select invoice_id from ap_invoices_interface b
where b.status='REJECTED'
and b.source='ACSS'
and b.group_id=to_char(v_batch_no)
union all
select invoice_line_id from ap_invoice_lines_interface a, ap_invoices_interface b
where b.status='REJECTED'
and b.source='ACSS'
and b.group_id=to_char(v_batch_no)
and a.invoice_id=b.invoice_id);

BEGIN

v_batch_no:=p_batch_no;

 DBMS_OUTPUT.PUT_LINE('KYINVERRZS11 started on '||to_char(sysdate, 'DD-MON-YYYY hh24:mi:ss'));
 DBMS_OUTPUT.PUT_LINE('Batch NO is '||V_BATCH_NO);

 
FOR r_e IN c_e
LOOP

     if x_error_cnt=0 then
      DBMS_OUTPUT.PUT_LINE('.');
      DBMS_OUTPUT.PUT_LINE('Batch / Table / Parent ID / Reject Lookup Code / Creation Date');
      DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------');
     end if;
     DBMS_OUTPUT.PUT_LINE(v_batch_no||' '||r_e.parent_table||' '||r_e.parent_id||' '||r_e.reject_lookup_code||' '
  ||to_char(r_e.creation_date,'YYYY-MM-DD HH24:MI:SS'));
  x_error_cnt:=x_error_cnt+1;

end loop;

     if x_error_cnt!=0 then
      DBMS_OUTPUT.PUT_LINE('AP Invoice Import is failed.');
      DBMS_OUTPUT.PUT_LINE('AP_INVOICES_INTERFACE status is reset to NULL for resubmit AP Invoice Import.');
      DBMS_OUTPUT.PUT_LINE('Check Interface tables AP_INVOICES_INTERFACE AP_INVOICE_LINES_INTERFACE AP_INTERFACE_REJECTIONS');
      DBMS_OUTPUT.PUT_LINE('.');
   Update ap_invoices_interface
     set status=NULL,
  request_id=NULL
  where status='REJECTED'
  and source='ACSS'
  and group_id=v_batch_no;
   commit;
      LV_FUNC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS ('ERROR',NULL);
   GOTO ENDPGM;
  else
      DBMS_OUTPUT.PUT_LINE('AP Invoice Import is successful.');
      DBMS_OUTPUT.PUT_LINE('.');
     end if;
 
-------------
 
<<ENDPGM>> 

 DBMS_OUTPUT.PUT_LINE('KYINVERRZS11 ended on '||to_char(sysdate, 'DD-MON-YYYY hh24:mi:ss'));
 
END;
/