Re: [GENERAL] Oracle to PostgreSQL

 CREATE OR REPLACE PROCEDURE "A"."Datastore" 

 (

   

 , In_Param1 IN VARCHAR2

 , In_Param2 IN VARCHAR2

 , In_Param3 IN VARCHAR2

 , Out_Param1 OUT VARCHAR2 

 , ERROR_MSG OUT VARCHAR2

 ) AS 

   TEMP_ERR_MSG VARCHAR2(4000);

   IS_FIRST CHAR(1);

   TYPE INV_LINES_RT IS RECORD(

         VENDOR_NUM A.Datastore.VENDOR_NUM%TYPE,

         VENDOR_SITE_CODE A.Datastore.VENDOR_SITE_CODE%TYPE,

         INVOICE_NUM A.Datastore.INVOICE_NUM%TYPE,

         TXN_CNT NUMBER

   );

   TYPE INV_LINES_T IS TABLE OF INV_LINES_RT;

   L_INV_LINES INV_LINES_T;

 IS_FIRST:='Y';

 WITH Distinct_Invoices As ( select DISTINCT VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM,DESCRIPTION,CURR_CODE,INVOICE_AMOUNT,IMAGE_NUMBER, VENDOR_NAME, PAYMENT_METHOD, GL_DATE, TERMS_NAME, RETURN_ID, PAYGROUP, INVOICE_TYPE, INVOICE_DATE from A.Datastore where CASE_ID = In_Param1 )

 Select VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM, count(*) as TXN_CNT  BULK COLLECT INTO L_INV_LINES from Distinct_Invoices group by VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM having count(*) > 1;

   IF  L_INV_LINES.COUNT>0 THEN

     

     ERROR_COUNT:=0;

       FOR indx in L_INV_LINES.first .. L_INV_LINES.last LOOP

         IF IS_FIRST!='Y' THEN

              TEMP_ERR_MSG  :=  TEMP_ERR_MSG || ', ';

         END IF;

         IS_FIRST:='N';

         TEMP_ERR_MSG  :=  TEMP_ERR_MSG || '''' || L_INV_LINES(indx).INVOICE_NUM || '''';