Thursday, August 28, 2014

TO Update the Oracle Project Budget Line and Baseline Project Through API

DECLARE
   --> variables needed for API standard parameters
   l_budget_lines_in           pa_budget_pub.budget_line_in_tbl_type;
   l_budget_lines_in_rec       pa_budget_pub.budget_line_in_rec_type;
   l_budget_lines_out          pa_budget_pub.budget_line_out_tbl_type;
   l_api_version_number        NUMBER                                 := 1.0;
   l_commit                    VARCHAR2 (1)                           := 'T';
   l_return_status             VARCHAR2 (1);
   l_init_msg_list             VARCHAR2 (1)                           := 'T';
   l_msg_count                 NUMBER                                 := 0;
   l_msg_data                  VARCHAR2 (2000);
   l_data                      VARCHAR2 (2000);
   l_msg_index_out             NUMBER;
   v_task_id                   apps.pa_tasks.task_id%TYPE;
   L_LINE_STATUS               NUMBER                                 := 0;
  
   --> variables needed for Oracle Project specific parameters
   l_user_id                   NUMBER;
   l_responsibility_id         NUMBER;
  
   l_pm_product_code           VARCHAR2 (10);
  
   l_budget_type_code          VARCHAR2 (50);
   l_pa_project_id             NUMBER;
   l_pm_project_reference       VARCHAR2(40);
   l_workflow_started          VARCHAR2 (1);
  
   l_pa_task_id                NUMBER;
   l_resource_list_member_id   NUMBER;
   l_raw_cost                  NUMBER;
   l_quantity                  NUMBER;
  
   API_ERROR                   EXCEPTION;
  
BEGIN
   -- PRODUCT RELATED DATA
   l_pm_product_code := 'GMS';
  
   -- BUDGET DATA
   l_pa_project_id              := 6271;
   l_budget_type_code             := 'FC';
   l_pm_project_reference        := NULL;
  
   -- BUDGET LINE DATA
   l_pa_task_id                 := 405229;
   l_resource_list_member_id    := 1034;
   l_raw_cost                    := 1000;
   l_quantity                    := 1000;

   ---->>================================================================
   -- SET GLOBAL INFO
   SELECT user_id,
          responsibility_id
     INTO l_user_id,
          l_responsibility_id
     FROM pa_user_resp_v
    WHERE user_name = 'RCHINTHAPATLA';

   PA_INTERFACE_UTILS_PUB.SET_GLOBAL_INFO (p_api_version_number      => 1.0,
                                           p_responsibility_id       => l_responsibility_id,
                                           p_user_id                 => l_user_id,
                                           p_calling_mode              => 'AMG',
                                           p_msg_count               => l_msg_count,
                                           p_msg_data                => l_msg_data,
                                           p_return_status           => l_return_status
                                          );

   IF l_return_status != 'S'
   THEN
      RAISE API_ERROR;
   END IF;

   ---->>====================================================================
  BEGIN
    SELECT DISTINCT 1 INTO L_LINE_STATUS
    FROM PA_BUDGET_LINES_V
    WHERE 1=1
    AND TASK_ID  = l_pa_task_id
    ;
  END;  
 
         DBMS_OUTPUT.put_line ('L_LINE_STATUS :' || L_LINE_STATUS);

   ---->>============================================================
   --> CALLING UPDATE BUDGET LINE
        PA_BUDGET_PUB.UPDATE_BUDGET_LINE
                                    (p_api_version_number           => l_api_version_number,
                                     p_commit                       => l_commit,
                                     p_init_msg_list                => l_init_msg_list,
                                     p_msg_count                    => l_msg_count,
                                     p_msg_data                     => l_msg_data,
                                     p_return_status                => l_return_status,
                                     p_pm_product_code              => l_pm_product_code,
                                     p_pa_project_id                => l_pa_project_id,
                                     p_budget_type_code             => l_budget_type_code,
                                     p_pa_task_id                   => l_pa_task_id,
                                     p_resource_list_member_id      => l_resource_list_member_id,
                                     p_raw_cost                     => l_raw_cost,
                                     p_quantity                     => l_quantity
                                    );

       IF l_return_status != 'S'
       THEN
          RAISE API_ERROR;
             DBMS_OUTPUT.PUT_LINE('MSG_COUNT: '||TO_CHAR(L_MSG_COUNT));
       END IF;

       DBMS_OUTPUT.put_line ('Update Budget Line Status :' || l_return_status);

   ---->>============================================================
   --> CALLING BASELINE BUDGET
   IF l_return_status = 'S'
   THEN
   PA_BUDGET_PUB.BASELINE_BUDGET (p_api_version_number        => l_api_version_number,
                                  p_commit                    => l_commit,
                                  p_init_msg_list             => l_init_msg_list,
                                  p_msg_count                 => l_msg_count,
                                  p_msg_data                  => l_msg_data,
                                  p_return_status             => l_return_status,
                                  p_workflow_started          => l_workflow_started,
                                  p_pm_product_code           => l_pm_product_code,
                                  p_pa_project_id             => l_pa_project_id,
                                  p_pm_project_reference      => l_pm_project_reference,
                                  p_budget_type_code          => l_budget_type_code
                                 );
                                
   IF l_return_status != 'S'
   THEN
      RAISE API_ERROR;
         DBMS_OUTPUT.PUT_LINE('MSG_COUNT: '||TO_CHAR(L_MSG_COUNT));
   END IF;

   DBMS_OUTPUT.put_line ('BASELINE BUDGET STATUS :' || l_return_status);
  
   END IF;
   ---->>==================================================================
  
--> HANDLE EXCEPTIONS
EXCEPTION
   WHEN API_ERROR
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         pa_interface_utils_pub.get_messages (p_msg_data           => l_msg_data,
                                              p_data               => l_data,
                                              p_msg_count          => l_msg_count,
                                              p_msg_index_out      => l_msg_index_out
                                             );
         DBMS_OUTPUT.put_line ('Error Msg : ' || l_data);
         DBMS_OUTPUT.put_line ('Error Msg : ' || l_msg_data);
      END LOOP;
     
   WHEN OTHERS
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         pa_interface_utils_pub.get_messages (p_msg_data           => l_msg_data,
                                              p_data               => l_data,
                                              p_msg_count          => l_msg_count,
                                              p_msg_index_out      => l_msg_index_out
                                             );
         DBMS_OUTPUT.put_line ('Error Msg : ' || l_data);
         DBMS_OUTPUT.put_line ('Error Msg : ' || l_msg_data);
      END LOOP;
     

END

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect