Wrong Batch Complition Robotic Mail can fire Automatically:

Auto Robotic Mail can be fired out if any wrong Batch Completed by OPM Production like below:

 

 

 

 -- create a table :  CREATE TABLE BI.MAIL_SENDS
(
  MAIL_OID         NUMBER(10)                   NOT NULL,
  MAIL_DATE        DATE                         DEFAULT sysdate,
  MAIL_TO          VARCHAR2(3255 BYTE)          NOT NULL,
  MAIL_CC          VARCHAR2(4000 BYTE),
  MAIL_BCC         VARCHAR2(255 BYTE),
  MAIL_SUBJT       VARCHAR2(2000 BYTE),
  MAIL_BODYHTML    CLOB                         NOT NULL,
  MAIL_SENDTIME    VARCHAR2(50 BYTE),
  MAIL_REFERENCE   VARCHAR2(50 BYTE),
  MAIL_STATUS      VARCHAR2(50 BYTE)            DEFAULT 'N'                   NOT NULL,
  MAIL_CANCEL      VARCHAR2(64 BYTE)            DEFAULT 'N'                   NOT NULL,
  MAIL_ELOG        VARCHAR2(50 BYTE),
  IUSER            VARCHAR2(30 BYTE)            DEFAULT user,
  IDATE            DATE                         DEFAULT sysdate,
  EUSER            VARCHAR2(30 BYTE)            DEFAULT user,
  EDATE            DATE                         DEFAULT sysdate,
  UDT              DATE                         DEFAULT sysdate,
  VER              NUMBER(5)                    DEFAULT 1,
  MAIL_HEADER      CLOB,
  MAIL_FOOTER      CLOB,
  MAIL_BODYHTML2   CLOB,
  MAIL_BODYHTML3   CLOB,
  MAIL_BODYHTML4   CLOB,
  MAIL_BODYHTML5   CLOB,
  MAIL_BODYHTML6   CLOB,
  MAIL_BODYHTML7   CLOB,
  MAIL_BODYHTML8   CLOB,
  MAIL_BODYHTML9   CLOB,
  MAIL_BODYHTML10  CLOB
)

 

 

create a PROCEDURE named bi.mpfl_mail_batch_dtl

