Monday, December 13, 2021

Oracle Payables AP Invoice Interface

 

DECLARE
   v_inv_type ap_invoices_interface.INVOICE_TYPE_lookup_code%TYPE;

v_invoice_date ap_invoices_interface.INVOICE_DATE%TYPE;

V_vendor_id ap_invoices_interface.vendor_id%TYPE;

v_vendor_site_id ap_invoices_interface.vendor_site_id%TYPE;

v_invoice_num ap_invoices_interface.INVOICE_NUM %TYPE;

v_invoice_curr ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE;

v_invoice_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;

v_payment_cross_rate ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE;

v_cross_payment_curr ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE;

v_pay_rate_date ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE;

v_payment_rate_type ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE;

v_description ap_invoices_interface.DESCRIPTION%TYPE;


v_rate_type ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE;

v_exchange_date ap_invoices_interface.EXCHANGE_DATE%TYPE;

v_exchange_rate ap_invoices_interface.EXCHANGE_RATE%TYPE;

v_terms_date ap_invoices_interface.TERMS_DATE%TYPE;

v_terms ap_invoices_interface.TERMS_ID%TYPE;

v_payment_method ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE;
v_discountable_amt ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE;
v_invoice_recieved_date ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE;
v_recieved_goods_date ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE;
v_pay_code_combination_id ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE;

v_inv_count number;
V_CODE_COMBINATION_ID NUMBER;
v_invoice_id NUMBER;
v_invoice_line_id NUMBER;
v_line_no NUMBER;
v_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;
v_accounting_date DATE;
v_line_type VARCHAR2(400);
V_LOOKUP_CODE VARCHAR2(400);
V_CURRENCY_CODE VARCHAR2(400);
V_INVOICE_NUMBER VARCHAR2(400);
V_concatenated_segment VARCHAR2(4000);
V_ERROR_MESSAGE VARCHAR2(4000);
V_ERROR_FLAG VARCHAR2(40);
cursor c_invoice IS

SELECT INVOICE_NUMBER,INVOICE_DATE INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY

FROM XX_INVOICES_STG

WHERE NVL(STATUS,'NEW') NOT IN ('PROCESSED')

GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY;

cursor c_inv_detls(c_inv_number varchar2,c_supplier_number varchar2) IS

SELECT INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY,LINE_NUMBER,LINE_AMOUNT,GL_ACCOUNT,ATTRIBUTE1,ATTRIBUTE2

FROM XX_INVOICES_STG

where INVOICE_NUMBER=c_inv_number
and SUPPLIER_NUMBER=c_supplier_number;


 begin
  
 for f_invoice in c_invoice loop
V_ERROR_MESSAGE:=NULL;
V_ERROR_FLAG:=NULL;
v_inv_type :=NULL;
v_invoice_date:=NULL;
v_invoice_num:=NULL;
v_invoice_curr:=NULL;
v_invoice_amount:=NULL;
v_description:=NULL;


BEGIN
  SELECT INVOICE_NUMBER into V_INVOICE_NUMBER
FROM XX_INVOICES_STG
WHERE INVOICE_NUMBER=f_invoice.invoice_number
GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY;


EXCEPTION
  WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Header Information is not Identical';
V_Error_flag:='Y';

END;
　
 BEGIN
  select LOOKUP_CODE INTO V_LOOKUP_CODE from fnd_lookup_values
where LOOKUP_CODE='STANDARD'
AND LOOKUP_TYPE='INVOICE TYPE';
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type';
V_Error_flag:='Y';

END;
  


　

　
 begin
  select VENDOR_ID into v_vendor_id from ap_suppliers
where segment1=f_invoice.SUPPLIER_NUMBER
and VENDOR_NAME=f_invoice.SUPPLIER_NAME;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Selection';
V_Error_flag:='Y';
END;

begin
  select VENDOR_ID into v_vendor_site_id from ap_supplier_SITES_ALL
where VENDOR_ID=v_vendor_id
and VENDOR_SITE_CODE=f_invoice.SUPPLIER_SITE;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Vendor Site is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Site Selection';
V_Error_flag:='Y';
END;
  
begin
  select CURRENCY_CODE into V_CURRENCY_CODE from fnd_currencies
where CURRENCY_CODE=f_invoice.INVOICE_CURRENCY;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Invoice Currency is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Currency Selection';
V_Error_flag:='Y';
END;
  
 for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop

