APPS.WMS_RULE_PVT_EXT_PSETJ SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 67

Procedure update_mmtt(
  p_task_id           IN    NUMBER
, p_operation_plan_id IN    NUMBER
, x_return_status     OUT   NOCOPY VARCHAR2
) is
    l_debug  NUMBER;

Line: 82

         trace(' Entering procedure update_mmtt  '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);

Line: 87

     update mtl_material_transactions_temp mmtt
        set mmtt.operation_plan_id   = p_operation_plan_id
     where  mmtt.transaction_temp_id = p_task_id;

Line: 92

         trace(' Successfully updated MMTT record with operation_plan_id = ' || p_operation_plan_id, 1);

Line: 96

        trace(' Exiting procedure update_mmtt  '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);

Line: 105

            trace(' Unable to update MMTT due to Error : ' || sqlerrm(sqlcode), 1);

Line: 106

            trace(' Exiting procedure update_mmtt  '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);

Line: 110

end update_mmtt;

Line: 195

      select rules.rule_id, mmtt.organization_id, mmtt.wms_task_type, rules.type_hdr_id
      from   wms_rules_b rules, wms_op_plans_b wop, mtl_material_transactions_temp mmtt
      where  rules.type_code = 7
      and    rules.enabled_flag = 'Y'
      and    rules.type_hdr_id = wop.operation_plan_id
      and    wop.system_task_type = NVL(mmtt.wms_task_type, wop.system_task_type)
      and    mmtt.transaction_source_type_id IN (2, 8) --restrict to sales order and internal order mmtts only
      and    mmtt.transaction_temp_id = p_task_id
      and    rules.organization_id IN (mmtt.organization_id, -1)
      and    wop.enabled_flag = 'Y'
      and    wop.activity_type_id = 2 -- (Outbound)
      order by rules.rule_weight desc, rules.creation_date;

Line: 211

      select rules.rule_id, mmtt.organization_id, mmtt.wms_task_type, rules.type_hdr_id,
             wop.plan_type_id -- new column added
      from   wms_rules_b rules, wms_op_plans_b wop, mtl_material_transactions_temp mmtt,
             mtl_txn_request_lines mtrl -- new tables added
      where  rules.type_code = 7
      and    rules.enabled_flag = 'Y'
      and    rules.type_hdr_id = wop.operation_plan_id
      and    rules.organization_id in (mmtt.organization_id, -1)
      and    (wop.organization_id = mmtt.organization_id or wop.common_to_all_org = 'Y') -- changed after review
      and    wop.enabled_flag = 'Y'
      and    mmtt.transaction_temp_id = p_task_id
      and    mtrl.line_id = mmtt.move_order_line_id
      and    mtrl.organization_id = mmtt.organization_id
      and    wop.activity_type_id = 1
--
--    @@@    if (mtrl.backorder_delivery_detail_id is not null)
--    @@@    then
--    @@@       (backordered line)
--    @@@    else
--    @@@       
--    @@@       if (mtrl.inspection_flag is 1)
--    @@@       then
--    @@@         (Inspection routing)
--    @@@       elseif (mtrl.inspection_flag in (2, 3, null) or any other values)
--    @@@       then
--    @@@         (Standard Routing assumed)
--    @@@       end if;

Line: 264

      select wop.plan_type_id, wop.operation_plan_id
      from   mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl,
             wms_op_plans_b wop
      where  mmtt.transaction_temp_id = p_task_id
      and    mtrl.line_id = mmtt.move_order_line_id
      and    wop.activity_type_id= 1
--
--    @@@    Changed on October 7th 2003. This decode is supposed to be read as follows:
--    @@@    if (mtrl.backorder_delivery_detail_id is not null)
--    @@@    then
--    @@@       (backordered line)
--    @@@    else
--    @@@       
--    @@@       if (mtrl.inspection_flag is 1)
--    @@@       then
--    @@@         (Inspection routing)
--    @@@       elseif (mtrl.inspection_flag in (2, 3, null) or any other values)
--    @@@       then
--    @@@         (Standard Routing assumed)
--    @@@       end if;

Line: 302

      select Nvl(default_pick_op_plan_id,1)
        from mtl_parameters mp, mtl_material_transactions_temp mmtt
       where mp.organization_id = mmtt.organization_id
       and   mmtt.transaction_temp_id = p_task_id
       AND   mmtt.transaction_source_type_id IN (2, 8); ----restrict to sales order and internal order mmtts only

Line: 313

      SELECT COUNT(object_name)
        FROM user_objects
       WHERE object_name = l_package_name;

Line: 505

	 -- ### Update MMTT.
         if (l_debug = 1) then
            trace('Before calling procedure update_mmtt'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')
                 || 'Progress Indicator ' || l_progress, 1);

Line: 513

	 -- ### Update MMTT.
	 update_mmtt(
	    p_task_id           =>  p_task_id
	 ,  p_operation_plan_id =>  l_operation_plan_id
	 ,  x_return_status     =>  l_ret_status);

Line: 520

            trace(' After calling procedure update_mmtt'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')
               || ' Progress Indicator ' || l_progress, 1);

Line: 625

     update_mmtt(
        p_task_id           =>  p_task_id
     ,  p_operation_plan_id =>  l_operation_plan_id
     ,  x_return_status     =>  l_ret_status
     );

Line: 632

        trace('*** Default Operation Plan ID derived is null and hence cannot update MMTT....***');