----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE bi.mpfl_mail_batch_dtl (paramtype VARCHAR2)
AS
   l_verify_flag     CHAR (1);
   l_org_id          hr_operating_units.organization_id%TYPE;
   l_error_message   VARCHAR2 (32000);
   owner_id          VARCHAR2 (100);
   mailform          VARCHAR2 (2000);
   ccmail            VARCHAR2 (3000);
   tomail            VARCHAR2 (3000);
   body_text         CLOB;
   body_text3        CLOB;
   slsq              NUMBER;
   temp_var          NUMBER;

   CURSOR h_invoice
   IS
      SELECT DISTINCT locations || '_PF' locations, org_code, org_name,
                      org_id, batch_no, batch_id
                 FROM (SELECT   *
                           FROM (SELECT SUBSTR
                                              (o.organization_name,
                                               1,
                                               3
                                              ) locations,
                                        bb.line_type,
                                        o.organization_id org_id,
                                        o.organization_code org_code,
                                        bh.batch_id, bh.batch_no,
                                        o.organization_name org_name, line_no,
                                        DECODE (bh.batch_status,
                                                -1, 'Cancelled',
                                                1, 'Pending',
                                                2, 'WIP',
                                                3, 'Completed',
                                                4, 'Closed'
                                               ) batch_status,
                                        CASE
                                           WHEN bb.line_type = 1
                                           AND line_no = 1
                                              THEN 'FG-Product'
                                           WHEN bb.line_type = 1
                                           AND line_no <> 1
                                              THEN 'Co-Product'
                                           WHEN bb.line_type = 2
                                              THEN 'By-Product'
                                           ELSE ''
                                        END item_type,
                                        msi.segment1 item_code,
                                        msi.description item_name,
                                        bb.plan_qty, bb.wip_plan_qty,
                                        bb.actual_qty, bb.dtl_um,
                                        NVL (bb.cost_alloc, 0.00) cost_alloc,
                                        NVL (bb.original_qty, 0) original_qty,
                                        SUM (NVL (bb.cost_alloc, 0.00)
                                            ) OVER (PARTITION BY o.organization_code, bh.batch_no)
                                                                   tcostalloc,
                                        DECODE
                                           (SUM (NVL (bb.cost_alloc, 0.00)) OVER (PARTITION BY o.organization_code, bh.batch_no),
                                            1, 'OKAY',
                                            'NOT'
                                           ) "DESC2",
                                        ROUND
                                           (    bb.actual_qty
                                              / (CASE
                                                    WHEN NVL (bb.original_qty,
                                                              0
                                                             ) = 0
                                                       THEN 1
                                                    ELSE bb.original_qty
                                                 END
                                                )
                                            - 1,
                                            3
                                           ) v_percent,
                                        bh.plan_start_date,
                                        bh.actual_start_date,
                                        bh.actual_cmplt_date
                                   FROM gme_batch_header@prod_db_link bh,
                                        gme_material_details@prod_db_link bb,
                                        org_organization_definitions@prod_db_link o,
                                        mtl_system_items_b@prod_db_link msi,
                                        hr_operating_units@prod_db_link ou
                                  WHERE bh.organization_id =
                                                            bb.organization_id
                                    AND bh.batch_id = bb.batch_id
                                    AND o.operating_unit = ou.organization_id
                                    AND bh.organization_id = o.organization_id
                                    AND bh.organization_id =
                                                           msi.organization_id
                                    AND bb.inventory_item_id =
                                                         msi.inventory_item_id
                                    AND bh.batch_status IN (3, 4)
                                    AND (   bb.actual_qty <> 0
                                         OR bb.original_qty <> 0
                                        )
                                    AND TO_DATE (bh.actual_cmplt_date,
                                                 'dd-mm-yy'
                                                ) =
                                             TO_DATE (SYSDATE - 1, 'dd-mm-yy')) hira
                          WHERE (   (   hira.v_percent >= .1
                                     OR hira.v_percent <= -1
                                    )
                                 OR "DESC2" <> 'OKAY'
                                )
                       ORDER BY org_code);

   CURSOR l_invoice (p_org_id VARCHAR2, p_batch_id VARCHAR2)
   IS
      SELECT   bb.line_type, o.organization_code org_code, bh.batch_no,
               o.organization_name org_name, line_no,
               DECODE (bh.batch_status,
                       -1, 'Cancelled',
                       1, 'Pending',
                       2, 'WIP',
                       3, 'Completed',
                       4, 'Closed'
                      ) batch_status,
               CASE
                  WHEN bb.line_type = 1 AND line_no = 1
                     THEN 'FG-Product'
                  WHEN bb.line_type = 1 AND line_no <> 1
                     THEN 'Co-Product'
                  WHEN bb.line_type = 2
                     THEN 'By-Product'
                  ELSE ''
               END item_type,
               msi.segment1 item_code, msi.description item_name, bb.plan_qty,
               bb.wip_plan_qty, bb.actual_qty, bb.dtl_um,
               NVL (bb.cost_alloc, 0.00) cost_alloc, bb.original_qty,
               (NVL (bb.actual_qty, 0) - NVL (bb.original_qty, 0)) yield_var,
               SUM (NVL (bb.cost_alloc, 0.00)) OVER (PARTITION BY o.organization_code, bh.batch_no)
                                                                   tcostalloc,
               DECODE
                  (SUM (NVL (bb.cost_alloc, 0.00)) OVER (PARTITION BY o.organization_code, bh.batch_no),
                   1, 'OKAY',
                   'NOT'
                  ) "DESC2",
               bh.plan_start_date, bh.actual_start_date, bh.actual_cmplt_date
          FROM gme_batch_header@prod_db_link bh,
               gme_material_details@prod_db_link bb,
               org_organization_definitions@prod_db_link o,
               mtl_system_items_b@prod_db_link msi,
               hr_operating_units@prod_db_link ou
         WHERE bh.organization_id = bb.organization_id
           AND bh.batch_id = bb.batch_id
           AND o.operating_unit = ou.organization_id
           AND bh.organization_id = o.organization_id
           AND bh.organization_id = msi.organization_id
           AND bb.inventory_item_id = msi.inventory_item_id
           AND bh.batch_status IN (3, 4)
           AND bb.line_type <> -1
           AND bb.organization_id = p_org_id
           AND bb.batch_id = p_batch_id
      ORDER BY o.organization_code, bh.batch_no, line_no, msi.segment1;

   CURSOR d_invoice (p_org_id VARCHAR2, p_batch_id VARCHAR2)
   IS
      SELECT   o.organization_code org_code, bh.batch_no,
               o.organization_name org_name, line_no,
               DECODE (bb.line_type,
                       -1, 'Ingredient',
                       1, 'FGProduct',
                       2, 'ByProduct',
                       NULL
                      ) item_type,
               msi.segment1 item_code, msi.description item_name,
               bb.original_qty, bb.plan_qty, bb.wip_plan_qty, bb.actual_qty,
               bb.dtl_um,
               ROUND (NVL (bb.actual_qty, 0) - NVL (bb.original_qty, 0),
                      3
                     ) qty_variance,
               ROUND (    bb.actual_qty
                        / (CASE
                              WHEN NVL (bb.original_qty, 0) = 0
                                 THEN 1
                              ELSE bb.original_qty
                           END
                          )
                      - 1,
                      3
                     ) v_percent,
               CASE
                  WHEN bb.formulaline_id IS NOT NULL
                     THEN 'FormulaItem'
                  ELSE 'ExtraAdded'
               END formula
          FROM gme_batch_header@prod_db_link bh,
               gme_material_details@prod_db_link bb,
               org_organization_definitions@prod_db_link o,
               mtl_system_items_b@prod_db_link msi,
               hr_operating_units@prod_db_link ou
         WHERE bh.organization_id = bb.organization_id
           AND bh.batch_id = bb.batch_id
           AND o.operating_unit = ou.organization_id
           AND bh.organization_id = o.organization_id
           AND bh.organization_id = msi.organization_id
           AND bb.inventory_item_id = msi.inventory_item_id
           AND bh.batch_status IN (3, 4)
           AND bb.line_type = -1
           AND bb.organization_id = p_org_id
           AND bb.batch_id = p_batch_id
      ORDER BY o.organization_code, bh.batch_no, line_no, msi.segment1;
