2 Before You Install the Recipe

Log in to your Oracle ATP database instance as an Administrator and perform the following tasks.

1. Perform general configuration tasks. See Prerequisites for Creating a Connection.
2. Create necessary objects.

   To create necessary objects, run the following script:

   CREATE TABLE "ADMIN"."SUPPLIERS" 
      (    "ID" NUMBER, 
       "SUPPLIER" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "SUPPLIER_NUMBER" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "TAX_ORGANIZATION_TYPE_CODE" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "TAX_ORGANIZATION_TYPE" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "SUPPLIER_TYPE_CODE" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "SUPPLIER_STATUS" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "BUSINESS_RELATIONSHIP_CODE" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "BUSINESS_RELATIONSHIP" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "TAX_REGISTRATION_COUNTRY" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "TAXPAYER_COUNTRY_CODE" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "TAXPAYER_COUNTRY" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "TAXPAYER_ID" VARCHAR2(200 BYTE) COLLATE "USING_NLS_COMP", 
       "CREATION_DATE" TIMESTAMP (6) DEFAULT ON NULL SYSDATE, 
       "UPDATION_DATE" TIMESTAMP (6) DEFAULT ON NULL SYSDATE
      )  DEFAULT COLLATION "USING_NLS_COMP" ;
   
   CREATE UNIQUE INDEX "ADMIN"."SUPPLIERS_PK" ON "ADMIN"."SUPPLIERS" ("ID") 
     ;
   
   ALTER TABLE "ADMIN"."SUPPLIERS" ADD CONSTRAINT "SUPPLIERS_PK" PRIMARY KEY ("ID")
     USING INDEX  ENABLE;
   
   ALTER TABLE "ADMIN"."SUPPLIERS" MODIFY ("CREATION_DATE" NOT NULL ENABLE);
   
   ALTER TABLE "ADMIN"."SUPPLIERS" MODIFY ("UPDATION_DATE" NOT NULL ENABLE);
   
   CREATE OR REPLACE EDITIONABLE TRIGGER "ADMIN"."SUPPLIERS_TRIGGER" 
   BEFORE INSERT ON SUPPLIERS
   FOR EACH ROW
       WHEN (new.ID IS NULL) BEGIN
     :new.ID := SUPPLIERS_SEQ.NEXTVAL;
   END;
   /
   ALTER TRIGGER "ADMIN"."SUPPLIERS_TRIGGER" ENABLE;
   
   CREATE OR REPLACE EDITIONABLE TRIGGER "ADMIN"."SUPPLIERS_UPDATE_DATE" BEFORE UPDATE ON SUPPLIERS 
   FOR EACH ROW
   BEGIN
    :NEW.UPDATION_DATE := SYSDATE;
   END;
   
   CREATE OR REPLACE EDITIONABLE PACKAGE "ADMIN"."XX_SUPPLIERS_PAGINATION_PKG" AS
       PROCEDURE GET_SUPPLIERS_PAGINATION_PROC (
           I_PAGE      IN NUMBER,
           I_LIMIT     IN NUMBER,
           I_PAGE_SIZE OUT NUMBER,
           P_DATA      OUT SYS_REFCURSOR
       );
   
   END XX_SUPPLIERS_PAGINATION_PKG;
   /
   
   CREATE OR REPLACE EDITIONABLE PACKAGE BODY "ADMIN"."XX_SUPPLIERS_PAGINATION_PKG" AS
   
       PROCEDURE GET_SUPPLIERS_PAGINATION_PROC (
           I_PAGE      IN NUMBER,
           I_LIMIT     IN NUMBER,
           I_PAGE_SIZE OUT NUMBER,
           P_DATA      OUT SYS_REFCURSOR
       ) AS
           COUNT1 NUMBER;
       BEGIN
           SELECT
               COUNT(*)
           INTO COUNT1
           FROM
               SUPPLIERS;
   
           I_PAGE_SIZE := CEIL(COUNT1 / I_LIMIT);
           OPEN P_DATA FOR SELECT
                                               *
                                           FROM
                                               SUPPLIERS
                           ORDER BY
                               ID
                           OFFSET NVL((I_PAGE - 1), 0) * I_LIMIT ROWS FETCH NEXT I_LIMIT ROWS ONLY;
   
       END;
   
   END XX_SUPPLIERS_PAGINATION_PKG;