Wednesday, December 9, 2020

PR Workflow Custom Message HTML

 CREATE OR REPLACE PACKAGE BODY APPS.xx_po_approval_pkg

AS

   /*  --------------History -----------------------------*/

   -- Created By Vishal  18 Mar 2015  For Adding Approver In PO Approval Workflow-----

   -- Purpose

   --1 - Check By Budget Amount

   --2 - Getting Notification Note

   --3 - Showing The Budget Details

   --4 - Creating Action History

   /* ------------End -----------------------------------*/


   PROCEDURE check_budget_amt (itemtype    IN     VARCHAR2,

                               itemkey     IN     VARCHAR2,

                               actid       IN     NUMBER,

                               funcmode    IN     VARCHAR2,

                               resultout      OUT VARCHAR2)

   IS

      l_po_header_id                 NUMBER;

      l_planned                      VARCHAR2 (2);

      l_task_id                      NUMBER;

      l_expenditure_type             VARCHAR2 (50);

      l_dist_attribute1              VARCHAR2 (20);

      l_budget_details               VARCHAR2 (3000);

      l_amount                       NUMBER;

      l_note                         VARCHAR2 (500);

      l_performer                    VARCHAR2 (100);

      l_performer_id                 NUMBER;

      l_line_details                 VARCHAR2 (4000);

      l_check_eastern_org            NUMBER;

      l_xx_total_approver_count      NUMBER := 0;

      l_xx_current_approver_number   NUMBER := 0;

   BEGIN

      IF (funcmode = 'RUN')

      THEN

         IF fnd_profile.VALUE ('ORG_ID') = 721

         THEN

            l_po_header_id :=

               wf_engine.getitemattrtext (itemtype   => itemtype,

                                          itemkey    => itemkey,

                                          aname      => 'DOCUMENT_ID');




            IF EST_PKG.GET_STATUS_PO_HEADER (l_po_header_id) = 1

            THEN

               SELECT COUNT (*)

                 INTO l_xx_total_approver_count

                 FROM xxea_po_budget_approver

                WHERE STATUS = 'Y';


               wf_engine.setitemattrnumber (

                  itemtype   => itemtype,

                  itemkey    => itemkey,

                  aname      => 'XX_TOTAL_APPROVER_COUNT',

                  avalue     => l_xx_total_approver_count);



               IF NVL (l_xx_total_approver_count, 0) > 0

               THEN

                  l_xx_current_approver_number := 0;




                  wf_engine.setitemattrnumber (

                     itemtype   => itemtype,

                     itemkey    => itemkey,

                     aname      => 'XXCURRENT_APPROVER_NUMBER',

                     avalue     => l_xx_current_approver_number);




                  l_planned := 'N';



                  BEGIN

                     l_line_details :=

                        'PLSQL:APPS.XX_PO_APPROVAL_PKG.GET_LINE_DETAILS/'

                        || l_po_header_id;


                     wf_engine.setitemattrtext (

                        itemtype   => itemtype,

                        itemkey    => itemkey,

                        aname      => 'XXLINE_DETAILS',

                        avalue     => l_line_details);

                  EXCEPTION

                     WHEN OTHERS

                     THEN

                        NULL;

                  END;


                  BEGIN

                     BUDGET_OUTPUT_MEG (l_po_header_id, l_budget_details);


                     wf_engine.setitemattrtext (

                        itemtype   => itemtype,

                        itemkey    => itemkey,

                        aname      => 'XX_BUDGET_DETAILS',

                        avalue     => l_budget_details);

                  EXCEPTION

                     WHEN OTHERS

                     THEN

                        NULL;

                  END;

               ELSE

                  l_planned := 'Y';

               END IF;

            ELSE

               l_planned := 'Y';

            END IF;

         ELSE

            l_planned := 'Y';

         END IF;

      ELSE

         l_planned := 'Y';

      END IF;




      IF l_planned = 'N'

      THEN

         resultout := 'N';

         RETURN;

      ELSE

         resultout := 'Y';

         RETURN;

      END IF;

   EXCEPTION

      WHEN OTHERS

      THEN

         wf_core.context ('XX_PO_APPROVAL_PKG.CHECK_BUDGET_AMT',

                          itemtype,

                          itemkey,

                          TO_CHAR (actid),

                          funcmode,

                          SUBSTR (SQLERRM, 1, 300));

         RAISE;

   END check_budget_amt;


   PROCEDURE get_next_approver (itemtype    IN     VARCHAR2,

                                itemkey     IN     VARCHAR2,

                                actid       IN     NUMBER,

                                funcmode    IN     VARCHAR2,

                                resultout      OUT VARCHAR2)

   IS

      l_po_header_id                 NUMBER;

      l_xx_total_approver_count      NUMBER := 0;

      l_xx_current_approver_number   NUMBER := 0;

      l_XXPERFORMER                  VARCHAR2 (100);

      XX_BUDGET_PERFORMER_ID         NUMBER;

      l_temp_employee_id             NUMBER;



      l_planned                      VARCHAR2 (10);

   BEGIN

      l_planned := 'N';


      IF (funcmode = 'RUN')

      THEN

         l_po_header_id :=

            wf_engine.getitemattrtext (itemtype   => itemtype,

                                       itemkey    => itemkey,

                                       aname      => 'DOCUMENT_ID');




         l_xx_current_approver_number :=

            wf_engine.getitemattrnumber (

               itemtype   => itemtype,

               itemkey    => itemkey,

               aname      => 'XXCURRENT_APPROVER_NUMBER');


         l_xx_total_approver_count :=

            wf_engine.getitemattrnumber (

               itemtype   => itemtype,

               itemkey    => itemkey,

               aname      => 'XX_TOTAL_APPROVER_COUNT');



         IF l_xx_total_approver_count > l_xx_current_approver_number

         THEN

            l_xx_current_approver_number := l_xx_current_approver_number + 1;


            SELECT USER_NAME, USER_ID

              INTO l_XXPERFORMER, XX_BUDGET_PERFORMER_ID

              FROM xxea_po_budget_approver

             WHERE STATUS = 'Y' AND l_level = l_xx_current_approver_number;



            SELECT employee_id

              INTO g_system_employee_id

              FROM PO_ACTION_HISTORY

             WHERE     object_id = l_po_header_id

                   AND sequence_num = (SELECT MAX (sequence_num)

                                         FROM PO_ACTION_HISTORY

                                        WHERE object_id = l_po_header_id)

                   AND ROWNUM = 1;


            SELECT employee_id

              INTO l_temp_employee_id

              FROM fnd_user

             WHERE user_id = XX_BUDGET_PERFORMER_ID;


            UPDATE PO_ACTION_HISTORY

               SET employee_id = l_temp_employee_id

             WHERE     object_id = l_po_header_id

                   AND sequence_num = (SELECT MAX (sequence_num)

                                         FROM PO_ACTION_HISTORY

                                        WHERE object_id = l_po_header_id)

                   AND ROWNUM = 1;


            wf_engine.setitemattrnumber (

               itemtype   => itemtype,

               itemkey    => itemkey,

               aname      => 'XX_BUDGET_PERFORMER_ID',

               avalue     => XX_BUDGET_PERFORMER_ID);


            wf_engine.setitemattrnumber (

               itemtype   => itemtype,

               itemkey    => itemkey,

               aname      => 'XXCURRENT_APPROVER_NUMBER',

               avalue     => l_xx_current_approver_number);


            wf_engine.setitemattrtext (itemtype   => itemtype,

                                       itemkey    => itemkey,

                                       aname      => 'XXPERFORMER',

                                       avalue     => l_XXPERFORMER);




            l_planned := 'Y';

         END IF;

      END IF;



      IF l_planned = 'N'

      THEN

         resultout := 'N';

         RETURN;

      ELSE

         resultout := 'Y';

         RETURN;

      END IF;

   EXCEPTION

      WHEN OTHERS

      THEN

         wf_core.context ('XX_PO_APPROVAL_PKG.CHECK_BUDGET_AMT',

                          itemtype,

                          itemkey,

                          TO_CHAR (actid),

                          funcmode,

                          SUBSTR (SQLERRM, 1, 300));

         RAISE;

   END get_next_approver;



   PROCEDURE update_approval (itemtype    IN     VARCHAR2,

                              itemkey     IN     VARCHAR2,

                              actid       IN     NUMBER,

                              funcmode    IN     VARCHAR2,

                              resultout      OUT VARCHAR2)

   IS

      l_po_header_id                 NUMBER;

      l_planned                      VARCHAR2 (10);


      l_xx_current_approver_number   NUMBER;


      l_xx_total_approver_count      NUMBER;

   BEGIN

      IF (funcmode = 'RUN')

      THEN

         l_po_header_id :=

            wf_engine.getitemattrtext (itemtype   => itemtype,

                                       itemkey    => itemkey,

                                       aname      => 'DOCUMENT_ID');



         l_xx_current_approver_number :=

            wf_engine.getitemattrnumber (

               itemtype   => itemtype,

               itemkey    => itemkey,

               aname      => 'XXCURRENT_APPROVER_NUMBER');


         l_xx_total_approver_count :=

            wf_engine.getitemattrnumber (

               itemtype   => itemtype,

               itemkey    => itemkey,

               aname      => 'XX_TOTAL_APPROVER_COUNT');


         IF l_xx_total_approver_count <= l_xx_current_approver_number

         THEN

            UPDATE PO_ACTION_HISTORY

               SET employee_id = g_system_employee_id

             WHERE     object_id = l_po_header_id

                   AND sequence_num = (SELECT MAX (sequence_num)

                                         FROM PO_ACTION_HISTORY

                                        WHERE object_id = l_po_header_id)

                   AND ROWNUM = 1;


            l_planned := 'Y';

         ELSE

            l_planned := 'N';

         END IF;




         IF l_planned = 'N'

         THEN

            resultout := 'N';

            RETURN;

         ELSE

            resultout := 'Y';

            RETURN;

         END IF;

      END IF;

   EXCEPTION

      WHEN OTHERS

      THEN

         wf_core.context ('XX_PO_APPROVAL_PKG.CHECK_BUDGET_AMT',

                          itemtype,

                          itemkey,

                          TO_CHAR (actid),

                          funcmode,

                          SUBSTR (SQLERRM, 1, 300));

         RAISE;

   END update_approval;



   PROCEDURE NOTIFICATION_NOTE (itemtype   IN     VARCHAR2,

                                itemkey    IN     VARCHAR2,

                                actid      IN     NUMBER,

                                funcmode   IN     VARCHAR2,

                                RESULT        OUT VARCHAR2)

   IS

      l_nid            NUMBER;

      Note             VARCHAR2 (480);

      l_performer      VARCHAR2 (100);

      l_performer_id   NUMBER;

      l_po_header_id   NUMBER;

      l_person_id      NUMBER;

   BEGIN

      IF (funcmode IN ('RESPOND'))

      THEN

         l_nid := wf_engine.context_nid;

         Note := wf_notification.getattrtext (l_nid, 'BUDGET_NOTE');


         l_po_header_id :=

            wf_engine.getitemattrtext (itemtype   => itemtype,

                                       itemkey    => itemkey,

                                       aname      => 'DOCUMENT_ID');



         l_performer_id :=

            wf_engine.getitemattrtext (itemtype   => itemtype,

                                       itemkey    => itemkey,

                                       aname      => 'XX_BUDGET_PERFORMER_ID');



         SELECT employee_id

           INTO l_person_id

           FROM fnd_user

          WHERE user_id = l_performer_id;



         CREATE_ACTION_HISTORY (l_po_header_id, l_person_id, Note);


         wf_engine.setitemattrtext (itemtype   => itemtype,

                                    itemkey    => itemkey,

                                    aname      => 'NOTE',

                                    avalue     => Note);

      END IF;




      RESULT := NULL;

   EXCEPTION

      WHEN OTHERS

      THEN

         RESULT := SQLERRM;

   END;




   PROCEDURE budget_output_meg (document_id NUMBER, l_doc_attr OUT VARCHAR2)

   IS

      l_doc_attr_val       VARCHAR2 (3000);




      p_task_id            NUMBER := 0;

      p_expenditure_type   VARCHAR2 (200);

      p_amount             NUMBER := 0;

      l_dist_attribute1    VARCHAR2 (200);

   BEGIN

        SELECT pda.task_id, expenditure_type, pda.attribute1

          INTO p_task_id, p_expenditure_type, l_dist_attribute1

          FROM po_headers_all pha, po_lines_all pla, po_distributions_all pda

         WHERE     pha.po_header_id = pla.po_header_id

               AND pla.po_line_id = pda.po_line_id

               AND pha.po_header_id = document_id

      GROUP BY pda.task_id, expenditure_type, pda.attribute1;



      l_doc_attr :=

         EST_PKG.GET_AMT_DET (p_task_id,

                              NVL (p_expenditure_type, l_dist_attribute1),

                              0);

   END;


   PROCEDURE CREATE_ACTION_HISTORY (document_id     NUMBER,

                                    performer_id    NUMBER,

                                    note            VARCHAR2)

   IS

      l_doc_attr_val   VARCHAR2 (3000);


      CURSOR c1

      IS

         SELECT *

           FROM PO_ACTION_HISTORY

          WHERE     object_id = document_id

                AND sequence_num = (SELECT MAX (sequence_num)

                                      FROM PO_ACTION_HISTORY

                                     WHERE object_id = document_id)

                AND ROWNUM = 1;

   BEGIN

      FOR i IN c1

      LOOP

         --         DBMS_OUTPUT.put_line ('Start');

         

          UPDATE PO_ACTION_HISTORY

            SET sequence_num = i.sequence_num + 1 ,ACTION_DATE = sysdate

          WHERE     object_id = document_id

                AND sequence_num = (SELECT MAX (sequence_num)

                                      FROM PO_ACTION_HISTORY

                                     WHERE object_id = document_id)

                AND ROWNUM = 1;


         INSERT INTO PO.PO_ACTION_HISTORY (object_id,

                                           object_type_code,

                                           object_sub_type_code,

                                           sequence_num,

                                           last_update_date,

                                           last_updated_by,

                                           creation_date,

                                           created_by,

                                           action_code,

                                           action_date,

                                           employee_id,

                                           note,

                                           object_revision_num,

                                           last_update_login,

                                           request_id,

                                           program_application_id,

                                           program_id,

                                           program_update_date,

                                           approval_path_id,

                                           offline_code)

              VALUES (document_id,

                      i.object_type_code,

                      i.object_sub_type_code,

                      i.sequence_num,

                      SYSDATE,

                      fnd_global.user_id,

                      SYSDATE,

                      fnd_global.user_id,

                      'FORWARD',

                      SYSDATE,

                      performer_id,

                      note,

                      0,

                      fnd_global.login_id,

                      0,

                      0,

                      0,

                      '',

                      i.approval_path_id,

                      '');

      --         DBMS_OUTPUT.put_line ('After Insert');

      END LOOP;


      COMMIT;

   --      DBMS_OUTPUT.put_line ('After Commit');

   EXCEPTION

      WHEN OTHERS

      THEN

         NULL;


         DBMS_OUTPUT.put_line ('Exception : ' || SUBSTR (SQLERRM, 1, 100));

   END;


   /* ------------End -----------------------------------*/

   PROCEDURE PR_check_budget_amt (itemtype    IN     VARCHAR2,

                                  itemkey     IN     VARCHAR2,

                                  actid       IN     NUMBER,

                                  funcmode    IN     VARCHAR2,

                                  resultout      OUT VARCHAR2)

   IS

      l_po_header_id       NUMBER;

      l_planned            VARCHAR2 (2);

      l_task_id            NUMBER;

      l_expenditure_type   VARCHAR2 (50);

      l_dist_attribute1    VARCHAR2 (20);

      l_budget_details     VARCHAR2 (3000);

      l_amount             NUMBER;

      l_note               VARCHAR2 (500);

      l_performer          VARCHAR2 (100);

      l_performer_id       NUMBER;

   BEGIN

      IF (funcmode = 'RUN')

      THEN

         l_po_header_id :=

            wf_engine.getitemattrtext (itemtype   => itemtype,

                                       itemkey    => itemkey,

                                       aname      => 'DOCUMENT_ID');


         l_planned := 'Y';



         BEGIN

              SELECT pord.task_id,

                     expenditure_type,

                     pord.attribute1,

                     NVL (

                        SUM (pord.req_line_quantity * NVL (porl.unit_price, 1)),

                        0)

                        amt

                INTO l_task_id,

                     l_expenditure_type,

                     l_dist_attribute1,

                     l_amount

                FROM apps.po_requisition_headers_all porh,

                     apps.po_requisition_lines_all porl,

                     apps.po_req_distributions_all pord

               WHERE porh.requisition_header_id = porl.requisition_header_id --    AND porl.item_id IS NOT NULL

                     AND porl.requisition_line_id = pord.requisition_line_id

                     AND porh.REQUISITION_HEADER_ID = l_po_header_id

            GROUP BY pord.task_id, expenditure_type, pord.attribute1;




            IF fnd_profile.VALUE ('ORG_ID') = 721 AND l_task_id IS NOT NULL

            THEN

               IF est_pkg.get_status (

                     l_task_id,

                     NVL (l_expenditure_type, l_dist_attribute1),

                     l_amount) = 1

               THEN

                  l_planned := 'N';




                  BEGIN

                     SELECT USER_NAME, EMPLOYEE_ID

                       INTO l_performer, l_performer_id

                       FROM fnd_user

                      WHERE user_name LIKE 'VISHAL.JACOB';


                     IF l_performer IS NULL

                     THEN

                        l_performer := 'SYSADMIN';

                     END IF;

                  EXCEPTION

                     WHEN OTHERS

                     THEN

                        NULL;


                        l_performer := 'SYSADMIN';

                  END;


                  BEGIN

                     BEGIN

                        wf_engine.setitemattrtext (

                           itemtype   => itemtype,

                           itemkey    => itemkey,

                           aname      => 'XX_BUDGET_APPROVER',

                           avalue     => l_performer);

                     EXCEPTION

                        WHEN OTHERS

                        THEN

                           NULL;

                     END;


                     BEGIN

                        PR_BUDGET_OUTPUT_MEG (l_po_header_id,

                                              l_budget_details);


                        wf_engine.setitemattrtext (

                           itemtype   => itemtype,

                           itemkey    => itemkey,

                           aname      => 'XX_BUDGET_DETAILS',

                           avalue     => l_budget_details);

                     EXCEPTION

                        WHEN OTHERS

                        THEN

                           NULL;

                     END;

                  EXCEPTION

                     WHEN OTHERS

                     THEN

                        l_budget_details := NULL;

                  END;

               END IF;

            END IF;

         EXCEPTION

            WHEN OTHERS

            THEN

               l_planned := 'Y';

         END;


         --                     l_planned := 'N'  ; ---- Checking the workflow remove this line


         IF l_planned = 'N'

         THEN

            resultout := 'N';

            RETURN;

         ELSE

            resultout := 'Y';

            RETURN;

         END IF;

      END IF;

   EXCEPTION

      WHEN OTHERS

      THEN

         wf_core.context ('XX_PO_APPROVAL_PKG.PR_CHECK_BUDGET_AMT',

                          itemtype,

                          itemkey,

                          TO_CHAR (actid),

                          funcmode,

                          SUBSTR (SQLERRM, 1, 300));

         RAISE;

   END PR_check_budget_amt;



   PROCEDURE PR_NOTIFICATION_NOTE (itemtype   IN     VARCHAR2,

                                   itemkey    IN     VARCHAR2,

                                   actid      IN     NUMBER,

                                   funcmode   IN     VARCHAR2,

                                   RESULT        OUT VARCHAR2)

   IS

      l_nid            NUMBER;

      Note             VARCHAR2 (480);

      l_performer      VARCHAR2 (100);

      l_performer_id   NUMBER;

      l_po_header_id   NUMBER;

   BEGIN

      IF (funcmode IN ('RESPOND'))

      THEN

         l_nid := wf_engine.context_nid;

         Note := wf_notification.getattrtext (l_nid, 'BUDGET_NOTE');


         l_po_header_id :=

            wf_engine.getitemattrtext (itemtype   => itemtype,

                                       itemkey    => itemkey,

                                       aname      => 'DOCUMENT_ID');



         l_performer_id :=

            wf_engine.getitemattrnumber (

               itemtype   => itemtype,

               itemkey    => itemkey,

               aname      => 'XX_BUDGET_PERFORMER_ID');



         PR_CREATE_ACTION_HISTORY (l_po_header_id, l_performer_id, Note);


         wf_engine.setitemattrtext (itemtype   => itemtype,

                                    itemkey    => itemkey,

                                    aname      => 'NOTE',

                                    avalue     => Note);

      END IF;




      RESULT := NULL;

   EXCEPTION

      WHEN OTHERS

      THEN

         RESULT := SQLERRM;

   END PR_NOTIFICATION_NOTE;




   PROCEDURE PR_budget_output_meg (document_id       NUMBER,

                                   l_doc_attr    OUT VARCHAR2)

   IS

      l_doc_attr_val              VARCHAR2 (3000);


      CURSOR proj_cur (

         pl_task_id NUMBER)

      IS

         SELECT pha.project_id,

                t.task_id,

                pha.segment1 project_num,

                t.task_number || ' (' || t.task_name || ')' task_name,

                pb.resource_list_id,

                (SELECT pp.organization_id

                   FROM pjm_project_parameters pp

                  WHERE pp.project_id = pha.project_id AND ROWNUM = 1)

                   organization_id,

                org_id

           FROM pa_projects_all pha, pa_budget_versions pb, pa_tasks t

          WHERE     pha.project_id = pb.project_id

                AND pb.budget_status_code = 'B'

                AND pb.current_flag = 'Y'

                AND pb.budget_type_code = 'AC'

                AND pb.project_id = t.project_id

                AND t.task_id = pl_task_id;


      CURSOR exp_cur (

         p_list_id              NUMBER,

         pl_expenditure_type    VARCHAR2)

      IS

         SELECT t.expenditure_type,

                t.expenditure_category,

                (SELECT r.resource_list_member_id

                   FROM xx_pa_resource_v r

                  WHERE     r.resource_list_id = p_list_id

                        AND r.expenditure_type = t.expenditure_type /* Modified by Kazi on 30 - Mar - 2015 */

                        AND ROWNUM = 1)

                   resource_list_member_id

           FROM pa_expenditure_types t

          WHERE t.expenditure_type = pl_expenditure_type;


      v_proj_rec                  proj_cur%ROWTYPE;

      v_expenditure_category      VARCHAR2 (200);

      v_expenditure_type          VARCHAR2 (200);

      v_resource_list_member_id   NUMBER;

      v_resource_name             VARCHAR2 (200);

      v_organization_id           NUMBER;

      v_proj_bgt                  NUMBER;

      v_task_bgt                  NUMBER;

      v_res_bgt                   NUMBER;

      v_cst_act                   NUMBER := 0;

      v_cst_unp                   NUMBER := 0;

      v_cst_cmt                   NUMBER := 0;

      v_cst_stk                   NUMBER := 0;

      v_cst_po                    NUMBER := 0;

      v_cst_req                   NUMBER := 0;

      v_cst_tot                   NUMBER := 0;

      v_rem_bgt                   NUMBER := 0;

      v_msg                       VARCHAR2 (2000);

      p_task_id                   NUMBER := 0;

      p_expenditure_type          VARCHAR2 (200);

      p_amount                    NUMBER := 0;

   BEGIN

      v_msg := NULL;




        SELECT pord.task_id,

               expenditure_type,

               NVL (SUM (pord.req_line_quantity * NVL (porl.unit_price, 1)), 0)

                  amt

          INTO p_task_id, p_expenditure_type, --                     l_dist_attribute1,

                                             p_amount

          FROM apps.po_requisition_headers_all porh,

               apps.po_requisition_lines_all porl,

               apps.po_req_distributions_all pord

         WHERE     porh.requisition_header_id = porl.requisition_header_id

               --               AND porl.item_id IS NOT NULL

               AND porl.requisition_line_id = pord.requisition_line_id

               AND porh.REQUISITION_HEADER_ID = document_id

      GROUP BY pord.task_id, expenditure_type;


      DBMS_OUTPUT.put_line ('v_over_bgt   2 ' || v_over_bgt);


      OPEN proj_cur (p_task_id);


      FETCH proj_cur INTO v_proj_rec;


      CLOSE proj_cur;


      OPEN exp_cur (v_proj_rec.resource_list_id, p_expenditure_type);


      FETCH exp_cur

      INTO v_expenditure_type,

           v_expenditure_category,

           v_resource_list_member_id;


      CLOSE exp_cur;


      IF v_resource_list_member_id IS NULL

      THEN

         v_resource_name := ' Unclassified';

      ELSE

         v_resource_name := v_expenditure_category;

      END IF;


      SELECT NVL (SUM (l.burdened_cost), 0) proj_bgt,

             NVL (SUM (DECODE (l.task_id, p_task_id, l.burdened_cost, 0)), 0)

                task_bgt,

             NVL (

                SUM (

                   DECODE (l.resource_list_member_id,

                           v_resource_list_member_id, l.burdened_cost,

                           0)),

                0)

                res_bgt

        INTO v_proj_bgt, v_task_bgt, v_res_bgt

        FROM xx_pa_budget_lines l

       WHERE project_id = v_proj_rec.project_id;


      SELECT NVL (SUM (cs.project_burdened_cost), 0) cst_act,

             NVL (

                SUM (

                   CASE

                      WHEN cs.project_burdened_cost IS NULL THEN cs.quantity

                      ELSE 0

                   END),

                0)

                cst_unp

        INTO v_cst_act, v_cst_unp

        FROM pa_expenditure_items_all cs

       WHERE     cs.project_id = v_proj_rec.project_id

             AND cs.task_id = p_task_id

             AND cs.expenditure_type = p_expenditure_type;


      SELECT NVL (SUM (cs.proj_burdened_cost), 0)

        INTO v_cst_cmt

        FROM pa_commitment_txns cs

       WHERE cs.project_id = v_proj_rec.project_id AND cs.task_id = p_task_id

             AND EXISTS

                    (SELECT 1

                       FROM pa_expenditure_types t

                      WHERE t.expenditure_type = cs.expenditure_type

                            AND t.expenditure_category =

                                   v_expenditure_category);


      SELECT ROUND (

                NVL (

                   SUM (

                      (NVL (plla.quantity, 0)

                       - NVL (plla.quantity_received, 0))

                      * NVL (pla.unit_price, 0)

                      * NVL (pha.rate, 1)),

                   0),

                2)

                amt

        INTO v_cst_po

        FROM po_headers_all pha, po_lines_all pla, po_line_locations_all plla

       WHERE     pha.po_header_id = pla.po_header_id

             AND pha.org_id = pla.org_id

             AND pla.po_header_id = plla.po_header_id

             AND pla.po_line_id = plla.po_line_id

             AND pla.org_id = plla.org_id

             AND pha.type_lookup_code = 'STANDARD'

             --AND pha.authorization_status = 'APPROVED'

             AND pha.org_id = v_proj_rec.org_id

             AND pla.item_id IS NOT NULL

             AND plla.quantity <> plla.quantity_received

             AND NVL (pla.closed_code, ' ') NOT IN

                    ('CLOSED', 'FINALLY CLOSED')

             AND EXISTS

                    (SELECT 1

                       FROM po_distributions_all pda, pa_expenditure_types t

                      WHERE     pda.line_location_id = plla.line_location_id

                            AND pda.project_id = v_proj_rec.project_id

                            AND pda.task_id = p_task_id

                            AND DECODE (pda.attribute_category,

                                        'Cost Code', pda.attribute1) =

                                   t.expenditure_type

                            AND t.expenditure_category =

                                   v_expenditure_category);


      SELECT NVL (SUM (pord.req_line_quantity * NVL (porl.unit_price, 1)), 0)

                amt

        INTO v_cst_req

        FROM apps.po_requisition_headers_all porh,

             apps.po_requisition_lines_all porl,

             apps.po_req_distributions_all pord

       WHERE     porh.requisition_header_id = porl.requisition_header_id

             AND porl.item_id IS NOT NULL

             AND porl.requisition_line_id = pord.requisition_line_id

             AND porh.segment1 = porh.segment1 || ''

             AND porl.line_num = porl.line_num + 0

             AND porh.org_id = v_proj_rec.org_id

             AND pord.project_id = v_proj_rec.project_id

             AND pord.task_id = p_task_id

             AND NOT EXISTS

                    (SELECT 1

                       FROM po_distributions_all pod

                      WHERE pod.req_distribution_id = pord.distribution_id)

             AND EXISTS

                    (SELECT 1

                       FROM pa_expenditure_types t

                      WHERE DECODE (pord.attribute_category,

                                    'Cost Code', pord.attribute1) =

                               t.expenditure_type

                            AND t.expenditure_category =

                                   v_expenditure_category);


      v_cst_po := v_cst_po + v_cst_req;

      v_cst_tot := v_cst_act + v_cst_unp + v_cst_cmt + v_cst_po + v_cst_stk;

      v_rem_bgt := v_res_bgt - v_cst_tot;


      IF v_rem_bgt < NVL (p_amount, 0)

      THEN

         v_over_bgt := 'Y';

      ELSE

         v_over_bgt := 'N';

      END IF;


      DBMS_OUTPUT.put_line ('v_over_bgt  ' || v_over_bgt);


      l_doc_attr_val :=

            'Project        : '

         || v_proj_rec.project_num

         || '