BEGIN
   FOR h1 IN h_invoice
   LOOP
      l_verify_flag := 'Y';
      l_error_message := NULL;
      owner_id :=
            h1.locations
         || ', ORG: '
         || h1.org_code
         || ' : '
         || h1.org_name
         || ', Batch No: '
         || h1.batch_no;
      mailform := NULL;
      body_text := NULL;
      mailform := NULL;
      slsq := 0;

      SELECT NVL (bi.user_smtp (h1.org_code || '_BATCH'),
                  'xxx@xxxx.com'
                 )
        INTO tomail
        FROM DUAL;

      BEGIN
-- For Batch Header -----------------------------------------------------------------------------
         FOR l1 IN l_invoice (h1.org_id, h1.batch_id)
         LOOP
            BEGIN
               slsq := slsq + 1;

               --  ******************* Batch Header **********************************
               IF (l1.tcostalloc < 1 OR l1.tcostalloc > 1)
               THEN
                  body_text :=
                        body_text
                     || '<tr><td align="right">'
                     || slsq
                     || '</td><td align="center">'
                     || l1.item_type
                     || '</td><td align="left">'
                     || l1.item_code
                     || '</td><td align="left"> '
                     || l1.item_name
                     || '</td><td align="left">'
                     || l1.dtl_um
                     || '</td><td align="right" style="background-color:red;">'
                     || l1.cost_alloc
                     || '</td><td align="right">'
                     || l1.original_qty
                     || '</td><td align="right">'
                     || l1.actual_qty
                     || '</td><td align="right">'
                     || l1.yield_var
                     || '</td><td align="left">'
                     || l1.batch_status
                     || '</td></tr>';
               ELSE
                  body_text :=
                        body_text
                     || '<tr><td align="right">'
                     || slsq
                     || '</td><td align="center">'
                     || l1.item_type
                     || '</td><td align="left">'
                     || l1.item_code
                     || '</td><td align="left"> '
                     || l1.item_name
                     || '</td><td align="left">'
                     || l1.dtl_um
                     || '</td><td align="right">'
                     || l1.cost_alloc
                     || '</td><td align="right">'
                     || l1.original_qty
                     || '</td><td align="right">'
                     || l1.actual_qty
                     || '</td><td align="right">'
                     || l1.yield_var
                     || '</td><td align="left">'
                     || l1.batch_status
                     || '</td></tr>';
               END IF;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_verify_flag := 'N';
                  l_error_message :=
                     l_error_message || 'Data not generated into ap table...';
                  COMMIT;
            END;
         END LOOP;

         body_text := body_text || '<tr><td colspan="10">.</td></tr>';
         body_text :=
               body_text
            || '<tr><th align="right">SLNO</th><th align="right">ITEM TYPE</th><th align="center">ITEM CODE</th><th align="left">ITEM NAME</th><th align="left">UOM</th><th align="center">STD QTY</th><th align="right">ACTUAL QTY</th><th align="right">USES VARIANCE </th><th align="right">VAR(%)</th><th align="left">STATUS</th><th align="left"></th></tr>';
         slsq := 0;