begin
  select COUNT(*) into V_concatenated_segment from gl_code_combinations_kfv
where trim(concatenated_segments)=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'-'||f_inv_detls.GL_ACCOUNT||'-'||'Error: GL Code is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: GL Code Selection';
V_Error_flag:='Y';
END;
  end loop;



　
 --v_status:= cur_invoice.status;
   




-- SELECT COUNT(*)

-- INTO v_inv_count

-- FROM AP_INVOICES_ALL

-- WHERE  INVOICE_NUM = V_INVOICE_NUM

-- AND VENDOR_ID     = v_vendor_id;

--

--IF  v_count > 0  THEN

--   FND_FILE.PUT_LINE(FND_FILE.LOG,' Invocie already Exists - '||v_invoice_num);

--end if;
   




--inserting validated values to interface table(headers)='N'
   
if NVL(V_Error_flag,'N')='N' THEN

SELECT ap_invoices_interface_s.nextval INTO v_invoice_id FROM dual;

v_inv_type := f_invoice.INVOICE_TYPE;

v_invoice_date:=to_DATE(f_invoice.INVOICE_DATE,'MM/DD/YYYY');

v_invoice_num:= f_invoice.invoice_number;

v_invoice_curr:= f_invoice.INVOICE_CURRENCY;

v_invoice_amount:= f_invoice.invoice_amount;
v_description:= f_invoice.description;
INSERT INTO ap_invoices_interface (
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
description,
status,
source,
creation_date,
created_by,
last_update_date,
last_updated_by
 )
VALUES
(
  v_invoice_id,
v_invoice_num,
v_inv_type,
v_invoice_date,
v_vendor_id,
v_vendor_site_id,
nvl(v_invoice_amount,0),
v_invoice_curr,
v_description,
'NEW',
'TEST SOURCE',
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
  
for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop
select ap_invoice_lines_interface_s.nextval into v_invoice_line_id from dual;
v_line_no:=f_inv_detls.line_NUMBER;
v_amount:=f_inv_detls.line_amount;
v_accounting_date:=sysdate;
v_line_type:='Item';--(Item, Fright,Tax,Miscellaneous)
select CODE_COMBINATION_ID into V_CODE_COMBINATION_ID from gl_code_combinations_kfv
where concatenated_segments=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));



　
 -- BEGIN

--        SELECT 'Y'

--        INTO   v_found

--        FROM   gl_period_statuses

--        WHERE  application_id  = 200--ASSUMTION

--        AND    set_of_books_id = 3--ASSUMTION

--        AND    SYSDATE between start_date AND end_date

--        AND    closing_status IN ('O', 'F')

-- AND    NVL(adjustment_period_flag, 'N') = 'N'

--        AND    ROWNUM = 1;

-- EXCEPTION

--  WHEN NO_DATA_FOUND THEN

--             FND_FILE.PUT_LINE(FND_FILE.log,'Accounting Date Is Not In Open');

--    END;

/*------start to Validate Line Level for po_number matching/validating----------*/
    -- get the info of header (if invoice is related to po_number)





/*------end to Validate Line Level for po_number matching/validating----------*/
   /*start to inserting invoice line*/

INSERT INTO ap_invoice_lines_interface (
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
dist_code_combination_id,
creation_date,
created_by,
last_update_date,
last_updated_by
  )