'

         || 'Task              : '

         || v_proj_rec.task_name

         || '

'

         || 'Exp Cat         : '

         || v_expenditure_category

         || '

'

         || 'Resource    : '

         || v_expenditure_type                               --v_resource_name

         || '

===================================

'

         || 'Project bgt            : '

         || TO_CHAR (v_proj_bgt, '9,999,999,999,999.00')

         || '

'

         || 'Task bgt               : '

         || TO_CHAR (v_task_bgt, '9,999,999,999,999.00')

         || '

'

         || 'Resource bgt(A) : '

         || TO_CHAR (v_res_bgt, '9,999,999,999,999.00')

         || '

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

'

         || 'Actual Cost          :'

         || TO_CHAR (v_cst_act, '9,999,999,999,999.00')

         || '

'

         || 'Unprocsd Cost      :'

         || TO_CHAR (v_cst_unp, '9,999,999,999,999.00')

         || '

'

         || 'Commitments       :'

         || TO_CHAR (v_cst_cmt, '9,999,999,999,999.00')

         || '

'

         || 'Inv Stocks         :'

         || TO_CHAR (v_cst_stk, '9,999,999,999,999.00')

         || '

'

         || 'Pend. PO/REQ     :'

         || TO_CHAR (v_cst_po, '9,999,999,999,999.00')

         || '