-- For Batch Line -----------------------------------------------------------------------------
         FOR d1 IN d_invoice (h1.org_id, h1.batch_id)
         LOOP
            BEGIN
               --  Batch Line  with 'ExtraAdded';
               slsq := slsq + 1;

               -- temp_var := d1.ORIGINAL_QTY;   -- temp_var2 := d1.ACTUAL_QTY;
               IF (d1.original_qty = 0 AND d1.actual_qty = 0)
               THEN
                  temp_var := 0;
               ELSE
                  temp_var := d1.v_percent * 100;
               END IF;

               --   IF (d1.V_PERCENT <=-1 or d1.V_PERCENT>=9 ) THEN
               IF (temp_var <= -1 OR temp_var >= 9)
               THEN
                  body_text3 :=
                        body_text3
                     || '<tr><td align="right">'
                     || slsq
                     || '</td><td align="center">'
                     || d1.item_type
                     || '</td><td align="left">'
                     || d1.item_code
                     || '</td><td align="left"> '
                     || d1.item_name
                     || '</td><td align="left">'
                     || d1.dtl_um
                     || '</td><td align="right">'
                     || d1.original_qty
                     || '</td><td align="right">'
                     || d1.actual_qty
                     || '</td><td align="right">'
                     || d1.qty_variance
                     || '</td><td align="right"  style="background-color:red;">'
                     || temp_var
                     || '</td>';
               ELSE
                  body_text3 :=
                        body_text3
                     || '<tr><td align="right">'
                     || slsq
                     || '</td><td align="center">'
                     || d1.item_type
                     || '</td><td align="left">'
                     || d1.item_code
                     || '</td><td align="left"> '
                     || d1.item_name
                     || '</td><td align="left">'
                     || d1.dtl_um
                     || '</td><td align="right">'
                     || d1.original_qty
                     || '</td><td align="right">'
                     || d1.actual_qty
                     || '</td><td align="right">'
                     || d1.qty_variance
                     || '</td><td align="right" style="background-color:white;">'
                     || temp_var
                     || '</td>';
               END IF;

               IF d1.formula <> 'ExtraAdded'
               THEN
                  body_text3 :=
                        body_text3
                     || '<td align="left" style="background-color:white;">'
                     || d1.formula
                     || '</td></tr>';
               ELSE
                  body_text3 :=
                        body_text3
                     || '<td align="left" style="background-color:red;">'
                     || d1.formula
                     || '</td></tr>';
               END IF;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_verify_flag := 'N';
                  l_error_message :=
                     l_error_message || 'Data not generated into ap table...';
                  COMMIT;
            END;
         END LOOP;
      --commit;
      END;

      body_text3 :=
            body_text3
         || '<tr><th align="right"></th><th colspan="2" align="center"><b>Total line in Batch: </B></th><th align="right"><b>'
         || slsq
         || '</b></th><th></th><th></th><th></th><th></th><th></th><th></th></tr>';

      INSERT INTO bi.mail_sends
                  (mail_oid, mail_to, mail_cc,
                   mail_subjt,
                   mail_bodyhtml,
                   mail_bodyhtml3,
                   mail_reference
                  )
           VALUES (sq_mail_sends.NEXTVAL, tomail, ccmail,
                   'Batch Detail Information' || '-' || h1.locations,
                      '<br/><b><br/></b>Please find the Batch Detail Information.
     <br/><b>'
                   || owner_id
                   || '</b><br/><table><tr><td align="center", style="background-color:yellow">Batch Detail Information</td></tr><tr><td>
     <table border="1" cellspacing="0"><tr><th>SLNO</th><th>ITEM TYPE</th><th>ITEM CODE</th><th>ITEM NAME</th><th>UOM</th><th>COST ALLOC</th><th>STD QTY</th><th>ACTUAL QTY</th><th>YIELD VAR</th><th>STATUS</th></tr>'
                   || body_text,
                      body_text3
                   || '</table>
    </td><td></td></tr></table> <br/><br/>This is test Batch Detail Information mail from OBIEE, Trn Date:'
                   || TO_DATE (SYSDATE, 'dd-mm-yy hh24:mi:ss')
                   || '<br/> <hr width="820", align="left"> This is system generated automatic mail, do not send reply mail.',
                   'ICT-Report Robot'
                  );

      COMMIT;
      body_text := NULL;
      body_text3 := NULL;
      owner_id := NULL;
      --body_text2 := null;
      tomail := NULL;
      slsq := 0;
      mailform := NULL;
   END LOOP;

   DELETE FROM bi.mail_sends
         WHERE mail_status = 'Y';

   COMMIT;
END;
/
-- Also Developer can select data directly without DB Link and can send mail or

after insert data to Mail_Sends table, you can fire mail using app or OBIEE mail bursting service or ebs mail scheduler