SET SERVEROUTPUT ON SIZE 1000000SET LINESIZE 512DECLARE
P_BATCH_NO number:='&1'; -- get it from sequence .
V_BATCH_NO NUMBER;
LV_FUNC_STATUS boolean;x_error_cnt number:=0;
cursor c_eisselect v_batch_no, parent_table, parent_id, reject_lookup_code, creation_date from ap_interface_rejectionswhere parent_id in(select invoice_id from ap_invoices_interface bwhere b.status='REJECTED'and b.source='ACSS'and b.group_id=to_char(v_batch_no)union allselect invoice_line_id from ap_invoice_lines_interface a, ap_invoices_interface bwhere 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_eLOOP
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;/