'

         || 'Total Cost (B)       :'

         || TO_CHAR (v_cst_tot, '9,999,999,999,999.00')

         || '

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

'

         || 'Remn. Bgt(A-B)     :'

         || TO_CHAR (v_rem_bgt, '9,999,999,999,999.00');


      l_doc_attr := l_doc_attr_val;

   --   DBMS_OUTPUT.put_line (v_msg);

   END PR_budget_output_meg;


   PROCEDURE PR_CREATE_ACTION_HISTORY (document_id     NUMBER,

                                       performer_id    NUMBER,

                                       note            VARCHAR2)

   IS

      l_doc_attr_val   VARCHAR2 (3000);


      CURSOR c1

      IS

         SELECT *

           FROM PO_ACTION_HISTORY

          WHERE     object_id = document_id

                AND sequence_num = (SELECT MAX (sequence_num)

                                      FROM PO_ACTION_HISTORY

                                     WHERE object_id = document_id)

                AND ROWNUM = 1;

   BEGIN

      FOR i IN c1

      LOOP

         --         DBMS_OUTPUT.put_line ('Start');



         UPDATE PO_ACTION_HISTORY

            SET sequence_num = i.sequence_num + 1

          WHERE     object_id = document_id

                AND sequence_num = (SELECT MAX (sequence_num)

                                      FROM PO_ACTION_HISTORY

                                     WHERE object_id = document_id)

                AND ROWNUM = 1;


         INSERT INTO PO.PO_ACTION_HISTORY (object_id,

                                           object_type_code,

                                           object_sub_type_code,

                                           sequence_num,

                                           last_update_date,

                                           last_updated_by,

                                           creation_date,

                                           created_by,

                                           action_code,

                                           action_date,

                                           employee_id,

                                           note,

                                           object_revision_num,

                                           last_update_login,

                                           request_id,

                                           program_application_id,

                                           program_id,

                                           program_update_date,

                                           approval_path_id,

                                           offline_code)

              VALUES (document_id,

                      i.object_type_code,

                      i.object_sub_type_code,

                      i.sequence_num,

                      SYSDATE,

                      fnd_global.user_id,

                      SYSDATE,

                      fnd_global.user_id,

                      'FORWARD',

                      SYSDATE,

                      performer_id,

                      note,

                      0,

                      fnd_global.login_id,

                      0,

                      0,

                      0,

                      '',

                      i.approval_path_id,

                      '');

      --         DBMS_OUTPUT.put_line ('After Insert');

      END LOOP;


      COMMIT;

   --      DBMS_OUTPUT.put_line ('After Commit');

   EXCEPTION

      WHEN OTHERS

      THEN

         NULL;

   --         DBMS_OUTPUT.put_line ('Exception : ' || SUBSTR (SQLERRM, 1, 100));

   END PR_CREATE_ACTION_HISTORY;


   /* ------------End -----------------------------------*/

   PROCEDURE get_line_details (document_id     IN            VARCHAR2,

                               display_type    IN            VARCHAR2,

                               document        IN OUT NOCOPY VARCHAR2,

                               document_type   IN OUT NOCOPY VARCHAR2)

   AS

      l_item_type                 wf_items.item_type%TYPE;

      l_item_key                  wf_items.item_key%TYPE;


      TYPE po_number_tbl_type IS TABLE OF VARCHAR2 (20);


      TYPE po_date_tbl_type IS TABLE OF DATE;


      TYPE line_num_tbl_type IS TABLE OF VARCHAR2 (20);


      TYPE category_tbl_type IS TABLE OF VARCHAR2 (200);


      TYPE item_name_tbl_type IS TABLE OF VARCHAR2 (200);


      TYPE item_description_tbl_type IS TABLE OF VARCHAR2 (250);


      TYPE supplier_name_tbl_type IS TABLE OF VARCHAR2 (200);


      TYPE quantity_tbl_type IS TABLE OF NUMBER;


      TYPE unit_price_tbl_type IS TABLE OF NUMBER;


      TYPE unit_meas_lookup_code_tbl_type IS TABLE OF VARCHAR2 (20);


      TYPE amount_tbl_type IS TABLE OF NUMBER;


      TYPE project_name_tbl_type IS TABLE OF VARCHAR2 (20);


      TYPE task_number_tbl_type IS TABLE OF VARCHAR2 (25);


      TYPE expenditure_type_tbl_type IS TABLE OF VARCHAR2 (25);


      TYPE budget_details_tbl_type IS TABLE OF NUMBER;




      po_number_tbl               po_number_tbl_type;

      po_date_tbl                 po_date_tbl_type;

      line_num_tbl                line_num_tbl_type;

      category_tbl                category_tbl_type;

      item_name_tbl               item_name_tbl_type;

      item_description_tbl        item_description_tbl_type;

      supplier_name_tbl           supplier_name_tbl_type;

      quantity_tbl                quantity_tbl_type;

      unit_price_tbl              unit_price_tbl_type;

      unit_meas_lookup_code_tbl   unit_meas_lookup_code_tbl_type;

      amount_tbl                  amount_tbl_type;

      project_name_tbl            project_name_tbl_type;

      task_number_tbl             task_number_tbl_type;

      expenditure_type_tbl        expenditure_type_tbl_type;

      budget_details_tbl          budget_details_tbl_type;




      nl                          VARCHAR2 (1) := fnd_global.newline;

      l_approver_count            NUMBER;

      l_pr_header_id              NUMBER;

      l_document                  VARCHAR2 (32000) := '';


      CURSOR c1

      IS

           SELECT pha.segment1 po_number,

                  TO_CHAR (pha.creation_date, 'DD-MON-YYYY') po_date,

                  pla.line_num,

                  mc.concatenated_segments category,

                  msi.concatenated_segments item_name,

                  pla.item_description item_description,

                  ap.vendor_name supplier_name,

                  pla.quantity,

                  pla.unit_price,

                  pla.unit_meas_lookup_code,

                  (pla.quantity * pla.unit_price) amount,

                  (SELECT segment1

                     FROM pa_projects_all ppa

                    WHERE ppa.project_id = pda.project_id)

                     project_name,

                  (SELECT task_number

                     FROM pa_tasks ppa

                    WHERE ppa.task_id = pda.task_id)

                     task_number,

                  NVL (pda.EXPENDITURE_TYPE, pda.attribute1) EXPENDITURE_TYPE,

                  ROUND (

                     EST_PKG.get_rem_budget (

                        pda.task_id,

                        NVL (pda.EXPENDITURE_TYPE, pda.attribute1),

                        (pla.quantity * pla.unit_price)),

                     2)

                     budget_details

             FROM po_headers_all pha,

                  po_lines_all pla,

                  po_distributions_all pda,

                  mtl_categories_kfv mc,

                  mtl_system_items_kfv msi,

                  ap_suppliers ap,

                  ap_supplier_sites_all aps

            WHERE     pha.po_header_id = pla.po_header_id

                  AND mc.category_id(+) = pla.category_id

                  AND msi.inventory_item_id(+) = pla.item_id

                  AND pda.po_line_id = pla.po_line_id

                  AND ap.vendor_id(+) = pha.vendor_id

                  AND aps.vendor_site_id(+) = pha.vendor_site_id

                  AND (msi.organization_id =

                          oe_sys_parameters.VALUE ('MASTER_ORGANIZATION_ID',

                                                   pla.org_id)

                       OR msi.organization_id IS NULL)

                  AND pha.po_header_id = l_pr_header_id

         ORDER BY pha.po_header_id, pla.line_num;


      i                           NUMBER := 0;

      max_approver_dsp            NUMBER := 100;

      l_num_records_to_display    NUMBER;

      curr_len                    NUMBER := 0;

      prior_len                   NUMBER := 0;

      action_mesg                 fnd_new_messages.MESSAGE_TEXT%TYPE;

      lc_error                    VARCHAR2 (4000);

   BEGIN

      l_pr_header_id := TO_NUMBER (document_id);


      IF l_pr_header_id IS NULL

      THEN

         document := l_document;

         RETURN;

      END IF;



      IF max_approver_dsp IS NULL

      THEN

         max_approver_dsp := 100;

      END IF;


      OPEN c1;


      FETCH c1

      BULK COLLECT INTO po_number_tbl,

           po_date_tbl,

           line_num_tbl,

           category_tbl,

           item_name_tbl,

           item_description_tbl,

           supplier_name_tbl,

           quantity_tbl,

           unit_price_tbl,

           unit_meas_lookup_code_tbl,

           amount_tbl,

           project_name_tbl,

           task_number_tbl,

           expenditure_type_tbl,

           budget_details_tbl;


      l_approver_count := c1%ROWCOUNT;


      CLOSE c1;



      IF (l_approver_count > max_approver_dsp)

      THEN

         l_num_records_to_display := max_approver_dsp;

      ELSE

         l_num_records_to_display := l_approver_count;

         action_mesg := NULL;

      END IF;


      IF NVL (display_type, 'text/html') = 'text/html'

      THEN

         l_document :=

            nl || nl || '<!-- Requisition Lines -->' || nl || nl || '<P><B>';

         -- l_document := l_document || fnd_message.get_string ('po', 'po_wf_notif_action_history')|| nl;

         l_document := l_document || '</B>' || nl || nl || '<P>';


         IF (action_mesg IS NOT NULL)

         THEN

            l_document := l_document || action_mesg || '<P>' || nl;

         END IF;


         l_document :=

               l_document

            || '<TABLE class="xxpostyle" '

            || ' Purchase Order Line Details '

            || '">'

            || nl;

         l_document := l_document || '<TR>';

         --         l_document := l_document || '<TH id="Url_1">-</B> </TH>' || nl;

         l_document :=

            l_document || '<TH id="Url_1"><B>PO Number</B> </TH>' || nl;

         l_document :=

            l_document || '<TH id="Url_1"><B>Line Number</B> </TH>' || nl;

         l_document :=

            l_document || '<TH id="Url_1"><B>Category</B> </TH>' || nl;

         l_document :=

            l_document || '<TH id="Url_1"><B>Item Name</B> </TH>' || nl;

         l_document :=

               l_document

            || '<TH id="Url_1"><B>Item Description</B> </TH>'

            || nl;

         l_document :=

            l_document || '<TH id="Url_1"><B>Supplier Name</B> </TH>' || nl;

         --         l_document :=

         --            l_document || '<TH id="Url_1"><B>Site Name</B> </TH>' || nl;

         l_document :=

            l_document || '<TH id="Url_1"><B>Quantity</B> </TH>' || nl;

         l_document :=

            l_document || '<TH id="Url_1"><B>Unit Price</B> </TH>' || nl;

         l_document := l_document || '<TH id="Url_1"><B>Unit</B> </TH>' || nl;

         l_document :=

            l_document || '<TH id="Url_1"><B>Amount</B> </TH>' || nl;

         l_document :=

            l_document || '<TH id="Url_1"><B>Project No.</B> </TH>' || nl;

         l_document :=

            l_document || '<TH id="Url_1"><B>BOQ Item No.</B> </TH>' || nl;

         l_document :=

            l_document || '<TH id="Url_1"><B>Cost Code</B> </TH>' || nl;

         l_document :=

               l_document

            || '<TH id="Url_1"><B>Remaining Budget</B> </TH>'

            || nl;

         l_document := l_document || '</TR>' || nl;

         curr_len := LENGTHB (l_document);

         prior_len := curr_len;



         FOR i IN 1 .. l_num_records_to_display

         LOOP

            IF (curr_len + (2 * (curr_len - prior_len))) >= 32000

            THEN

               EXIT;

            END IF;



            l_document := l_document || '<TR>' || nl;

            l_document :=

                  l_document

               || '<TD nowrap align=center headers="po_num_1">'

               || NVL (po_number_tbl (i), '-')

               || '</TD>'

               || nl;


            l_document :=

                  l_document

               || '<TD nowrap headers="linenum_1">'

               || NVL (line_num_tbl (i), '-')

               || '</TD>'

               || nl;


            l_document :=

                  l_document

               || '<TD nowrap headers="category_1">'

               || NVL (category_tbl (i), '-')

               || '</TD>'

               || nl;


            l_document :=

                  l_document

               || '<TD nowrap headers="item_name_1">'

               || NVL (item_name_tbl (i), '-')

               || '</TD>'

               || nl;


            l_document :=

                  l_document

               || '<TD nowrap headers="item_description_1">'

               || NVL (item_description_tbl (i), '-')

               || '</TD>'

               || nl;

            l_document :=

                  l_document

               || '<TD nowrap headers="supplier_name_1">'

               || NVL (supplier_name_tbl (i), '-')

               || '</TD>'

               || nl;


            l_document :=

                  l_document

               || '<TD nowrap headers="quantity_1">'

               || NVL (quantity_tbl (i), '0')

               || '</TD>'

               || nl;


            l_document :=

               l_document || '<TD nowrap headers="unit_price_1">'

               || NVL (

                     TO_CHAR (unit_price_tbl (i), '999,999,999,999,999.00'),

                     0)

               || '</TD>'

               || nl;



            l_document :=

                  l_document

               || '<TD nowrap headers="unit_meas_1">'

               || NVL (unit_meas_lookup_code_tbl (i), '-')

               || '</TD>'

               || nl;


            l_document :=

                  l_document

               || '<TD nowrap headers="amount_1">'

               || NVL (amount_tbl (i), 0)

               || '</TD>'

               || nl;


            l_document :=

                  l_document

               || '<TD nowrap headers="project_name_1">'

               || NVL (project_name_tbl (i), '-')

               || '</TD>'

               || nl;


            l_document :=

                  l_document

               || '<TD nowrap headers="task_number_1">'

               || NVL (task_number_tbl (i), '-')

               || '</TD>'

               || nl;


            l_document :=

                  l_document

               || '<TD nowrap headers="expenditure_type_1">'

               || NVL (expenditure_type_tbl (i), '-')

               || '</TD>'

               || nl;


            l_document :=

               l_document || '<TD nowrap headers="budget_details_1">'

               || NVL (

                     TO_CHAR (budget_details_tbl (i),

                              '999,999,999,999,999.00'),

                     '0')

               || '</TD>'

               || nl;


            l_document := l_document || '</TR>' || nl;

            curr_len := LENGTHB (l_document);

         END LOOP;


         l_document := l_document || '</TABLE></P>' || nl;

         document := l_document;

         document_type := 'text/html';

      END IF;

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line ('Error');

         lc_error := SQLERRM;

         document := 'Error';

   END get_line_details;

END;

/


No comments:

Post a Comment