APPS.WSH_TRANSACTIONS_TPW_UTIL SQL Statements

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

Line: 444

      SELECT Event_Key,
             Item_Type,
             Internal_Control_Number
      FROM   wsh_transactions_history
      WHERE  Transaction_ID = (SELECT MAX (Transaction_ID)
                               FROM   wsh_transactions_history
                               WHERE  Document_Number = c_Orig_Document_Number
                               AND    Trading_Partner_ID = c_Trading_Partner_ID
                               AND    Document_Direction = 'I'
                               AND    Action_Type = 'A');

Line: 611

      SELECT Entity_Number,
             Trading_Partner_ID,
             Transaction_Status
      FROM   wsh_transactions_history
      WHERE  Transaction_ID = (SELECT MAX (Transaction_ID)
                               FROM   wsh_transactions_history
                               WHERE  Document_Number = c_Orig_Document_Number
                               AND    Trading_Partner_ID = c_Trading_Partner_ID
                               AND    Document_Direction = 'I'
                               AND    Action_Type = 'A');

Line: 624

         SELECT status_code, Delivery_ID
         FROM   wsh_new_deliveries
         WHERE  Organization_id = c_Organization_ID
         AND    Name = c_Delivery_Name;

Line: 632

         SELECT Delivery_Interface_id
         FROM   wsh_new_del_interface
         WHERE  Name = c_Name
         AND    Organization_ID = c_Org_ID;

Line: 641

      wsh_update_history        EXCEPTION;

Line: 733

         WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
                                                                   l_txn_hist_record.transaction_id,
                                                                   l_Return_Status );

Line: 737

            wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);

Line: 740

            RAISE wsh_update_history;

Line: 743

         WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
                                                               l_Return_Status );

Line: 746

          wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);

Line: 784

         WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
                                                               l_Return_Status );

Line: 787

          wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);

Line: 795

         WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
                                                                   l_txn_hist_record.transaction_id,
                                                                   l_Return_Status );

Line: 799

          wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);

Line: 802

            RAISE wsh_update_history;

Line: 864

         WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
                                                                   l_txn_hist_record.transaction_id,
                                                                   l_Return_Status );

Line: 868

         wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);

Line: 871

            RAISE wsh_update_history;

Line: 926

         WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
                                                                   l_txn_hist_record.transaction_id,
                                                                   l_Return_Status );

Line: 931

         wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);

Line: 935

            RAISE wsh_update_history;

Line: 938

         WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
                                                               l_Return_Status );

Line: 942

         wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);

Line: 1089

      WHEN wsh_update_history THEN
         X_return_status := WSH_UTIL_CORE.g_ret_sts_error;

Line: 1117

          WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);

Line: 1118

          WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');

Line: 1187

         WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
                                                                   l_txn_hist_record.transaction_id,
                                                                   l_Return_Status );

Line: 1281

      SELECT wth1.Transaction_ID,
             wth1.Document_Type,
             wth1.Document_Direction,
             wth1.Document_Number,
             wth1.Orig_Document_Number,
             wth1.Entity_Number,
             wth1.Entity_Type,
             wth1.Trading_Partner_ID,
             wth1.Action_Type,
             wth1.Transaction_Status,
             wth1.ECX_Message_ID,
             wth1.Event_Name,
             wth1.Event_Key,
             wth1.Item_Type,
             wth1.Internal_Control_Number,
             wth1.Attribute_Category,
             wth1.Attribute1,
             wth1.Attribute2,
             wth1.Attribute3,
             wth1.Attribute4,
             wth1.Attribute5,
             wth1.Attribute6,
             wth1.Attribute7,
             wth1.Attribute8,
             wth1.Attribute9,
             wth1.Attribute10,
             wth1.Attribute11,
             wth1.Attribute12,
             wth1.Attribute13,
             wth1.Attribute14,
             wth1.Attribute15
      FROM   wsh_transactions_history wth1,
             wsh_transactions_history wth2
      WHERE  wth2.Item_Type = c_Item_Type
      AND    wth2.Event_Key = c_Item_Key
      AND    wth1.Document_Number = wth2.Orig_Document_Number
      AND    wth1.Trading_Partner_ID = wth2.Trading_Partner_ID
      AND    wth1.Document_Direction = 'I'
      AND    wth1.Action_Type = 'A';

Line: 1426

      SELECT wth1.Document_Number,
             wth1.Transaction_Status,
             wth1.Event_Key
      FROM   wsh_transactions_history wth1
      WHERE  Transaction_ID = (SELECT MAX (Transaction_ID)
                               FROM   wsh_transactions_history
                               WHERE  Entity_Number = c_Entity_Number
                               AND    Trading_Partner_ID = c_Org_ID
                               AND    Entity_Type = 'DLVY'
                               AND    Action_Type = 'A'
                               AND    Document_Direction = 'I'
                               AND    Document_Type = 'SR');

Line: 1440

SELECT Name, status_code
FROM   wsh_new_deliveries
WHERE  Organization_id = c_Organization_ID
AND    Delivery_ID = c_Delivery_ID;

Line: 1446

SELECT 'X'
FROM   wsh_delivery_details wdd,
wsh_delivery_assignments_v  wda,
wsh_new_deliveries   wnd
WHERE  wdd.container_flag = 'N'
AND    wdd.source_code = 'WSH'
AND    wdd.Delivery_Detail_ID = wda.Delivery_Detail_ID
AND    wda.Delivery_ID = wnd.Delivery_ID
AND    wnd.Delivery_ID = c_Delivery_ID
AND    wnd.Organization_ID = c_Org_ID
AND    rownum = 1;

Line: 1459

select	transaction_status from wsh_transactions_history
where	transaction_id =(select max(transaction_id) from wsh_transactions_history
where	entity_number= p_entity_number
and	trading_partner_id=p_org_id
and	document_direction = 'O'
and	document_type ='SA'
and	entity_type ='DLVY'
and	action_type = 'A');

Line: 1469

SELECT  'X'
FROM   	wsh_delivery_details wdd,
	wsh_delivery_assignments_v  wda,
        mtl_system_items msi
WHERE  wdd.container_flag = 'N'
AND    wdd.released_status <> 'D'
AND    nvl(wdd.inv_interfaced_flag,'N') <> 'Y'
AND    wdd.Delivery_Detail_ID = wda.Delivery_Detail_ID
AND    msi.inventory_item_id = wdd.inventory_item_id
AND    msi.organization_id     = wdd.organization_id
AND    msi.serial_number_control_code IN (2,5,6)
AND    wda.Delivery_ID = cp_entity_id
AND    rownum = 1;