PERSONALIZATION - REQUISITION AMOUNT AND PROJECT BUDGET AMOUNT VALIDATION

Requirment :

If user enter requisition amount more than project budget amount then the project amount should not save.

For this purpose personalization is done on the Requisition form. Here shows the steps of personalization.

Navigation:           

  

Purchasing--> Requisitions--> Requisitions

The screen to do form personalization is

The navigation for personalization is given above

Personalization steps are given below

DESCRIPTION : Project Budget Amount line

 

Trigger Event: WHEN VALIDATE RECORD

Trigger Object: LINES

Condition:          1=1

  

Now click the actions tab. Four global variables are created for the requirement. They are

1. Global Variable Name: XX_BUDG_AMT

               Property Name            : VALUE

                Value                             : =(select apps.xx_req_pa_budget_amt(:DISTRIBUTIONS.PROJECT_ID,:DISTRIBUTIONS.TASK_ID,:LINES.UNIT_PRICE,:DISTRIBUTIONS.REQ_LINE_QUANTITY,:DISTRIBUTIONS.DISTRIBUTION_ID,:LINES.REQUISITION_LINE_ID) from dual)

For this Global Variable a function is created. The query is given as

CREATE OR REPLACE FUNCTION APPS.xx_req_pa_budget_amt (

   p_project_id        IN   NUMBER,

   p_task_id           IN   NUMBER,

   p_unit_price        IN   NUMBER,

   p_quantity          IN   NUMBER,

   p_distribution_id   IN   NUMBER,

   p_line_id           IN   NUMBER

)

   RETURN CHAR

IS

   v_project_id        NUMBER;

   v_task_id           NUMBER;

   v_unit_price        NUMBER;

   v_quantity          NUMBER;

   v_distribution_id   NUMBER;

   l_budget_amt        NUMBER;

   l_pr_amt            NUMBER;

   l_total_pr_amt      NUMBER;

BEGIN

   v_project_id := p_project_id;

   v_task_id := p_task_id;

   v_unit_price := p_unit_price;

   v_quantity := p_quantity;

   v_distribution_id := p_distribution_id;

   IF p_distribution_id IS NULL

   THEN

      SELECT project_id, task_id, distribution_id,req_line_quantity

        INTO v_project_id, v_task_id, v_distribution_id,v_quantity

        FROM po_req_distributions_all

       WHERE requisition_line_id = p_line_id AND ROWNUM = 1;

   END IF;

   IF p_unit_price IS NULL

   THEN

      SELECT prla.unit_price

        INTO v_unit_price

        FROM po_requisition_lines_all prla, po_req_distributions_all prda

       WHERE prla.requisition_line_id = prda.requisition_line_id

         AND prda.distribution_id = p_distribution_id

         AND ROWNUM = 1;

   END IF;

   BEGIN

      SELECT NVL (SUM (burdened_cost), 0)

        INTO l_budget_amt

        FROM pa_budget_lines_v

       WHERE project_id = v_project_id

         AND task_id = v_task_id

         AND budget_version_id =

                (SELECT MAX (budget_version_id)

                   FROM pa_budget_versions pbv

                  WHERE pbv.project_id = v_project_id

                    AND pbv.budget_type_code = 'AC'

                    AND pbv.budget_status_code IN ('W', 'S'));

   EXCEPTION

      WHEN NO_DATA_FOUND

      THEN

         l_budget_amt := 0;

   END;

   BEGIN

      SELECT NVL (SUM (prl.quantity * prl.unit_price), 0)

        INTO l_pr_amt

        FROM po_req_distributions_all prd, po_requisition_lines_all prl

       WHERE prd.requisition_line_id = prl.requisition_line_id

         AND prd.project_id = v_project_id

         AND prd.task_id = v_task_id

         AND prd.distribution_id != NVL (v_distribution_id, 99999);

   EXCEPTION

      WHEN NO_DATA_FOUND

      THEN

         l_pr_amt := 0;

   END;

   l_total_pr_amt := l_pr_amt + (v_quantity * v_unit_price);

   --fnd_message.set_string('Budget Amount is '||l_budget_amt|| ' AED. Previously entered value including current PR Amount is '||l_total_pr_amt||' AED');

   --fnd_message.show;

   IF l_budget_amt != 0 AND l_budget_amt < l_total_pr_amt

   THEN

      RETURN (   'The entered amount is exceeding the allocated budget amount. Budget Amount is '

              || l_budget_amt

              || ' AED. Previously entered value including current PR Amount is '

              || l_total_pr_amt

              || ' AED'

             );

   ELSE

      RETURN NULL;

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN NULL;

END;

/

DESCRIPTION : Project Budget Amount dist

Trigger Event: WHEN VALIDATE RECORD

Trigger Object: DISTRIBUTIONS

Condition:          1=1

Now click the actions tab. Four global variables are created for the requirement. They are

Global Variable Name: XX_BUDG_AMT_DIST

               Property Name            : VALUE

                Value                             : =(select apps.xx_req_pa_budget_amt(:DISTRIBUTIONS.PROJECT_ID,:DISTRIBUTIONS.TASK_ID,:LINES.UNIT_PRICE,:DISTRIBUTIONS.REQ_LINE_QUANTITY,:DISTRIBUTIONS.DISTRIBUTION_ID,:LINES.REQUISITION_LINE_ID) from dual)

DESCRIPTION : Project Budget Amount MSG

Trigger Event: WHEN VALIDATE RECORD

Trigger Object: LINES

Condition:          :global.XX_BUDG_AMT  is  NOT NULL

Now click the actions tab. Four global variables are created for the requirement. They are

1.The message to be shown while inactivating the item is given as

                               Message Type  : Show

                               Message :          =:global.XX_BUDG_AMT

2.The Builtin should be called  Builtin Type : RAISE_FORM_TRIGGER_FAILURE.

DESCRIPTION : Project Budget Amount Msg Dist

Trigger Event: WHEN VALIDATE RECORD

Trigger Object: DISTRIBUTIONS

Condition:          :global.XX_BUDG_AMT_DIST  is  NOT NULL

Now click the actions tab. Four global variables are created for the requirement. They are

1.The message to be shown while inactivating the item is given as

                               Message Type  : Show

                               Message :          =:global.XX_BUDG_AMT_DIST4

2.The Builtin should be called

                               Builtin Type : RAISE_FORM_TRIGGER_FAILURE.

Validate these conditions and save it.

The form personalization works as above