Oracle E-Business Suite: March 2013

DECLARE
   CURSOR c1
   IS
      SELECT *
        FROM temp_gl_code;

   v_chart_of_accounts_id    VARCHAR2 (200);
   v_n_code_combination_id   VARCHAR2 (200);
BEGIN
   FOR i in c1
   LOOP
      SELECT gsob.chart_of_accounts_id      -- To Get the Chart of Accounts id
        INTO v_chart_of_accounts_id
        FROM gl_sets_of_books gsob
       WHERE gsob.set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');

      v_n_code_combination_id :=
         fnd_flex_ext.get_ccid ('SQLGL',
                                'GL#',
                                v_chart_of_accounts_id,
                                TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                                i.CODE_COBIN
                               );

      IF v_n_code_combination_id <> 0
      THEN
         DBMS_OUTPUT.put_line ('success');
      ELSE
         DBMS_OUTPUT.put_line ('failure');
      END IF;
   END LOOP;
END;

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

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

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

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

Oracle E-Business Suite

Thursday, March 7, 2013

API - Create New Code Combination In GL

PERSONALIZATION - REQUISITION AMOUNT AND PROJECT BUDGET AMOUNT VALIDATION

Followers

Traffic