Bom Components end date api (BOM Update)

CREATE TABLE PURE_BOM_UPDATE_STG
(
  ORGANIZATION_NAME      VARCHAR2(100 BYTE),
  BOM_CODE               VARCHAR2(200 BYTE),
  BOM_DESCRIPTION        VARCHAR2(500 BYTE),
  ALTERNATIVE_BOM        VARCHAR2(100 BYTE),
  COMPONENT_CODE         VARCHAR2(250 BYTE),
  COMPONENT_DESCRIPTION  VARCHAR2(500 BYTE),
  OPERATION_SEQ_NUM      NUMBER,
  ITEM_SEQUENCE          NUMBER,
  COMPONENT_QUANTITY     NUMBER,
  SUPPLY_SUBINVENTORY    VARCHAR2(500 BYTE),
  STATUS_MESSAGE         VARCHAR2(3000 BYTE),
  STATUS_FLAG            VARCHAR2(10 BYTE),
  PROCESS_FLAG           VARCHAR2(10 BYTE)
)


CREATE OR REPLACE PROCEDURE pure_bom_update_api
IS
---DECLARE
   l_bom_header_rec             bom_bo_pub.bom_head_rec_type
                                          := bom_bo_pub.g_miss_bom_header_rec;
   l_bom_revision_tbl           bom_bo_pub.bom_revision_tbl_type
                                        := bom_bo_pub.g_miss_bom_revision_tbl;
   l_bom_component_tbl          bom_bo_pub.bom_comps_tbl_type
                                       := bom_bo_pub.g_miss_bom_component_tbl;
   l_bom_ref_designator_tbl     bom_bo_pub.bom_ref_designator_tbl_type
                                  := bom_bo_pub.g_miss_bom_ref_designator_tbl;
   l_bom_sub_component_tbl      bom_bo_pub.bom_sub_component_tbl_type
                                   := bom_bo_pub.g_miss_bom_sub_component_tbl;
   l_error_message_list         error_handler.error_tbl_type;
   l_x_bom_header_rec           bom_bo_pub.bom_head_rec_type;
   l_x_bom_revision_tbl         bom_bo_pub.bom_revision_tbl_type;
   l_x_bom_component_tbl        bom_bo_pub.bom_comps_tbl_type;
   l_x_bom_ref_designator_tbl   bom_bo_pub.bom_ref_designator_tbl_type;
   l_x_bom_sub_component_tbl    bom_bo_pub.bom_sub_component_tbl_type;
   l_x_return_status            VARCHAR2 (2000);
   l_x_msg_count                NUMBER;
   i                            NUMBER                                 := 1;
   l_count                      NUMBER;
   l_status_flag                VARCHAR2 (10)                         := NULL;
   l_error_message              VARCHAR2 (20000)                      := NULL;
   l_process_flag               VARCHAR2 (10);
   l_organization_id            NUMBER;
   l_start_eff_date             DATE;
   l_user_id                    NUMBER                                 := -1;
   l_resp_id                    NUMBER                                 := -1;
   l_application_id             NUMBER                                 := -1;
   l_user_name                  VARCHAR2 (1000)                     := 'PURE';
   l_resp_name                  VARCHAR2 (1000)                 := 'PURE_BOM';
   l_error_table                error_handler.error_tbl_type;
   l_return_status              VARCHAR2 (10)                         := NULL;
   l_msg_count                  NUMBER                                 := 0;
   l_assembly_item_id           NUMBER                                := NULL;
   l_organization_code          VARCHAR2 (100)                        := NULL;
   l_component_item_id          NUMBER                                := NULL;
   l_wip_supply_type            VARCHAR2 (50)                         := NULL;
   ---NUMBER;
   l_dis_date                   DATE;
   l_bill_sequence_id           NUMBER                                := NULL;

   CURSOR c_process
   IS
      SELECT   cust.process_flag, ood.organization_code,
               ood.organization_name, ood.organization_id,
               bom.assembly_item_id, msi.segment1 bom_code, msi.description,
               bic.item_num, bic.operation_seq_num, bic.component_item_id,
               msi1.segment1 component_code, ml.meaning supply_type,
               bic.wip_supply_type, bic.effectivity_date, bic.disable_date,
               cust.alternative_bom
          FROM mtl_system_items_b msi,
               org_organization_definitions ood,
               bom_bill_of_materials bom,
               bom_inventory_components bic,
               mtl_system_items_b msi1,
               mfg_lookups ml,
               pure_bom_update_stg cust
         WHERE bom.assembly_item_id = msi.inventory_item_id
           AND bom.bill_sequence_id = bic.bill_sequence_id
           AND msi.organization_id = ood.organization_id
           AND bom.organization_id = ood.organization_id
           AND msi1.inventory_item_status_code = 'Active'
           AND bic.component_item_id = msi1.inventory_item_id
           AND msi1.organization_id = ood.organization_id
           AND ml.lookup_code(+) = bic.wip_supply_type
           AND ml.lookup_type(+) = 'WIP_SUPPLY'
           AND ood.organization_name = cust.organization_name
           AND msi.segment1 = cust.bom_code
           AND msi1.segment1 = cust.component_code
           AND bic.operation_seq_num = cust.operation_seq_num
           AND item_sequence = item_num
           AND NVL (bom.alternate_bom_designator, 'x') =
                                               NVL (cust.alternative_bom, 'x')
           ---AND cust.status_flag <> 'C'
           AND cust.bom_code IN
                ('MGCRHDBR00200020', 'MGCRHDBR00200045', 'MGCRHDCN00001008')
      ORDER BY bom_code ASC;
