Thursday, 25 August 2011

Before report trigger to show the parameter using if condition



FUNCTION BeforeReport
   RETURN BOOLEAN
IS
   item1      VARCHAR2 (250);
   item2      VARCHAR2 (250);
   org_name   VARCHAR2 (250);
   Supplier_name    varchar2(75);
BEGIN
   :from_receipt_date :=
      TO_CHAR (TO_DATE (:from_receipt_date, 'YYYY/MM/DD HH24:MI:SS'),
               'DD-MON-RRRR');
   :to_receipt_date :=
      TO_CHAR (TO_DATE (:to_receipt_date, 'YYYY/MM/DD HH24:MI:SS'),
               'DD-MON-RRRR');


   SELECT   organization_name
     INTO   org_name
     FROM   org_organization_definitions
    WHERE   organization_id = :ORGANIZATION_ID;

   srw.MESSAGE (2, 'org_name' || org_name);
   :CP_ORGANIZATION := org_name;
   srw.MESSAGE (3, ':CP_ORGANIZATION' || :CP_ORGANIZATION);
   :CP_FROM_RECEIPT_DATE := :FROM_RECEIPT_DATE;
   :CP_TO_RECEIPT_DATE := :TO_RECEIPT_DATE;

   BEGIN
      IF :FROM_MATERIAL_CODE IS NOT NULL
      THEN
         SELECT   segment1
           INTO   item1
           FROM   mtl_system_items_b
          WHERE   inventory_item_id = :FROM_MATERIAL_CODE;

         :CP_FROM_MATERIAL_CODE := item1;
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
   END;

   BEGIN
      IF :TO_MATERIAL_CODE IS NOT NULL
      THEN
         SELECT   segment1
           INTO   item2
           FROM   mtl_system_items_b
          WHERE   inventory_item_id = :TO_MATERIAL_CODE;
         :CP_TO_MATERIAL_CODE := item2;
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
   END;
Begin
     IF :VENDOR_NAME IS NOT NULL then
          SELECT   VENDOR_NAME
           INTO   Supplier_name
           FROM   po_vendors
          WHERE   vendor_id= :VENDOR_NAME;
   :CP_VENDOR_NAME := Supplier_name;
     END IF;
     end;
   RETURN (TRUE);
END;

No comments:

Post a Comment