VALUES(v_invoice_id,
v_invoice_line_id,
v_line_no,
v_line_type,
v_amount,
v_accounting_date,
V_CODE_COMBINATION_ID,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
end loop;
UPDATE APPS.XX_INVOICES_STG
SET STATUS='PROCESSED',
ERROR_MESSAGE=V_ERROR_MESSAGE
WHERE invoice_number=f_invoice.invoice_number
AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;
ELSE
UPDATE APPS.XX_INVOICES_STG
SET STATUS='ERROR',
ERROR_MESSAGE=V_ERROR_MESSAGE
WHERE invoice_number=f_invoice.invoice_number
AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;
END IF;
COMMIT;
END LOOP;




END;

/  DECLARE
   v_inv_type ap_invoices_interface.INVOICE_TYPE_lookup_code%TYPE;
v_invoice_date ap_invoices_interface.INVOICE_DATE%TYPE;
V_vendor_id ap_invoices_interface.vendor_id%TYPE;
v_vendor_site_id ap_invoices_interface.vendor_site_id%TYPE;
v_invoice_num ap_invoices_interface.INVOICE_NUM %TYPE;
v_invoice_curr ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE;
v_invoice_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;
v_payment_cross_rate ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE;
v_cross_payment_curr ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE;
v_pay_rate_date ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE;
v_payment_rate_type ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE;
v_description ap_invoices_interface.DESCRIPTION%TYPE;
v_rate_type ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE;

v_exchange_date ap_invoices_interface.EXCHANGE_DATE%TYPE;
v_exchange_rate ap_invoices_interface.EXCHANGE_RATE%TYPE;
v_terms_date ap_invoices_interface.TERMS_DATE%TYPE;
v_terms ap_invoices_interface.TERMS_ID%TYPE;
v_payment_method ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE;
v_discountable_amt ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE;
v_invoice_recieved_date ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE;
v_recieved_goods_date ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE;
v_pay_code_combination_id ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE;
v_inv_count number;
V_CODE_COMBINATION_ID NUMBER;
v_invoice_id NUMBER;
v_invoice_line_id NUMBER;
v_line_no NUMBER;
v_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;
v_accounting_date DATE;
v_line_type VARCHAR2(400);
V_LOOKUP_CODE VARCHAR2(400);
V_CURRENCY_CODE VARCHAR2(400);
V_INVOICE_NUMBER VARCHAR2(400);
V_concatenated_segment VARCHAR2(4000);
V_ERROR_MESSAGE VARCHAR2(4000);
V_ERROR_FLAG VARCHAR2(40);

cursor c_invoice IS

SELECT INVOICE_NUMBER,INVOICE_DATE INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME,SUPPLIER_SITE,INVOICE_CURRENCY
FROM XX_INVOICES_STG
WHERE NVL(STATUS,'NEW') NOT IN ('PROCESSED')
GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY;

cursor c_inv_detls(c_inv_number varchar2,c_supplier_number varchar2) IS

SELECT INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY,LINE_NUMBER,LINE_AMOUNT,GL_ACCOUNT,ATTRIBUTE1,ATTRIBUTE2
FROM XX_INVOICES_STG
where INVOICE_NUMBER=c_inv_number
and SUPPLIER_NUMBER=c_supplier_number;






 begin
  for f_invoice in c_invoice loop
V_ERROR_MESSAGE:=NULL;
V_ERROR_FLAG:=NULL;
v_inv_type :=NULL;
v_invoice_date:=NULL;
v_invoice_num:=NULL;
v_invoice_curr:=NULL;
v_invoice_amount:=NULL;
v_description:=NULL;

BEGIN
  SELECT INVOICE_NUMBER into V_INVOICE_NUMBER
FROM XX_INVOICES_STG
WHERE INVOICE_NUMBER=f_invoice.invoice_number
GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY;

EXCEPTION
  WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Header Information is not Identical';
V_Error_flag:='Y';
END;
  

 BEGIN
  select LOOKUP_CODE INTO V_LOOKUP_CODE from fnd_lookup_values
where LOOKUP_CODE='STANDARD'
AND LOOKUP_TYPE='INVOICE TYPE';
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type';
V_Error_flag:='Y';
END;
  

 begin
 select VENDOR_ID into v_vendor_id from ap_suppliers
where segment1=f_invoice.SUPPLIER_NUMBER
and VENDOR_NAME=f_invoice.SUPPLIER_NAME;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor is Not Valid';

V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Selection';
V_Error_flag:='Y';
END;
  

begin
  select VENDOR_ID into v_vendor_site_id from ap_supplier_SITES_ALL
where VENDOR_ID=v_vendor_id
and VENDOR_SITE_CODE=f_invoice.SUPPLIER_SITE;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Vendor Site is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Site Selection';
V_Error_flag:='Y';
END;
  
begin
  select CURRENCY_CODE into V_CURRENCY_CODE from fnd_currencies
where CURRENCY_CODE=f_invoice.INVOICE_CURRENCY;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Invoice Currency is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Currency Selection';
V_Error_flag:='Y';
END;
  
 for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop

begin
  select COUNT(*) into V_concatenated_segment from gl_code_combinations_kfv
where trim(concatenated_segments)=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'-'||f_inv_detls.GL_ACCOUNT||'-'||'Error: GL Code is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: GL Code Selection';

V_Error_flag:='Y';




END;
  end loop;



　
 --v_status:= cur_invoice.status;
   




-- SELECT COUNT(*)

-- INTO v_inv_count

-- FROM AP_INVOICES_ALL

-- WHERE  INVOICE_NUM = V_INVOICE_NUM

-- AND VENDOR_ID     = v_vendor_id;

--

--IF  v_count > 0  THEN

--   FND_FILE.PUT_LINE(FND_FILE.LOG,' Invocie already Exists - '||v_invoice_num);

--end if;
   




--inserting validated values to interface table(headers)='N'
   
if NVL(V_Error_flag,'N')='N' THEN

SELECT ap_invoices_interface_s.nextval INTO v_invoice_id FROM dual;

v_inv_type := f_invoice.INVOICE_TYPE;

v_invoice_date:=to_DATE(f_invoice.INVOICE_DATE,'MM/DD/YYYY');

v_invoice_num:= f_invoice.invoice_number;

v_invoice_curr:= f_invoice.INVOICE_CURRENCY;

v_invoice_amount:= f_invoice.invoice_amount;

v_description:= f_invoice.description;

INSERT INTO ap_invoices_interface (

invoice_id,

invoice_num,

invoice_type_lookup_code,

invoice_date,

vendor_id,

vendor_site_id,

invoice_amount,

invoice_currency_code,

description,

status,

source,

creation_date,

created_by,

last_update_date,



last_updated_by
 )

VALUES

(
   v_invoice_id,

v_invoice_num,

v_inv_type,

v_invoice_date,

v_vendor_id,

v_vendor_site_id,

nvl(v_invoice_amount,0),

v_invoice_curr,

v_description,

'NEW',

'TEST SOURCE',

sysdate,

FND_GLOBAL.user_id,

sysdate,

FND_GLOBAL.user_id




);
  
for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop

select ap_invoice_lines_interface_s.nextval into v_invoice_line_id from dual;

v_line_no:=f_inv_detls.line_NUMBER;

v_amount:=f_inv_detls.line_amount;

v_accounting_date:=sysdate;

v_line_type:='Item';--(Item, Fright,Tax,Miscellaneous)

select CODE_COMBINATION_ID into V_CODE_COMBINATION_ID from gl_code_combinations_kfv

where concatenated_segments=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));



　
 -- BEGIN