-- AND process_flag NOT IN ('C', NULL);
BEGIN
   DBMS_OUTPUT.ENABLE (1000000);

   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user_name;

   -- Get the application_id and responsibility_id
   SELECT application_id, responsibility_id
     INTO l_application_id, l_resp_id
     FROM fnd_responsibility
    WHERE responsibility_key = l_resp_name;

   -- intiialize applications information
   fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);
  --- i := 0;

   FOR j IN c_process
   LOOP
      i := 1;
      l_error_message := NULL;                           --- startt C_PROCESS

      IF j.disable_date IS NOT NULL
      THEN
         UPDATE pure_bom_update_stg
            SET status_flag = 'C',                       ---l_x_return_status,
                status_message = 'Already end dated ',
                process_flag = 'C'
          WHERE organization_name = j.organization_name
            AND bom_code = j.bom_code
            AND component_code = j.component_code
            AND operation_seq_num = j.operation_seq_num
            AND item_sequence = j.item_num;

         ---dbms_output.put_line(disabled component )||;
         COMMIT;
      END IF;

--      BEGIN
--         SELECT COUNT (*)
--           INTO l_count
--           FROM bom_inventory_components
--          WHERE component_item_id = j.component_item_id
--            ---and ORGANIZATION_ID=j.ORGANIZATION_ID
--            AND item_num = j.item_num
--            AND operation_seq_num = j.operation_seq_num;

      --         IF l_count =0
--         THEN
--            UPDATE pure_bom_update_stg
--               SET status_flag = 'E',                    ---l_x_return_status,
--                   status_message = 'Component doesnot exist ',
--                   process_flag = 'I'
--             WHERE organization_name = j.organization_name
--               AND bom_code = j.bom_code
--               AND component_code = j.component_code
--               AND operation_seq_num = j.operation_seq_num
--               AND item_sequence = j.item_num;

      --            COMMIT;
--         END IF;
--      END;
      DBMS_OUTPUT.put_line ('l_start_eff_date ' || l_start_eff_date);
      DBMS_OUTPUT.put_line ('j.wip_supply_type ' || l_wip_supply_type);
      l_bom_component_tbl (i) := bom_bo_pub.g_miss_bom_component_rec;
      l_bom_component_tbl (i).transaction_type := 'UPDATE';
      l_bom_component_tbl (i).organization_code := j.organization_code;
      l_bom_component_tbl (i).assembly_item_name := j.bom_code;
      l_bom_component_tbl (i).start_effective_date := j.effectivity_date;
      --- TO_DATE (j.effectivity_date, 'DD-MON-YYYY HH24:MI:SS');
      l_bom_component_tbl (i).disable_date := SYSDATE;
      --- TO_DATE (SYSDATE, 'DD-MON-YYYY HH24:MI:SS');
      l_bom_component_tbl (i).component_item_name := j.component_code;
      l_bom_component_tbl (i).item_sequence_number := j.item_num;
      l_bom_component_tbl (i).operation_sequence_number := j.operation_seq_num;
      l_bom_component_tbl (i).return_status := NULL;
      l_bom_component_tbl (i).wip_supply_type := j.wip_supply_type;
      --- j.supply_type;
      l_bom_component_tbl (i).alternate_bom_code :=
                                                 NVL (j.alternative_bom, NULL);

      IF j.disable_date IS NULL
      THEN
         BEGIN
            DBMS_OUTPUT.put_line ('Calling API...');
            DBMS_OUTPUT.put_line (' j.organization_code'
                                  || l_organization_code
                                 );
            DBMS_OUTPUT.put_line (' j.bom_code' || j.bom_code);
            DBMS_OUTPUT.put_line (' l_start_eff_date' || j.effectivity_date);
            DBMS_OUTPUT.put_line (' j.component_code ' || j.component_code);
            DBMS_OUTPUT.put_line (' j.item_sequence' || j.item_num);
            DBMS_OUTPUT.put_line (' j.operation_seq_num'
                                  || j.operation_seq_num
                                 );
            DBMS_OUTPUT.put_line (' wip_supply_type' || j.wip_supply_type);
        --- IF j.disable_date IS NOT NULL
      ---THEN
