Orders created from the Spares Management Queries for alerts

Orders created from the Spares Management Queries

SELECT B.requirement_header_id,a.ORDER_LINE_ID,  a.ORDER_BY_DATE, a.* FROM CSP_REQUIREMENT_LINES A , CSP_REQUIREMENT_HEADERS B  WHERE
A.REQUIREMENT_HEADER_ID = B.REQUIREMENT_HEADER_ID
AND B.ORDER_TYPE_ID = <XXX>
order by   a.ORDER_BY_DATE desc;

select OH.ORIG_SYS_DOCUMENT_REF

       ,OH.ORDER_NUMBER

       ,OH.CREATION_DATE

       ,PPF.FULL_NAME

       ,FU.EMPLOYEE_ID

from OE_ORDER_HEADERS_ALL OH

     ,PO_REQUISITION_HEADERS_ALL POH

     ,FND_USER FU

     ,PER_ALL_PEOPLE_F PPF

     where 1                    =1

and OH.ORDER_TYPE_ID       =xxx0

and OH.SOURCE_DOCUMENT_ID  = POH.REQUISITION_HEADER_ID

and POH.TYPE_LOOKUP_CODE   = 'INTERNAL'

and POH.INTERFACE_SOURCE_CODE   = 'CSP'

and FU.USER_ID                  =OH.CREATED_BY

and FU.EMPLOYEE_ID              =PPF.PERSON_ID

and TRUNC(sysdate) between TRUNC(PPF.EFFECTIVE_START_DATE) and TRUNC(PPF.EFFECTIVE_END_DATE)

and TRUNC(OH.CREATION_DATE)     = TRUNC(sysdate)

inv_item_sub_default_pkg.insert_upd_item_sub_defaults Update Default ubinventory in Order Management tab : Item Update conversion

begin
 ip_trx_from_id   IN       NUMBER,
      ip_trx_to_id     IN       NUMBER

   IS
      --update assignment variables
      CURSOR cur_eur_upd
      IS
          SELECT   *
             FROM XX_EUR_ITEM_SHIP_SUBINV_STG
            where 1 = 1
              and STATUS_STG = 'V'
              and TRANSACTION_ID_STG between ip_trx_from_id and ip_trx_to_id
         ORDER BY transaction_id_stg;
   x_return_status   VARCHAR2 (100);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (2000);
   p_commit          VARCHAR2 (100)  DEFAULT fnd_api.g_false;
   l_msg_dummy       VARCHAR2 (2000);
   x_output          VARCHAR2 (2000);
   l_resp_id NUMBER;
   l_resp_appl_id NUMBER;
    l_user_id          NUMBER                         := xxx;
   BEGIN
      BEGIN
           select RESPONSIBILITY_ID, APPLICATION_ID
           INTO l_resp_id, l_resp_appl_id
           from FND_RESPONSIBILITY_TL
          WHERE responsibility_name  =  'Inventory';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
                 XX_asa.g_error_message :=
               'Entered into Exception when getting the responsibiliy id and applicationid';
                  END;
      fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
/*====================================================================
           updating  assignments DFF
 ==============================================================*/
      FOR item_rec IN cur_eur_upd
      LOOP
         BEGIN
         
            inv_item_sub_default_pkg.insert_upd_item_sub_defaults
                          (x_return_status          => x_return_status,
                           x_msg_count              => x_msg_count,
                           x_msg_data               => x_msg_data,
                           p_organization_id        => item_rec.ORGANIZATION_ID,
                           p_inventory_item_id      => item_rec.INVENTORY_ITEM_ID,
                           p_subinventory_code      => 'FG_xxbujh',
                           --shipping_subinv2,
                           p_default_type           => 1,
                           p_creation_date          => SYSDATE,
                           p_created_by             => xxx,
                           p_last_update_date       => SYSDATE,
                           p_last_updated_by        => xxxx,
                           p_process_code           => 'SYNC',
                           p_commit                 => p_commit
                          );
      IF x_msg_count > 0
      THEN
         FOR j IN 1 .. x_msg_count
         LOOP
            fnd_msg_pub.get (j, fnd_api.g_false, x_msg_data, l_msg_dummy);
            x_output := ('Msg' || TO_CHAR (j) || ': ' || x_msg_data);
            FND_FILE.put_line(FND_FILE.output,SUBSTR (x_output, 1, 255));
         END LOOP;
      END IF;
           
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               PRINT ('When Others Error - Update shipping subinventory in EUR' || SQLERRM);
                        END;