--        SELECT 'Y'

--        INTO   v_found

--        FROM   gl_period_statuses

--        WHERE  application_id  = 200--ASSUMTION

--        AND    set_of_books_id = 3--ASSUMTION

--        AND    SYSDATE between start_date AND end_date

--        AND    closing_status IN ('O', 'F')

-- AND    NVL(adjustment_period_flag, 'N') = 'N'

--        AND    ROWNUM = 1;

-- EXCEPTION

--  WHEN NO_DATA_FOUND THEN

--             FND_FILE.PUT_LINE(FND_FILE.log,'Accounting Date Is Not In Open');

--    END;

/*------start to Validate Line Level for po_number matching/validating----------*/
    -- get the info of header (if invoice is related to po_number)





/*------end to Validate Line Level for po_number matching/validating----------*/
   /*start to inserting invoice line*/

INSERT INTO ap_invoice_lines_interface (

invoice_id,

invoice_line_id,

line_number,

line_type_lookup_code,

amount,

accounting_date,

dist_code_combination_id,

creation_date,

created_by,

last_update_date,



last_updated_by
  )

VALUES(v_invoice_id,

v_invoice_line_id,

v_line_no,

v_line_type,

v_amount,

v_accounting_date,

V_CODE_COMBINATION_ID,

sysdate,

FND_GLOBAL.user_id,

sysdate,

FND_GLOBAL.user_id

);

end loop;

UPDATE APPS.XX_INVOICES_STG

SET STATUS='PROCESSED',

ERROR_MESSAGE=V_ERROR_MESSAGE

WHERE invoice_number=f_invoice.invoice_number

AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;

ELSE

UPDATE APPS.XX_INVOICES_STG

SET STATUS='ERROR',

ERROR_MESSAGE=V_ERROR_MESSAGE

WHERE invoice_number=f_invoice.invoice_number

AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;

END IF;

COMMIT;

END LOOP;




END;

/


After Inserting Invoices in Oracle Interface you have need to run 'Payables Open Interface' to import Invoices in Oracle Invoice Base Tabes   

 

-

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

SQL Important Queries

  How to delete rows with no where clause The following example deletes  all rows  from the  Person.Person  the table in the AdventureWork...