---bms_output.put_line(' j.bom_code' || j.bom_code);
            DBMS_OUTPUT.put_line ('Line count....' || i || j.bom_code);
            error_handler.initialize;
            bom_bo_pub.process_bom
                      (p_bo_identifier               => 'BOM',
                       p_api_version_number          => 1.0,
                       p_init_msg_list               => TRUE,
                       p_bom_header_rec              => l_bom_header_rec,
                       p_bom_revision_tbl            => l_bom_revision_tbl,
                       p_bom_component_tbl           => l_bom_component_tbl,
                       p_bom_ref_designator_tbl      => l_bom_ref_designator_tbl,
                       p_bom_sub_component_tbl       => l_bom_sub_component_tbl,
                       x_bom_header_rec              => l_x_bom_header_rec,
                       x_bom_revision_tbl            => l_x_bom_revision_tbl,
                       x_bom_component_tbl           => l_x_bom_component_tbl,
                       x_bom_ref_designator_tbl      => l_x_bom_ref_designator_tbl,
                       x_bom_sub_component_tbl       => l_x_bom_sub_component_tbl,
                       x_return_status               => l_x_return_status,
                       x_msg_count                   => l_x_msg_count,
                       p_debug                       => 'N',
                       p_output_dir                  => '',
                       p_debug_filename              => ''
                      );
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_message :=
                      l_error_message || 'Error after processing ' || SQLERRM;

               ----- DBMS_OUTPUT.put_line ('1' || l_error_message);
               UPDATE pure_bom_update_stg
                  SET status_flag = 'E',                 ---l_x_return_status,
                      status_message = l_error_message,
                      process_flag = 'I'
                WHERE organization_name = j.organization_name
                  AND bom_code = j.bom_code
                  AND component_code = j.component_code
                  AND operation_seq_num = j.operation_seq_num
                  AND item_sequence = j.item_num;

               --AND ROWID = j.ROWID;
               COMMIT;
         END;
      END IF;

      error_handler.get_message_list (l_error_message_list);
 
      DBMS_OUTPUT.put_line ('Return Status = ' || l_x_return_status);
      DBMS_OUTPUT.put_line ('Message Count = ' || l_x_msg_count);

      IF l_x_return_status <> 'S'
      THEN
         ----DBMS_OUTPUT.put_line ('3' || l_error_message);
         FOR k IN 1 .. l_x_msg_count
         LOOP
            ----- DBMS_OUTPUT.put_line ('4' || l_error_message);
            DBMS_OUTPUT.put_line
                               (   TO_CHAR (k)
                                || ' MESSAGE TEXT '
                                || SUBSTR
                                        (l_error_message_list (k).MESSAGE_TEXT,
                                         1,
                                         250
                                        )
                               );
            DBMS_OUTPUT.put_line (   TO_CHAR (k)
                                  || ' MESSAGE TYPE '
                                  || l_error_message_list (k).MESSAGE_TYPE
                                 );
            l_error_message :=
                  l_error_message
               || TO_CHAR (k)
               || ' MESSAGE TEXT '
               || SUBSTR (l_error_message_list (k).MESSAGE_TEXT, 1, 250);
            ROLLBACK;

            UPDATE pure_bom_update_stg
               SET status_flag = l_x_return_status,
                   status_message = l_error_message,
                   process_flag = 'I'
             WHERE organization_name = j.organization_name
               AND bom_code = j.bom_code
               AND component_code = j.component_code
               AND operation_seq_num = j.operation_seq_num
               AND item_sequence = j.item_num;

            COMMIT;
         END LOOP;

         DBMS_OUTPUT.put_line (l_error_message);
      ELSE
         IF l_x_return_status = 'S'
         THEN
            UPDATE pure_bom_update_stg
               SET status_flag = 'C',                   ----l_x_return_status,
                   status_message = 'END DATED',
                   process_flag = 'C'
             WHERE organization_name = j.organization_name
               AND bom_code = j.bom_code
               AND component_code = j.component_code
               AND operation_seq_num = j.operation_seq_num
               AND item_sequence = j.item_num;
            COMMIT;
         END IF;
      END IF;

   END LOOP;                                                  ---END C_PROCESS
END;

SELECT 1
  FROM DUAL